Enum list result depend on the previous Enum list

[Hossam_Khalil]

Hi all,

Can someone please support me if i want to make Enum list where it’s results are dependent on a previous Enum list, i have tried many things but it won’t work with me.

for example, if we have products where each product has many suppliers and the user can select multiple products in his request and also can select the suppliers, so i have enum list for products and when the user select his products, i need the second enum list which is for suppliers to be dependent on the products which were selected by the user.

when i try below expression in Data Validation the supplier enum list disappears and i don’t understand why?.

FILTER(Supplier, List([Product]) = Supplier[Product])

I would really appreciate your support or advice regarding above scenario.

[Marc_Dillon]

How does the data look like in your Supplier table? Does each record connect 1 Supplier with 1 Product?

Keep in mind that the condition statement in a FILTER() expression is going to be applied to every row in the table, and in context of that row.

[Hossam_Khalil]

Thanks for your feedback @Marc_Dillon, the data in supplier table each supplier has many products and the product has many suppliers.

for filter i got your point, so you mean i should try select?

[Marc_Dillon]

So how are you wanting Suppliers to be selected then; if they have even a single one of the selected products, or only if they have all of the selected products?

Conditionals in SELECT() statements will work exactly the same as in FILTER().

[Hossam_Khalil]

based on user selection in products, for example if product 1 has supplier 1 & 2 and product 2 has supplier 1.

the user selects products 1 & 2 so the valid choice in supplier is only supplier 1.

[Marc_Dillon · Accepted solution]

I think this will work:

FILTER( Supplier, COUNT( INTERSECT( [Products] , [_THISROW].[Products] ) ) = COUNT( [_THISROW].[Products] ) )

In other words, return the supplier if the number of common items between the selected list of products, and a suppliers list of products (count of intersect), is the same as the count of selected products.

help.appsheet.com

INTERSECT()

Items common to two lists

[Hossam_Khalil]

@Marc_Dillon bro thank you so much for your support and quick feedback, it worked perfectly

[Marc_Dillon]

Great!

You also might have been able to wrap each list in an ORDERBY() and directly compare them. I’m not positive if that would have worked though.

FILTER( Supplier , ORDERBY( [Products] , xx ) = ORDERBY( [_THISROW].[Products] , xx ) )

[Hossam_Khalil]

Thanks again bro, i will try it later

[Hossam_Khalil]

Now i’m facing another issue, the input is Enum list so the values are stored in the worksheet in one cell separated with commas, and this breaks all links in the table.

How to fix this issue?

[Steve]

Hossam_Khalil:

the input is Enum list so the values are stored in the worksheet in one cell separated with commas

That how EnumList works.

[Hossam_Khalil]

@Steve yeah i got it now , the question didn’t sound right .

but if i want to take multiple inputs from user in same request specially in product, supplier scenario, is there a way to do it without breaking table links?.

[Steve]

What do you mean by “table links”?

[Hossam_Khalil]

I mean the columns that create links between tables for example as shown below

if i make product request form view and enabled the user to multi select product and suppliers, now the table columns will be updated with the values separated by “,” when this happens it creates one row in the table and break the links happening between other tabels.

[Steve]

Ah, yes. If you want only one product and one supplier, you’ll have to either process the choices collected in the EnumList with actions to create the desired rows, or you’ll have to choose a different product and supplier input model, such as adding rows to a child table.

[Hossam_Khalil]

@Steve Thanks for your feedback and support.

[Hossam_Khalil]

@Steve @Marc_Dillon , I’m sorry for this but i’m new in the community and a new user for appsheet and i’m trying to figure out things, i would really appreciate your support and guidance.

i don’t understand why below expression is not working i tested it and it returned all suppliers not the one that match the product.

Expression:

Filter(Supplier,Intersect([_thisrow].[products],[products]) = Intersect([_thisrow].[products],Supplier[product]))

[Marc_Dillon]

What is the goal of this new expression?

[Hossam_Khalil]

i’m trying to list only the suppliers that have the product.

this expression stopped working too

Marc_Dillon:

FILTER( Supplier, COUNT( INTERSECT( [Products] , [_THISROW].[Products] ) ) = COUNT( [_THISROW].[Products] ) )

[Marc_Dillon]

Did you make any changes since that expression was working?

[Hossam_Khalil]

yes, i made alot of changes to my app but didn’t touch the expression or the columns

[Marc_Dillon]

Break the expression apart into smaller pieces and test each piece, ensure it is returning what you expect it to return.

[Hossam_Khalil]

yes this is what i did here Post

but it should return the suppliers for that product but what’s happening is that it returns all suppliers.

[Hossam_Khalil]

@Marc_Dillon thanks alot for your support, i figured it out.

[Dana]

Hi, I have same problem with you but I don’t understand the solution. Could you show me how data about supplier and products express. Thank you!