Skip to main content
Solved

Trigger Automation on record create AND update

T
Forum|alt.badge.img+1

Howdy, Automation slots are so, so precious. :face_with_raised_eyebrow: In order to ensure we stay under the Automation limit, I’ve had to switch everything away from “When record matches conditions” triggers to “When record updated” triggers, watch a whole bunch of unrelated fields, and combine scripts to be massive & idempotent. All good, but significant work keeping track of everything. :sweat:

For some tables, we need the data to always be correct - even immediately after creation. For these tables, it seems like the minimum number of Automations is two: one on the update trigger, and one on the create trigger - just to set something inconsequential to trigger the update.

My question is,º is there some non-human, non-Automation trickery that we can use to drop the number of required Automations per table to one? That is, how can I ensure the update event will trigger immediately after creation also - without using another Automation slot?

Please and thank you! :pray:

ºAssuming there’s no way to get the Automations below one per table? :grinning_face_with_smiling_eyes:

(Also, I’d like to avoid time-based external solutions like watching with Integromat or Zapier; they’ll just error later when the time is coincidentally off & be super hard to debug.)

Best answer by Tim_M_GT

Documented here for others (and probably future me :winking_face: ):

First up, create the following fields:

  • Created: “Created time” type field with time
  • Last Modified: “Last modified time” field w/ time
  • LastModifiedOrCreated: Formula field
    IF({Last Modified}, {Last Modified}, Created)
  • LastAutomationRun: Date field w/ time (updated by Automation only; lock it, if you can)
  • RunAutomationReset: Checkbox field (Automation only; locked)
  • RunAutomation: Formula field
    IF(RunAutomationReset, FALSE(), OR(NOT(LastAutomationRun), DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10))

The Automation itself should use the Trigger ‘when a record matches conditions’ and will need two subsequent Update record actions. (Hint: be sure to put your scripts after these steps - if your script fails and the other steps don’t run, your record will be stuck with RunAutomation on & won’t trigger the automation anymore.)

Trigger with “When RunAutomation = 1”:

Then add the 2 Update record steps. The first sets the RunAutomationReset to true (which sets RunAutomation to false:

The second sets RunAutomationReset back to false & updates LastAutomationRun using LastModifiedOrCreated:

RunAutomation: The linchpin of the whole setup is OR(NOT(LastAutomationRun), DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10). The first clause NOT(LastAutomationRun) will be 1 when the record is first created. The second clause DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10 will be 1 whenever a field is modified more than 10 seconds after the last automation run.

Unfortunately this buffer is needed because the timestamps never end up being equal. Timestamp equality could probably be achieved if the “Last modified” field only took into account certain fields, but if you choose to use specific fields, when you add a new field, you’ll need to edit the “Last modified” field to have it count. I’ve seen a difference of up to 7 seconds from a single run, but they’re mostly 2-5 seconds, so hopefully 10 is long enough to avoid recursive triggering and short enough to allow all legit edits in your application. ¯\_(ツ)_/¯

RunAutomationReset: This field is toggled by the Automation, meaning RunAutomation is always reset. Without this, if the Automation run takes longer than the next edit, the record will be permanently stuck with RunAutomation on and will never trigger another Automation run (to trigger the automation, the record needs to not match the conditions first).

LastModifiedOrCreated: This field ensures we always have a timestamp to put into LastAutomationRun, even if the record is new. If your automation uses a script, it’s probably better to make this choice there & avoid the extra field.

I made an example base, but when you copy it, it won’t have the automation, hence the instructions and explanation above.

Thanks and credit once again to @kuovonne!

View original
J
E
S
19 people like this
  • J
    Johannes_M
  • E
    Elizabeth_Schaf
  • S
    sudo_binr
  • D
    Dylan_Sather1
  • R
    Robyn_Thiessen-
  • A
    aaron_altamura
  • T
    tom_m
  • M
    Michael_Fray
  • E
    Eduardo_Cavazos
  • C
    Colleen_Briant
  • S
    Stephan_Strittm
  • M
    Martha_Creedon
  • J
    Joao_Azevedo
  • R
    Roman_Leventov
  • J
    Jack_Sloan
  • K
    Kevin_Cox
  • F
    Francisco_Quiro
  • M
    Martina_Pavelko
  • D
    douglaslassance
Did this topic help you find an answer to your question?

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • March 4, 2021

Here is a possibility, if there will be a long enough time between changes for the automation to run.

Use two fields:

  • one editable field for the last time an automation was run {LastAutomationRun}
  • a formula field that uses the {LastAutomationRun}, LAST_MODIFIED_TIME, and CREATED_TIME to determine if the automation should run.

The last part of the automation should be to update the {LastAutomationRun}.

You may need to tweak the fields you include in the LAST_MODIFIED_TIME.

T
Forum|alt.badge.img+1
  • Tim_M_GT
  • Inspiring
  • March 4, 2021
kuovonne wrote:

Here is a possibility, if there will be a long enough time between changes for the automation to run.

Use two fields:

  • one editable field for the last time an automation was run {LastAutomationRun}
  • a formula field that uses the {LastAutomationRun}, LAST_MODIFIED_TIME, and CREATED_TIME to determine if the automation should run.

The last part of the automation should be to update the {LastAutomationRun}.

You may need to tweak the fields you include in the LAST_MODIFIED_TIME.


Hey @kuovonne thanks for the reply! I’ve tried playing around with formula fields but as best I can tell, they’re considered instantaneous from a triggers perspective. Each time I create a new record, the update automation won’t fire. :frowning:

kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • March 4, 2021

Use a “when record meets conditions” trigger based off the formula field. The formula calculates upon record creation to say that the automation should run.

Here is the logic of the formula:

  • If there is no {LastAutomationRun} (because it is blank on record creation), then run the automation time.
  • If the last modified time is after the {LastAutomationRun}, then run the automation. If there is no last modified time, compare with the created time instead.

You will need to indicate which fields to watch for the last modified time, as you need to be sure to not include the {LastAutomationRun} field itself.

Note that you may still have issues with fields that are manually typed (text fields and number fields), because the fields are considered updated with every key press. Depending on your data entry, you might be able to have the formula do other data validation to check if data entry is complete.

If a second update arrives before the automation completes, the system might have difficulty handling the second update.

If you would like to hire me to assist in writing this formula for your base or discussing the issues with this approach, you can book an appointment.

T
Forum|alt.badge.img+1
  • Tim_M_GT
  • Inspiring
  • March 11, 2021
kuovonne wrote:

Use a “when record meets conditions” trigger based off the formula field. The formula calculates upon record creation to say that the automation should run.

Here is the logic of the formula:

  • If there is no {LastAutomationRun} (because it is blank on record creation), then run the automation time.
  • If the last modified time is after the {LastAutomationRun}, then run the automation. If there is no last modified time, compare with the created time instead.

You will need to indicate which fields to watch for the last modified time, as you need to be sure to not include the {LastAutomationRun} field itself.

Note that you may still have issues with fields that are manually typed (text fields and number fields), because the fields are considered updated with every key press. Depending on your data entry, you might be able to have the formula do other data validation to check if data entry is complete.

If a second update arrives before the automation completes, the system might have difficulty handling the second update.

If you would like to hire me to assist in writing this formula for your base or discussing the issues with this approach, you can book an appointment.


Aah of course, thanks so much! I now completely understand your original message including the time component; that had me a bit confused before, sorry. Unfortunately, it took a little bit more setup to get it stable, but I think I have it now. :grinning_face_with_big_eyes:

kuovonne
1 person likes this
  • kuovonne
    kuovonne
T
Forum|alt.badge.img+1
  • Tim_M_GT
  • Inspiring
  • March 11, 2021

Documented here for others (and probably future me :winking_face: ):

First up, create the following fields:

  • Created: “Created time” type field with time
  • Last Modified: “Last modified time” field w/ time
  • LastModifiedOrCreated: Formula field
    IF({Last Modified}, {Last Modified}, Created)
  • LastAutomationRun: Date field w/ time (updated by Automation only; lock it, if you can)
  • RunAutomationReset: Checkbox field (Automation only; locked)
  • RunAutomation: Formula field
    IF(RunAutomationReset, FALSE(), OR(NOT(LastAutomationRun), DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10))

The Automation itself should use the Trigger ‘when a record matches conditions’ and will need two subsequent Update record actions. (Hint: be sure to put your scripts after these steps - if your script fails and the other steps don’t run, your record will be stuck with RunAutomation on & won’t trigger the automation anymore.)

Trigger with “When RunAutomation = 1”:

Then add the 2 Update record steps. The first sets the RunAutomationReset to true (which sets RunAutomation to false:

The second sets RunAutomationReset back to false & updates LastAutomationRun using LastModifiedOrCreated:

RunAutomation: The linchpin of the whole setup is OR(NOT(LastAutomationRun), DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10). The first clause NOT(LastAutomationRun) will be 1 when the record is first created. The second clause DATETIME_DIFF({Last Modified}, LastAutomationRun) > 10 will be 1 whenever a field is modified more than 10 seconds after the last automation run.

Unfortunately this buffer is needed because the timestamps never end up being equal. Timestamp equality could probably be achieved if the “Last modified” field only took into account certain fields, but if you choose to use specific fields, when you add a new field, you’ll need to edit the “Last modified” field to have it count. I’ve seen a difference of up to 7 seconds from a single run, but they’re mostly 2-5 seconds, so hopefully 10 is long enough to avoid recursive triggering and short enough to allow all legit edits in your application. ¯\_(ツ)_/¯

RunAutomationReset: This field is toggled by the Automation, meaning RunAutomation is always reset. Without this, if the Automation run takes longer than the next edit, the record will be permanently stuck with RunAutomation on and will never trigger another Automation run (to trigger the automation, the record needs to not match the conditions first).

LastModifiedOrCreated: This field ensures we always have a timestamp to put into LastAutomationRun, even if the record is new. If your automation uses a script, it’s probably better to make this choice there & avoid the extra field.

I made an example base, but when you copy it, it won’t have the automation, hence the instructions and explanation above.

Thanks and credit once again to @kuovonne!

T
A
dilipborad
3 people like this
  • T
    Tim_Mackey
  • A
    Air2Flow
  • dilipborad
    dilipborad

Reply


Most helpful members this week

Terms & Conditions