How can I lock a record once it's closed out?

Griff · 12-28-2020 06:28 AM

I know I can make a field not editable but is there an easy way to make the entire record un-editable once the user has marked it as [Closed Out]=true without having to conditionally lock each column?

Steve

AppSheet has no way to make individual rows strictly unmodifiable. The best you can do is configure the Only if this condition is true expression of the table’s Edit action.

Rafael_ANEIC-PY

Hello @Griff, it’s just as @Steve says, you could try to work around that by making slices of your table using the criteria [Closed Out]=true for the first slice and [Closed Out]=false for the second.

The first slice wouldn’t allow updates or adds, but the second one would, if you think having different views for each record status is a viable approach for the app design you have in mind, this may work for you.

Griff

Thanks Steve and Rafael. I would rather not have different views but I will give it some thought. I was expecting this would be a common problem and some common solution had been found.

OptimiX_XcrY

I use the different views with slices strategy, by making those views conditionally ShowIf displayed. The end user does not see the difference, they see one or the other,in the same place. It feels safer , not having to be careful with VC , or other settings and formulas

Griff

I tried a different approach that means the entire row remains locked even if I later change the columns without having to add column by column locks, but I need a final bit of assistance

What I did was add a column called [Last Edited] with an initial value of NOW() and Reset on Edit set to True.
The Valid if Constraint is: IF([_THISROW_BEFORE].[ClosedOut]=True,[_THISROW_BEFORE].[LastEdited]=[_THISROW_AFTER].[LastEdited],TRUE)

The Invalid Value error is “This Record is closed and Cannot be Edited”

The net result is that every time the record is updated the value of LastEdited changes automatically. But if you try to edit a record where [ClosedOut] is True, the app tries to reset [LastEdited] to NOW() but that fails the Valid If test so it displays the error message and prevents the user from saving any changes to the Record. I may change the formula to give the user a few minutes after closing out to make corrections.

However can anyone help me figure out either:
A) how to only display Last Edited IF it is in error OR;
B) Prevent users from editing the field - Usually I would simply hide a field if I didn’t want the initial value to be changed but in this case I need the error message to display

Steve

Use App formula rather than Initial value and Reset on edit?, or use Initial value and Reset on edit? and set Editable? to the expression, FALSE:

Griff

Oh, that’s easier than I thought.

The comments on the Editable? condition says “Can users (or automatic app formulas) modify data in this column?..” I thought that meant that if you set it to FALSE then the Reset on Edit and Initial Value options wouldn’t work because they are automatic formulas. Thanks

Initial value and Reset on edit? and set Editable? to the expression, FALSE : works. Why is the expression FALSE different to not ticked?

Steve

The short answer: it just is.

The long answer: back in the day, there were two distinct settings: READ-ONLY and Editable_If. READ-ONLY, if ON, prevented all modification to the column value, including by users, App formula, Initial value, and actions. If OFF, all non-user changes (i.e., all automated changes) were allowed, but user changes were subject to Editable_If. If Editable_If, an expression, was blank or evaluated to TRUE, the user could modify the column value; if the expression evaluated to FALSE, the user could not modify the value. More recently, these two separate settings were merged into the Editable? setting. Handling ON, OFF, and expression differently preserves the original semantics.

Editable? OFF: no changes allowed (READ-ONLY ON, Editable_If ignored).

Editable? ON: all changes allowed (READ-ONLY OFF, Editable_If blank).

Editable? set to an expression: automated changes allowed, user changes subject to expression result (READ-ONLY OFF, Editable_If not blank).

Note that changes by server-side actions (performed by workflows or reports, or through API calls) are treated as user edits, not as automated edits, and are therefore subject to the Editable? expression if set.

Marc_Dillon

Interesting.
Was there a particular reason that they combined the READ-ONLY switch into EDITABLE_IF? I quite often find myself entering FALSE into EDITABLE_IF, and am constantly annoyed by having to go into the expression assistant to do so, instead of just toggling an option.

Steve

I dunno. It just happened in an update of the app editor.

Griff

Thanks for both explanations. I’m surprised and amazed at myself in that the long explanation makes sense to me. Appreciate it.

Griff

I use the different views with slices strategy, by making those views conditionally ShowIf displayed. The end user does not see the difference,

That sounds like a good alternative depending on need. The reason I opted to use the VC is that the updateability is dynamic. Once the record has been marked as closed, it can still be seen, just not edited. But I can see the case for two slices is functional too