How should I do with the formula to get the desired result?

AppSheet AppSheet Q&A
1

Hello.

3X_6_3_6327aa8def136b709fa3c44975b73f3f976e07e7.png
3X_6_3_6327aa8def136b709fa3c44975b73f3f976e07e7.png1156×202 19.6 KB

When creating Form, select FormName MLB News.
The category of MLB News is Baseball and MLB.
In the Member Table, John and Nick are the people whose Interests are Baseball or MLB.
I’d like to bring John and Nick’s email list, how can I do the formula?
I tried this, but I couldn’t get the result I wanted.

select(Member[Email], in(select(Form[Category], [FormName] = “MLB News”), split([Interests], " , ")))

Please help me.
Thank you.

2

What types of columns are [Category] and [Interests]? EnumLists, or…?

KangJinil:

select(Member[Email], in(select(Form[Category], [FormName] = “MLB News”), split([Interests], " , ")))

Your expression will take a form more like this:

SELECT(
  Member[Email] ,
  #some-sort-of-comparison-between# [Interests] #and# [_THISROW].[Category]
)
1 Like
3

Hello, Marc_Dillon.
[Category] and [Interests] columns are EnumLists.
I don’t understand the #sort-sort-of-comparison-between# part well.
Please explain.

4

Sorry, typo. Was supposed to say “some sort…”.

It is good that both are EnumLists, so you won’t need to use SPLIT(). The comparison basically just needs to be “is there any interest in common”, correct?

If so, how about we just use ISNOTBLANK() with INTERSECT()

Try this:

SELECT(
  Member[Email] ,
  ISNOTBLANK( INTERSECT( [Interests] , [_THISROW].[Category] ) )
)
3 Likes
5

Wow!
It’s been solved in a cool way to go.

SELECT(
Member[Email],
ISNOTBLANK(INTERSECT([Interests], SELECT(Form[Category], [ID] = [_THISROW].[Form])))
)

Thank you so much, Marc_Dillon.

2 Likes
6

Why are you using:

KangJinil:

SELECT(Form[Category], [ID] = [_THISROW].[Form])

instead of just:

Marc_Dillon:

[_THISROW].[Category]

?

1 Like
7

The Interests column is in the Member Table, and the Category column is in the Form Table.
"[_THISROW].[Category] "Unable find to column “Category” error occurs if you enter only this.
And when I looked again, the Category Column attribute was Enum, so when I changed it to Enum List, there was an error and I corrected it as follows.

SELECT(
Member[Email],
ISNOTBLANK(INTERSECT([Interests], SPLIT(SELECT(Form[Category], [ID] = [_THISROW].[Form]), ","))
)