I am struggling on creating a unique list for...

Aaron_Moolb · 12-27-2018 04:00 PM

I am struggling on creating a unique list for my workflow.

My table is a list of products ordered with a product image column. I use the same image for multiple products that have different sizes.

I am including full resolution photos of the products in my email with this expression:

SELECT(Vendor PO Products[Product Image],[Vendor PO ID]=[_THISROW])

My problem is that this expression is including duplicate images. I want only 1 of each product photo. How do I exclude duplicates from my Product Image column?

MultiTech

at the end of your SELECT() formula, add a third argument: true.

This limits the select to unique values, so duplicates only show up as one.

https://help.appsheet.com/expressions/functions/select

MultiTech

cl.ly - Screen Recording 2018-12-28 at 06.08.47.34 PM.gif Screen Recording 2018-12-28 at 06.08.47.34 PM.gif cl.ly

RezaRaoofi

That means each image can be in more than one row of Vendor PO Products table; I believe there are 2 options to work this out:

Have the same image file name when more than one row are having the same image 2) Have a separate table to store product image so the product rows could have a Ref column to the image table, then the rows that shared the same image will be referring to the same row in the image table.

Then you should be able to use an expression like this in order to have a list of distinct images: SELECT(Vendor PO Products[Product Image],[Vendor PO ID]=[_THISROW],true)

MultiTech

@RezaRaoofi I like #2 better, parent-child structure is always the way to go.

If you set that hypothetical table up with more than just an image, you could set your column up as a REF, then you could de-ref the image instead of using a SELECT.

RezaRaoofi

@MultiTech_Visions exactly!

I was waiting for Aaron’s response to expand on #2 further, if needed.

Aaron_Moolb

@MultiTech_Visions adding the TRUE as a 3rd argument worked. And I do have a table with just images that are Ref so I could bring in the ID… Thanks guys! Sometimes I am so deep in the expressions that I forget the simple basics so thank you for reminding me.

MultiTech

Do you dream in AppSheet? @Aaron_Moolb lol

I’ve been there. (^_^)

So deep in the rabbit hole of formulas, lol.

micahj

I’ve tried to accomplish this using some of the advertised functions like UNIQUE() and SELECT(), but they don’t seem to work as expected…

In one case I ran into an error with this simple expression
SELECT(Main Database[Main Category], TRUE, TRUE)

Goal being that I get a unique list of main categories.

The error that came out was:
"The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No"

My alternate route that I tried to accomplish this was by creating a separate data table of unique category values, but it was difficult to link my unique list within a view to my main database through view linking and such.

This is an extremely important item that many of us will try to accomplish… hopefully somebody will break the code. I’ll post in here if I figure out how to get around this barrier.

Steve

Where are you trying to use your expression?

micahj

Here:
Data > Slices > Row Filter Condition > Expression Assistant

Steve

Try this instead:

(
  [_THISROW]
  = MINROW(
    "Main Database",
    "_ROWNUMBER",
    ([Main Category] = [_THISROW].[Main Category])
  )

micahj

It worked!
All I had to do was add another closing parentheses… no biggie.

Thanks a bunch man. This was a hard nut to crack for me initially, glad to have some help.

Any idea why UNIQUE() or SELECT() wouldn’t work in that section for the row filter?

Steve

A slice row filter expression must answer the question, “does this row belong in the slice?” The question is asked separately for each row of the table on which the slice is based. The question expects a Yes or No answer, but your expression produced a list of categories, which just doesn’t make sense. Your expression would be perfectly fine for some other uses, such as for Valid If or Suggested values to produce a dropdown list of choices.