Solved: Data Validation for Text & Number Together

li_Hua · 04-23-2020 01:40 AM

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?

WillowMobileSys

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

View solution in original post

WillowMobileSys

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.

Steve

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

(NUMBER(value) <> 0)

Patrick_Paul

Hi, I am attempting to simply do input validation on a text column and this does not work. I put in the validation field: (NUMBER([_THIS]) <> 0) and it will allow me to put in any text I like...? Upon running a "test" it appears any input evaluates to true?

joseyeri

Hi Patrick. I used the following expression to prevent text input on a phone type column and it works for me:

1. My phone numbers must contains 10 digits.

2. My phone numbers can't contain letters or symbols.

3. This Validation is used when rows added.

AND(LEN([_THIS])=10,COUNT(EXTRACTNUMBERS([_THIS]))>0,COUNT(SPLIT([Phone_Number]," "))=1)

The_Ravals

Hi John,

While researching, I noticed you were able to solve an issue similar to mine, hence this message. In fact I have three separate issues, please guide me with a solution, or point me to the right person. I’m less than a month old non-techie in this AppSheet adventure, but do understand spreadsheets well: both Excel and Google Sheets.

Correct syntax for two Valid If conditions to validate correct input

I’ve used one Valid If condition in my App and it works fine. Now I need to add another Valid If condition that uses a calculated number from another sheet/table/cell. I know I’m supposed to use the AND in my validation condition, but I don’t know exactly how. BTW, I’m a non-technical newbie, so please bear with me, and make the answer simple to understand and implement! Many thanks!

In a Sign In Table, View type Form, Column Type SHOW, Type Details > Contents (Cell value in Text), tests okay but doesn’t appear on the screen of the App

For my AppSheet app, I’ve created a Sign In form where the top row is supposed to show Availability (a “Available/Busy, Please wait” value calculated by the underlying Google Sheet). The Column Type is SHOW. Under Type Details > Category, I’ve put the Page_Header, and on the Sign In screen of the App, it correctly shows “Availability” in big, bold fonts. However, under Type Details > Contents, I’ve put =[Availability] to insert the current value of Availability , which does not show up on the App screen. However, when I do Test from Expression Assistant, the Test Page DOES show the correct values for Availability! It just doesn’t appear on the App’s Sign In screen! What am I missing?

Send Email from Workflow tests okay, but not actually sending it

I want to send an automated Order Confirmation email to each customer when he places an order. I’ve been able to design an Order Report and it works fine from the Test > Send method, and I do get an email with a nicely formatted PDF attachment also. However, this is not happening automatically, as it is supposed to, when the customer syncs the app with all the items ordered after saving all the purchases in the App. So what am not doing right?

Any help will be greatly appreciated!! Thanks in advance!

Parag Raval

Steve

Please post a screenshot of the column’s configuration screen showing the topmost section and the Type details section.

Is the app deployed?

The_Ravals

Hi Steve,

Thanks for your prompt response.
I’m enclosing two screenshot images.
The one with the red “arrow” (the App Screen side) indicates where I’m expecting to see (but it shows nothing) the spreadsheet calculated Content value of the column [Availability], which is either “Available” or “Busy, Please wait”, next to the value for the Page_Header, which shows correctly as Availability, as expected.
The second page is just an enlargement of the left side of the screen.
Is this what you were expecting to see?

Many thanks for your help!

Parag

Steve

I don’t understand what those screenshots are showing. They appear corrupted?

The_Ravals

On the first one, there is some over-print in the upper left side of the screen. Please ignore that. The bottom left is what you were looking for and right side is the App Screen on a laptop screen.

I tried Ctrl + P three times but same thing happened each time!

Steve

Referenced by @WillowMobileSystems:

li_Hua

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?

Steve

Please provide a screenshot of the error message.

li_Hua

Here it is.

WillowMobileSys

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

Suvrutt_Gurjar

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

WillowMobileSys

Some very good points!

li_Hua

Thank you for all your help!

Maldo_Asociados

It would be very convenient to have an “input mask” option. Where you will filter the data capture. For example 3 numbers 4 letters and 3 numbers.
000LLLL000
Like Microsoft Access does.