[New Feature Proposal]Barcode scan to create/update records in child table, useful for stock movement and orders

I would like to introduce a very simple barcode scan feature, it simply add one barcode field at the target document header, users scan material barcode( or manually input barcode then press ENTER), the javascript will search Item Master by barcode, add a new record in the items child table if first time scan, otherwise locate to the existing records and increase qty by 1.
this feature can be used by warehouse guy to do stock movement and office clerk for orders(PO, SO etc) which include child tables with Item and Qty field.
scan_barcode

Here what I have done

  1. in the customize form, add one field mycode(field name barcode is already used by ERPNext) for Purchase Order doctype
  2. in purchase_order.js add the following field function( if hosted on SaaS, the code can be maintained as custom script)
mycode: function(frm, cdt, cdn){
		var row = frm.fields_dict["items"].grid.grid_rows.filter(function(d)
			{ return d.doc.barcode===frm.doc.mycode })[0];
		if (row ===undefined){			
			frappe.db.get_value('Item', {barcode: frm.doc.mycode}, 'name', (r) => {
				if(r !==undefined){
					var rowlength=frm.fields_dict['items'].grid.grid_rows.length
					if (rowlength>=1){
						lastrowindex = rowlength-1;
						lastrow = frm.fields_dict['items'].grid.grid_rows[lastrowindex];
						if (lastrow.doc.item_code ===undefined){
							newrowname = lastrow.doc.name;
						}
						else{
							newrowname =frm.fields_dict['items'].grid.add_new_row().name
						}
					}
					else{
						newrowname =frm.fields_dict['items'].grid.add_new_row().name
					}
					frappe.model.set_value(frm.fields_dict['items'].grid.doctype, newrowname, "item_code", r.name);
				}
			});
			
		}
		else{

			frappe.model.set_value(frm.fields_dict['items'].grid.doctype, row.doc.name, "qty", row.doc.qty + 1);

		}	
		frm.set_value('mycode','');	
	},
  1. this can be easily extended to other doctypes which have Item in the child table.

any idea and feedback are welcomed

9 Likes

This is good, I will test it.

Scanning barcodes using the existing item field in the item grid is not smooth. This has the potential to ease the pain.

Will test and let you know

Good job

1 Like

Actually this is the feature implemented by Odoo based ERP www.gooderp.org

it is working on my v10 instance for sales order!

scan_bar_code: function(frm, cdt, cdn){
		if (frm.doc.scan_bar_code!==undefined){	
			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;
	},
2 Likes

in latest v11(beta.erpnext.com), either the tab or ENTER key triggered on field change will also auto open the grid row form which is not as expected, so return false; to be added , see the updated code.

I believe this can tackle the following:

Thank @kennethsequeira @szufisher

I am using the SaaS of ErpNext, have added the scan_bar_code in Customize Form > Sales Invoice

Secondly following is the code I added in Custom Script as below:

frappe.ui.form.on('Sales Invoice', {
	validate: function(frm) {
		
		var grouped_list = []
		var already_filtered = [];

		// Loops through all items of array
		for(var i = 0, len1=frm.doc.items.length; i < len1; i++) {

			// Skip rest of the loop if the item has been grouped with some previous item
			if(already_filtered.indexOf(i) != -1) {
				continue;
			}

			// Split item name by - , to separate name color size
			var item_name_formats = frm.doc.items[i].item_name.split(' - ');

			// Create an object with item color as key and item size as value
			var item_variance = {};
			item_variance[item_name_formats[2]] = [item_name_formats[3]];

			// Object to hold item temporary before grouping
			var grouped_item = {
				idx : i,
				item_code : frm.doc.items[i].item_variant_of,
				item_name : [item_name_formats[0] + ' - ' + item_name_formats[1]],
				gst_hsn_code: frm.doc.items[i].gst_hsn_code,
				qty : frm.doc.items[i].qty,
				uom : frm.doc.items[i].uom,
			        net_rate : frm.doc.items[i].net_rate,
				net_amount: frm.doc.items[i].net_amount
			};


			// Loop to check the current item with rest of the items
			for(var j = i+1; j < len1; j++) {

				// Skip this loop if the current item has been grouped with some previous item.
				if(already_filtered.indexOf(j) != -1) {
					continue;
				}

				// Check if item_variant_of and net_rate is equal of 2 items.
				if(frm.doc.items[i].item_variant_of === frm.doc.items[j].item_variant_of && frm.doc.items[i].net_rate === frm.doc.items[j].net_rate) {
					
					// Add item quantity
					grouped_item.qty += frm.doc.items[j].qty;

					// Add net amount
					grouped_item.net_amount += frm.doc.items[j].net_amount;

					// Split item name by - , to separate name color size
					var next_item_name_formats = frm.doc.items[j].item_name.split(' - ');

					// if this color is already present, add 1 more size to it
					// else create another key for another color
					if(item_variance[next_item_name_formats[2]]) {
						item_variance[next_item_name_formats[2]].push(next_item_name_formats[3]);
					}
					else {
						item_variance[next_item_name_formats[2]] = [next_item_name_formats[3]]
					}

					// Push the item to already_filtered array so it can be skipped later while comparing with rest of the items
					already_filtered.push(j);
				}
			}

			// Loop through all colors and join sizes by ,
			var variances = '';
			for(var key in item_variance) {
				variances += "<br >" + key + ' - ' + item_variance[key].join(', ')
			}

			// Join item names with each variance
			grouped_item.item_name = grouped_item.item_name + variances;

			// Add the grouped item to the final output list
			grouped_list.push(grouped_item);

		}
			frm.set_value('grouped_items', JSON.stringify(grouped_list));

	},
	scan_bar_code: function(frm, cdt, cdn){
		if (frm.doc.scan_bar_code!==undefined){	
			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;
	}
})

I am getting the following error:

SyntaxError: Invalid or unexpected token
    at Class.setup (https://pratham.erpnext.com/assets/js/form.min.js?ver=1529656413.0:2691:18)
    at _f.Frm.setup (https://pratham.erpnext.com/assets/js/form.min.js?ver=1529656413.0:172:22)
    at _f.Frm.refresh (https://pratham.erpnext.com/assets/js/form.min.js?ver=1529656413.0:446:9)
    at Class.load (https://pratham.erpnext.com/assets/js/form.min.js?ver=1529656413.0:87:33)
    at https://pratham.erpnext.com/assets/js/form.min.js?ver=1529656413.0:73:10
    at Object.callback (https://pratham.erpnext.com/assets/js/desk.min.js?ver=1529656413.0:5516:6)
    at Object.callback [as success_callback] (https://pratham.erpnext.com/assets/js/desk.min.js?ver=1529656413.0:1437:16)
    at _ (https://pratham.erpnext.com/assets/js/desk.min.js?ver=1529656413.0:1461:34)
    at Object. (https://pratham.erpnext.com/assets/js/desk.min.js?ver=1529656413.0:1562:5)
    at i (https://pratham.erpnext.com/assets/frappe/js/lib/jquery/jquery.min.js:2:27151)

Please help me with this error.

I really appreciate the efforts by @szufisher

@rishinikhil
just tried on beta.erpnext.com, it works for sales invoice also, you can check it.


it turned out that system auto converted some of the quote sign " ’ after upload into the forum thread( maybe due to my windows system in Chinese locale?), see the 2 version comparison in PC Charm as attached,
you can copy the most updated/correct version of the code either from beta.erpnext.com or here
SAPScript/scan_bar_code at master · szufisher/SAPScript · GitHub

1 Like

This is a really excellent feature and you guys deserve recognition for it. Please submit a pull request to get it into the core.

1 Like

@szufisher, Yes. I copied the custom script from beta.erpnext and it worked.

good to hear that, I am thinking to submit a new pull request, adding this feature into core!

3 Likes

@szufisher, this is failing.

As soon as the count crosses 60-70 items, the page slows down. It becomes a confusion for the data entry guy whether the record has been added or not. He then scrolls down to check it, and finds that it has not been added.

Now how do we go about it? following is what I can think

  1. An alert which tells that the item has been added.
  2. Reverse the order of rows, so that the data entry guy can check without scrolling. i am not sure if this is possible.
  3. Side by side view like in POS.

What do you think?

yes, the same feature in other system(actually vba Excel based)has some kind of feedback per each scan, we got to add these kind of feedback!

@rishinikhil
the updated code can be copied from here SAPScript/ERPNext Barcode Scan V2 at master · szufisher/SAPScript · GitHub

let me know the result, thanks.

@rishinikhil,
can you let me know the current status of the updated version?

@szufisher, I am sorry, I was travelling. Will check and get back to you. Thanks.

I copied @szufisher and put it in a branch with some additional changes at https://github.com/SaiFi0102/erpnext/tree/ChildTable-Barcode-Scan (branch of master: 10.1.43)

I also added a TODO list and a question:
-Determine whether it would be better to wait for enter pressed/blur event to run the handler or use a timeout to run the handler (in my branch I’m currently using timeout)
-Allow the scan field to search for: batch number, serial number, item code

I have deployed the code, will let you know the results by Wednesday. Thanks a lot for your efforts.

@rishinikhil,
Can you help to update the current status? thanks.

Hi @szufisher, the team says its better than before. They like the feature which tells them the row number when the item is incremented.

Its also not slowing up the page when items more than 70-80 are added.

What did you do for the page speed?