Normalising existing data

Let me see if I understand your post.

1) you have a table (table1) that has duplicate data in a field (field1)

2) now you want to take the data in the field, strip out the duplicates and create a new table (table2) that has a record for each distinct data (field2)

3) then you want to link a record in table1 to the the record in table2 using the data that is in field1

I know someone else wrote up a nice response for this question, but I can't find it.

Here is one possible solution. The code is in a button in Table2:

let startTable := (select Table2);
let Array1 := unique(startTable.text(Service));
for loop1 in Array1 do
    let newRec := (create Table4);
    newRec.(empty := loop1)
end;
for loop2 in startTable do
    loop2.(Table4 := first((select Table4)[empty = text(loop2.Service)]))
end

Table2 is the start table that has a field called Service. It is a choice field. I want to take all the data that is in this field and create new records in Table4 on distinct data. Then I will link the records in Table2 to the new records in Table4. But first I have to create a new reference field in Table2 to Table4. By default it is called Table4.

In Table2 I have 5 records with the following data selected in Service:

Microsoft, Netflix, Ninox, Microsoft, Dropbox

Line 1 creates a variable that selects all records from Table2.

Line 2 creates a variable that takes all of the data in the Service field and filters out any duplicates with the unique function. I have to use the text function around Service since it is a choice field. If you have a text field already then you can drop the text function.

So I get an array with:

Microsoft, Netflix, Ninox. Dropbox

Lines 3 - 6 is my first loop command. Line 3 creates a loop called loop1 and uses the array in the Array1 variable.

Line 4, creates a new record for each item in the array.

Line 5, will set the field called empty in each new record to be equal to the Service name that is in the loop.

In this example it will create 4 new records.

Lines 7 - 9 is my second loop command to link the records in Table2 with the new records in Table4.

Line 7 starts a loop called loop2 and uses the array in the startTable variable.

Line 8 takes each record in loop2 and sets the new reference field Table4 to be equal to the first record in a select of Table4 where the empty field equals to the text of the data in Service. I have to use the first command as Ninox always assumes a select will return multiple records even if only 1 record is returned.

Once this is all done I can delete the field Service from Table2 as the data is now not needed anymore.

Another way of doing this is to leave out the second loop, and update all records in Table2 using the Update Multiple Records function. I would leave out the loop part and put something like this in the Assigned calculated value for Table4:

let t := this;
first((select Table4)[empty = text(t.Service)])

I hope this helps.