1

# How to create a unique list of records

Let us say I have a table, Table1, with records that store years in a field called Number. Now I want to create a dynamic Choice field in Table2 that shows the unique years that are in Table1. Since we know dynamic fields need record level data to function we have to find a way to find all records but eliminate the records that are duplicate except for one.

Here is my one solution. I have attached a DB, that breaks down each step, and will post the full formula below.

``````#{
Replace: Table1 and Number to match your table and field names
Step 1: gather all the data from the table you need
}#;
let step1 := (select Table1);
#{
Step 2: filter the results in Step 1 to only the records with duplicate data.
step A: starting with the step2A variable that gets the data from the field that is duplicated.
step B: Then filters the step1 variable
}#;
let step2A := for loop1 in step1 do
loop1.Number
end;
let step2B := step1[(var N := Number;
count(step2A[= N])) > 1];
#{
Step 3: filter the step2B results to only show the first instance of each duplicated data.
step A: find the value that is duplicated and then get an unique array of the values
step B: using a for loop, find the first record in each set of records that have duplicate values
}#;
let step3A := unique(step2B.Number);
let step3B := for loop1 in step3A do
first(step2B[Number = loop1])
end;
#{
Step 4: Creating an array of records that should be removed from Step 1 since they are duplicates
}#;
let step4 := step2B[var t := this;
count(step3B[= t]) = 0];
#{
Step 5: take the array in Step 4 and remove it from the array in Step 1 and you are left with a list of records that have a unique value
}#;
step1[var t := this;
count(step4[= t]) = 0]
``````

The main issue I see is that you can't use the soft links that dynamic choice fields allow you to create, but if all you want is a list then this could work.

Also if you don't want the first instance of a duplicate set then I'm not sure what you would do, but I'm sure others will figure something out.

Look forward to seeing what others come up with.

4replies Oldest first
• Oldest first
• Popular
• UweG
• UweG
• 4 days ago
• Reported - view

Wow!!!

Like
• Fred
• Fred
• 4 days ago
• Reported - view

I’ve wanted to do something like this for about a year now.

Like
• Thanks to Fred for inviting us to look into how to create a function equivalent to group in SQL. It's a good exercise and it can be really useful.
My solution looks like this. Does it match your project?

``````var all := (select Table1);
for i in unique(all.Number) do
first(all[Number = i])
end
``````

I do it with your database and I get the same result:

Like 1
• Fred
• Fred
• 3 days ago
• Reported - view

Well Jacques, your code makes a lot of sense and is much easier to use.

Like
Like1 Follow
• 1 Likes
• 3 days agoLast active
• 4Replies
• 82Views
• 3 Following