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”, “” )
May I ask why do you need to write a formula there? Why don’t you just type “No spaces,…666”?
@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
@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??
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.
@praveen ok noted
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.
@Aleksi_Alkio let me try that tonight and give feedback on the User Experience
@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)
Have you tried… AND(NOT(CONTAINS([_THIS]," "),LEN(TEXT([_THIS]))=10, ISNOTBLANK([_THIS])) with the Valid_If?
@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
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.
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.
@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.
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.
@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?
I want to show the same Invalid value error if:
The column is empty
OR
The value is not in a list
So I tried:
AND(
ISNOTBLANK([_THIS]),
IN([_THIS],Table[ID])
)
The list validation is working. But it does not validate if the column is blank.
I know I can use “Require”. And under Localize “This entry is required” I could enter the same message like I have in the ValidIf.
But I would like to handle it only with ValidIf.
Has anyone found a way?
Adding also @Steve
Hi @Fabian ,
I am sure @Steve will guide with more insights.
An almost identical requirement was discussed last year as below. @Steve had guided there as well on Valid_if for blanks- valid_if does not appear to be evaluating for blanks.
[Using the column Constraint](https://community.appsheet.com/t/using-the-column-constraint/35153) Questions
Im trying to ask the user to fill a text field if a set of given conditions. I was nor working properly for me, so I reduced the complexity of the expression to a very simple one to isolate the issue. I have defined it as follows: IF( ISBLANK([_THIS]), false, true) [image] When clicked on Save, the expected behaviour is: if the field is empty the message must be displayed and not saved. if not, the record must be saved Well, it saves allways the record. Seems that ISBLANK is providin…
Thank you so much @Suvrutt_Gurjar this is answering my question.
So I will enable “Required”.
Suvrutt_Gurjar:
valid_if does not appear to be evaluating for blanks.
Correct. If Required? is OFF and the column is blank, Valid If is not evaluated at all.