CiviCRM Community Forums (archive)

Hey James - not sure but I know we got caught out when we imported State short form ie CA - but did not import a Country - and though the site was set to Default = US we ended up with a load of people in Uruguay - presumably cos there is another state in Uruguay with CA as its acronym - was kind of shocked to find all these people in Palo Alto living in Uruguay,

Just mentioning in case it is a pointer for you or anyone else as to what might have happened.

I had the same thing happen to me following an import a few weeks ago. I'm curious if you figured out what happened in your case? It turned out that in mine, the country field was imported correctly, so I'm not sure what went wrong.

I had the same symptom as described above: The address appeared to have the correct state abbreviation in the contact summary, but then was missing the field in the search results list. I confirmed that the correct country was imported, and the country id had been correctly set, but that the wrong state_province_id was set.

So, for instance, an address would have country_id=1228 with state_province_id=2098. These are not compatible ids when compared to the civicrm_state_province table. So although the abbreviation was the same ("MA"), the logic behind the data was broken (let alone the problems inherent in having an incorrect value for state_province_id).

The import was done in 3.1.4 (it wasn't an update - just a standard import into a fresh Civi install), and I searched the issues for that version, but didn't find anything that appeared relevant. Am I missing something obvious?

IMHO this is messed up, really a bug.  I just had the same issue.  In my case, I'm in CA so I most of the addresses are for CA, which came up Ciego de Avila, over 2,000 of them.

I played with it a bit and it appears that it wants the state spelled out, not the abbreviation.  I tried importing to updating a record, first with just CA and United States, then I reversed it to give it country first, then I spelled out CA.  That worked.  I then tried import with just the state.  It changed the country to Cuba and kept the state Ciego de Avila. 

I suppose if I waded into the code I could see just what it is doing.  The logic is not quite right

What's screwy is I had some one with an address in England that is in the "province" of Surrey but it wouldn't take that, I had to put in the abbreviation in the import file, SRY, backwards of what seems to work for US.

In looking at the results clearly there are some abbreviations where there is only one or it happens to pick the correct one.

Having not looked that code this may be a dumb point, but shouldn't the CRM_Core_PseudoConstant::stateProvinceAbbreviations consider the country?  Then it should only get one abbreviation.  If no country is entered it should take the default or if it's an update or fill take what's already stored. 

This seems pretty obvious so I'm probably insulting someone's intelligence

BTW, since I had so many to fix and I figured it take me just as long to write code to convert the abbreviations to fully spelled out as it would write sql to just fix it, I wrote the sql.  Being new to the whole CiviCRM world, I don't know if people post sql here or if it's frowned upon.  So here is the sql to identify the "wrong" ones and how many.


SELECT    sp2.id as CorrectStateID,
   a.state_province_id as WrongStateID,
   sp2.name as CorrectStateName,
   sp1.name as WrongStateName,
   sp2.abbreviation,
   c.name as CorrectCountry,
   c2.name as CountryOfWrongState,
   count(*)
FROM    civicrm_address a,
   civicrm_state_province sp1,    /* what is stored */
   civicrm_state_province sp2,    /* correct */
   civicrm_country c,       /* what is stored */
   civicrm_country c2       /* country of stored state */
where    sp1.id = a.state_province_id
and   sp1.abbreviation = sp2.abbreviation
and   a.country_id = sp2.country_id
and   a.country_id = c.id
and     sp1.country_id = c2.id
and   sp1.id <> sp2.id      /* leave out the ones that are correct */
group by sp2.id,
   a.state_province_id,
   sp2.name,
   sp1.name,
   sp2.abbreviation,
   c.name,
   c2.name

This has now been fixed for 3.4:

http://issues.civicrm.org/jira/browse/CRM-7597

basically if there is a country in the import line, it restricts the states to that country. if no country exists, it reverts to the previous behavior

lobo