Data Validation for Text & Number Together

Hi,
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.

1 Like

Referenced by @WillowMobileSystems:



2 Likes

So long as the value isn’t zero to begin with, this approximates numeric validation:

(NUMBER(value) <> 0)
3 Likes

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.


Here it is.

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
Screen Shot 2020-04-25 at 8.18.00 AM

5 Likes

Hi @WillowMobileSystems,

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
LIST(β€œA”,β€œB”,β€œC”,β€œD”,β€œE”,β€œF”,β€œG”,β€œH”,β€œI”,β€œJ”,β€œK”,β€œL”,β€œM”,β€œN”, β€œO”,β€œP”,β€œQ”,β€œR”,β€œS”,β€œT”,β€œU”,β€œV”,β€œW”,β€œX”,β€œY”,β€œZ”)

Used string length validation expression suggested by you and numerical digits verification expression suggested by @steve ((NUMBER(value) <> 0))

4 Likes

Some very good points!

Thank you for all your help! :smiley:

2 Likes