Skip to main content

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:

TeamMonthYearProject# Hours Worked
OperationsJanuary2023Project A 
OperationsJanuary2023Project B 
OperationsJanuary2023Project C 
OperationsJanuary2023Project D 
OperationsJanuary2023Project E 
OperationsJanuary2023Project F 
OperationsJanuary2023Project 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!

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

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


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!

 

 

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!

 

 


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

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


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!

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!


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

Apologies if it’s poor form to bump this but I’m looking for a solution that is almost identical so I’m hoping it’s more helpful to add to this rather than make a new thread. 

I’m essentially in the same position however I currently have a single base, with multiple email contacts, the potential for multiple line items per contact, and I only want 1 x email per contact. 

In the replies you’ve mentioned: 

the simplest way to deal with this would be to have an automation per team 

As far as I understand in my instance this would mean 1 x automation per contact which is not viable with roughly 250 contacts. Am I asking too much of my automations here or is there a way to try this? 

As far as I understand in my instance this would mean 1 x automation per contact which is not viable with roughly 250 contacts. Am I asking too much of my automations here or is there a way to try this? 

 

Ah, yeah, I’d recommend trying the repeating group thing in this case.  Assuming you’re triggering the automation once a week, this is how a simple version might look, and I’ve set it up here for you to check out
 

 

Adam you’re an absolute wonder and I’m so grateful you took the time to do that!
Admittedly I’m a very low level of experience on the airtable scale so I had to wrap my head around it first but It worked a treat 😎 I learnt so much from your sample, thank you again 

Terms & ConditionsAccessibility statement