Whew, this is kind of messy huh? Here is an approach that can get you started:
Assuming your data looks something like this:
Where it is wider than it is long… (not tidy). Then your first task is to build a pretty ugly filter for a new view:
And then trigger automation when a record enters that view.
From there I would recommend ‘Run a script’. If you do this tedious work once, you can hopefully reap the benefits long term:
Basically saying that we’ll pull every date into this script and let a function decide what is relevant or not.
// 🧲
//const params = input.config(); // 👈 this is pretty typical, but i would do the next line instead
const params = Object.entries(input.config())
//console.log(params)
// 🛫 lift off
const today = new Date().toISOString().slice(0, 10) // today's date as a string to match your inputs
//console.log(now)
let updates = {} // container for multiple field updates if necessary
// 🧞♀️
// if today matches the expiry date, set a new date five years into the future
function setNewExpiry(a,b){
if( a[1] == b){
// create date in the future
let new_expiry = new Date()
new_expiry.setFullYear(new_expiry.getFullYear() + 5); // hardcoded 5 years
// generate update based on param names to the left
let columnName = a[0]
//console.log(columnName)
let update = {
[columnName]: new_expiry.toISOString().slice(0,10)
};
//console.log(update)
Object.assign(updates, update) // modern way to combine objects (very useful for Airtable API)
//console.log(updates)
}// else {console.log('not today')}
}
// if any updates need occur, do them together
async function updateRecords(params, updates){
let rec_id = params.slice(-1)[0][1] // just make sure you choose your last input variable to be the
record ID
//console.log(rec_id)
await base.getTable('employees').updateRecordAsync(rec_id, updates)
}
// 🛬 landing
params.map( cert => setNewExpiry(cert,today) ) // run this function for each certification date
await updateRecords(updates, params)
So, not exactly simple? But at least this will get those updates started and this setup would still allow you to do something similar if you also will need to send alerts 6mos prior to the next nearest expiry date per employee or something.
