Country/Region ID Modification

(0) Share

Report

Posted on by Community Member

I have a requirement to change the Country/Region ID in AX2012 Addresses to ISO 2 digit code instead of the existing 3 digit code like for country United States, replace USA to US. How can I change the entire country list to the 2 digit ISO code? If we change it does it have any impact elsewhere?

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (0)

Suggested answer

Denis Patrakov on at

Like (0)

Report
A general way to modify an id that is a primary key is to use primary key rename functionality (Record Info/Rename). The kernel uses application metadata to find all the related table fields and runs an update statement for every such field in a single transaction to ensure referential integrity. There are two obvious issues in your case:

a country list can consist of 270+ entries so it's hardly reasonable to run a PK rename for every entry by hand.

country/region id field can be found in tables like CustInvoiceTrans, IntrastatReportLines, InventBatch, PdsMRCEEventTracker, RetailTransactionAdddressTrans, TaxReport347Trans, TrvExpTrans, VendInvoiceTrans, VendSettlementTax1099, etc., that potentially contain lots of records.

So, you should automate the PK rename process and consider adding (temporary) indexes to those tables to speed up the renaming.

Here is a sample job to rename country/region id to 2-character ISO code:

#macrolib.AviFiles LogisticsAddressCountryRegion addressCountryRegion; SysOperationProgress progress; str msg; ; msg = strFmt("%1 %2 -> %3?", "@SYS27865", fieldPName(LogisticsAddressCountryRegion, CountryRegionId), fieldPName(LogisticsAddressCountryRegion, ISOcode)); if (Box::okCancel(msg, DialogButton::Cancel) != DialogButton::Ok) { return; } startLengthyOperation(); progress = SysOperationProgress::newGeneral(#AviUpdate, "@SYS141137", (select count(RecId) from LogisticsAddressCountryRegion).RecId); while select addressCountryRegion where addressCountryRegion.CountryRegionId != addressCountryRegion.ISOcode && addressCountryRegion.ISOcode { progress.setText(strFmt("%1 -> %2", addressCountryRegion.CountryRegionId, addressCountryRegion.ISOcode)); progress.update(true); if (isConfigurationkeyEnabled(configurationKeyNum(SIG))) { SIGBaseDocument::checkAndCacheRename(addressCountryRegion, fieldNum(LogisticsAddressCountryRegion, CountryRegionId), addressCountryRegion.ISOcode); } ttsbegin; addressCountryRegion.CountryRegionId = addressCountryRegion.ISOcode; addressCountryRegion.renamePrimaryKey(); ttscommit; progress.incCount(); } endLengthyOperation();

Was this reply helpful? Yes No
ZvikaR 168 on at

Like (0)

Report

Question is, why on earth anyone would want to do something like that?

the 2 letter code is already one of the fields on that table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Well, the client has other applications where the country ID is referenced with the 2-digit ISO code. They feel it'd be easier for their users to understand having a uniform structure across and for other integration purposes. My concern is the country/region ID is used across several places so whether it'd be a right practice to do this so looking for possible solutions.

Was this reply helpful? Yes No
Suggested answer

ZvikaR 168 on at

Like (0)

Report

Not only this field is used in many places, there is special attributes on that table that will re-fill it with the list of countries (with 3 digits) even when you try to delete them.

I think this is one those cases that as a consultant you need to advise your customer to map using the existing 2-letter ISO code which is already populated on the record.

Was this reply helpful? Yes No
RaulVillaronga 95 on at

Like (0)

Report

So, Svika, you are saying that simply updating the ISOCODE field on the ADDRESSCOUNTRYREGION table would establish the 2-letter ISO code relationship to the CountryRegionID which is pushed to many AX tables, thus negating the need to update the 3-letter CountryRegionID field itself?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

The table is prefilled with ISO-3 characters. The 2 digit code is in the table as well for integrations. Also local functionality depends on this table. The recommendation is to NOT modify auto generated country/regions. There is a field called 'Immutable' in this table as well. If you know what this word means.. then you better not try to modify records with a value 'Yes' in it.

Was this reply helpful? Yes No
ajmalsherif 935 on at

Like (0)

Report

Hi Andre

we are migrating from v4 into AX2012. In the AX 2102 Address Setup > Country/region the ISO column is showing countries with only two digits eg Country/region BEL (Belgium) has an ISO of BE.

I will be grateful if you can comment on this? If the ISO of BE is wrong as it is two digits and not three, then can you please point to some documentation from MS?

THANKS, Ajmal

Was this reply helpful? Yes No
André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

Hi Ajmal,

You have to map the AX4 country/regions to the AX2012 in a pre-processing task. You can find it on page 152 of the Upgrade guide: www.microsoft.com/.../details.aspx

Was this reply helpful? Yes No
ajmalsherif 935 on at

Like (0)

Report

Andre, sorry to labor the point ...the page (link) refers to Country/region which I agree was 2 digits in v4 and in AX 2012 is 3 digits.

However my question is on the ISO column of Country/region form. This is 2 digits in v4 and remains as 2 digits in AX2012. Is this correct?

Lastly, if possible, can you kindly also advise what the default AX v4 Country/region code for Sri Lanka. Many thanks in advance.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,030 Super User 2025 Season 2 on at

Like (0)

Report

Hi Ajmal,

This is not correct. Is it for every country?

There is no default AX v4 country code. Only Ax2012 is prefilled with the iso-3 countries.

You can search for valid ISO codes for countries on this page: www.worldatlas.com/.../ctycodes.htm

Was this reply helpful? Yes No