0

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

null
    • Fred
    • 1 yr ago
    • Reported - view

    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
      • 1 yr ago
      • Reported - view

      Fred Hi Fred thanks for the response. Unfortunately the data is still showing up as if "unique" wasn't even there, so I'm still getting duplicates.

      • Fred
      • 1 yr ago
      • Reported - view

      Jordine De Guzman can you upload a test DB? so I can see what is going on. It works for me

      with:

      concat(unique(Vendite.Article.'Article name'))

      I start with a view element that does a simple select to a table, Movimenti. Then I create a column with the above code to get a list of Articles that are linked to the record in Movimenti. This goes through the reference field Vendite.

      Vendite and Article are reference fields and 'Article name' is a text field.

      1:N between Movimenti and Vendite

      1:N between Vendite and Article

      • Jordine_De_Guzman
      • 1 yr ago
      • Reported - view

      Fred Sorry I'm not sure how to do that!

      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?

    • Fred
    • 1 yr ago
    • Reported - view
    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.

      • Jordine_De_Guzman
      • 1 yr ago
      • Reported - view

      Fred I was trying this out but was struggling a bit. But you gave me the idea to go a bit simpler and I've somehow made it work!

      let allCats := concat(ITEMS.PRODUCTS.'PRODUCT CATEGORIES');
      let pcList := split(allCats, ", ");
      unique(pcList)
      

       

      Thanks very much for your help working through this.

      • Fred
      • 1 yr ago
      • Reported - view

      How did you get the text of your dMC to show? I had to add text() around my dMC field.

      I also couldn't get the column to display in a view element or a table view without a concat() around the unique at the end.

      • Jordine_De_Guzman
      • 1 yr ago
      • Reported - view

      Fred Sorry I didn't copy the whole thing. I realized that after I posted it that my column disappeared! This is all of it:

      let allCats := concat(ITEMS.PRODUCTS.'PRODUCT CATEGORIES');
      let pcList := split(allCats, ", ");
      text(unique(pcList))

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 8Replies
  • 163Views
  • 2 Following