Why would the ISBLANK not validate if I use t...

(Henry Scott) #1

Why would the ISBLANK not validate if I use this in a Error_Message_If_Invalid for a Phone Number? It allows the field to be empty The other parts of the formula of CONTAINS and the LEN of TEXT does validate

=IF(OR(CONTAINS([_THIS], " "), LEN(TEXT([_THIS])) <> 10, ISBLANK([_THIS])), “No Spaces, Phone Number Length should be = 10, EXAMPLE 0821234567 OR 0125556666”, “” )

(Aleksi Alkio) #2

May I ask why do you need to write a formula there? Why don’t you just type “No spaces,…666”?

(Henry Scott) #3

@Aleksi_Alkio because I do not want the Users to capture “invalid” Phone numbers, meaning I do not want spaces in the phone number and I do not want the phone number to be shorter or longer than 10 numbers. I want to at the same time while doing those validations also want to check if it is left Blank as it is Required, but I want it to display as such as they Click to the next Field and not only when they Save

(Henry Scott) #4

@praveen your statement:

The odd thing is — if you have a blank entry, then LEN(TEXT([_THIS])) <> 10 will also be true : does not catch the Blank either, surely if it is Blank then Blank should equate to

<>10??

(Praveen Seshadri (AppSheet)) #5

I think if it is blank, the Valid_If is just not being evaluated. I’d recommend marking it as Required and that is the simplest solution. Required inputs are also marked with a star.

(Henry Scott) #6

@praveen ok noted

(Aleksi Alkio) #7

One workaround is if you add a virtual column which will check the status and give you a note if it’s blank. You can write the formula like… IFS(ISBLANK([Phone Number]),“Phone number can’t be blank”). You can do the same with the Show/Text field.

(Henry Scott) #8

@Aleksi_Alkio let me try that tonight and give feedback on the User Experience

(Henry Scott) #9

@Aleksi_Alkio The Valid_If is not Validating

the ISBLANK([_THIS]) of the formula. This is actually my question

=IF(OR(CONTAINS([_THIS], " "), LEN(TEXT([_THIS])) <> 10, ISBLANK([_THIS])), FALSE, TRUE)

(Aleksi Alkio) #10

Have you tried… AND(NOT(CONTAINS([_THIS]," "),LEN(TEXT([_THIS]))=10, ISNOTBLANK([_THIS])) with the Valid_If?

(Henry Scott) #11

@Aleksi_Alkio it still does not Validate the Phone number when left Blank and let the user continue to the Next Field.

It does validate the Spaces in the Phone number and it does Validate the length

(Aleksi Alkio) #12

That’s actually true. We are not very good to handle a blank value. When the column’s value is blank, ISBLANK or ISNOTBLANK is giving the same result as true. That’s why it won’t validate.

(Praveen Seshadri (AppSheet)) #13

I’m confused by this thread. @Henry_Scott, when you say “wont Validate”, I’m assuming the expression tester is validating the expression fine, but you mean when running the app, the Valid_If condition with ISBLANK([_THIS]) succeeds? or it fails? The odd thing is — if you have a blank entry, then LEN(TEXT([_THIS])) <> 10 will also be true, so you’d never even get to the ISBLANK() part of the OR clause.

And @Aleksi_Alkio I didn’t understand your comment either :]. When a column value is blank, ISBLANK([_THIS]) will return true but ISNOTBLANK([_THIS]) will definitely return false.

(Aleksi Alkio) #14

@praveen If you type either ISBLANK([_THIS]) or ISNOTBLANK([_THIS]) into to Valid_If and you left the column as blank, you can save the record in both cases.

(Praveen Seshadri (AppSheet)) #15

Ah got it. This is because when a field isn’t marked as Required, then it means we are willing to tolerate an unassigned (i.e. blank) value in it. Also, every new row starts out with blank values and we don’t want to go mark all of them as invalid right upfront. So we treat a blank value specially when it comes to whether or not to check the Valid_If condition. Adding @Adam_Stone_AppSheet to confirm this behavior.

(Henry Scott) #16

@praveen @Aleksi_Alkio. I have marked the Field as Required and it catches if it is Blank on the Save, but the behaviour I explained above on the

Valid_If when you Click to the next Field

when I have the Formula above with the ISBLANK in the Valid_If does not display the

Error_Message_If_Invalid. So when Adam Stone confirms this what would the solution be? Or is it a case of not possible and catch it on the Save with the Required setting?