This is part of a series of customization projects I would like to give back to the community, feedback about the script and the way we implement is welcome.
Target;
- calculate total price by weight (total weight * price per kg. = total price for weight)
- calculate price list rate by dividing total price for weight against units ( total price for weight / units = price list rate)
- calculate the average weight per unit to control warehouse management, because some products look the same but have different average weights, so are different products, but when they weigh the products and the system calculates average weight, and turns out to be incorrect the sales person can immediately notify warehouse to double check the order. ( item weight / units = average weight )
How:
- create the custom fields in “Sales Order Item” needed to achieve all the above as follows:
1- Item Weight (item_weight)
2- Item Weight Price (item_weight_price)
3- Item Weight Price Total (item_weight_price_total)
4- Qty in Units (qty_real)
5- Average Weight (average_weight_unit)
-
Change precision for rate field to 9, so when the script calculates the weight price to unit price it will show the correct value in “amount” field, because if you have a lot of decimals with precision not set it will round off incorrectly.
-
In this case you would not want the rate to show 9 decimals in the print format, so there is a way in jinja to show only 2 decimals
{{'{:20,.2f}'.format(row.rate,'')}} -
Next comes the script:
frappe.ui.form.on("Sales Order Item", "item_weight", function(frm, doctype, name) { var row = locals[doctype][name]; row.item_weight_price_total = row.item_weight * row.item_weight_price; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "item_weight_price", function(frm, doctype, name) { var row = locals[doctype][name]; row.item_weight_price_total = row.item_weight * row.item_weight_price; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "item_weight_price_total", function(frm, doctype, name) { var row = locals[doctype][name]; row.price_list_rate = row.item_weight_price_total / row.qty_real; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "qty_real", function(frm, doctype, name) { var row = locals[doctype][name]; row.price_list_rate = row.item_weight_price_total / row.qty_real; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "qty_real", function(frm, doctype, name) { var row = locals[doctype][name]; row.rate = row.item_weight_price_total / row.qty_real; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "qty_real", function(frm, doctype, name) { var row = locals[doctype][name]; row.qty = row.qty_real; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "qty_real", function(frm, doctype, name) { var row = locals[doctype][name]; row.amount = row.price_list_rate * row.qty_real; refresh_field("items"); }); frappe.ui.form.on("Sales Order Item", "qty_real", function(frm, doctype, name) { var row = locals[doctype][name]; row.average_weight_unit = row.item_weight / row.qty_real; refresh_field("items"); });
- These custom fields and script above should be replicated according to your workflow (Sales Order → Delivery Note → Sales Invoice), just change the
frappe.ui.form.on("Sales Invoice Item",into the correct doctype in the script. - This method has given us the fastest and easiest way to sell in one unit and manage stock in another unit in a fast and accurate manner.
Needs:
The warehouse control by average weight is great, but we are trying to control the average weight automatically, so that if the sales person inputs the data and the script has calculated the average weight, there should be a validation against minimum average weight and maximum average weight, if the calculated average weight is less or more than min/max allowed, sales person should at least get a warning message popup.
- for this we have created 2 custom fields in item master, called “min_average_weight” and “max_average_weight” and they are fetched into “Sales Order Item” when we choose the item, so far so good. Now the script to validate in child table. Keep you updated…
This was possible thanks to @kolate_sambhaji @jof2jc @nabinhait @max_morais_dmm @rmehta and the community through all the topics