Looking for a bit of help with the LIST and Substitute functions (I think).

Grocery Store mapping application.  An Item is in multiple locations in a single store.

Item: ABCD is in locations 1A, 5B, 12A and at all the Checkouts (in this example, 5 checkouts)

The table ITEM is the parent and the child table is SKU (which is all occurrences of an ITEM in a STORE)

Using List, I get this for Item ABCD

1A

5B

12A

Checkout

Checkout

Checkout

Checkout

Checkout

Nesting the list in Substitute to change the RETURNS to COMMAS and a trailing space and get this:

1A, 5B, 12A, Checkout, Checkout, Checkout, Checkout, Checkout

That is exactly what I want except I want to only return "Checkout" once no matter how many times the item is at the checkout in a store.  Checkout may appear zero or 1 times (so what I have works) or 2, 3, 4 or more times.  I need to remove those additional occurrences.  It must be done in the function (or in a script) but cannot be done in the child records.

I'm sure someone smarter than me can easily see how to do this!  Any suggestions would be appreciated!  Thanks in advance!

Edited October 30, 20205 yr by vanderark1

You could use the UniqueValues() function:
https://help.claris.com/en/pro-help/content/uniquevalues.html

Even better: if you have a table of Locations, get the list from there.