On How To Never Destroy Data

[Brian_Morgan]

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:

1. Every table must include two columns: LifecycleStart and LifecycleEnd, each of type DateTime.
2. On a “Create record” operation, set [LifecycleStart]=NOW(), and set [LifecycleEnd]=MAXDATE.
3. On a “Delete record” operation, set [LifecycleEnd]=NOW().
4. On an “Update record” operation, do the equivalent of “Delete record” and then “Create record”.
5. 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

[Marc_Dillon]

Brian_Morgan:

The only hurdle is how to override the default system-generated CRUD actions.
…
I’m aware that I don’t need to override the “Add” action.

Hide the system-generated Delete and Edit Actions. Create your own Actions to replace them. Delete should be easy, just a data change. Edit should probably be a “Grouped” Action, with the same data change as Delete, then an “Add new row”.

---

As for the second portion of your post, I’m afraid I’m not even sure what you’re talking about.

[Brian_Morgan]

Thanks for your reply, Marc. Sorry about that second portion of my post. I was just expressing frustration that I don’t have greater control. “No Code” sounds great, but for a programmer it means “Handcuffs”.

I’ve been experimenting prior to discovering your reply. I ended up doing exactly as you suggested by creating custom Actions. Everything works great except the final step in my Edit Action. Let me explain what I’m doing:

I created an Action called “Lifecycle Edit”.
It executes a sequence of Actions:

1. “Lifecycle Delete”.
2. “Copy this row and edit the copy”.
3. “Promote Lifecycle to Current”.

I created an Action called “Lifecycle Delete”.
It sets [LifecycleEnd]=NOW() for this row.

I created an Action called “Copy this row and edit the copy”.
It executes “App: copy this row and edit the copy”.

I created an Action called “Promote Lifecycle to Current”.
It sets [LifecycleStart]=NOW() for this row
and [LifecycleEnd]=DATETIME(“12/31/2999 23:59:59”)

When I click the “Lifecycle Edit” button on the current row, everything works except the last step “Promote Lifecycle to Current”. I was expecting that the “current row” would point to the copied record from the previous step. However, it appears as if the current row is undefined.

Do you know if this is expected behavior? Perhaps I should put in a ticket?

Thanks,
Brian

[Lynn]

Hi @Brian_Morgan
I believe your sequence stops at the second action because this one involves a save and stops the process.
Added edit
You may need to use LINKTO FORM.

[Brian_Morgan]

Thank you, Lynn, I’ll give that a try.
Brian

[Marc_Dillon]

Brian_Morgan:

everything works except the last step

Lynn:

I believe your sequence stops at the second action because this one involves a save and stops the process.

Sort of. It is because the “copy and edit” Action is a navigation Action.

This is why I suggested the “Add new row” Action, in which you can specify values for the fields in the new row. You may also wish to add a LINKTOROW() navigation as a 3rd Action in the group, if the record needs edited any further by the user.

[Brian_Morgan · Accepted solution]

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:

[Brian_Morgan]

Marc and Lynn,

Thank you very much for your help, but I just came up with a scenario that can only be solved by the AppSheet core since it is aware of the schema. For example, say I have two related tables: Parent and Child. Suppose I create a Parent record: “Carol”. Carol has two children: “Amanda” and “Melissa”. Now let’s say that I misspelled the parent’s name. It is really “Carola”. So I edit the parent record and Bam! The two child records point to the old retired “Carol” record. Not good.

Like I said I think this behavior is best handled by the AppSheet team. Perhaps they might want to offer it in a future version. In the meantime, I’ll revert to Plan B, writing to an Audit log.

Thanks again for your help. I send you “Likes”!
Brian