U.S. State Abbreviations Defaulting to Italian

When importing third party data U.S. state abbreviations are defaulting to Italian province names. For example PA (Pennsylvania) changes to Palermo (in Italy). This holds true even if the country code in the import record is set to “US”. The only solution I was able to find is to delete the dictionary reference for the Italian province in question. Dolibarr then uses the correct state definition.

DOLIBARR ENVIRONMENT
- Version: 8.0.1
- Operating System: Microsoft Windows 10 Pro for Workstations
- Web Server: Apache/2.4.9 (Win32)
- PHP: PHP 5.5.12
- Database: MySQL 5.0.45
- URL(s): http://dolibarr/dolibarr/imports/emptyexample.php?format=xlsx&datatoimport=adherent_1&excludefirstline=1&separator=%2C&enclosure=%22

Have you tried upgrading to v8.0.2?

The management of states/provinces is in my opinion poorly implemented and this is an example of it: it is still like this in the development version.

What happens on an import is:
First, it looks for the value “PA” as the rowid (integer) in the llx_c_departements table, which of course it wont find.
Second (and this is the crazy part), it then looks in the code section to find a matching code. The code column is not-unique, and all sorts of values have been used inconsistently, depending on who actually added the sql for their country. This means that the first match it finds is the one it uses (PA-Palermo in your case) as it does not use the country to filter at all!
I know this after spending the last few months gradually importing a great deal of legacy data and finding how terribly incomplete and neglected the whole import process is.

So the ONLY way to get the state import to work correctly is to use the correct rowid in the state column of your csv and prefix it with “id:” to force the import check to look only for the rowid.
This means you somehow have to match the provinces in your legacy data to the correct rowid of the llx_c_departements table.
How to do that?
The method I used was to export the table as a CSV using an SQL editor, then use it as a lookup array with the spreadsheet VLOOKUP function to return the correct rowid for the province name in your data.

2 Likes

Upgraded to 8.0.2 successfully. Bug persists.

1 Like

I’m not sure if this is relevant:

MAIN_FORCE_DEFAULT_STATE_ID (>= 8.0.1) => Enables to force the selection of a country / province / county from a country specifying IDs

No. But it was poorly described in the Wiki so I checked it. Wiki updated.

MAIN_FORCE_DEFAULT_STATE_ID
This is the rowid (integer) of a state/province as defined in llx_c_departements. On the creation of a third party/contact, the state/province corresponding to this rowid is preselected in the dropdown list.