I managed the run … Now Iam in the change of chart name to English. it showing this error… BTW… the names are changed on the server. I managed to reach to the right names. the error now I am facing is:
#1452 - Cannot add or update a child row: a foreign key constraint fails (i2549752_doli1.doli_accounting_account, CONSTRAINT fk_accountingaccount_fk_pcg_version FOREIGN KEY (fk_pcg_version) REFERENCES doli_accounting_system (pcg_version))
version 5.0.1
Hi Afiffidaali… you are a great man. all works great… thank you very much… I upgraded my coding skills with you
Anashaddad,
I’m glad to be of help. Has it worked properly as in is it now showing English Chart of accounts in Dolibarr?
Yes its showing English… and thank you
Hi Arif
I am trying to do same action, by changing the French COA to English. but I am facing this error when I want to change the name of to ENG-Base
#1452 - Cannot add or update a child row: a foreign key constraint fails (i3639013_doli1.doli_accounting_account, CONSTRAINT fk_accountingaccount_fk_pcg_version FOREIGN KEY (fk_pcg_version) REFERENCES doli_accounting_system (pcg_version))
Hi anashaddad,
You have to uncheck/untick ‘Enable foreign key check’ this is just below the query box. Sorry I have missed that step.
UPDATE: Here is a way to get English Chart of Accounts as Dolibarr does not include one. This is a revision on the earlier post which had a small mistake.
The way I’m creating an English COA is to translate the PCG99-BASE - the base french accountancy plan’s French labels to English. The rest of this plan remains the same.
WARNING: BEFORE ATTEMPTING any of the steps below please BACKUP Dolibarr or backup your Dolibarr database from your control panel. Although the procedures below will not do any serious damage to your system, you’d rather be safe than sorry. I will not be responsible for any system malfunctions.
So here are the steps you need to take. Make sure you carry out the procedure step by step, do not miss any step or it wont work.
STEP 1:
Run the following SQL Queries using phpMyAdmin which can be accessed depending on how you are running Dolibarr:
- If running locally (from local computer) then in your browser type: localhost/phpmyadmin or from the localhost main page look for phpMyAdmin.
- If running from a hosted environment then in the control panel under Database section look for phpMyAdmin.
Once you are in PhpMyAdmin:
On the left hand side make sure the Dolibarr database is selected then look for SQL Tab.
Copy and paste the following query in the box where you clicked on SQL Tab. Make sure it has: Run SQL query/queries on database name_of_dolibarr_db:
CREATE TABLE your_temp_table LIKE llx_accounting_account;
This creates a table called your_temp_table
Click on Go button to run the query. If everything ran successfully you should have this message : MySQL returned an empty result set (i.e. zero rows). (Query took 0.1491 seconds.) with a green tick
STEP 2:
Download the attached file English Chart of Accounts.csv
English Chart of Accounts
Making sure the database selected is the Dolibarr database, run the query below:
SELECT * FROM your_temp_table;
click on Go button
Then look for Import tab. Once you click on Import Tab make sure it says: Importing into the table “your_temp_table”
Click on choose file then select the downloaded English Chart of Accounts.csv file. Under Format choose CSV.
In the Columns separated with box type: ;
Click on Go button. If its successful you will get a tick and a number of rows will be inserted in the your_temp_table created earlier.
STEP 3:
Copy and paste the following query making sure to Uncheck -> ‘Enable foreign key checks’ then click on Go button:
UPDATE `llx_accounting_account` SET `fk_pcg_version` = 'ENG-BASE' WHERE `fk_pcg_version` = 'PCG99-BASE'
The above query renames PCG99-BASE to ENG-BASE
Then run the following query making sure to Uncheck -> ‘Enable foreign key checks’
UPDATE `llx_accounting_system` SET `pcg_version`='ENG-BASE',`label` = 'The base accountancy plan in English' WHERE `pcg_version` = 'PCG99-BASE';
STEP 4:
Copy and paste the following query:
UPDATE llx_accounting_account
INNER JOIN your_temp_table on your_temp_table.rowid = llx_accounting_account.rowid
SET llx_accounting_account.label = your_temp_table.label;
Click on Go button
If its successful you will get something like this: 340 rows affected. (Query took 0.1315 seconds.)
Now check on your Accountancy setup if all is done properly you should now see in the drop down list box: ENG-BASE - The base accountancy plan in English
Voila!
Phew! this was quite long!
Give a big thank you if I made your DoliLife easier!! 
Very big thank you
Arif… do you have any idea how to modify the field of the public member form… I will be using this form on the web. but needs to delete some fields and change the text in the introduction…
regards…
I am having issues here,
In phpMyAdmin version 4.3.8, I see no (Uncheck -> ‘Enable foreign key checks’) way or button.
I tried to run (SET FOREIGN_KEY_CHECKS = 0;) this seem to run ok, but when I try to run the (UPDATE llx_accounting_account SET fk_pcg_version = ‘ENG-BASE’ WHERE fk_pcg_version = ‘PCG99-BASE’) I still get the (#1452 - Cannot add or update a child row: a foreign key constraint fails)
It looks like WHERE fk_pcg_version = ‘PCG99-BASE’
Matched rows: 343
How do I get around this and update these rows?
Hi Alvin,
I hope you are using my revised tutorial which is here: English COA
See attached screenshot to locate enable foreign key checks:
You have to untick Enable foreign key checks.
sorry that check box is not there on mine. That is the one provided by the host, I could load my own copy of phpMyAdmin, but that would be a pain.
Any other ideas?
what about exporting llx_accounting_account open it with editor and find/replace PCG99-BASE with ENG-BASE then re import?
Alvin,
Try running SET FOREIGN_KEY_CHECKS = 0 for both tables:
llx_accounting_system
llx_accounting_account
In that order
Don’t forget afterwards when you are done trying out the tutorial to return to:
SET FOREIGN_KEY_CHECKS = 1
To make this work I had to select the [Edit] button at the bottom of the box that shows the statement.
I hope this helps others,
Thanks again,
Hello,
I also struggle with implementing the English Chart of Accounts.
I opened http://localhost/phpmyadmin/ but I cannot login.
My account credentials don’t work so I looked further and I found database credentials but there is default password which I do not know.
What is the way to log in to phpMyAdmin please? I use version 6.0.4
Last post in that:
In the end removing “Require valid-user” from the phpmyadmin.conf file allowed me in to the web ui.
Thank you for you reply.
My knowledge in this area is quite poor so explanation as simple as possible would be appreciated.
I found the file you mention and this is the only content:
[size=2]Alias /phpmyadmin “c:/dolibarr/apps/phpmyadmin4.1.14/”
<Directory “c:/dolibarr/apps/phpmyadmin4.1.14/”>
Options Indexes FollowSymLinks MultiViews
AllowOverride all
Require local
</Directory>[/size]
Can you please advise me what should I remove?
