Send notification to email list based on other column with the same value

[romanus]

I am trying to send a new comment notification (Comment4) to users (User1,User2,User3) of the same article title:

Title User   Comment
A     User1  Comment1
A     User2  Comment2
A     User1  Comment3
A     User3  Comment4

In the To (Email list for workflow rule) for single row I write expression LOOKUP([_THISROW].[User], Users, ID, Email), but how to list email based on the other column with same article title?

[Steve]

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[romanus]

Hi @Steve thank you, I read that function guide, but still no luck with my limited knowledge

My User has Article, and Article has Comment, I wrote

SELECT(
  User[Email], 
  User[ID]=(SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))
)

In expression assistant I have below which is IMHO logic:

The list of values of column 'Email'
....from rows of table 'USER'
....where this condition is true: ((USER[ID]) is equal to (The list of values of column 'User'
........from rows of table 'COMMENT'
........where this condition is true: ((The value of column 'Title') is equal to (The value of 'Title' from the row referenced by 'ID'))))

In test it has no result

(SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))

The above expression produce correct list of users commenting same title, then I need to lookup their email. I tried write below expression

LOOKUP(
  (SELECT(Comment[User], 
  ([Title] = [_THISROW].[Title]),TRUE)), 
  Users, ID, 
  Email
)

I Cannot compare Text with List…

[Suvrutt_Gurjar]

romanus:

SELECT(
  User[Email], 
  User[ID]=(SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))
)

You may wish to try

SELECT(
Users[Email],
IN([ID]=(SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))
))

However the above expression is likely to be sync time expensive, because there is a SELECT() within a SELECT() .

May I request @Steve to guide at his convenience on whether splitting such expressions in two different fields, each having one SELECT() will help. Will seperating the two expressions in two fields will reduce internal SELECT() iterations that are likely to happen in case of a SELECT() within a SELECT()?

[romanus]

Thank you @Suvrutt_Gurjar it also produce Cannot compare Text with List

[Suvrutt_Gurjar · Accepted solution]

Suvrutt_Gurjar:

SELECT(
Users [Email],
IN([ID]=(SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))
))

Sorry my bad. There was a typo.

SELECT(
Users [Email],
IN([ID], (SELECT(Comment[User], ([Title] = [_THISROW].[Title]),TRUE))
))

[romanus]

Super! Thank you so much @Suvrutt_Gurjar