AnsweredAssumed Answered

Zip code woes..

Question asked by RCaauwe on Apr 12, 2011
Latest reply on Aug 1, 2011 by PeterDurant


Zip code woes..


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