valid if - Not able to edit and save the form

[jaichith]

Hi ,

I have used to valid if expression to avoid duplicates in the same column. which has created one more issue that it is allowing to create new entries but when the old entry form is edited and save button is hit, It does not allows to save it and displays valid if error . How to solve this ?

Valid if expression : NOT(IN([_THIS], SELECT(USER ROLE[USER ROLE],TRUE)))

[TeeSee1 · Accepted solution]

This is the valid if for [num_col] which is a simple number column in Table T3.

I have three rows with 2, 3, 4 in their respective [num_col] to start with.

cannot create a new row with 2 in [num_col]

With 1 (no duplicates) , it works.

new row created.

I can edit it and save it

Result.

Somehow you have different conditions than I have.

[TeeSee1]

TRY

NOT(IN([_THIS], SELECT(USER ROLE[USER ROLE], [key col] <> [_THISROW]))

[jaichith]

NOT WORKING, IT ALLOWS TO CREATE DUPLICATE VALUES

[TeeSee1 · Accepted solution]

This is the valid if for [num_col] which is a simple number column in Table T3.

I have three rows with 2, 3, 4 in their respective [num_col] to start with.

cannot create a new row with 2 in [num_col]

With 1 (no duplicates) , it works.

new row created.

I can edit it and save it

Result.

Somehow you have different conditions than I have.

[jaichith]

COULD YOU PLEASE FIGURE WHY IT IS NOT WORKING FOR ME ?

[TeeSee1]

I used the same expression in another field (type REF) and works exactly as expected - no duplicates, can edit.

Without more details of your table structures, I have no further clue why it is not working for you.

[Suvrutt_Gurjar]

Hi @jaichith ,

May I request you to evaluate @TeeSee1 ' s suggestion.

May I also request you to desist if possible from using all capitals in a response to another member?

I request you to browse articles about what it means to type a response all in capital letters(" All caps" as it is referred)

Best  wishes with your app creation.

[jaichith]

@Suvrutt_Gurjar 

I am sorry , My caps lock was on,  that I didn't notice properly. and I was in urgency to fix the expression error which has lead to the unnoticing of the All Caps writings . incident which had happened was not intentional . I apologize for it.  thanks for indicating the mistake.    

[jaichith · Accepted solution]

FINALLY THIS WORKS FOR ME .

NOT(
     IN([_THIS], SELECT(USER ROLE[USER ROLE],[_RowNumber]<>[_THISROW].[_RowNumber]))
)

BASED ON YOUR WORK @TeeSee1 .

1. TO EXCLUDE CURRENT COLUMN/CELL  VALUES I HAVE REFERRED  ROWNUMBER <> [THISROW].[ROWNUMBER] IN SELECT() FUNCTION.  

THE EXPRESSION IS IN TESTING LET SEE HOW IT WORKS. 

THIS IS COMPLETELY BECAUSE OF YOU @TeeSee1 . THANK YOU

[MultiTech]

DO NOT USE [_ROWNUMBER]!

• Use the ID column!

Habits are built by the individual one-by-one actions we take throughout our lives.  If you use [_rownumber] here, you're validating to yourself that this is a valid option - which means that next time around, your mind is more likely to go this route.  And if you do it again, then it's even easier for your mind to think this is okay... and easier and easier and easier - until this is the method that your mind goes to when you're thinking about something like this.

[_rownumber] is a very fragile thing to work with - it's not a set number, so depending on what you're doing it might not work.   It sounds like a good idea, and seems like it would be okay, and you might be right - but it's a bad idea to move yourself down a road that's slippery.

Praveen had a lot to say about this in the early days.  You might be able to find those posts - maybe.

Glad you got something working though!

[jaichith]

@MultiTech , @AleksiAlkio @Suvrutt_Gurjar @WillowMobileSys  PLEASE HELP ME THIS

[MultiTech]

Some thoughts:

1. Instead of using `[_this]` for the column, actually use the column [User_Role].  (Perhaps the use of [_this] is causing some problem.)
2. Instead of using just [_thisrow] to get the row ID - use [_thisrow].[ID_Column].  (It's more explicit) 
3. You need to conditionalize your formula.

Conditionalizing Your Valid If Formula

You need to conditionalize your formula so it has one criteria when you're making the record, and another when you're editing the record.

1. Right now the formula is rejecting your value because the 2nd time it's evaluated, the value that's inside the field is now inside the list of user roles - it wasn't the first time - so that's why it's rejected.
2. What you need is the criteria to apply when you're creating the record, but then "loosen up" after wards.

In order to make this happen, you need some way to differentiate the first time you're creating/editing the record vs. all the others.   

• In the Standard Starting Template I've got a column specifically for this purpose called "EditCount" - which counts the number of times the record has been edited.
    - Learn more here
• So with this, all I need to do is wrap my formula in something to check and see if the `[EditCount] <= 1
  • If it is, then we're creating the record (and therefor editing it for the first time); otherwise we're editing the record.

You can make your own EditCount column with the following steps:

1. Add the [EditCount] column to your table
2. Use the following column settings:
   • Show If: false/hidden
   • Type: ChangeCounter
   • Columns: none
   • Values: none
   • Update Mode: Accumulate

---------------------------------------------------------------------------------------------------------------------

Once you've got the edit counter in place, you can conditionalize your formula criteria based on this:

if([EditCount] <= 1, 
  NOT(IN([User_Role], Users[User_Role])),
true)

[jaichith]

Thanks for the work around @MultiTech .

The issue which I am facing is it bug in appsheet or my logical mistakes? Is there any other work around that helps to make it complete without using these valid if expression?

[jaichith]

this expression checks for duplicates in the [User_Role] column if [EditCount] is less than or equal to 1. If [EditCount] is greater than 1, it returns TRUE without checking for duplicates. Here I have doubt , whenever edit happens count will increase accordingly  if the count is more then it skips duplicate values checking due to IF statement. 

If the intention is to check for duplicates only when [EditCount] is less than or equal to 1, then this expression is correct. Otherwise, if I want to always check for duplicates I don't know still how could I achieve this.

Please validate. 

[MultiTech]

---

@TeeSee1 wrote:

NOT(IN([_THIS], SELECT(USER ROLE[USER ROLE], [key col] <> [_THISROW]))

---

To be clear: some version of this IS the answer in most instances - and likely the one you want to use here.

• If you're trying to enforce that a role value can only be entered once - and never re-used for another user - then this formula is what you'd want.
• You need to "pull all the values from the table, excluding the current record" - this way you get a fresh list of values from the table, but we exclude the current records' value from the list.

[MultiTech]

---

@jaichith wrote:

my logical mistake

---

This is a common pitfall.

[Ronma]

Make the USER ROLE column as Primary Key without using the Valid_If expression

[jaichith]

[USER ROLE] IS REF SO IF I MAKE IT AS KEY COLUMN THEN IT IS FREEZING THE FIELD/[COLUMN] IN FORM. THIS IS DUE TO KEY VALUE CANNOT BE EDITED IF THE FORM IS SAVED ?

[Suvrutt_Gurjar]

---

@MultiTech wrote:

You need to "pull all the values from the table, excluding the current record" - this way you get a fresh list of values from the table, but we exclude the current records' value from the list.

---

Yes, Matt has beautifully summed up the essence of the valid_if requirement. My personal favourite expression to avoid duplicates through valid_if is the expression mentioned at the end of the article referred below. I believe that expression was written by @Steve in the article. I find it compact, even though FILTER() is also a version of SELECT()

An image of relevant expression from the article

List expressions - AppSheet Help

@jaichith 

So in your particular case , the VALID_IF expression to avoid duplicates could be something like 

ISBLANK(
FILTER(
"USER ROLE",
([_THIS] = [USER ROLE])
)
- LIST([_THISROW])
)