I would like to implement a container number in the app sheet. Is it possible to validate based on the first 4 characters are alphabets and the next 7 characters numbers and shows that entry is invalid if it does not meet the requirement?
Yes. You would need to implement an expression in the Valid_If property of the column. AppSheet does not use mask strings for validation so you will need to manually “inspect” the input to determine its validity.
There are functions RIGHT() and LEFT() where you can get the substrings to test validity.
I’m not certain but I believe I’ve read that you can use the NUMBER() function to validate if the value is a number and even if its NOT a number.
Referenced by @WillowMobileSystems:
So long as the value isn’t zero to begin with, this approximates numeric validation:
(NUMBER(value) <> 0)
Hi, Thanks for answering earlier!
I have tried out the formula LEFT() and the expression is fine but it shows this error: ERROR INVALID CONSTRAINT instead once I saved it. Do you have any idea why?
Please provide a screenshot of the error message.
Unfortunately the expression will need to be much more complicated. So you’ll need to use an expression like the below. I have tested it and it works well.
- [_THIS] is a special variable that refers to the value, text in this case, of the current column.
- The MID() function picks off each single character by its position in the text.
- NUMBER() will return zero if the character is alphabetic.
- NUMBER() will also return zero if the value is number 0 so we explicitly do not allow zero in the first 4 positions but DO allow “0000000” for the last 7.
AND( LEN([_THIS]) = 11, AND(NUMBER(MID([_THIS],1,1))=0, MID([_THIS],1,1) <> "0"), AND(NUMBER(MID([_THIS],2,1))=0, MID([_THIS],2,1) <> "0"), AND(NUMBER(MID([_THIS],3,1))=0, MID([_THIS],3,1) <> "0"), AND(NUMBER(MID([_THIS],4,1))=0, MID([_THIS],4,1) <> "0"), OR(NUMBER(RIGHT([_THIS],7)) > 0, RIGHT([_THIS],7) = "0000000") )
I would also recommend including a custom error message for when the format is invalid. An example might be:
"Correct format is 'xxx0000000' - 11 characters with 4 letters followed by 7 numbers"
Customer Message Goes Here:
Message Shows like this
Yours is a nice, compact expression.
Just in case the first 4 alphabets need to exclude the special characters also , then following approach may be tried. The approach is made up of two columns
The valid_if expression in the container number column is as below
AND(LEN([_THIS])=11, IN(MID([_THIS],1,1),[VerifyList]), IN(MID([_THIS],2,1),[VerifyList]), IN(MID([_THIS],3,1),[VerifyList]), IN(MID([_THIS],4,1),[VerifyList]), NUMBER(RIGHT([_THIS],7))<>0)
Here the [Verifylist] is a list type VC with following expression. This VC works as alphabet validation reference for the valid_if expression mentioned above
Used string length validation expression suggested by you and numerical digits verification expression suggested by @steve ((NUMBER(value) <> 0))
Some very good points!
Thank you for all your help!