Unique / Concat giving different results

My guess the column is disappearing is because when you use unique() it keeps the data in an array and Ninox can't show data that is in an array in a table column.

That is why concat works, since concat converts an array into a string.

You can try:

concat(unique(ITEMS.PRODUCTS.'PRODUCT CATEGORIES'))

And this should get the data to show up.

I'm guessing 'PRODUCT CATEGORIES' is a simple choice field, but if it is a reference field then you should add the fieldname of the data you want to show.

Jordine De Guzman said:
Could it be that because it's a dynamic Multiple Choice, it's reading a product with "WALNUT" and "LEGS" as one unit, and so it sees a product "WALNUT" and "RACETRACK" as another unique unit? Rather than seeing all 4 as separate?

 You are correct. It is reading each dynamic multi choice individually in each record NOT joining all the record choices and then doing a unique.

Since you have many product records that could have many choices in the dynamic multi choice (dMC) field you need to use the for loop command.

Something like:

let x := for loop1 in ITEMS.PRODUCTS do
        for loop2 in numbers(loop1.'PRODUCT CATEGORIES) do
            record(dMCTable,loop2).fieldname
        end
    end;
let b := split(concat(x), ",");
concat(unique(b))

Line 1: starts the first for loop where we cycle through each Product linked to the Items table.

Line 2: then starts the second for loop that cycles through all of the choices made in the ‘PRODUCT CATEGORIES’ dMC field. 

Line 3: we use the record() command to go back to the table that is referenced in the dMC and we pull the data from the appropriate fieldname. You need to add the appropriate table name and field name.

Line 6: we take the results of x and convert it into an array. I tried without the concat() but Ninox gave me an error.

Line 7: now we can use the unique() command to find only unique values in the array we created in line 6, then we add the concat() command so we can see the values.