You have made a really good and strong Valid_if expression so that just certain criteria should be checked before saving a new record.
It works great for that porpuse!
BUT, then you realize that you canโt successfully edit the same record if the criteria is not made.
You have a
Date
column and you want the users to be able to just add records that are between 5 days ago and today. So you have an expression like this one:AND( [_THIS]>(TODAY()-5), [_THIS]<=TODAY() )
The problem is that, if you are going to edit the record, the Valid_If is going to check the condition again. If the date that is in the record is more than 5 days ago you wonโt be able to save that record.
This is not intuitive at all to new users as it wasnโt for me at first.
To fix this, I havenโt found any post talking directly about it, just similar things but not around edit problems on Valid_If that are related to this detail.
Without further ado, letโs fix it (there may be better ways):
IF( IN( [_THISROW].[KEYCOLUMN], TABLENAME[KEYCOLUMN] ), [_THISROW_BEFORE].[_THIS]=[_THIS], /*I'm just using this as an example, use a better expression*/ AND( [_THIS]>(TODAY()-5), [_THIS]<=TODAY() ) )
Read this:
IF() | AppSheet Help Center
IN() | AppSheet Help Center
Valid_If Column Constraint | AppSheet Help Center
Accessing column values before and after an update | AppSheet Help Center
The IF()
function checks if this is an edit or an add. It does by using the IN()
function.
IN()
checks if the KEY column of the current record is already on the table or not. If it is there, itโs an old record being edited. If itโs not (because you have to save a new record to add it to the table) itโs a new one, an add.
Make sure to use a robust Initial Value expression for your key column. This is always important.
UNIQUEID()
works perfect, donโt mess it up.
Then if the IN()
returns true (the record is an old one / already in the table / edit, not add) you can make a Valid_If expression that suits your needs for Edit. Like making sure itโs not changed: [_THISROW_BEFORE].[_THIS]=[_THIS]
(you should make an editable_if expression though, but thatโs another thing).
If the IN()
returns false (the record is an new one / itโs not in the table / add, not edit) you can make the Valid_If expression that was in the beginning, the one that checks for a day between 5 days ago and today.
As you can see, you can make Valid_if smart enough to identify if itโs a new record being added or an old one being edited. You could create any custom expression for each case.
A bit simpler, if youโre assuming the value isnโt going to be edited:
OR(
IN( [key] , Table[Key] ) ,
#original-valid_if-expression#
)
Yep. This should solve the problem easier but with the IF()
you could have separate Valid_If. One for Edit and another for Adds
Unless I am mistaken (which is quite possible), the challenge here is that the date is only validated when you first save the record, after that you are fixing the value. This means that you cannot change you mind and edit the date even if the new date would pass the original validation.
Personally I would add a new field to store the date the record in first created (initial value = TODAY() and hidden) and then use the validation based upon that date rather than TODAY().
Actually itโs not about dates specifically, Itโs the fact that a Valid_If is rechecked everytime you open a form that has the column/field with the Valid_If expression.
This is why I have struggled with Valid_If myself and love Suggested Values instead when it comes to lists.
This topic touch into the fact that we need different Valid_If. One for the Add and another for the Edit. You could even add a condition to check the form view name as @MultiTech_Visions said.
About the fact that a Date column should be modified or not, I have an App where there is a supervisor that can change almost any field she wants from the expenses the workers add into the app. They take a picture of the doc that shows the expense. They make mistakes now and then and the supervisor can and needs to modify that.
Seeing as how youโre wanting to enforce that the number NOT change after itโs been set, why not mark the column as Un-editable after itโs been filled?
IsBlank([_this])
Of course youโll want to make a way for the user to be able to change things the first time
I call mine [Form_Type], and I use it as a place to put temporary system flags or variables that I may need.
[Form_Type] = "New"
This sort of setup allows me to use the presence of that flag for things I need to do the 1st-time someone is entering the form.
Iโve typically taken a bit different route. I make 2 separate Form Views, one for new records, and one for editing, each with different โColumn Orderโ settings. And if I need to display a value in one, but not be editable, I use CONTEXT("View")
in the Editable_If.
Unless Iโm mistaken, this also gets around the issue of the OP, in that a validity rule isnโt going to be re-evaluated if that column isnโt even part of the Form View.
Different solutions for different cases, the choice is yours.
If the column/field is not in the form, it wonโt be re-evaluated.
If it is, even though is uneditable, it will be re-evaluated, unabling the user to save the record.
That is annoying because a field that is not even editable and has the same value as before is not allowed to be that way because by the time the edit is being made the valid_if may not be aplicable. Thatโs why I think we need different valid_if, one for edits, another one for adds, right there in the editor. Iโm making the feature request right now
I also prefer this one. Because you can localize the SAVE Button with CONTEXT():
Thatโs more intuitive to the user.
Sounds good. Also because this concept + the thing mentioned on the OP would be even more useful and easier to configure. For example, instead of the IN()
expression we could use CONTEXT()
and have a Valid_if for different views
Yeah, I get it. In this case is not about that, as I told Colin.
To prevent user interaction I would use an Editable_If expression instead of the [_THISROW_BEFORE].[_THIS]=[_THIS]
that I used in the first post.
For Editable_If that allows user interaction to add and not to edit I prefer something similar to the one in the first post:
NOT(
IN(
[_THISROW].[KEYCOLUMN],
TABLENAME[KEYCOLUMN]
)
)
Essentially allowing input/edit just if this row is not added yet to the table.
PD: I changed the title of this topic since it was not so clear I think
I was attempting to bait you into posting that. Success!