Data type characters validation?

[anonymous]

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

[Steve]

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

[Marc_Dillon]

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] , " ") ) )

[Suvrutt_Gurjar]

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.

[Suvrutt_Gurjar]

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

[anonymous]

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

[Suvrutt_Gurjar]

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?

[anonymous]

I have tried something like this which is not getting saved

[Suvrutt_Gurjar]

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.

Suvrutt_Gurjar:

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

Expression in valid_if setting

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

[anonymous]

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

[Aleksi]

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.

[Suvrutt_Gurjar]

Hi @Aleksi,

Very compact. As usual an ace solution from you.

[Aleksi]

You’re welcome

[Ami]

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.

[LeventK]

@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)="_"),
)

[Steve]

Or:

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

[Ami]

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

[LeventK]

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

[anonymous]

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

[LLD]

Suvrutt_Gurjar:

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!