How to create a unique list of records

Fred
2 yrs ago
12replies

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.

- UweG
- 2 yrs ago
- Reported - view
Wow!!!
- Fred
- 2 yrs ago
- Reported - view
I’ve wanted to do something like this for about a year now.
- Ninox developper
- Jacques_TUR
- 2 yrs ago
- Reported - view
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:
- Fred
  
  2 yrs ago
  
  Reported - view
  Well Jacques, your code makes a lot of sense and is much easier to use.
- Leo_Woer
  
  12 days ago
  
  Reported - view
    Hi Jacques
  
  have looked into you script and tried it, however, I get the error that it does not create a record.
  
  I have a table with some member payments where the member I would like to link the payment to does not exist - I such a case I want toe create the record in the membership table, and sometimes there are more than one payment, but I will only create the member once.
  
  Here is my script, which gives the error that it cannot see j.XXX
  
  let xYear := concat('Hvilket år - link og se betalingsliste');
  var all := (select Betalingsliste where 'Periode der er betalt for' = xYear and 'Linket til medlemmer' != true);
  for j in unique(all.Medlemsnr) do
  let i := (create medlemmer);
     i.(Medlemsnr := j.Medlemsnr);
     i.(Navn := j.Navn)
  end
  
  Can you see what is wrong with this? If I don't use unique it works but then I get records created for each payment even if it is same member number. the following script works with the exception that I create duplicate members.
  
  let xYear := concat('Hvilket år - link og se betalingsliste');
  for j in (select Betalingsliste where 'Periode der er betalt for' = xYear and 'Linket til medlemmer' != true); do
  let i := (create medlemmer);
     i.(Medlemsnr := j.Medlemsnr);
     i.(Navn := j.Navn)
  end
  
  Thanks a lot for your help
  
  rgds
  
  Leo
- Leo_Woer
  
  12 days ago
  
  Reported - view
  Hi again Jacques
  
  I solved it with the following script :
  
  let xYear := concat('Hvilket år - link og se betalingsliste');
  var Xarray := unique((select Betalingsliste where 'Periode der er betalt for' = 2025 and 'Linket til medlemmer' != true).Medlemsnr);
  for j in Xarray do
     let Xmedlem := j;
     let i := (create medlemmer);
     let Xnavn := first((select betalingsliste where Medlemsnr=Xmedlem).Navn);
     i.(Medlemsnr := Xmedlem);
     i.(Navn := Xnavn)
  end
  
  Thanks for your time
  
  rgds
  
  Leo
- Ninox developper
  
  Jacques_TUR
  
  12 days ago
  
  Reported - view
  unique(all.Medlemsnr) returns an array of unique IDs, not an array of unique records. Therefore, in the function for j in unique(all.Medlemsnr) do, j refers to a record ID. You must therefore use the record(Betalingsliste, j)function to retrieve the corresponding record
- Ninox developper
  
  Jacques_TUR
  
  12 days ago
  
  Reported - view
  You can also update your code as follows:
  
  let xYear := concat('Hvilket år - link og se betalingsliste');
  var all := (select Betalingsliste where 'Periode der er betalt for' = xYear and 'Linket til medlemmer' != true);
  for j in unique(all.Medlemsnr) do
     let rec := first(all[all.Medlemsnr=j]);
  let i := (create medlemmer);
     i.(Medlemsnr := rec.Medlemsnr);
     i.(Navn := rec.Navn)
  end
- spinner_7580
  
  8 days ago
  
  Reported - view
  Yes, I use this format and it is super useful when creating a UI for non-developer users.
  
  I didn't "invent" the code however, I found an example of it a year or so ago on this forum. I don't recall if the original post was you or not but Chapeau!
- Leo_Woer
- 12 days ago
- Reported - view
Thank you Jacques

I appreciate your time.

rgds

Leo
- Fred
- 9 days ago
- Reported - view
We should update this to take advantage of the fact that you can now use JSON in dynamic choice fields.

var all := (select Table1 order by Number); for i in unique(all.Number) do { id: index(all,i)+1, caption: i } end

The problem is that you can't use text() (as of v3.15.4 in public cloud) when trying to retrieve the value in a JSON single choice field. You can use text() with multi choice fields.

A bug that was reported in 3.14 (or when JSON dynamic fields first came out).
- Leo_Woer
  
  9 days ago
  
  Reported - view
  Thank you
  
  I will look into it.
  
  rgds
  
  Leo

How to create a unique list of records

12 replies

Content aside

Categories

Overview