We are doing data cleanup and are attempting to fix the country Master. We would like to update all records to new/non duplicate country codes and delete duplicate country codes/descriptions.
Because Country is linked to a vast variety of tables and the description (of all things is the primary key!!!) what would be the best way to tackle this?
As an example we have Netherlands AND NL (someone added Netherlands because they didn’t know NL is Netherlands) and we just want the full name.
Support cannot figure this one out, so I’m now going to the community for help. Any insight would be appreciated! Thanks!
County table preview with duplicate Netherlands:
I don’t think you’ll be able to change the “wrong” Country records. Just find all the references to the “wrong” country codes and update them to “right” one.
If possible delete the “wrong” ones. If not, add a UD field “Inactive_c”, then customize any screen that allows you to select the Country, adding a filter to prevent the Inactive ones from being selected again.
Also, I suggest using ISO 3166-1 as the basis for creating countries. That’s a standard used by add-on systems (like Tax Connect, and probably shipping programs).
that could be really messy. You can’t delete it if in use so you would have to find all the places it is used and update them using DMT (or something I shall not mention…LOL). You MIGHT be able to delete it if not in use.
We had some similar issues and also wanted to match the countries in different companies.
What I discovered was you can change the country description by using an updateable BAQ but then you also need to update all the linked records in other tables eg: In Customer and change their country to something else then back to your updated country for it to save.
Then for countries that still couldn’t be deleted I renamed them to have zz in front of them so users wouldn’t use them as they were at the bottom of the list.
For anyone who is interested:
I was unable to update descriptions via dmt or the UI. I was able to update the descriptions by accessing the country business object directly and using the updateext method
:
