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 rosenjonView original
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).
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:
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.
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!
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.
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):
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.
@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?