Country field on Location Table

Robin Hearne
Tera Expert

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

1 ACCEPTED SOLUTION

dantenow
ServiceNow Employee
ServiceNow Employee

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


View solution in original post

5 REPLIES 5

Mike Allen
Mega Sage

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.


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
ServiceNow Employee
ServiceNow Employee

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


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



Eric