Find Records Automation With Date Condition

[Spencer_Mullane]

So close to getting the whole base working the way I want I can see the light at the end. I think the final hold up is getting the "Find Records" step working properly.

My automation triggers when a new record is created in "Trainings". The "Trainings" table has the following fields:

• Training Type (Single Linked Record to Another Table Called Training Types)
• Date (date field)
• Attendees (Multiple Linked Record to Another Table Called Employees)
• Expiration Period (Roll-Up from Training Type Table)
• Expiration Status (Formula Based on Date Field and Expiration Period Roll-Up)
• Expiration Date (Formula Based on Date Field and Expiration Period Roll-Up)

The other table "Training Assignments" has the following fields:

• Employee (Single Linked Record To Employees)
• Training Type (Single Linked Record to Training Type)
• Assigned (Checkbox)
• Trainings (Single Linked Record to Training Type)
• Expiration Status (Look-Up To "Trainings" Table Based on "Trainings" Field Linked Record)
• Expiration Date (Look-Up To "Trainings" Table Based on "Trainings" Field Linked Record)

The automation should run when a new record is created in "Trainings". It will grab the multiple linked record field "Attendees" and loop through the linked values there. It then does a Find Records on "Training Assignments" and looks for all records (there will only be one match for each loop) that match both the employee and the training type. It should then update the "Trainings" linked field in "Training Assignments" to link the found record to the created initiating record.

For example, if I create a CPR training on 3/14/2024 and attendees are Steve and Jeff, there will already be two records (one for each employee) in "Training Assignments" that are [Jeff, CPR] and [Steve, CPR]. The automation will then update and link the two found records to the initiating trigger record. This is all working great.

I need a final step/condition which is to Find Records that A) Match the employee(s) looped through from the initiating trigger record B) Match the training type from the initiating trigger record AND C) are older (less than) the date of the initiating trigger record. I cannot seem to get the date part. I have the lookup field in "Training Assignments" that looks up the date (which itself is a formula) but the condition I can add in Find Records is not "greater than", "equal too", "less than", etc but rather "is", "is exactly", "contains", etc. Seems it sees that field as a string? I have tried making a helper column with a formula using DATETIME_PARSE, DATETIME_FORMAT, etc but still those are seen as strings as well. I also just added a manual date field to test it and it still appears to not see that date field as something to have logical operators on, just string checks? Just need to figure out how to get that Find Records action to see the date field as a date/number and I should be in business. The intent is that while doing find records, if the find record date is less than the initiating trigger record date, then the automation will find the record in "Training Assignments" and update it. If the found date is more recent than the initiating trigger date (IE if someone were backdating records and putting in a training from 2 years ago for completeness) then that record gets skipped over and thus its found date remains the same since it is more recent than the triggering date.

Any help here (or a sad "airtable can't do this") would be much appreciated. Been backburner chewing on this use case for a few years and finally had the lightbulb on this method after I couldn't quite get the scripts correct (someone could just not be).

Thanks,

[ScottWorld · Accepted solution]

You've stumbled upon 2 different issues that you’ve correctly identified: 

1. The first problem is that lookup fields and rollup fields are arrays which could contain multiple values, which is why you can’t do the type of search you are looking for on a lookup field. If you’re sure that your lookup field will only have ONE value in it at all times, then you could create a formula that results in a different field type. 

2. However, the other problem that you’ve discovered is that even if you created a formula field that converts the lookup field into a date field (or even if you just use a normal date field), Airtable’s automations have a strange limitation where we are not allowed to perform dynamic searches on dates. No idea why. 🤷🏻‍♂️

So, the solution is to transform your date into a NUMBER FIELD by creating a formula that converts your date into a UNIX TIMESTAMP NUMBER.

At its most basic level, this is how your formula would look:

VALUE(DATETIME_FORMAT({Date},'X'))

The X turns any date into a number, and Airtable DOES allow you to perform dynamic searches on numbers.

So, if you have any date fields in your Airtable base that need to be dynamically searched in an Airtable automation, turn those date fields into numbers by using the UNIX timestamp.

That should solve the problem for you!

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[Spencer_Mullane]

Much appreciated, I think I did try the 'X' number format but must have implemented wrong. Since I'm comparing the two record dates helper columns would definitely be fine in this instance though. Will try as soon as I'm back at my desk.

Agreed it's very weird it can't search/filter by dates, wonder what that's about

[TheTimeSavingCo]

Hmm, if I'm understanding you right, your end goal is for the "Expiration Date" field in the "Training Assignments" to always have the latest expiration date, is that right?

[Spencer_Mullane]

Alright, think we have success or something extremely close. I had tried DATETIME_FORMAT(Date,'X') before but that was still kicking a string, adding a "+0" to the end to force it into a number cleared that up and gave me the conditional logic operators =, >, <, <>, etc. Was able to get it filtered and the automation is doing exactly as I had hoped. Thank you Scott that was the missing piece and will remember availability for future, this is kind of a personal prototype to see if we could expand to greater functionality across the division (replacing a spreadsheet that has been around far longer than I have 10+ years).

TheTimeSavingCo that is essentially correct, with the addition of linking the "Trainings" record instance with the relevant "Training Assignments" record(s). There would be a single record for each employee and assigned training combo in the "Training Assignments" table IE Steve-CPR, Jeff-CPR, Dave-CPR, Steve-Harassment, Jeff-Harassment, Dave-Harassment, Steve-Rigging, Jeff-Scaffold, etc etc.

The automation that is now working takes the new record of a Training in "Trainings", which includes fields for training type, date, and attendees IE CPR on 3/14/2022 attended by Jeff and Steve and then links that record to both Jeff and Steves CPR record lines in the "Training Assignments" table IF that just entered training is more current than the training that either of them previously took. At the end of the day these Training Assignments are linked back to employees and I can have two lookup columns filtering for which trainings they have been assigned that are active or expired (or close to expiring) as well as how many people will expire or have expired on a certain training soon to know what to schedule.

One hurdle now seems to be lack of "pause" in the automation. If I add the training via form we are all good as it submits all fields at the same time but it seems like if I add a training by going into the grid and making a manual input it triggers as soon as I start messing with the new row, which then means the attendees haven't been tagged yet and thus the automation has nothing to loop through and link. Form is more controlled entry anyway but flexibility would be nice. On to that issue now.

[ScottWorld]

Using a form is a great way to solve that problem.

Alternatively, you can create a single-select field or a checkbox field that triggers your automation.

So, after you have finished all your data entry on the record, just choose an item from a single-select field (or check the checkbox) and then your automation will start.

More tips & tricks on the advantages of using a single-select field in this thread. 

Also, if you do need pauses in your automations, I HIGHLY recommend using Make's automations for Airtable. You can create the world's most advanced Airtable automations with Make.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[TheTimeSavingCo]

Hmm, if my understanding of your base structure is right, couldn't we use a rollup field with "MAX(values)" for this instead?
 

Link to base

[Spencer_Mullane]

I think that could work as well with dynamic filtering (new-ish feature it seems?) but I think I still need the automation to link the "Training" instance record to the "Training-Assignments" record for each employee. The dynamic filter with a MAX roll-up could save the filtering step in the automation ? That would make it simpler for other folks who might touch the base if we end up deploying it.

[TheTimeSavingCo]

Hmm, I'm not using dynamic filtering in my setup?  And yeap, as you've said, you'd definitely need to link them together but wouldn't need that filtering step in the automation after!

[luckylou]

Hi @ScottWorld - I have a similar issue needing to set condition based on date comparison.

I was so excited to try the UNIX date formula you shared but the operator the automation gives me is only "contains, does not contain, is" and so on, that are not numerical comparisons.

My formula is exactly as you specified: DATETIME_FORMAT({Date},'X') although I used my own date field name, of course.

Any tips?