Can you use a switch statement on a workflow rule?

[Tom_Cat]

Hi,
So I’m tracking down a product as it goes from “order received” to “shipped” with various in-between step processes. I have on my spreadsheet an enum column that has the [Status] and an additional hidden column for each of the possible [Status]. I want to have a workflow rule that sets the date of when that status was achieved:
For example, if I receive an order and input it in the system it automatically starts as “order received” and the date is inputed as today. Whenever a user changes status from “received” to “assembling” I want the [Assembling] column to have a timestamp on when it was set to that status.
My workflow would be something like
TRIGGER ON UPDATE ONLY:
and it would be a statement something like this (note this will be in another language but i hope you can help me translate this into appsheet:
Switch([Status])
Case: Received
{ set [Received] = TODAY()}
Case: Assembling
{ set [Assembling] = TODAY()}
Case: Status3
{set [Status3] = TODAY()}

and so on for all he possible status

[Suvrutt_Gurjar · Accepted solution]

Hi @Tom_Cat,

You may wish to try following approach if I have clearly understood your requirement.

In each of those hidden time stamp columns relates to each status, please have an expression such as below

A) In [Receiving] hidden column of DateTime type 's “Initial value” setting

IF([Status]=“Receiving”,NOW(), “”)
In Reset on edit? setting of the same [Receiving] column
ISBLANK([_THIS])

B) In [Assembling] hidden column of DateTime type 's “Initial value” setting
IF([Status]=“Assembling”,NOW(), “”)
In Reset on edit? setting of the same Assembling] column
ISBLANK([_THIS])

Please repeat this for all hidden time stamp columns related to each status. If you wish to have the hidden columns as “Date” type rather than “DateTime” type please use TODAY() in place of NOW()
Advantage of using “DateTime” is testing becomes easier as you get different datetime stamps within just 10minutes of testing. For date type, you may need to change your device date to carry out testing.
Hope this helps.

[Tom_Cat]

Hi Suvrutt,
While your formula works, the “IF FALSE” statements makes it so that the date gets deleted when it is updated.
So if I stated that the date for changing from “Building” to “finished” is 1/09/2019 but then I change it to “Shipped” on 1/10/2019, the finished date is overwritten by the FALSE statement to “” and therefore deletes the record.

EDIT: NEVERMIND, I MISSED THE SECOND FORMULA FOR THE RESET ON EDIT