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.
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.
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])
1 Like
This worked and you just saved me a huge headache! Thank you so much.
1 Like
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.
Do you have the same issue if you use [_thisRow].[Department]?
select(Emails[Email], Emails[Department]=[_thisRow].[Department])
Yes, at first I thought you were using [_thisRow]. as an example, but even with it added I get the same result.
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?
I am using a bot. When I select test the result comes up as a -.
In your bot, are you putting the expression in the ‘Filter Condition’ or are you using the expression in the ‘To’ field?
The expression is in the ‘To Email address’ field
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])
1 Like
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?
1 Like
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!
1 Like
Wow, thanks a lot! This is super clear. I appreciate the help you’ve given me.
Glad I could help!
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.
Yeah it is possible. What is the error you are getting?
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.
Interesting - Is your PROCESS acting on the EMAILS table or the ‘table for record line items’