Expression - Zip Code validation

[Former Member]

We have the field called Zip Code and this is a text field and 10 Chrs long.

Field 1: State

Field u2018Stateu2019 is a lookup field and the lookup table has Country, state code and state name.

Field 2: Zip Code

Zip Code = text field and the length is 10 chrs.

The ZIP Code Validation should be either NNNNN format (Block of 5 digits) for standard ZIP Code OR

NNNNN-NNNN format (Block of 5 and 4 digits) for ZIP+4 format

For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

Can anybody help me to write the expression. Full points will be rewarded.

[Former Member]

technically you can write a logical statement to find out if an address in Illinois has a zip starting with 6, but there are other states whose zip start with 6 (Nebraska, Kansas and more) which are not Illinois, so the most accurate would be to keep a combination of all possible first 2 digits of zip, and each combination should be associated with a single state, as far as I know.

Another approach would be to use external zip validation as part of address validation and to check that not only the zip is valid within the state, but also agrees with the city, street and residence.

See previous forum discussions about address validation.

Edna

[Former Member]

Hi,

Try this it should work,

For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

I am assuming here State = IL indicates state code = IL which is field in your Lookup table. As you said your Lookup table having fields Country, state code and state name

IF(FIND(State.Country, "US") AND FIND(State.state code, "IL"), LEFT(Zip Code,1)=6 AND HAS_ALL_CHARS(RIGHT(Zip Code,4),0,9), FALSE)

otherwise if your State having IL value is simple text field in maintable then replace it as

IF(FIND(State.Country, "US") AND FIND(State, "IL"), LEFT(Zip Code,1)=6 AND HAS_ALL_CHARS(RIGHT(Zip Code,4),0,9), FALSE)

Hope it will Help you,

Rewards if Useful......

Mandeep Saini

[Former Member]

Thanks Ravi.

But the we missed the first check whether the country = US and State code = IL. The Zipcode field validation is based on the country and state code of the record. That is Field 1, I explained earlier.

"Field 1: State

Field u2018Stateu2019 is a lookup field and the lookup table has Country, state code and state name."

[Former Member]

Hi JP

validation statements should be written with generic execution in mind. we should avoid such specific value and field combination for executing validations.

In this case use IF((country.lookup value =select value as US AND Sate code=IL) THEN valdation statement............)

Award points if useful

Best regards-

Ravi

[Former Member]

Hi JP

For example, if the country = US and state = IL then the first digit of the postal code field will be 6 then remaining 4 digits must be numeric for the standard 5 digit Zip code.

let me try. First use LEFT(ZIP,1) this will return first digit of ZIP code check if it is equal to "6".

Use RIGHT(ZIP,4) to get the remaining 4 digits.

Next task is to find out that all 4 digits are numeric. Use FIND and HAS ANY in combination (not sure).

Simple option wud be to make ZIP as type Integer. No need for step 3.

hope this helps.

Regards-

Ravi