11 Replies Latest reply on Aug 1, 2011 12:16 PM by PeterDurant

# Zip code woes..

### Title

Zip code woes..

### Post

I am importing an Excel file into FM Pro 11.  Some zip codes have a leading zero. Formatting the cells in Excel can make it show the leading zeros, but apparently FM Pro strips the leading zero on import.

I set up the script to do an Update Import by using Replace Field Contents calculation like this:  Case(MemberInfo Table:: US Member = "Y"; (Right("0" & MemberInfo Table::Zip; 5))).  That worked like a champ EXCEPT when a member enters a 9 digit zip code.  The result was like this: If the zip was 7660-1234, it came out like -1234 instead of 07660.

I expect I'm going to have to examine the length of the zip data to see how many numbers to move in the calculation in each case.  -Not exactly sure how to do this. I have to be able to correct a zip if it looks like this: 7660 (should be 07660), or, if it looks like this: 7660-1234 (should be 07660-1234).

• ###### 1. Re: Zip code woes..

Let ( [ Z = Filter ( MemberInfo Table::Zip ; -1234567890 ) ;
dash =  Position ( Z ; "-" ; 1 ; 1 ) ;
zip5 = Right ( "0" & If ( dash ; Left ( Z ; dash - 1 ) ; Z ) ; 5 ) ] ;
If ( dash ; zip5 & "-" & Right ( Z ; Length ( Z ) - dash ) ; zip5 )
)

• ###### 2. Re: Zip code woes..

Wow! I'm impressed..that worked beautifully.  Now I have to try to figure out what it all means...:)

Thank you. Thank you. Thank you.

• ###### 3. Re: Zip code woes..

The key part is that I am using Let to break up the calculation into simpler expressions and to avoid repeatedly typing in long strings such as MemberInfo Table::Zip.

Filter is used to drop out all characters except 0..9 and the dash

dash is the position of a dash in the filtered text. If there is no dash, it's zero.

zip5 will either be either the text to the left of the dash or the entire text if there is no dash with a leading "0" added and the right function then strips it back off if there is no need for the leading zero.

The final if function either combines Zip5 with the text to the right of the dash or simply returns zip5 if there is no dash.

• ###### 4. Re: Zip code woes..
Just out of curiosity, wouldn't importng the Zip Code into a Text field retain the leading zeros? RW
• ###### 5. Re: Zip code woes..

I believe so Rick

Chris

• ###### 6. Re: Zip code woes..

No.  I tried that and it didn't work.

• ###### 7. Re: Zip code woes..

It may be a function of how the codes were stored in Excel.

If you put a single quote in front of each zipcode in Excel so that it is treated as text, it may import successfully. Since the above fix is easier than editing all those cells in Excel, I stuck with the suggestion I made here.

• ###### 8. Re: Zip code woes..

Oh shucks...  Just when I thought all was okay, I somehow messed it up.  I put a Case statement in front of your code; like this:

Case(MemberInfo Table::US Member="Y";
(Let ( [ Z = Filter ( MemberInfo Table::Zip ; -1234567890 ) ;
dash =  Position ( Z ; "-" ; 1 ; 1 ) ;
zip5 = Right ( "0" & If ( dash ; Left ( Z ; dash - 1 ) ; Z ) ; 5 ) ] ;
If ( dash ; zip5 & "-" & Right ( Z ; Length ( Z ) - dash ) ; zip5 )))
)

My U.S. members zip codes come out okay, but now the Canadian zip codes aren't there at all.  Canadian members are identified by a field called "Foreign".  If Foreign="C" they are Canadian.  (All other foreign are "F", U.S. are blank).  I have another field called US Member which is a "Y" or "N".

Canadian Zip codes are like:  T2Z 3V7  (always letter-number-letter, space, number-letter-number)

I also have other Foreign members whose zip codes are all over the place in format.

Any ideas of how I can keep the corrected U.S. zip codes and just import the Canadian (and foreign) unchanged?  I tried putting a Find statement in the script, but the Import/update seems to ignore it.

• ###### 9. Re: Zip code woes..

Case(MemberInfo Table::US Member="Y";
Let ( [ Z = Filter ( MemberInfo Table::Zip ; -1234567890 ) ;
dash =  Position ( Z ; "-" ; 1 ; 1 ) ;
zip5 = Right ( "0" & If ( dash ; Left ( Z ; dash - 1 ) ; Z ) ; 5 ) ] ;
If ( dash ; zip5 & "-" & Right ( Z ; Length ( Z ) - dash ) ; zip5 )) ;
/*else*/ MemberInfo Table::Zip )

Your original expression returned Null if the record wasn't for a US member. You just need to return the original field value if this is not the case.

• ###### 10. Re: Zip code woes..

That did it...works great!  Thanks again.  Your help has been invaluable.

• ###### 11. Re: Zip code woes..

Wondering if you could help me out.  I have the same problem of mixed zip codes.  There are no dashes in my fields and when I run the script all numbers end up being the last five (with a leading zero)  am I doing something wrong?