IS AN ITEM OF ENUMLIST IN ANOTHER ENUMLIST?

AppSheet AppSheet Q&A
1

I have to limit the choice of an ACTIVITY [EMPLOYEE] column with a valid if condition.

In the EMPLOYEE table there is a column [COMPANY_ID] (enum list of type ref to COMPANY table)

I would like to see only the EMPLOYEE [ID_EMPLOYEE], of which at least one item in [COMPANY_ID] is contained in the enumlist [_THISROW]. [COMPANY_ID] (table ACTIVITY).

example

JACK (employee)
[COMPANY_ID] = A, C, E

MATT (employee)
[COMPANY_ID] = A, D, F

ACTIVITY [COMPANY_ID] (THIS ROW) = C, G, R

result: only JACK appears because he works for company C which is contained in the ACTIVITY [COMPANY_ID] enumlist of this row that is being compiled

I don’t know if I have explained the example clearly

2

Have you evaluated the INTERSECT() function in Appsheet?

2 Likes
3

I tried this but it doesn’t seem to work even though the expression is correct

SELECT(
EMPLOYEE[ID_EMPLOYEE],
IN(INTERSECT(
[COMPANY_ID],
[_THISROW].[COMPANY_ID]),

[_THISROW].[COMPANY_ID])

)

4

Maybe this?

SELECT(
EMPLOYEE[ID_EMPLOYEE],
ISNOTBLANK(INTERSECT( [COMPANY_ID], [_THISROW].[COMPANY_ID]))

)

Edit: When using same column in multiple tables, it may be a better idea to name it slightly differently so that debugging could be a bit easier. For example [COMPANY_ID_E] and [COMPANY_ID_A]

2 Likes