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,
