Automate record status based on dates

[ta2530]

Hi there - I am having trouble creating automations to change the status of a single select field based on start and end dates.

Example 1 - I need the status to change from "Scheduled" to "Live" once a start date has passed. As in, if a class has a start date of 1/5/24, then this record would switch from "Scheduled" to "Live" once 1/5/24 begins.

Example 2 - I need the status to change from "Live" to "Completed" once the end date of the class has passed. I set the automation trigger for "When a record matches conditions" If END DATE is before today. The action step is "Update record" but this is where I believe I'm having trouble getting the automation to read the entire table and make all applicable changes.

Attaching screenshots of what I tried based on END Date, but it did not work. Any help is appreciated.

[simmonkc]

When you test the steps in the automation, do they show that they're working?

If the automation is working on new records, but not past records, I would select the entire END DATE row, delete all the data... then CTRL + Z... that should cause the automation to check every row.

[dilipborad]

Hello @ta2530 

There are 2 options you can use.

Formula option updates automatically based on current date but Automation option is works only based on Start Date and End Date are changed.

1) Using Formula

Based on Start Date and End Date I've used 3 statuses called "Past", "Future" and "Live".

IF(IS_BEFORE({End Date}, TODAY()), 'Past', IF(AND(IS_BEFORE(TODAY(), {End Date}), IS_BEFORE({Start Date}, TODAY())), 'Live', IF(IS_AFTER({Start Date}, TODAY()), 'Future', '')))

 2) Using Automation

Step-1 : Set Automation for Start Date and End Date when a record is updated in a table.

Step-2 : Then use Script block and use startDate and endDate as Input Variables.

Use the following script.

function getEventStatus(startDate, endDate) {
    const today = new Date();
    startDate = new Date(startDate);
    endDate = new Date(endDate);
    if (endDate < today) {
        return 'Past';
    } else if (today >= startDate && today <= endDate) {
        return 'Live';
    } else if (startDate > today) {
        return 'Future';
    } else {
        return '';
    }
}
let config = input.config();
let startDate = config.startDate;
let endDate = config.endDate;
const eventStatus = getEventStatus(startDate, endDate);
output.set("status",eventStatus);

 Step-3 : Use Record Update Step, Select Record ID from Step-1, then for the field update use Status Value(Dynamic selection) from output of the Step-2.

I hope this helps.

👍

[ta2530]

Thank you for your response! 

I love the simplicity of the forumla as I am very unfamiliar with Scripts. My only issue is that I wouldn't be able to manually change the status in a formulated field and I would need to do that as well.

Example - if I have a class that is scheduled but then becomes cancelled, I need to keep a record of when the original scheduled dates were & ensure the status reads as Cancelled and doesnt switch to "Live" or "Past".

Status' needed in my case are Scheduled, Live, Completed, Cancelled.

Any further suggestions?