- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2025 11:52 AM
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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2025 12:59 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2025 01:06 PM
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}
}
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 08:44 AM
@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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2025 09:01 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 31, 2025 11:51 AM
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...?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 03, 2025 06:55 AM
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.
