Valid if expression

[James_Fussell1]

I am using a Valid If expression to flag up a duplicate entry which works great.

NOT(IN([PONumber], SELECT(Customer Orders[PONumber], [CustName]=[_THISROW].[CustName])))

The problem I have is if I go back and need to edit the row, the duplicate warning flags up and prevents me from saving any changes. What am I missing?

[Aleksi]

Your formula will work only when you add a new record. When you are modifing it, you need to add the existing value as well.

[Henry_Scott]

@Aleksi how would one achieve that? Is there a “state” in Appsheet to know if it is a New Record or a Record being modified? - “Your formula will work only when you add a new record. When you are modifing it, you need to add the existing value as well.” @MultiTech_Visions
I have the same challenge as what @James_Fussell1 had originally. On New Record the Duplicate validation work and if it is not a Duplicate then it Saves the Record. The challenge comes in that when you Modify the Record by editing one of the fields where a Duplicate Test is not required, then one cannot Save the Record, because the Original Field is complete and thus fails the Duplicate validation

[Steve]

This is the pattern I prefer:

ISBLANK(
  FILTER(
    "MyTable",
    ([_THISROW].[MyColumn] = [MyColumn])
  )
  - LIST([_THISROW])
)

1. FILTER("MyTable", ...) finds all of the rows in the MyTable table that match the given criteria (...; see (2)).

2. ([_THISROW].[MyColumn] = [MyColumn]) matches rows where the MyColumn column value matches the MyColumn column value of the form.

3. ... - LIST([_THISROW]) removes the row currently in the form from the list of rows found by the FILTER() in (1).

4. ISBLANK(...) asks, “is the list of rows produced by (3) empty?”

(1) finds all rows that duplicate the current form’s value, possibly including the form row if it had previously been saved. (3) removes the form row from this list, producing a list of rows other than the form row that have the duplicate value. (4) the confirms that there are no other rows with duplicate values.

[Henry_Scott]

@Steve - thanks let me try this

[Henry_Scott]

Steve:

ISBLANK( FILTER( “MyTable”, ([_THISROW].[MyColumn] = [MyColumn]) ) - LIST([_THISROW]) )

@Steve my formula does not work using your example.
It works on New Record being created, but…
If I go back into the record and Modify other fields then I get the error that the duplicate field exists.
Here is my edited formula

ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)

• LIST([_THISROW])
  )

I have this in the VisitorIDNumber’s Valid_If

[Steve]

Is VisitorIDNumber the table’s key column?

[Henry_Scott]

Nope I have another Column as Key that is hidden
VisitorIDNumer is a Customer Number

[Steve]

Please provide a screenshot of the complete expression as displayed in Expression Assistant, and of the app with the error displayed.

[Prodragus_Gamer]

Been looking for this everywhere! My god! And it’s been a long time since I have been looking for an answer like this. Never found out how to do it until now. Brilliant!

[James_Fussell1]

OK. Is there a a formula that will work only when creating a new record? I am just needing something to prevent duplicate orders being entered. I use a UniqueID() as my key.

Any suggestions?

[MultiTech]

You can include a condition to exclude the record you’re looking at:

NOT(
  IN([PONumber], 
    SELECT(Customer Orders[PONumber], and(
      [CustName]=[_THISROW].[CustName], 
      [PONumber] <> [_thisrow].[PONumber]
    ))
  )
)

help.appsheet.com

List Expressions and Aggregates

List expressions use operators that result in a list or numeric value.

[James_Fussell1]

That works. Thanks. I am now needing the same thing, but only showing a duplicate warning, but still allows the record to be saved.

[MultiTech]

Okay, instead of using that formula in the valid if, where it will restrict the field from creating duplicates, move it to the Description - or create a Show (text) column - and create some warning that the user can see.

A combination of IF() and concatenate() like:

if(IN([PONumber], 
    SELECT(Customer Orders[PONumber], [CustName]=[_THISROW].[CustName])
  ),
Concatenate("PO Number (WARNING: POnumber (", [PONumber], ") has already been created.)"), 
"PO Number"
)

[James_Fussell1]

Thanks MultiTech_Visions, that works just great.

[MultiTech]

Any time.

[Henry_Scott]

@Steve - see attached Screenshots. I have an additional Validation on VisitorIDNumber in addition to the Duplicate Check

[Steve]

Missing a minus sign:

[Henry_Scott]

@Steve , thanks I removed the extra comma and added the “-” minus sign. Seems to be working

On my “Invalid Value Error” formula the formula Error Messages I had to change around to evaluate and display as per the scenario, but the messages does not relate to the formulas
IF(
IF([VisitorIDNumberIndicator] = “SA ID”, IF(10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, FALSE), [_THIS])
,
"This ID Number ALREADY EXISTS"
,
IF(ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
-LIST([_THISROW])
)
,
"The SA ID Number is NOT VALID"
,

[VisitorIDNumber]
)
)

So “This ID Number ALREADY EXISTS” should be with “your” formula and “The SA ID Number is NOT VALID” should be with the top part of the validation formula

[Steve]

It appears all you need to do is swap the text of those two messages.

[Henry_Scott]

@Steve, that is what I thought and I did but then the message displays incorrectly related to the logic of the validation

[Steve]

Please provide the exact expression after the swap.

[Henry_Scott]

@Steve. Apologies for the late reply. See attached Screenshot.

With this Formula…

As you can see the messages does not relate to the logic of the formula because the VALID ID is the one ending with 083 and does exist. The ID ending with 081 is the Invalid one actually.

I will do the Swap at the end of our working day so that the system still function while we are using it while we have Visitors.
Thanks for the help so far

[Henry_Scott]

@Steve. I switched the message around and it is still not displaying the correct error message.
In the Screenshot the error that is displayed is that the “ID Number ALREADY EXISTS”, but the value in the Field I captured to proof my point is that of a Mobile Number and the error message that should be displayed is "ID Number is NOT VALID

[Steve]

Please post a screenshot of the expression that doesn’t work.

[Henry_Scott]

[Steve]

Whoops! My bad! Change ISBLANK() to ISNOTBLANK().

[Henry_Scott]

@Steve. Damn I missed it as well
Thanks for your help with all of this and the other formulas. Your help is always appreciated

[Steve]

Also, an extraneous comma:

[Henry_Scott]

@Steve, does it mean I have to also change ISBLANk in the Valid_IF also to ISNOTBANK?
So not just in the “Invalid value error” section

[Steve]

No: the value is valid if the FILTER() expression returns a blank list (ISBLANK()), meaning there are no other rows with the same value.

[Henry_Scott]

Ok noted. Makes logical sense

[Henry_Scott]

@Steve, me again.
I want to also include the following into the 2nd part of the Validation Error where I used your code to test for Duplicates…IF([VisitorIDNumberIndicator] = “SA ID”
I use that in the first part of the validation Error.
How would I do that.
Here is the Screenshot of the current formula

BTW is the FALSE statement at the end before all IF STatements are terminated the way to do it if one wnats the formula not to validate? :-0

I Tried below, but Seems to not be working:
IF(
[VisitorIDNumberIndicator] = “SA ID”,
IFS(
IF(
10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, “ID Number is INVALID”),
IF(ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
– LIST([_THISROW])
)
,TRUE
,“ID Number ALREADY EXIST”
)
)
,
FALSE
)

My CURRENT VALID_IF formula is below as a guide. I want to introduce the Validation Messages as above in the Validation Error, but could not find a way to do that using the Copy and Paste of the VALID_IF as the …IF([VisitorIDNumberIndicator] = “SA ID” is already testing both conditions and I wanted to use it as-is

IF(
[VisitorIDNumberIndicator] = “SA ID”,
AND(
IF(
10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, FALSE),
ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
– LIST([_THISROW])
)
)
,
[_THIS]
)

[Steve]

I’ve put off addressing this…

Both of your expressions are poorly constructed and confusing, making troubleshooting difficult. Please try this Valid if expression:

IFS(
  (“SA ID” <> [VisitorIDNumberIndicator]),
    TRUE,
  ISNOTBLANK(
    FILTER(
      “Visitor”,
      ([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
    )
    - LIST([_THISROW])
  ),
    FALSE,
  (
    (10 - NUMBER(RIGHT((([IDNumberValidateOne] + [IDNumberValidateThree]) & ""), 1)))
    <> RIGHT([_THIS], 1)
  ),
    FALSE,
  TRUE,
    TRUE
)

and this as the Invalid value error expression:

IFS(
  (“SA ID” <> [VisitorIDNumberIndicator]),
    "",
  ISNOTBLANK(
    FILTER(
      “Visitor”,
      ([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
    )
    - LIST([_THISROW])
  ),
    "This ID Number ALREADY EXISTS",
  (
    (10 - NUMBER(RIGHT((([IDNumberValidateOne] + [IDNumberValidateThree]) & ""), 1)))
    <> RIGHT([_THIS], 1)
  ),
    "The SA ID Number is NOT VALID",
  TRUE,
    ""
)

Note that the two expressions are the same except the first produces TRUE/FALSE results and the second produces error messages.

[Henry_Scott]

@Steve. As I said I am a power user and not a developer Getting into Appsheet type development at a ripe age of 53 Thank you always for your guidance on the forum. I always check out posts where you respond to other people’s problems. I learn a lot from that and from your assistance on this abortion of code that I tried. The assistance is much appreciated

[Steve]

Not trying to be critical, @Henry_Scott. Just ready to move on to other problems, so thought I’d give you working code. Still happy to help you tweak it further if needed.

[Henry_Scott]

@Steve. I know. I welcome critique anyway. Only way I learn and improve.
Thanks for the code again

[Sebastian_Munoz]

Hi I want to undestand if this solve the problem. That when you are creating a new record see if there is the value in the base and if you are only editing an existing record dont check any of this