Unique / Concat giving different results
Hi all.
I have a table showing Orders, in which I have Items that are linked to Products, which each have their own Product Categories (in a multiple choice field). I'm trying to get a list of all the Product Categories associated with an order, without duplicates (as some orders for example, have 2 items with "Round" as a category, but I really only need to see it once).
I started with
concat(ITEMS.PRODUCTS.'PRODUCT CATEGORIES')
Copy
just to be sure I was pulling from the right location, and it seems to be working fine. The only issue is the duplicates.
So I tried the following:
unique(ITEMS.PRODUCTS.'PRODUCT CATEGORIES')
and it's suddenly resulting in completely different Product Categories that are entirely unrelated to the items/products? Further to that, the column disappears entirely if I leave the view and come back.
Any idea why this is happening? And any thoughts as to how I could go about getting the result that I want in a different way, if necessary?
Thanks all.
8 replies
-
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.
Content aside
- Status Answered
- 1 yr agoLast active
- 8Replies
- 163Views
-
2
Following