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

[KangJinil]

Hello.

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.

[Marc_Dillon]

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

[KangJinil]

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

[Marc_Dillon · Accepted solution]

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

help.appsheet.com

ISNOTBLANK()

Is value present?

help.appsheet.com

INTERSECT()

Items common to two lists

Try this:

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

[KangJinil]

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.

[Marc_Dillon]

Why are you using:

KangJinil:

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

instead of just:

Marc_Dillon:

[_THISROW].[Category]

?

[KangJinil]

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