Enum list result depend on the previous Enum list

AppSheet AppSheet Q&A
,
1

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.

2

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.

1 Like
3

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?

4

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().

1 Like
5

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.

6

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.

4 Likes
7

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

1 Like
8

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 ) )

9

Thanks again bro, i will try it later

10

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?

11

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.

12

@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?.

1 Like
13

What do you mean by “table links”?

14

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

3X_6_e_6e04bd8de89cd74ce82fde93db95c8f9d89d2853.png
3X_6_e_6e04bd8de89cd74ce82fde93db95c8f9d89d2853.png443×293 13.7 KB

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.

1 Like
15

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.

1 Like
16

@Steve Thanks for your feedback and support.

1 Like
17

@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]))

3X_8_f_8fb09a39bf20840f178417bb437058fe2e107966.png
3X_8_f_8fb09a39bf20840f178417bb437058fe2e107966.png1325×499 50.4 KB

18

What is the goal of this new expression?

1 Like
19

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] ) )

20

Did you make any changes since that expression was working?

1 Like