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!