Skip to main content

Hi!

I have a table where I am tracking when a certain item (Lot) needs to have its lab testing updated. Each record has a {Lab Status} single select field, a {Latest Lab Date} date field, a {Next Lab Due} field that calculates a date 6 months out from the date field, and a {Send By} field that calculates a date 1 month prior to the next due date. The rest of the fields are linked to records in another table of the same base but are not pertinent to this.

Record   Lab Status   Latest Lab Date    Next Lab Due     Send By
Lot AA    Up Next        08/01/2024          02/01/2025        01/01/2025

There are two functions I would like to implement, if possible, either into the table or with automations:

1. (automation or formula?) When we enter into a given month, I would like for any records with a Send By date in that month to automatically update the Lab Status to 'Up Next'. For example, on February 1st any record with a Send By date in february gets its status changed to 'Up Next'. For now I've tried to set all the dates to be the first of the month to more easily batch them.

The 'Lab Status' field contains options that we need to be able to self select given other circumstances, so it cannot be something that only allows the field to be auto-populated. The other options we have at the moment are 'Up to Date', 'Pending' (samples sent, awaiting results), 'Hold' (do not send even if due), and 'Overdue'.

2. (automation) When we enter into a given month, an email is sent notifying you of all the records with a Send By date in that month.

I've tinkered in automations and with formulas but can't seem to get it right.

If you see other ways to simplify this I also welcome that input. Thank you for all help and insights! 

My initial inclination on function 1 was a formula, but if you want it changeable in the same field, then an automation would be the way to go.

What you need to do is play with the Find Records function in automations, which will create lists of records that you can apply updates to en masse. So for example in function 1, the automation is trigger is the 1st day of every month, Find Records where Next Lab Due is within the calendar month. Then you run a repeating group action on that list, updating records to "Up Next". 

Same thing kind of applies to function 2, you're just using the output differently. Same trigger, same Find Record action, but the following action is send an email, and the list of records is contained within the body paragraph of the email.  

Would the below work for you? 

For #1 you could create an airtable automation:

- Trigger: "At scheduled time" trigger (with interval type monthly, every 1 month on the 1st at 12am)

- Action: Find records (where Send By is during this Month & Lab Status isn't Hold) - either by condition or you can filter it by view if you have a dedicated view for these

- Action: Update record (for all records that were found) - set Lab Status to "Up Next"

For #2 you could create another airtable automation:

- Same trigger

- Find Records where Send By is during this Month

- Send Email (

To: [your email]
Subject: Lab Testing Due This Month
Body: The following items require lab testing to be sent this month:

{
For each record in found records:
"Lot: " + {Lot} + " - Send By: " + {Send By}
}

@ibayub  and @DisraeliGears01  These are great suggestions! The problem I'm running into I think has to do with Send By not being a date field but a formula field. So when setting up the condition for the action I'm not offered the time based prompts like when i select a date field.

Instead I'm offered to choose data from the trigger (actual, expected, next), a specific time, or base structure. I've tried each but it returns zero records when I test it. I've included some screenshots of this as well as the formula used in the table.

Ah, that makes sense, I just assumed the dates were date fields, not formulas. When you Datetime format it converts to a text string, so you need to convert it back to a date string. Trying wrapping your whole Send by Formula with DateTime_Parse(). Just tried this in a demo base and it worked to let me select date based options as conditional triggers in an automation.  

Ah, that makes sense, I just assumed the dates were date fields, not formulas. When you Datetime format it converts to a text string, so you need to convert it back to a date string. Trying wrapping your whole Send by Formula with DateTime_Parse(). Just tried this in a demo base and it worked to let me select date based options as conditional triggers in an automation.  


when running the second action to update the record, i get 'received invalid inputs' in the results.

under Record ID in the results it correctly lists the records that need to next be updated as identified in the 'Find Records' action before it. 

Is the problem with the field or record id configuration, with something in how the record or table is setup, or...?

The update record action needs to be inside the repeating group. Right now the update is getting a huge string of ID values that it doesn't know what to do with. 

Reply


Terms & Conditions