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)))
Best answer by rosenjon
In my opinion, you should validate the addresses outside of Acumatica, and then just import them along with the Country field (see what
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.
+19Hi
Also, what is the issue your getting while you are importing the data? Can you please share this information/screenshots here?
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.
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):
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.
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!
In my opinion, you should validate the addresses outside of Acumatica, and then just import them along with the Country field (see what
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.
^[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).
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.