Lets say my form has three field
Name
Mobile Number
Civil ID
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
Mobile number should be 10 digits no less no more, no special characters, no alphabets
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! Go to 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)
There is no good way to restrict characters in text values, through you can limit the length using Valid If or column properties:
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
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
)
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |