[New Feature] copy multi records from Excel, paste into child table directly


Currently there are the 2 following import features

  1. import the doctype(document) together with the child table
  2. import the child table via the upload button at right corner of the child table grid

but these 2 import features are not so intuitive and productive, too many steps involved such as the following

  1. download/prepare the template,
  2. fill with data and then save to a local file
  3. click the upload button to launch the upload dialog
  4. browse to select the file
  5. click upload file button to upload the file content
  6. click import!

except the above mentioned steps, the other missing function of the import/upload feature is that it only copy the exact content per each uploaded field, in other word it does not auto fetch the relevant fields based on other depended key field, e.g in template file, if item code is maintained, but no price (either leave it empty or exclude price field), after successful import / upload , system will not auto get the item’s price from price master, this is the badly needed feature to be provided by import/upload!

What is more, for most of the standard doctypes, such as purchase order, sales order, stock movement, the child table upload feature is not activated by default!

Here my simple solution for copy paste directly from Excel

  1. prepare the raw data in Excel, make sure the first row is the field name
  2. drag and copy(ctrl+C) the content
  3. place the cursor into the target input field( the same scan bar code field), paste (Ctrl + V)

you can try on beta.erpnext.com for sales order at the moment.

Here what I have done

  1. Customize the sales order form, adding one custom field scan bar code above the items field
  2. create a new custom script with the following code( you can check it via beta.erpnext.com)
frappe.ui.form.on("Sales Order", {
    scan_bar_code: function(frm, cdt, cdn){
		if (frm.doc.scan_bar_code){	
			frappe.db.get_value('Item', {barcode: frm.doc.scan_bar_code}, 'item_code', (r) => {			
				if(r !==undefined){					
					var row_count=frm.grids[0].grid.grid_rows.length;
					var cur_grid= frm.grids[0].grid
					var find_exist_row = "no";
					for (var i = 0; i < row_count; i++) {
						if (cur_grid.grid_rows[i].doc.item_code ===undefined) { 							
							find_exist_row = "empty";
							break;
						}						
						else if (cur_grid.grid_rows[i].doc.item_code ===r.item_code) { 
							find_exist_row = "yes";						
							break;
						}						
					}
					if (find_exist_row === "no"){
						var new_row= cur_grid.add_new_row()
						if (new_row !==undefined){
							frappe.model.set_value(cur_grid.doctype, new_row.name, "item_code", r.item_code);
						};								
					}
					else if (find_exist_row === "empty"){
						frappe.model.set_value(cur_grid.doctype, cur_grid.grid_rows[i].doc.name, "item_code", r.item_code);
					}
					else{
						if (cur_grid.grid_rows[i].doc.qty !=undefined){
							frappe.model.set_value(cur_grid.doctype, cur_grid.grid_rows[i].doc.name, "qty", cur_grid.grid_rows[i].doc.qty + 1);
						}
					}					
				}
			});					
			frm.set_value('scan_bar_code','');	
		}
	return false;
	},	
    refresh: function(frm,cdt,cdn){
		var myElement= $(cur_frm.get_field('scan_bar_code').wrapper).find(':text')[0]		
		myElement.onpaste = function(e) {
		  var pastedText = undefined;
		  if (window.clipboardData && window.clipboardData.getData) { // IE
			pastedText = window.clipboardData.getData('Text');
		  } else if (e.clipboardData && e.clipboardData.getData) {
			pastedText = e.clipboardData.getData('text/plain');
		  }		  		 
		  var data = frappe.utils.csv_to_array(pastedText,'\t');
		  if (data.length > 1 || data[0].length > 1){			  
			  var cur_grid= cur_frm.get_field('items').grid;
			  var fieldnames = data.shift();
			  var row_name = undefined;			  
			  $.each(data, function(i, row) {				
					var blank_row = true;
					$.each(row, function(ci, value) {
						if(value) {
							blank_row = false;
							return false;
						}
					});
					if(!blank_row) {
						var row_count = cur_grid.grid_rows.length;
						if (cur_grid.grid_rows[row_count-1].doc.item_code===undefined){
							//row_name= $(e.target).closest("div:has(*[data-name]) .grid-row").data("name")							
							row_name = cur_grid.grid_rows[row_count-1].doc.name;
						}
						else
						{
							var new_row = cur_grid.add_new_row();
							row_name = new_row.name;
						}						
						if (row_name){
								$.each(row, function(ci, value) {
									var fieldname = fieldnames[ci];						
									frappe.model.set_value(cur_grid.doctype, row_name, fieldname, value);						
								});
							};	
					};					
				});    
				return false; // Prevent the default handler from running.
			}
		};		
	},
});
12 Likes

any idea whether this feature to be merged into the core?

@szufisher, this is awesome.

It increases the usability of the system. These are basic stuff of todays UIs

Great! You should start by creating a Pull Request. We can discuss the implementation details there.

2 Likes

@szufisher, I have been using this from last two days. Works awesome. Following are my observations after using it, please see how they can be taken care of.

  1. In your barcode feature, [New Feature Proposal]Barcode scan to create/update records in child table, useful for stock movement and orders, the item qty gets increased upon repeat. But when we copy the table here, it does not.
    Now , if the code is not made that way, then its perfectly fine, let it be as is.

  2. Speed - The page becomes unresponsive or it takes time after adding around 50-80 items. What I do is - save invoice, hard refresh and then again paste the remaining items.

@szufisher, any thoughts on this?

for speed, if your manual refresh is the solution then the code can do the same, lets try

pull requested submitted.