Selecting data from conditional Enumlist column

AppSheet AppSheet Q&A
,
1

Table 1

key1 equipment(text)
1 A
2 B
3 C
4 D
5 E
6 F
.
.
.

Table 2

key2 equipments(enumlist)
x A, B, C
y B, C
z A, C

equipments (enumlist) formul: table1[equipment]

Table 3

key3 key2 equipments1(enumlist)
1 x A, C
2 x B
3 x A,B,C
4 x C
5 y B
6 y B, C

What do I want to do is to select the equipments1(enumlist) column in table 3 from the values selected in the equipments column in table 2?

tried equipments1(enumlist) formulas;

SELECT(

Table 1[equipment],CONTAINS([key2].[equipments], [equipment])

)

and

SELECT(

Table 1[equipment],IN([equipment], [key2].[equipments])

)

I tried the formulas but it didn’t work, can you help me?

2

This should work in the Suggested values.. SPLIT(LOOKUP([_THISROW].[key2],Table2,Key2,Equipments1)," , ")

3

Thank you for the answer;
when I do it this way

[Key2].[equipments]

All values are listed, so they cannot be selected;

A B C

what i want

A
B
C
in the form of list

4

But did you try with the formula I gave in the Equipments1 column and Table3?

5

Yes, I tried but it didn’t work;

I am using “&” as a separator in Table 2. Is this a problem?

6

Yeap. Then it needs to be SPLIT(LOOKUP([_THISROW].[key2],Table2,Key2,Equipments1),“&”)

1 Like
7

I tried but unfortunately it’s still the same, all the values in Table 2 are listed as a whole.

8

It seems the editor doesn’t like the “&” as a separator. Change it to space-comma-space and then use SPLIT(CONCATENATE(LOOKUP([_THISROW].[key2],Table2,Key2,Equipments1))," , ")

3 Likes
9

I did as you said, but it still didn’t change.

10

You need to open all your records in Table2 so they would have the correct separator first. Then it sould work.

11

There are approximately 1800 rows of records in Table 2. Do I need to change the “&” separator in all of them?

12

After changing all the separators to “,” the problem was solved, thank you.

13

You’re welcome!

1 Like