Good day everyone, sometimes ago i posted the above subject matter as a topic and it was resolved perfectly.
however, i wanted a custom way to handle students of different classes, so that unique id are generated based on the classes.
In the CheckID function i have added this code, but no solution
= (ID as number, List as list, Class as text) =>
let
StartingValue =
if Text.Contains(Class, "JSS1") or Text.Contains(Class, "JSS2") or Text.Contains(Class, "JSS3") then 5
else if Text.Contains(Class, "SS1") or Text.Contains(Class, "SS2") or Text.Contains(Class, "SS3") then 2
else 0,
NewID = if List.Count(List.Select(List, each _ = Int64.From(ID))) > 1 then CheckID(StartingValue + Number.Round(Number.RandomBetween(10000000, 99999999), 0), List, Class) else ID
in
NewID
The excel sheet upload , the expected outcome sheet tab, should be the solution i want.So that when new records are added, the latest records takes precedence while maintaining same id for that StudentID.
The idea is that every student will maintain same id for junior classes ,while senior classes, follow same pattern.
Thank you
Please , who can resolve the above subject matter for me.
I have be looking forward from the public with a view to providing a solution to the challenge above.
A StartingValue of 0 is a bad idea, leading zero's are removed when converting to a number, will not work.
Have you tried to type a number that starts with 0 in excel?
@Catalin Bombea, thank you, your solution provided works, just that there is a challenge to sort the date in descending order, so that a student name will take the latest record for a given Name and Year but retain the same GUID.
Example: from the excel file, in the accreditation sheet tab, a student like Morgan whose record appears twice, maintains the same GUID across different years and classes while taking the latest record.How do i achieve that?
Thank you.
Have you tried to sort and it does not work?
Yes,i have tried it,but no solution.
I sorted the session in descending order ,with table.buffer and remove duplicates if any in the GUID column,yet am getting error
Provide the error description you get and a test file that replicates the error please.
@Catalin Bombea, i tried, sorting the column YEAR in DESCENDING ORDER AND WRA
ING IT WITH Table.Bufer(),However, what i get is the Guid id changing, instead of maintaining same GUID id number, and the latest year or session.
Etc, from the excel file, transformed table, "Table_StudentIDListV2" The name FENI appearing twice is wrong, instead i want it to display just a single name FENI with the latest year while maintaining a constant GUID number and not a changing GUID numbers
Thank you
NB: please use the last attachement file:
I intend to dynamically fetch Excel tables from a folder in real-time, append them together, and generate a unique identifier. This unique identifier should incorporate the latest class and year/session information while maintaining the same unique GUID for each student. For students in JSS classes (JSS1 to JSS3), the unique ID will have a prefix of '5', while for SS classes (SS1 to SS3), the prefix will be '2'. This ID structure remains consistent regardless of changes in class or year/session.So if a student moves from JSS1 TO JSS2, for instance, what i want is the current session year to display while retaining the UNIQUE ID number, ID, will change , only if he or she crosses to the senior classes.
I have made so many attempts, but all id are recalculating? I need help.
This is the sample zip folder :
This is the final transformation file:
Thank you
Thank you all,i was able to use Appscript to accomplish this task..
