To cover updated and created records with a single trigger

[Ubeyd_Gungor]

I am trying to create an automation that is triggered when a record within a table created or updated? Is it possible to cover both creates and updates with a single trigger?

[kuovonne]

There is no single built-in trigger that combines both the created and the updated events. However, you can combine a formula field and an editable date/time field managed by the automation to achieve this effect.

Here is a sample formula field.
In this formula, {LastAutomationRun} is the editable date/time field that is managed by the automation, and {Name} and {Status} are the two fields that are being watched for changes. You will need to edit this list of fields to match your base.

The trigger for the automation is when the {LastAutomationRun} is not empty. One of the actions for the automation must be to update the {LastAutomationRun} with the value from the formula field.

You will also need to adjust the delay to match your workflow, as the automation will not deal well with multiple updates performed in quick succession before the automaton can complete.

IF(
    NOT({LastAutomationRun}),
    CREATED_TIME(),
IF(
    DATETIME_DIFF(
        IF(
            LAST_MODIFIED_TIME({Name}, {Status}),
            LAST_MODIFIED_TIME({Name}, {Status}),
            CREATED_TIME()
        ),
        {LastAutomationRun},
        'milliseconds'
    ) > 0,
    IF(
        LAST_MODIFIED_TIME({Name}, {Status}),
        LAST_MODIFIED_TIME({Name}, {Status}),
        CREATED_TIME()
    )
))

[Lu]

@kuovonne wrote:

The trigger for the automation is when the {LastAutomationRun} is not empty.

Shouldn't it be when the formula field is not empty, i.e. it contains a new value to update the {LastAutomationRun} field?

Anyway, thanks to your helpful answer, I managed to reclaim a few automations by condensing some pairs down to singles. I used three fields to make it easier for me to understand:

A last modified formula:

IF(
    LAST_MODIFIED_TIME({Watched column}),
    LAST_MODIFIED_TIME({Watched column}),
    CREATED_TIME()
)​

A last run date (with its permissions set to editable by automations only).

A trigger formula:

IF(
    OR(
        NOT({Last run}),
        DATETIME_DIFF({Last modified}, {Last run}, 'ms') > 0
    ),
    {Last modified}
)​

My automation uses the "When a record matches" trigger type and looks for a non-empty result in the trigger formula field. The first step in that automation updates the last run date to the result from the trigger formula, which causes the trigger formula result to immediately reset to blank. This seems to prevent the race conditions from multiple updates that you warned about.