Can anyone help on Validations

[Former Member]

Hi SDNers,

I am facing a problem with validations.

Here is my requirement.

I had a field (Phone Number). In that business will enter either Lanline Number ot Mobile Number.

Ex - 0444 12345678 or 044 12345678 These are land line numbers.

ex:- Mobile numbers : 1234567891.

Here I need a validatin in this way.

1) First we need to check is NUL or not.

2) Then the phone shold not start with other than 0 or 1.

3) If its starts with 1 then there should not be a Space between those numbers.

4) If Landline number starts with 0 we need to check area code first 4 digits or 3 digits and space than phone number.

Ex - 0444 12345678 or 044 12345678. (We need to check phone number is in this format or not)

Any Help?

Thanks

Kiran

[former_member209780 · Accepted solution]

Hi,

This is working. Just paste this

IS_NOT_NULL(Phone Number) AND LEFT(Phone Number,1)<=1 AND IF(LEFT(Phone Number,1)=1,ABS(Phone Number)=Phone Number) AND (IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=2 OR IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=3))

And If you want to avoid more than one space between the number( Example: 044 123 45678)

Paste this:

IS_NOT_NULL(Phone Number) AND LEFT(Phone Number,1)<=1 AND IF(LEFT(Phone Number,1)=1,ABS(Phone Number)=Phone Number) AND (IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=2 OR IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=3)) AND IF(LEFT(Phone Number,1)=0,ABS(RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2)) = RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2)))

Regards

Goutham Kanithi

Reward Points if helpful

[Former Member · Accepted solution]

Hi Goutham,

Thanks for your reply. I saw that its good. Just I am working on that. Once finsihed I will update you?

Thanks

Kiran

[Former Member · Accepted solution]

HI Goutham,

Its perfectly working.

I need to add Country field also. Suppose the Validation should work only for india (IN) Country not for any other country. If the user from Australia (AUS) this validation should not trigger. This validation should work only for India (IN). not to any other country.

Thanks in Advance.

Thanks

Kiran

[gill367 · Accepted solution]

For checking whether the phn number enterned contain only numric values and space

HAS_ALL_CHARS(phone_number,32,57) AND IF((HAS_ALL_CHARS(phone_number,33,47)), FALSE)

for checking the required format

IS_NOT_NULL(phone_number)AND LEFT(phone_number,1)<=1 AND IF(LEFT(phone_number,1)=1, ABS(RECORD)=RECORD) AND

(IF (LEFT(phone_number,1) = 0, (LEN(ABS(phone_number))= 2) OR LEN(ABS(phone_number))=3))

For country specific thing

Add a IF then else condition in the beginning of the validation itself

If(country_code= IN , <write the validation here > , TRUE)

Edited by: sarbjeet singh on Mar 1, 2012 2:16 PM

[former_member209780 · Accepted solution]

Hi Kiran,

Say you have a lookup field country in your main table which looks into countries lookup table. If the country code (say "IN" for India), is the display field of that lookup table, below is the validation

IF(Country.Code="IN",IS_NOT_NULL(Phone Number) AND LEFT(Phone Number,1)<=1 AND IF(LEFT(Phone Number,1)=1,ABS(Phone Number)=Phone Number) AND (IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=2 OR IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=3)) AND IF(LEFT(Phone Number,1)=0,ABS(RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2)) = RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2))))

Regards

Goutham Kanithi

Reward Points if helpful

[former_member209780 · Accepted solution]

Hi Kiran,

Say you have a lookup field country in your main table which looks into countries lookup table. If the country code (say "IN" for India), is the display field of that lookup table, below is the validation

IF(Country.Code="IN",IS_NOT_NULL(Phone Number) AND LEFT(Phone Number,1)<=1 AND IF(LEFT(Phone Number,1)=1,ABS(Phone Number)=Phone Number) AND (IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=2 OR IF(LEFT(Phone Number,1)=0,LEN(ABS(Phone Number))=3)) AND IF(LEFT(Phone Number,1)=0,ABS(RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2)) = RIGHT(Phone Number,LEN(Phone Number)-LEN(ABS(Phone Number))-2))))

Regards

Goutham Kanithi

Reward Points if helpful

[former_member209780 · Accepted solution]

Hi Kiran,

Please update and close the thread

Regards

Goutham Kanithi

[Former Member]

Hello Kiran,

You can write seperate validations and run them as a group

1. IS_NOT_NULL(Phone number)

2. IF(Left(Phone number,1)=0 or left(phone number,1)=1,true,false)

3. IF(LEFT(phonen number,1)=1AND HAS_ANY_VALUES(phone number,"<space>"),False,True) OR If(LEFT(phone number,1)=0 and mid(4,1)="<space>",True,False) or IF(LEFT(phone number,1)=0 AND MID(3,1)="<space>",True,False)

I have tested the validations and they work fine.

Normal practice is to write validations seperately and run them as a Group

Rgds,

Prasad.

Edited by: Prasad Tuttagunta on Mar 1, 2012 8:30 AM