State/Province field showing incorrect values in lists & reports

[jimmyjam]

Community,

I've got a weird situation and would appreciate your help diagnosing and solving it.

The State/Province field is showing incorrect values in lists & reports for contacts with US addresses. See here:
http://awesomescreenshot.com/0222o2h88

But the correct values are shown in the contact summary (view):
http://awesomescreenshot.com/0e02o2odf

BUT.... the State/Province is missing when I go to contact summary (edit):
http://awesomescreenshot.com/0d12o2lc2

WTF? Does anyone have any idea how to solve this?

Thanks,
James

[petednz]

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.

[jimmyjam]

Thanks, Peter. That very well may be what happened in my case. I'll check to see if I neglected to import Country on the affected records.

Thank you,
James

[alabra]

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?

[petednz]

Am wondering if the Country column needs to be before the State column for such imports to correctly assign the correct State?

[questions]

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.

[demeritcowboy]

Ran into this a week ago too with a 3.2.2 install. Just getting to a closer look now.

It seems to be coming from CRM_Contact_BAO_Contact::resolveDefaults() where CRM_Utils_Array::lookupValue() combined with having enough countries enabled causes CRM_Core_PseudoConstant::stateProvinceAbbreviations to have duplicates, and so the array_flip call in lookupValue() causes it to just pick one.

[questions]

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

[demeritcowboy]

Contributed code is always welcome.

The pseudoconstant shouldn't do much thinking on its own - it's just a (pseudo)constant, but the resolveDefaults() code probably could. However sometimes country is not present, e.g. if most of your contacts are US then you might leave it out, and then CA is still ambiguous, so additionally there would need to be code to put it in the invalid pile during the previous stage.

So it's probably more about somebody taking the time to do that coding. It's just weird this hasn't come up all the time before, unless people weren't enabling multiple countries as much before.

[Donald Lobo]

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