Country field on Location Table

[Robin Hearne]

Does anyone know why the Country field on the Location table (cmn_location) is not a Reference field on the Country table (core_country)?

It seems logical that it should be, and I'm tempted  to change it so that it is, but I'm always inclined to think that things are setup a certain way for a reason.

Our instance is on Helsinki and it started on Fuji.

Thanks,

Robin

[Mike Allen]

That has always perplexed me, as well.   I would not change it, however, but maybe create another field that is a reference and use that.   Or, make the field's choices point to the country table.

[marcelschneide1]

Same is true for me. We need to load countries in addition and have them in cmn_location stored, just to be able to have the correct parent-child relation build.

If anyone has a clever answer on why core_country is not reference in cmn_location OOB… happy to read

[dantenow · Accepted solution]

Hi Robin,

Your question is a good one that a few other people have asked as well.   I stumbled across your question while trying to solve an incident submitted to Hi and figured I would share the wealth.

A brief ServiceNow history lesson: The cmn_location table was created about 11 years ago (that's a long time) and at the time that the table was created the core_company table didn't exist yet to reference.   Eight years after the cmn_location table's creation, we added the core_company table which was populated with the most up to date (in 2013) UN-approved country data.

When the core_company table was created, it was decided not to migrate the cmn_location.country field to reference the core_company table. But why? The cmn_location table is referenced by several modules which would have meant updating many out-of-box scripts and modules.   But more importantly, after existing in that state for 8 years, there are probably more than a few customers who have written customizations around that field being a string.   Updating the cmn_location.country field to reference the new (new at the time) core_country table would have forced our customers to update all of their custom scripts that referenced the cmn_location.country field.   We take great consideration when updating parts of the platform that have existed for a long time as customers have relied on or figured out their own workaround for the behavior.

Edit: If you were going to change this field to reference the core_company table, you could do that by clearing out all of the data or writing a script to match the sys_ids in the core_country table with the country string in the cmn_location table.   However, I WOULD STRONGLY ADVISE AGAINST THIS.   Not only would you need to rewrite any custom script that you've created that references this field, you would also need to modify out-of-box scripts and modules that reference this field.

I hope that the answer I have provided has been useful in answering your question.   If it answered your question, please mark this question as answered.

Best Regards,

Dante

ServiceNow Technical Support Engineer

[Eric M]

We created a new reference field pointing to Core country table and deactivated the other country field for the same reason.

Eric