Solved: On How To Never Destroy Data - Page 2

Brian_Morgan · 08-15-2021 02:02 PM

I’ve been contracted to rapidly develop an app for medical services. Part of the requirement is for me to ensure that data is never destroyed in order for the app to be certified.

This is commonly implemented in the following manner:

Every table must include two columns: LifecycleStart and LifecycleEnd, each of type DateTime.
On a “Create record” operation, set [LifecycleStart]=NOW(), and set [LifecycleEnd]=MAXDATE.
On a “Delete record” operation, set [LifecycleEnd]=NOW().
On an “Update record” operation, do the equivalent of “Delete record” and then “Create record”.
Create a Slice with the following filter condition: AND(NOW() >= [LifecycleStart]), NOW() < [LifecycleEnd])

Conceptually it works for me. The only hurdle is how to override the default system-generated CRUD actions. I’ve read a few articles here at the forum that makes me believe that it is possible, but I must be doing something wrong.

Can anyone point me to a YouTube video that demonstrates how to do it? Absent that, any articles or posts that you would recommend?

I’m aware that I don’t need to override the “Add” action. All I need to do is set these initial values: LifecycleStart=NOW() and LifecycleEnd=MAXDATE. So, I just need to override the “Edit” and “Delete” actions.

My rant:

I also planned for the possibility that Lifecycle management is beyond AppSheet’s ability. I’ve made the client aware of possible shortcomings. They are onboard with contracting the development of a custom mobile app if AppSheet can’t meet this requirement. At least they rapidly get to market with my app, and then use it to demonstrate to a software shop exactly what they want. That saves them a lot of money. The downside is that AppSheet loses paying customers when the custom app replaces mine.

I’m aware that I could log out data changes, but I don’t fancy having to re-create the app state by rewinding the audit trail. I prefer to simply go to the Slice filter condition and replace NOW() with a specific date in the past.

Any feedback is greatly appreciated.

Thanks,
Brian

Brian_Morgan

Thanks Marc, your suggestion worked, but I had to be a little creative. I keep forgetting that the only state variables are those stored in columns. You’ll see my solution below:

First, I added a column called “Edit ID” in my “Activity” table. “Edit ID” is a placeholder for a new key that will be generated when I execute the “Lifecycle Create ID” action.

Now, my reworked “Lifecycle Edit” action executes the following actions:
“Lifecycle Create ID”
“Lifecycle Delete”.
“Lifecycle Create Copy”.
“Lifecycle Edit Copy”.

Here is what “Lifecycle Create ID” does:
Data: set the values of some columns in this row
Set [Edit ID]=UNIQUEID() for this row.

Here is what “Lifecycle Delete” does:
Data: set the values of some columns in this row
Set [LifecycleEnd]=NOW()

Here is what “Lifecycle Create Copy” does:
Data: add a new row to another table using values from this row
From/To table: Activity
Set ID=[Edit ID]
Set Activity name=[Activity name]
Set LifecycleStart=NOW()
Set LifecycleEnd=DATETIME(“12/31/2999 23:59:59”)

Here is what “Lifecycle Edit Copy” does:
App: go to another view within the app
LINKTOROW([Edit ID],“Activity_Form”)

Here is a screenshot demo:

View solution in original post