Automation to Run After Record has held a Status for X Days

[Taylor_Olmstead]

I’m building a content marketing tracker and I’d like to be able to add an analytics and reporting status which automatically triggers 30 days after a piece of content is marked as published. What would be the best way to handle this in an automation?

[Databaser]

Hi @Taylor_Olmstead and welcome to the community!

I would combine a Last Modified Time field that only looks at the field you use to mark things as “published” (eg a checkbox or a single select with only that option), with a Formula field, eg:

IF(DATEADD({Last Modified}, 30, "days")=TODAY(), "it's been 30 days")

Then, use the trigger “when a record matches conditions” where the formula field says “it’s been 30 days” to start your automation.

If you don’t want to run this for every record, I would alter the formula field to give you the content that was 30 days in the past during the current week and combine it with a “at a scheduled time” trigger and “find records” action.

[Taylor_Olmstead]

Databaser:

I would combine a Last Modified Time field

I’m not quite sure what trigger I’d use for this, can you elaborate?

[Databaser]

Last modified time field

[Taylor_Olmstead]

Sorry I’m a total javascript noob. So my automation trigger is when record meets condition (status: complete). Then the script should be

IF(DATEADD({Last Modified}, 30, "days")=TODAY(), "it's been 30 days")
THEN(???)

I’m not sure the syntax for identifying my single select field “Status” and setting a selection.

[ScottWorld · Accepted solution]

You don’t need a script to do this, you just need to create 2 fields:

(1) a modified time field that only updates when the status field changes, and

(2) a formula field that yields a result (such as the number “1”j when both conditions are true:
(a) status field is “published”, and
(b) status field was modified on or greater than 30 days ago.

The formula is the tricky part. After you get that formula created, you would just trigger your automation based on the presence of the number “1” in that formula field.

Something like this might work, although I haven’t tested this:

IF(AND(Status="Published",DATETIME_DIFF(TODAY(),{Status Modified Time},'days')>=30),1)

[Taylor_Olmstead]

You helped me crack it!

This will allow me to ping our analyst when any of our various forms of collateral have shipped.

So here’s how I have it set up for those who need to do something similar:

Fields in Database

• Status
• Last Modified (Looking at Status)
• Formula

The Formula

IF(AND(Status="Published",DATETIME_DIFF(TODAY(),{Status Modified Time},'days')>=30),1)

My Automation
Trigger: Record Matches Conditions

• Status is any of “published, fulfilled, promoting”
• AND Formula = 1

Action: Update Record

• Field: Status
  • Analyzing
• Field: Assigned to
  • (Name of Marketing Analyst)

---

From there the analyst will enter stats into dedicated fields and marks the record as complete.

Then the collateral and its attendant analytics marked complete are included in a monthly summary digest our managers receive via email.

[ScottWorld]

Your formula will only result in the number 1 if the status is published, so your automation will never trigger for fulfilled or promoting.

[Taylor_Olmstead]

If I add the other status (separated by commas) to the formula would that work?

[ScottWorld]

No, that wouldn’t work. Is this a single-select field? If so, you can use an OR statement like this:

IF(
AND(
OR(Status="Published",Status="Fulfilled",Status="Promoting"),
DATETIME_DIFF(TODAY(),{Status Modified Time},'days')>=30
),1
)