Automatically update Status based on Date

GabrielBorges · 08-11-2021 06:01 AM

Hi,

I’m developing a delivery application that our deliveryman updates the status when he finishes a delivery, or when it is cancelled. In addition, we have the status “Postponed to tomorrow” which, for now, needs to be manually selected. I want the status in my app and in my googlesheet to update to “Postponed to tomorrow” automatically when the “Delivered date” column is greater than TODAY(). How can I do this?

I don’t know if it’s relevant to the situation, but my Data Table has a security filter to show only “TODAY()” data.

Thanks.

Chris_Woron

Hi Gabriel,

You’ll need to add an IF or IFS expression to your Status column’s formula. Obviously you have other status values but something like IF([Delivered date]>TODAY(),“Postponed to Tomorrow”,“Status 1”) where “Status 1” is another status that may apply.

I do this in one of my apps based on a variety of inputs to make the status automatically change. It can take some time to setup the logical statements as the complexity grows, but eliminating the input requirements for the status field are helpful in the long run. Generally, if users have to manually change the status field, it won’t get done reliably & it becomes useless.

WillowMobileSys

Does this mean it filters by [Delivered Date] = TODAY()? If so when would anyone see the status of “Postponed to tomorrow”?

When is [Delivered Date] set? More specifically, when is it set to be greater than TODAY()? You might be able to adjust the status when this date is modified. You can attach an action to a Form for additional activity to occur when an Edit is made.

For example, if you have an existing row and the [Delivered Date] is set to tomorrow, upon Save of that Form an action can be called that also adjusts the [Status] value.

WillowMobileSys

I believe this will work in cases where the status is ALWAYS automatically updated - which I believe a Status should be derived based on the state of the row. This expression could be placed in the App Formula - but this prevents users from manually setting a Status.

However, @GabrielBorges made this comment:

This means that the user needs to be able to choose the Status and now the app needs to, on occasion, be able to automatically adjust to “Postponed to tomorrow”. In this case, the automatic part can only happen with an attached action or some automation process. An attached action is best as it happens immediately in the current users app view.

GabrielBorges

[quote=“WillowMobileSystems, post:3, topic:52799”]
Does this mean it filters by [Delivered Date] = TODAY()? If so when would anyone see the status of “Postponed to tomorrow”?

We have a person at the company who is responsible for defining the delivery route using Google Sheets. The idea of "Postponed to tomorrow" is mainly for that person to know that an order was not delivered the day before, and that it should be allocated at some time (morning or afternoon) and for a driver on Google Sheets (where we do other sorts of control). An important factor is that the person responsible for defining the routes only uses Google Sheets, so he doesn’t open the app. What we need is for the status to update from “Open” to “Postponed to tomorrow” without any intervention in the app. I don’t know if that’s possible, but that’s why I’m here hahaha.

I tryed to use the Initial Value as “IF([Delivery Date]=TODAY();“Open”;“Postponed to Tomorrow”)”, and let the app user change to “Delivered” or “Canceled”, but it doesnt seem to update to “Postponed to Tomorrow” automatically.

I’m a new user, so what’s “Attached action” and where can I find it?

Chris_Woron

Thanks John, good point. Attached actions would work well for this for user input. I typically use them in conjunction with virtual columns and IFS statements to change the status as different conditions occur.

WillowMobileSys

Correct. In this case, additional info by the original poster further clarified the process. There would be no anticipated user activity in order to trigger an attached action. So, the only choice then is Automation run on some schedule - i.e. daily early morning for this one.

WillowMobileSys

These two statements confuse me a little bit. If an item was not delivered, how does the [Delivered Date] get set to a date that is greater than TODAY()?

Since this needs to happen without any intervention in the app, then Automation is your only choice. But how can you tell a row that needs to have the [Status] updated to “Postponed to tomorrow” versus a row that doesn’t yet need that update?

GabrielBorges

Sorry, it was a typo, it should be “Delivery Date” and not “Delivered Date”. Regarding the Delivery Date, we fill in this column with the current date, since our deliveries are usually made on the same day.

Regarding automation, I would need to relate the TODAY() function to the date we filled in the “Delivery Date” on Google Sheets. For example: Our driver will make 20 deliveries today (08/11). We’ve manually filled in today’s date in the “Delivered Date” column of Google Sheets. Tomorrow, deliveries with “Delivery Date” = 08/11 and Status = “Open”, will have their status changed to “Postponed to Tomorrow”. That’s the main idea.

WillowMobileSys

Ok, I see. Just to repeat back, Delivery Date is filled in as TODAY() for all deliveries EXPECTED to be delivered. When delivered, the driver will manually set the Status to Closed or some value to say its been completed.

The next day, anything left with a Deliver Date from yesterday and Status = OPEN should be set to “Postponed to Tomorrow”.

Yes, automation is the way to go here. But keep in mind that the Delivery Date will be YESTERDAY’s date. So you need to create a bot on your Deliveries table with the Filter Criteria as:

AND([Delivery Date] < TODAY(), [Status] = "Open")

Than add a Data Change task to set the [Status] = "Postponed to Tomorrow’.

I hope this helps. If you need more help just ask.

Lavanya

In status column,
IF(TODAY()<[Delivery Date],
[Status]=“Postponed to tomorrow”)