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

    Zip code woes..

    RCaauwe

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

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

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

              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..
                RickWhitelaw
                Just out of curiosity, wouldn't importng the Zip Code into a Text field retain the leading zeros? RW
                • 5. Re: Zip code woes..
                  c.wagner1

                  I believe so Rick

                  Chris

                  • 6. Re: Zip code woes..
                    RCaauwe

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

                    • 7. Re: Zip code woes..
                      philmodjunk

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

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

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

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

                            • 11. Re: Zip code woes..
                              PeterDurant

                              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?