Need Help trying to SELECT a list of Refs for another table

[Elijah_Magrane]

Good Day,

I have three tables Products, Order Details, and Customers.

On my Customer table I have a Enumlist Column with base type of Ref that links to the products table. The idea is that each customer has specific SKUs from the products table. This column has Products[Id]in the valid if constraint so as to populate the list.

Next I have an Order Details table where the user will select SKUs and quantity for a customer. I should be able to select the appropriate customer SKUs with this expression Select(Customers[SKUs],[Name]=[_THISROW].[Customer]), however I just get an enumerated raw list (see below)

I have tried a lot of troubleshooting with base types and ref. For example, changing the Order Details column from Ref to Enum (with and without base type ref). I am not sure if this is a limitation of the Schema of my products table but it feels like it should possible

p.s. I hope everyone is staying safe and well

[Kirk_Masden]

Elijah_Magrane:

Select(Customers[SKUs],[Name]=[_THISROW].[Customer])

Is this SELECT() expression in the “Valid if” part of your Enumlist? If not, try putting it there.

Oh, and it will say “This entry is invalid” until one of the choices is selected.

[Elijah_Magrane]

Yes the expression is in the “valid if” constraint however, the base type was not an “enumlist” type but rather a enum. IIf I change it it to enumlist I get several error messages stating that

I cannot compare Text with a list

Do I have to change the column to enumlist. Why wouldn’t it work with just enum

[Kirk_Masden]

I don’t know if this will help but here are my settings for an enum that is working well for me:

[WillowMobileSys]

It sounds like you have a Long Text column for the SKU’s in your Customer table- saved as a comma separated list of items?

From a data design perspective, this will be difficult to use and is difficult to maintain as you will most likely need to adjust the SKU’s list frequently for each Customer going forward.

I would recommend creating a separate table for the Customer SKUs list. And SKU’s are just a unique identifier for the Product so I would think of this as a Customer Products list…so I would name the table “Customer Products”.

Then in your Product Dropdown you can adjust your SELECT() to go against the Customer Product’s table instead.

E.g. `Select(Customer Products[ID],[Customer]=[_THISROW].[Customer])

Lastly, to remove the yellow caution symbol for a Ref column, you MUST use the Key/ID column in the SELECT(). Then turn on the “Label” option for the column you actually want displayed. AppSheet will store the Key value in the data but will automatically retrieve the Label column for display purposes.

[Elijah_Magrane]

Thank-you John. I was wondering If I could sneak it into the Main Customer Table but I think your right in that creating a separate Customer Product table would be the most efficient. Thank-you

[Steve]

Enum (or just Ref) allows the user to choose only one; EnumList allows the user to choose more than one. If you only want the user to choose one product, use Ref. If you want the user to be able to choose more than one product, use EnumList with a base type of Ref.

[Elijah_Magrane]

Hi Steve,

I did use a ref type but when I use the “valid if expression” Select(Customers[SKUs],[Name]=[_THISROW].[Customer])

I get

Like @WillowMobileSystems pointed out I am I have a Long Text column for the SKU’s in my Customer table- saved as a comma separated list of items.

[WillowMobileSys · Accepted solution]

You would need to split the returned comma-separated list like this:

SPLIT(
      Select(Customers[SKUs],[Name]=[_THISROW].[Customer]),
      ","
)

To get rid of the yellow caution symbol, the values in the resulting list MUST be the key value of what ever base table you have set.

[Elijah_Magrane]

WillowMobileSystems:

SPLIT( Select(Customers[SKUs],[Name]=[_THISROW].[Customer]), “,” )

That did the trick. Thank-you so much. I am still considering a separate Customer Products table but I feel better knowing that I can solve the current issue

[Kirk_Masden]

Good work! I didn’t expect the split to be necessary, though, because I thought a SELECT() would automatically produce a list.

[WillowMobileSys]

Actually, in this case the SELECT() is returning a list of a single item. That item happens to be a Long Text column housing a comma separated “set” of SKU’s. SPLIT() is creating a List out of that “set”.

This does make me wonder what would happen if there were several SKU “sets” returned in the SELECT()??

I imagine the result would be just one longer list of SKU’s…including any dups.

[Steve]

WillowMobileSystems:

Actually, in this case the SELECT() is returning a list of a single item. That item happens to be a Long Text column housing a comma separated “set” of SKU’s.

This is exactly what happens when SELECT() returns results from a List or EnumList column. And likewise, you have to SPLIT() the results to get a proper list of the component elements.

[WillowMobileSys]

@Steve Not sure if you’re agreeing with me or if I have said something inaccurate?

[Steve]

Neither, just observing that whats being done here with a LongText column containing a list is the same process commonly used to process list types. Just noting the similarity.