Solved

Import Scenario - Zip Code String

  • 1 March 2022
  • 7 replies
  • 108 views

Badge

I have created an import scenario for orders, but the international orders will not import.  How / what code do I need to use to allow the international zips to load (alpha numeric) and then to have Hong Kong default to 00000 (they don’t have a zip code) .

This is what we are using now:  =IIf(( Len([bill-postal-code])=4), ('0'+[bill-postal-code]),(Substring([bill-postal-code],0,5)))

icon

Best answer by rosenjon 4 March 2022, 18:47

View original

7 replies

Userlevel 7
Badge +12

Hi @annekseymour  Can you please share your import scenario XML file?

Also, what is the issue your getting while you are importing the data? Can you please share this information/screenshots here?

Userlevel 6
Badge +3

There is a Countries/States page where you can control the Zip Code validation using regular expressions(regex). So assuming you set this up and make sure to set the country in your import you should be good.

 

 

Badge

It is in the import scenario that i am breaking - =IIf(( Len([bill-postal-code])=4), ('0'+[bill-postal-code]),(Substring([bill-postal-code],0,5)))    

 

I think because the bill-postal-code   but many times people put the international codes with a space between so i am unclear if the above string accounts for letters and spaces

this is the error (this one happens to be for Canada):  

 

Userlevel 5
Badge +1

Your formula looks to be built for US addresses, and more specifically those that came from an Excel spreadsheet (Excel has a nasty habit of dropping leading zeros, among other data corruption issues….which is why most ppl who do data import/export for a living will avoid it for source files (it’s great for looking at data...not so good for actually manipulating and saving data).

=IIf(( Len([bill-postal-code])=4), ('0'+[bill-postal-code]),(Substring([bill-postal-code],0,5)))

This formula says if the the length of the postal code is 4 characters, add a zero to the first character The problem is that Canadian zip codes are 6 digits, and always start with an alpha character. You zip code is L0A1G, which is not 6 digits, so it fails validation.

Badge

Thank you, do do I just need to change the 5 to a 6?  or what would the proper formula be?  Sorry, I am just teaching myself as I go!

 

Userlevel 5
Badge +1

In my opinion, you should validate the addresses outside of Acumatica, and then just import them along with the Country field (see what @markusray17 said above….the correct regex for the postal code is going to depend on which Country the address belongs to...so there is no universal formula here that will work for every address type...unless all addresses are from the same country). Otherwise, this is going to be a really long and really complex IIF condition, which is going to possibly introduce bugs.

If we’re talking about Canada postal codes (and assuming there is no space in the postal code), then:

=IIf(( Len([bill-postal-code])=6), ([bill-postal-code]),(""))

 

I’m not that familiar with this “Excel-like” language used by Acumatica, so other on here can correct me if my formula is a bit off. The intent here is to say that the length of the field must be 6 characters (it would be 7 if there was a space in the middle of the postal code, which is standard for Canadian postal codes). If that is true, we just insert the postal code. If it’s false, we insert nothing… which is what the double quotes at the end are for.

 

Userlevel 6
Badge +3
^[ABCEGHJKLMNPRSTVXY]{1}\d{1}[A-Z]{1} *\d{1}[A-Z]{1}\d{1}$

I believe this is the default Canadian regex Acumatica uses so it does indeed allow for spaces(* means 0 or more of the preceding character which in this case is a space).

 

@rosenjon is correct, I would recommend formatting the zip codes before the import. If you are getting errors about the postal code you can go to that Countries/States page to get the regex for that country. You can use a site like https://regexr.com/ to test/understand the expression. 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2022  Acumatica, Inc. All rights reserved