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.

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

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

Some very good points!

Thank you for all your help!