Use automations to timestamp status updates
  • 05 Dec 2022
  • 7 Minutes to read
  • Dark
    Light

Use automations to timestamp status updates

  • Dark
    Light

Learn how to use Airtable Automations to track the amount of time a record maintains a specific status. This can be helpful for teams who need to stay on top of how long a record stays in each stage of a process.  Automations are helpful in this scenario because they can be used to input a static date and time into a date field based on when the status field changes. This would otherwise not be possible without leveraging a custom API call or a third-party tool like Zapier.

Introduction

Plan availabilityAll plan types
Permissions
  • Owners/Creators - Because the steps below involve creating new fields and automations, Creator permissions are required in order to perform these actions.
Platform(s)Web/Browser, Mac app, and Windows app
Related reading


Scenario

We'll use a scenario where a content marketing team wants to track how long blog articles in their development queue spend in each stage of their process. The end result is valuable data that can provide insights into their internal processes. Focusing on new blog content, each new article can move through five different stages:

  1. Planned
  2. Assigned
  3. Review
  4. Staging
  5. Published


Base design and setup

A few tips before reading ahead:

  • In the base that you are looking to apply these concepts, you'll likely use different names. Remember, you might use different values in later sections on formulas and automations.
  • You might need to modify the base that you are working in. It's a good idea to duplicate the original base and practice building in the copy to make sure everything looks correct before applying the concepts to the original base.
Part 1: Designate a status field

Use a single select field to designate each of the different statuses. In our example, we call this the "Status" field, but you might name this something else depending on your individual workflow's needs.

360094807574status.jpg

Part 2: Create date fields to timestamp each status change

To timestamp when a record changes to each unique status we need to create five date fields; one each for {Planned}, {Assigned}, {Review}, {Staging}, and {Published}. These fields will be populated later by the automation.

360097195033datefields.jpg

Part 3: Create a last modified time field

The last modified time field will be used by the automation to timestamp each of the four date fields we just created. After you add the last modified time field, make sure to select the "Specific fields" option and select the {Status} field. This will ensure that the last modified time is only displayed when that specific field changes.

360097109433lmt.jpg

Bonus: Looking to just track one particular value in a certain field? Click here.

If you are looking to just track updates in certain fields for certain values, then building out a formula using the LAST_MODIFIED_TIME() function alongside conditional IF() statements will likely be a better choice than using a Last modified time field.

For example, you might want to be notified whenever content goes live. So we can build out a formula field that only returns the date and time when the Status field is set as "Live".

360097771034formulaForAutomationTimestamp.png


Automation setup

Now that the required fields have been configured we can start building an automation. We'll create a single automation with five conditional groups that will map to each of the status date fields we created in the last section. Follow the steps below to create the entire automation.

Note
The automation we are building can't detect the current "Status" field of existing records, because the automation trigger relies on a change of state. Therefore, for any existing records, you'll want to manually fill in the approximate date/time each record went into each of its statuses. Once the automation has been turned on, all future records that are created and updated should have each status date field filled in automatically.
Part 1: Choose an automation trigger

Start by navigating to the Automations section in your base located in the top left part of the screen. Then click Create automation + to begin the setup process.

  1. Select the When record updated trigger. 
  2. Next, choose the name of the table you want the trigger to reference. In our case, this will be the "Content production" table.
  3. Then, configure the trigger to only watch for when the "Status" field has been updated. You can skip the optional view selection or Select a view if you only want this automation to be scoped to activity in a certain view.
  4. Test the trigger before moving on to the next step.

Part 2: Set up the first conditional action
  1. First, skip the Actions section and click the plus sign next to Conditional Actions.
  2. This will open up a menu to configure the first conditional group. This first group will be set up to only run the action we set next when the "Status" field is set to the "Planned" status option. Then, click the + Add action option
  3. Now, we want to set up the action so that the record will be automatically timestamped with the current value from the "Last modified time" field we set up earlier. First, choose the Update record option from the list of actions.1 Next, choose the same table from the trigger step, which in our use case is called "Content production."Then, you'll want to set the Record ID to be the same record from the trigger step. Click the blue plus sign button to insert that value from the token picker.Next, under Fields you'll click the + Choose field option and select the "Planned" date field. Finally, click the cogwheel so that the value can be filled dynamically with whatever the current value of the "Last modified time" field is showing.4
Part 3: Duplicate and modify conditional action groups
  1. To timestamp each of the status date fields, we'll need to have one unique conditional group per status date field. Since the first conditional action group is already configured we can quickly duplicate it and adjust the values in order to update each of the last four status date fields.
  2. After duplicating the conditional group, click into that group and then click Edit conditions to change this conditional group to run the update record action when the "Status" field is marked as "Assigned."
  3. Last, click into the Update record action. You'll need to delete the previous group's "Status" field and follow the same steps to dynamically add the "Last modified time" to update the corresponding status date field.
  4. Repeat these steps three more times and adjust the settings accordingly to create conditional action groups for the remaining status date fields.

Once all five conditional automation actions are configured, each date field will be timestamped whenever its corresponding status is changed. For example, when a record changes status from "Staging" to "Published," the "Published" date field will be timestamped with the exact date and time the status was changed.


Use formula fields to calculate each status duration

The final step is to calculate the duration that a record spent in a particular stage. In the example below, we'll calculate how long an article spent in the "Planned" stage before moving to "Assigned". You can then modify the formula values to calculate the time spent in each status. 

Note
Remember, you'll also need to adjust the values below to fit the names of the fields in your particular base if they don't perfectly align with the example used in this article.
Part 1: Create the first time difference formula

To start, add a new formula field. Then copy in the formula below (making adjustments for field names if yours are named differently).

IF(
    AND(
        {Planned}, {Assigned}
    ),
    DATETIME_DIFF(
        {Assigned},
        {Planned},
        'hours'
    )
)

The formula will check to make sure that both the {Planned} and {Assigned} fields have a date entered, and if they do, it will calculate the time difference between those dates in hours. If you would rather use a different time output, then check out our article on unit specifiers for DATETIME_DIFF() formulas here.

360094893374plannedtoassigned.jpg

Part 2: Duplicate and modify formula fields

To get the difference in time for the other stages, duplicate the formula field and adjust the values for each of the different statuses. For example, the next time difference to calculate is the time between "Assigned" and "Review", so the adjusted formula will look like this:

IF(
    AND(
        {Assigned}, {Review}
    ),
    DATETIME_DIFF(
        {Review},
        {Assigned},
        'hours'
    )
)

Now you should have four separate formula fields that each output the number of hours a record spent in each stage of the content process.

360094893414timeinstages.png

Part 3: Create a total time to "Published" field

You can also calculate the total time spent from "Planned" status to "Published" status by creating another formula field that sums together the time values from each of the four fields that you just created. That formula could be as simple as:

{Planned to Assigned}+{Assigned to Review}+{Review to Staging}+{Staging to Published}



Was this article helpful?