Sending a single email for multiple records that fit conditions

[ideixel]

We have a variety of projects and ask teams to log their cumulative monthly hours worked for each project. We have an "Hours" table where each row is associated with a project, a month, and a year. Then the team goes into the table to enter # of hours worked for each project. I have the table grouped by month. 

For example, the grouped field for January 2023 group has a list of 8 projects:

Team	Month	Year	Project	# Hours Worked
Operations	January	2023	Project A
Operations	January	2023	Project B
Operations	January	2023	Project C
Operations	January	2023	Project D
Operations	January	2023	Project E
Operations	January	2023	Project F
Operations	January	2023	Project G

Teams are asked to enter their hours by the first Monday of each month. 

I want to create an automation that sends an email to teams that haven't entered in their hours yet. However, I do not want it to send a separate email for every single record that is empty - I want it to be one email that says the team has missing hours information for the month. 

Is there a way I can use a formula to search within a grouped field (e.g. January 2023) and send one email if there are any empty values in the Hours Worked field?

Thank you!

[TheTimeSavingCo]

Hm, the simplest way to deal with this would be to have an automation per team, and each automation would have a "Find Records" action that would look for empty records, and have a conditional that would send the team a single email if it found empty records

Alternatively, assuming your Teams field is a linked field to a table where each team is represented by a record, you could create a formula field in your original data table that checks whether the record is empty and outputs "Empty" or something.  In your "Teams" table, you could then add a lookup field for said formula field, and so now you can see all the teams that haven't keyed in their hours via the "Teams" table.  You'd then have a single automation that would run every Monday to find all the Teams where said lookup field had the word "Empty" in it, and use a repeating group with a Send Email action to send each team an email

[ideixel]

Hi Adam,

Thank you for this! I think one of these options will work - but I have a few more questions. 

We currently have one base per team, so I can just make an automation on each base. Do you mind going into a little more detail on what this automation should look like? I was thinking:

1. I would add a "due date" field associated with each row.
2. Create an automation triggered "when a record matches these conditions": when "due date" is "today" AND "hours worked" is empty.
3. Send an email to the team that hours are due.

But would this end up sending an individual email for each empty record?

Does this sound right to you? 

Thanks!

[TheTimeSavingCo]

Ah if it's one automation each base then you can definitely go with the first option.

Here's how I'd set it up:

1. Triggers first Monday of each month (or whenever you want it)
2. "Find Records" action to look for records that have a "# Hours Worked" field that is empty
3. A conditional that checks whether any records were found (i.e. length of the Find Records action is > 0)
  - A "Send Email" action if length > 0

And so if the team has keyed in all the hours, the Find Records action would find no records, the length would be 0, and no email would be sent

[ideixel]

This is so helpful, thank you! One last question - 

For step #2, should I also have the find records action search for records with a "due date" of "today"? As you laid it out, I'm concerned it would find records that have empty "# Hours Worked" from future months. So, even if someone had filled in their hours for this current month that is due, the action would find empty records from future months and send this email regardless.

I'm not sure if the trigger being to find records that have a "due date" of "today" would fix this? 

Thank you again!

[TheTimeSavingCo]

Oooh yeah, you definitely want to have that due date field then and it would fix the issue you mentioned.  Apologies, I was thinking that the table would only have the current months records in them