Valid if expression

AppSheet AppSheet Q&A
21

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

22

Please provide the exact expression after the swap.

23

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

2X_d_d7ec44f0a44d8de3cc0e4bb8450c8aaf17988952.png
2X_d_d7ec44f0a44d8de3cc0e4bb8450c8aaf17988952.png468×748 16.8 KB

2X_1_1ddd2a53f125ad552279112fc6deddfeffb07e23.png
2X_1_1ddd2a53f125ad552279112fc6deddfeffb07e23.png457×745 16.4 KB

With this Formula…

2X_9_9fe6c3e23c37fbbc3660ad316fc71d022225d337.png
2X_9_9fe6c3e23c37fbbc3660ad316fc71d022225d337.png1920×1080 95.1 KB

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

24

2X_8_88bfc5aa147ec0ac4ecddd458d489dda8500b620.png
2X_8_88bfc5aa147ec0ac4ecddd458d489dda8500b620.png459×738 15.4 KB

@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

25

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

26

2X_4_4b4aa5ebe229ee9ce5bbefb0e6f577b7a183f1c6.png
2X_4_4b4aa5ebe229ee9ce5bbefb0e6f577b7a183f1c6.png1009×817 49.6 KB

27

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

28

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

29

@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

30

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

31

Ok noted. Makes logical sense

32

@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

2X_6_61d5f8b9bcb97082e72ebc001853e5b82a660778.png
2X_6_61d5f8b9bcb97082e72ebc001853e5b82a660778.png974×611 41.5 KB

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

33

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.

34

@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

35

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.

36

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

37

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!

38

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