Just create a Bulk Update Tool. Can’t think this did not existed for such a long time!
Use it via Setup > Bulk Update (or just type “bulk” on the toolbar)
Just create a Bulk Update Tool. Can’t think this did not existed for such a long time!
Use it via Setup > Bulk Update (or just type “bulk” on the toolbar)
This awesome, but I cannot Bulk Update on the Status field. I have all these Delivery Notes that are in the “To Bill” status that I just want to switch to “Closed” … Bulk Update won’t let me do this operation – meaning I’d have to edit each one individually. Any thoughts? The same goes for Purchase Receipts. I don’t use any of the billing stuff, so I want to skip To Bill and just move to Closed.
I guess that might be because you actually need to bill those DN’s in order to satisfy system’s logic.
But you don’t … I can go into each DN individually and change Status to Closed. I’m sure more things happen internally other than updating a value to close it, but I’d love to be able to do this in bulk.
Can one update 4500 records instead of 500
Write a Python script!
Last week I delved a bit deeper into the world of MariaDB. I followed this procedure manually first, which I am sure can be scripted with Python or made simpler, but I had to do a test and this worked. I used this for cost centers and item groups, uploading a combination of 49,500 groups and nodes per table. (These are UNSPSC categories for managing inventory and budgeting per item category). I have seen no stability issues, and ERPNext is working absolutely fine on my remote and VirtualBox servers. Adapt accordingly to your data structure.
If this is your first time doing this, I highly recommend taking a look in the data within the tables to learn about table data format
On a clean installation of ERPNext create at least 3 levels of cost center groups (folders) and then create one final node nested in level 3 group. If already have data, move to next step
Using SSH, login to your server. Or if using locally, move to next step
ssh user@erpnextserver
mysql -u root -p
show tables;
command.show databases;
use
command: (change db id accordingly)use 1af3b1647ab2768;
tabCost Center
` and `tabItem Group
`show tables;
describe
command. I personally copy and paste this data from my MAC terminal onto an excel spreadsheet. I manually transpose the paste to columns.describe `tabCost Center`;
SELECT * FROM `tabCost Center`;
SELECT * INTO OUTFILE ‘/tmp/costcenters.csv’
CHARACTER SET ‘utf8’
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
FROMtabCost Center
;
scp
command in MAC OS X (Linux too). Modify the username, server, and destination path at the end of the command + arguments according to your needs.
sudo scp user@erpnextserver:**/tmp/costcenters.csv** /Users/username/Documents/
Review the data, adding the column headers for easy reference. Also make sure the “rgt” and “lft” column headers are numbered accordingly. Everything else can be left either at “0” or “\N”, except for the obvious columns or headers you need imported.
Parse and then finally copy the data you wish imported into the structured table.
When data looks ready, copy only data onto a new blank excel spreadsheet. Save as .csv with utf-8 formatting.
In my case, I use an additional formula in excel to concatenate into one row per cell, it is complete with th enecessary commas. I then select each and copy onto a blank TextMate file, which I then save as a .csv with utf-8.
sudo scp /Users/username/Documents/costcenters-forimport.csv user@erpnextserver:/tmp/
ssh user@erpnextserver
mysql -u root -p
use
command: (change db id accordingly)use 1af3b1647ab2768;
LOAD DATA INFILE ‘/tmp/costcenters-forimport.csv’
REPLACE
INTO TABLE `tabCost Center`
CHARACTER SET utf8
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’;
Once you have done it once you will be able to figure out how to to the same process for other data items. It also helps to have as a sort of “backup” in case you need to rebuild an ERPNext server with the basic structure of accounts, cost centers, item groups, warehouses, and other data so you can then image the “Production ready” server for immediate deployment for each new business you work with
Any suggestions on how best to accomplish all these steps using a python script, are welcome!
I have a major update issue. I have got a full database of items, but the suppliers were not linked. I created a new file with the supplier column and started to upload it in bulk (3,000 records per time) I have 95,000 records. But the system ignores the update, it doesn’t add the new column data!
URGENT HELP NEEDED
Thanks, we know about it when we need it
Is it possible to update a custom field with the value of another field?