Data Import mapping to wrong locality

Question

I am trying to import from a SQL database. One column contains state abbreviations, the two letter codes. When I map this to UID_DoalogState.ShortName in Data Import, it will map to the first match in the 1IM locality table, which is often wrong and from some other country. For example "NC" for North Carolina maps to some place in the Philippines, so both the state and country are wrong. The database only has state, not country, as it is all in the US. I tried adding a fixed value for UID_DialogCountry.CountryName but it is overridden when importing by whatever 1IM thinks is correct according to what matches state. Using designer to specify allowed values results in an error as Data Importer tries to use the wrong value an it fails. How can I set it so Data Importer will only try and map states to those in the US?

Markus Weiss-Ehlers · Answer

One option would be, to map the state information to a custom property, and to create a value template (with Designer) on UID_DialogState that selects the correct state from the US, based on the value in the custom property.