Set Duration when Status Changed

[jinhaninfotree]

Hi,

I have a survey form that asks about the customer experience after the ticket has been "completed" or "rejected". I want to establish a duration for when a ticket's status changes to "completed" or "rejected". Users must complete the form before the time limit expires, otherwise the system will cease accepting form submissions.

Is this condition feasible in AppSheet? Is there any way to achieve it?

[TeeSee1 · Accepted solution]

Here is an idea you can evaluate to see if it suits your needs.

Create a column in the TICKETS table that records the time when a ticket changes to either completed or rejected. You can do this either by the Save event on the form or by a bot.

Create a column in the SURVEYS table and set the following in its App Formula

IF(
 ( NOW() - [ref to ticket].[time changed] )  < "duration you want", "Changeable",
 "Passed Deadline"
)

 and set the following in its Valid-If

LIST("Changeable")

This unfortunately is not perfect meaning a survey form opened before deadline can still be saved....

If you simply want to exclude or invalidate any surveys submitted after deadline, you can create a change time column in SURVEYS, compare that with the ticket's change time and exclude it from your scoring.

You can optionally create a slice on TICKETS with a filtering condition similar to

( NOW() - [time changed] ) < "duration you want"

 so only the tickets that you can submit surveys to are shown.

Maybe someone else has a better idea to do all this.

[TeeSee1 · Accepted solution]

Here is an idea you can evaluate to see if it suits your needs.

Create a column in the TICKETS table that records the time when a ticket changes to either completed or rejected. You can do this either by the Save event on the form or by a bot.

Create a column in the SURVEYS table and set the following in its App Formula

IF(
 ( NOW() - [ref to ticket].[time changed] )  < "duration you want", "Changeable",
 "Passed Deadline"
)

 and set the following in its Valid-If

LIST("Changeable")

This unfortunately is not perfect meaning a survey form opened before deadline can still be saved....

If you simply want to exclude or invalidate any surveys submitted after deadline, you can create a change time column in SURVEYS, compare that with the ticket's change time and exclude it from your scoring.

You can optionally create a slice on TICKETS with a filtering condition similar to

( NOW() - [time changed] ) < "duration you want"

 so only the tickets that you can submit surveys to are shown.

Maybe someone else has a better idea to do all this.

[Aurelien · Accepted solution]

Hi @jinhaninfotree 

I would use the ChangeTimeStamp type.

with this setting:

I just ran a test to make sure it works as expected.

Please note that if the status goes back to pending for example, the value stored won't be removed.

I would suggest to have one column for each status to track, such as [changetimestamp_rejected], [changetimestamp_completed], and so on.

Finally, you can use a simple calculation or a bot as described by @TeeSee1. to switch status to "passed deadline".

For reference:

Track changes using Change column types - AppSheet Help