Send an email to users in the same enumlist category

[greenecr17]

I have an app that allows specific users in departments to approve/deny requests via email. What I am trying to do is send an email to everyone in the same department when a new record is created. I have a table for the list of users who will be receiving emails, and a table for record line items. The department field is an Enumlist, and I'm not sure how I would write the expression for who the emails will get sent to. Any advice is appreciated, thanks.

[lynchk21 · Accepted solution]

If both your 'record line items' table and your 'list of users' table have a department field, then you can use a SELECT expression to get the appropriate department emails based on the record line item.

SELECT(list of users[email], [department]=[_thisRow].[department])

[lynchk21 · Accepted solution]

Glad it worked.

The SELECT() expression has the following syntax:

• SELECT(dataset-column, select-row?, [distinct-only?])
  • dataset-column = Table Name[Column]
  • select-row? = Condition to specify which rows from the dataset-column to return

In your condition, Select(Emails[Email], [Department]=[_thisRow].[Department]), it is saying:

• Select all [Email] columns from the Emails table in which the [Department] column is equal to the [Department] column in [_thisRow] of the table on the 'table for record line items' (i.e. the new record that triggered the action)

I hope that makes sense!

[greenecr17]

I might be going about this wrong with two tables, if anyone has any insight I'm open to all suggestions! I'm very new to appsheet.

[lynchk21 · Accepted solution]

If both your 'record line items' table and your 'list of users' table have a department field, then you can use a SELECT expression to get the appropriate department emails based on the record line item.

SELECT(list of users[email], [department]=[_thisRow].[department])

[greenecr17]

This worked and you just saved me a huge headache! Thank you so much.

[greenecr17]

After I've messed with this more, I've discovered that whenever I try to have more than one user in the 'list of users' table, I get an error saying that "To", "CC", and "BCC" are all empty. However when I only have one user in the 'list of users' table it works normally.

This is how I have my function written: 

select(Emails[Email], Emails[Department]=[Department])

with 'Emails' being the list of users table. I'm not exactly sure where to go from here.

[lynchk21]

Do you have the same issue if you use [_thisRow].[Department]?

select(Emails[Email], Emails[Department]=[_thisRow].[Department])

[greenecr17]

Yes, at first I thought you were using [_thisRow]. as an example, but even with it added I get the same result.

[lynchk21]

Are you using a bot, or running an action when the form is submitted? And when you put in the epxression select(Emails[Email], Emails[Department]=[_thisRow].[Department]) and go to Test, does it select the data you are expecting?

[greenecr17]

I am using a bot. When I select test the result comes up as a -.

[lynchk21]

In your bot, are you putting the expression in the 'Filter Condition' or are you using the expression in the 'To' field?

[greenecr17]

The expression is in the 'To Email address' field

[lynchk21]

Hmm.. looking at your expression, it appears maybe the extra Emails table reference might be to blame. 

select(Emails[Email], Emails[Department]=[_thisRow].[Department])

try removing it and seeing what happens:

select(Emails[Email], [Department]=[_thisRow].[Department])

[greenecr17]

It looks like this did the trick! Thanks! However, I don't understand how the department email and the Emails[Email] are communicating with each other. Do you mind explaining that?

[lynchk21 · Accepted solution]

Glad it worked.

The SELECT() expression has the following syntax:

• SELECT(dataset-column, select-row?, [distinct-only?])
  • dataset-column = Table Name[Column]
  • select-row? = Condition to specify which rows from the dataset-column to return

In your condition, Select(Emails[Email], [Department]=[_thisRow].[Department]), it is saying:

• Select all [Email] columns from the Emails table in which the [Department] column is equal to the [Department] column in [_thisRow] of the table on the 'table for record line items' (i.e. the new record that triggered the action)

I hope that makes sense!

[greenecr17]

Wow, thanks a lot! This is super clear. I appreciate the help you've given me.

[lynchk21]

Glad I could help!

[greenecr17]

Hello, would it be possible to send emails to 2 users at the same time? Everything works fine when there is only one email, however when there are two people with the same department role the bot errors out.

[lynchk21]

Yeah it is possible. What is the error you are getting? 

[greenecr17]

This is the error: Error: No workflow email was sent because 'To', 'CC', 'BCC' are all empty.

I'm guessing it's due to the function not knowing how to handle two different emails, or inputting two different columns in the "To:" field.

[lynchk21]

Interesting - Is your PROCESS acting on the EMAILS table or the 'table for record line items'

[greenecr17]

I'm not 100% sure, however if I had to guess it would be on the 'record line item' table. In the bots monitoring page the output data that first gets referenced is the record line items, and the EMAILS table isn't referenced anywhere in the process. I don't see it explicitly stated anywhere so that is what I would imagine it's using.

[lynchk21]

 You should be able to find it in your bot here:

Try updating it to the EMAILS table and see if that works

[greenecr17]

Hm that didnt seem to work, however I discovered another issue that I think is the root cause of the problem. I have some users in the EMAILS table that have more than one department assigned to them, and the 'Send an email' task doesn't work for those users.

[lynchk21]

In that case you can try: 

select(Emails[Email], CONTAINS([_thisRow].[Department],Emails[Department]))

[lynchk21]

Sorry should be:

select(Emails[Email], CONTAINS(Emails[Department],[_thisRow].[Department]))

[greenecr17]

Thank you for the suggestion, using the contains function inside the select function is a great idea. Unfortunately when I tried to implement this, the function no longer worked properly and would fail to input an email.

This is the logic that appsheet gives me, which sounds correct so I

The list of values of column 'Email'
....from rows of table 'Emails'
....where this condition is true: ((The value of 'Department' from the row referenced by 'Row ID') contains the text value (Emails[Department]))

[greenecr17]

Please disregard this! The appsheet forum didn't load your second response initially. After trying the new fix you sent it seemed like that was able to send out emails to people who previously weren't working. However, it appears to be sending emails to everybody in the Emails[Email] row, so I will tweak the function to try to fix that.

[greenecr17 · Accepted solution]

I ended up getting it working using this function: SELECT(Emails[Email], IN([_thisRow].[Department], [Department]))

[lynchk21]

Nice!