I’m posting this here to help anyone with this problem but I’ll make a Feature Request about it.

SkrOYC · 11-09-2021 08:03 AM

I’m posting this here to help anyone with this problem but I’ll make a Feature Request about it.

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.

I’ll give an example:

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()
   )
)

How this works

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

Just kidding (you should read those anyway)

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.

Marc_Dillon

A bit simpler, if you’re assuming the value isn’t going to be edited:

OR(
  IN( [key] , Table[Key] ) ,
  #original-valid_if-expression#
)

SkrOYC

Yep. This should solve the problem easier but with the IF() you could have separate Valid_If. One for Edit and another for Adds

alphacp

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().

SkrOYC

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.

MultiTech

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?

Edit If = IsBlank([_this])

Of course you’ll want to make a way for the user to be able to change things the first time

if you left if like that, as soon as they put a value there they wouldn’t be able to change it.

What I do nowadays is include a separate system column in each of my tables.

I call mine [Form_Type], and I use it as a place to put temporary system flags or variables that I may need.

In this case, I’d use an Initial Value for the [Form_Type] column, setting it to “New”
- I would then base the EditIf off the presence of that value inside the [Form_Type] column.
  [Form_Type] = "New"
Inside the form save event, I also include a data change action that clears the [Form_Type] when it saves
- This way the flag is there when the record is made, then it’s immediately cleared after it’s saved.

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.

Hence the name: [Form_Type]

By putting different flags inside this column, I can tell the system:
- We’re in a “New” form (meaning we’re entering data the first time)
- We’re in an “Edit” form
- We’re in a “Adjust_Client_Hours” form
And from these flags I can base all sorts of formulas (show if, edit if, valid if, etc.)
It’s just up to me (the developer) to manage setting/updating/clearing these flags)

Marc_Dillon

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.

SkrOYC

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

Fabian_Weller

I also prefer this one. Because you can localize the SAVE Button with CONTEXT():

Add Form: “Save”
Edit Form: “Update”

That’s more intuitive to the user.

SkrOYC

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

SkrOYC

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

MultiTech

(^_^)

Marc_Dillon

I was attempting to bait you into posting that. Success!

Different Valid_If for Adds and Edits

I’m posting this here to help anyone with this problem but I’ll make a Feature Request about it.

I’ll give an example:

What I do nowadays is include a separate system column in each of my tables.

Hence the name: [Form_Type]

(^_^)