Automation - Update record, and save date field with NOW() timestamp

[Karl_at_Easy_La]

I would like to update a date field in a record, with the last time the automation executed. Is this possible in an automation, in the Update Record Action?

If you choose static, you have to enter the field manually. If you choose a dynamic value, you are only allowed to choose from an existing field or data.

[ScottWorld]

The only way to do this with Airtable’s native automations is to create a formula field in your database with the NOW() formula. Then, in your update record action, you would insert that formula field into the field that you want to update.

p.s. Note that if you have a large base, the NOW() formula can slow down a base. Other workarounds for this issue include writing your own custom JavaScript, or using Make which offers its own internal NOW function.

[cesrua1]

Hi Karl,

Another workaround to Scott suggestion is instead a formula field with now formula, to create a Last Modified time field and insert the value of this field in the automation.

Kind regards, 

[ScottWorld]

Or just create a last modified time field with no automation at all! 🙂

[cesrua1]

That works if he doesn't update the record though other processes right?

[ScottWorld]

Yes, everything would depend on his particular workflow.

[Calvin_Young2]

This one is still killing me years after running into a roadblock with it.  

I have an automation that triggers when certain fields are updated.  So I have a "Last Modified Time" field which is tracking those same fields.  But over the years I have modified which fields trigger the automation.. and forgotten to simultaneously change the "Last Modified Time" configuration to match... And to make it worse, one of the fields that the automation is watching is a lookup.  Which means I need three more fields to make this work: a "Last Modified Time" for the table that's being looked up, a lookup of that timestamp, and a formula field to combine the actual last modified time and the looked-up last modified time.  What a mess.

All because you can't write the current time to a record.  And because NOW() doesn't actually work.  

Sorry, I'll stop complaining now!

[Calvin_Young2]

Actually.. thinking about it again finally prompted a better solution.

• The automation triggers
• Create or update a record (if this isn't naturally part of your automation, have make a dummy update)
• Update your record with the Last Modified timestamp from the above action

In the above example, both "Update record" actions are updating the same record.  But you need to break it into two separate actions so that the second can reference the first.  

This is a much more robust solution than my previous mess, and does not rely on the dubious NOW() function. 

[Karl_at_Easy_La · Accepted solution]

Actually I had solved this quite neatly, using a simple Javascript script. It keeps the table clean, no extra fields added, or having to use a formula field with NOW() either. I never remembered to post a reply about it.

It involves adding a 'Run a script' step, with the below simple Javascript code:

var dt = new Date();
output.set('nowTimestamp', dt.toISOString());

Then, you can use the output value 'nowTimestamp', in any value.

I hoep

I hope this helps. If anyone wants any further clarifications, ask away.

[ViRuS16]

@Karl_at_Easy_La - does the script solution slow down the Database? My use-case would require the script to run hundreds maybe thousands a day. 

Thank you