How can I automate the date field update to 5 years from now

[Jon_Schaffer]

Dear community,
We help our customers by notifying them when their employees certificates expire and they need to renew certificates. Employees have 10+ different certificates and I would like to automatically update the expiry data for the certificates to “5 years from today” when it expires. But how do I update the specific date field in the record when I have multiple date fields in each record? How does the automation know which date-field to update? Can I refer the to date that just expired, and if so, how do I do that.

Hope some of you have a solution.

Thanks,
Jon

[Bill_Felix]

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.

[Jon_Schaffer]

Hi Bill, Thanks for the advice. Looks like this could work out nicely, so I will give it a try, when we are ready to implement, and let you know how it goes.

Thanks,
Jon