Data type characters validation?

Lets say my form has three field

  1. Name

  2. Mobile Number

  3. Civil ID

  4. Now all 3 field are different in a way i want to limit the name characters to 10 Max with no special characters allowed to enter also no numbers

  5. Mobile number should be 10 digits no less no more, no special characters, no alphabets

  6. CIVIL ID can be alphanumeric but should be 13 digits exact any deviation from this formats it should not allow user to submit data

How to do this in AppSheet not able to find any solution to my problems also how to customize error message for each of this validationโ€ฆany help would be great

Solved Solved
0 27 6,205
1 ACCEPTED SOLUTION

Hi @techno_gateways,

Thank you for the update. Could you check the setting of 'Reset on edit" column?

Also could you please try settings as below?
Column Type: Phone as mentioned in earlier post. This willallow youto make the mobile number field callable.

Expression in valid_if setting

AND(NUMBER([_THIS])>=5555555555, NUMBER([_THIS])<=9999999999)

View solution in original post

27 REPLIES 27

Steve
Participant V

There is no good way to restrict characters in text values, through you can limit the length using Valid If or column properties:

2X_7_76b94a51654a9bc9829f065bd4008fb5fd2bf502.png

Well you can, but it would be a pain.

To specify validity for a phone number to make sure it doesnโ€™t contain dashes, spaces, or parentheses, you could do something like this (untested!)

valid_If = AND( NOT( CONTAIN([_THIS] , โ€œ-โ€) ) , NOT( CONTAIN([_THIS] , โ€œ(โ€) ) , NOT( CONTAIN([_THIS] , โ€œ)โ€) ) , NOT( CONTAIN([_THIS] , " ") ) )

Hi @techno_gateways,

For 10 digit numeric mobile phone numbers , you may wish to try valid_if expression of AND(NUMBER([_THIS])>=1111111111, NUMBER([_THIS])<=9999999999) with column type as phone

Testing showed that the expression invalidates all non numeric characters.-alphabets and special characters and any phone number less than 10 digits or more than 10 digits long.

this works for Aadhar number too!

I think the below is still better for checking 10 digit numbers. Please do test well per your requirement.
The above will fail for numbers below 1111111111 such as 1100000000

AND(NUMBER([_THIS])>=1000000000, NUMBER([_THIS])<=9999999999)

I am sorry. One correction in expression as below

AND(NUMBER([_THIS])>=1000000000, NUMBER([_THIS])<=9999999999)

Please note the change in lower limit. Realized that 1000000000 is the smallest 10 digit number and not 1111111111

Thank for all the reply will try them to be precise i already tried to achieve same with range but somehow did not work โ€ฆ Like Number should be > 5555555555 and < 9999999999

but as soon as i save its getting reset

Secondly for developers can it be added to wishlistโ€™s so in future u might add this features for AppSheet

Could you please mention what you mean by getting reset? Also iin which field you have entered these expressions. Screenshots of that field setting may probably help.

You may request the feature to be added in โ€œFeature Requestโ€

https://community.appsheet.com/c/requests

Could you please check teh setting for the field reset on edit? Is it off?

I have tried something like this which is not getting saved

Hi @techno_gateways,

Thank you for the update. Could you check the setting of 'Reset on edit" column?

Also could you please try settings as below?
Column Type: Phone as mentioned in earlier post. This willallow youto make the mobile number field callable.

Expression in valid_if setting

AND(NUMBER([_THIS])>=5555555555, NUMBER([_THIS])<=9999999999)

[quote=โ€œSuvrutt_Gurjar, post:9, topic:15568โ€][/quote]

Hi @Suvrutt_Gurjar.

To give me some flexibility, I have made a small modification to your validation to optionally include 10 digits number starting with 1 when appropriate. (Adding 1 before phone numbers outside your immediate area prevents you sometimes from being warned/notified by telephone company that you are making a long distance call.).

AND(NUMBER([_THIS])>=1111111111, NUMBER([_THIS])<=19999999999)

Thank you โ€ฆ i already got similar help from Alekshi (support team) also โ€ฆ my issue resolved now โ€ฆ thank you everyone who responded soo quickly โ€ฆ

The โ€œeasiestโ€ way to check that the value doesnโ€™t contain any letters or special characters is NUMBER([Column])>0. If the field contains any other than number, it should give a result as 0.

Hi @Aleksi,

Very compact. As usual an ace solution from you.

Youโ€™re welcome

Ami
Participant V

A follow up if i may,

I want my users to refrain from adding: spaces and special char(commas, dots etc.) in the end of a text they are inserting. When in excel, i can run Trim etc functions to detect that, is there a way in appsheet?

For instance: SOP for cleaning.
since there is a dot after cleaning, i want an error to appear for the user.

@1111
You can use an expression like this:

AND(
   NOT(RIGHT([ColumnA],1)="."),
   NOT(RIGHT([ColumnA],1)=","),
   NOT(RIGHT([ColumnA],1)=":"),
   NOT(RIGHT([ColumnA],1)=";"),
   NOT(RIGHT([ColumnA],1)="-"),
   NOT(RIGHT([ColumnA],1)="_"),
)

Or:

NOT(CONTAINS(".,:;-_", RIGHT([ColumnA], 1)))

I want to enter the โ€œSP66โ€ text field. How do I confirm the use of โ€œSPโ€ so that nothing but two letters and numbers can be used?

This?

AND(
  STARTSWITH([column], "SP"),
  (LEN([column]) > 2),
  OR(
    ISBLANK(SUBSTITUTE(RIGHT([column], (LEN([column]) - 2)), "0", ""))
    (NUMBER(RIGHT([column], (LEN([column]) - 2))) > 0)
  )
)

If this is used for data validation in the same field, is it correct to use [_this] instead of [coloumn]?

You may use [_THIS], yes.

great. its working.

Thanks, sorry it took me time to see this.
Do i write this in the valid_if?

@1111
As itโ€™s validation expression that will evaluate to TRUE/FALSE, yes you need to use Valid_if

 

This model can assist in validating complex code.
Character 1, only letters.
Character 2, only vowels.
Character 3 and 4 only letters.
Catarer 5 to 10, only numbers.
Character 11 to 13 numbers and letters
Example
CAMR770509MH2
in Valid If 
AND(
LEN([_THIS]) = 13,
FIND(UPPER(MID([_THIS],1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,
FIND(UPPER(MID([_THIS],2,1)),"AEIOU")>0,
FIND(UPPER(MID([_THIS],3,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,
FIND(UPPER(MID([_THIS],4,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")>0,
OR(NUMBER(MID([_THIS],5,6)) > 0, MID([_THIS],5,6) = "000000"),
FIND(UPPER(MID([_THIS],11,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")>0,
FIND(UPPER(MID([_THIS],12,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")>0,
FIND(UPPER(MID([_THIS],13,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")>0
)

 

Top Labels in this Space