Calculation of data from child table

1

Hi,

I want to multiply the valuation rate with the quantity in the child table of Sales Invoice. I tried the following script but the output is nothing:

frappe.ui.form.on("Sales Invoice Item", "qty", function(frm, cdt, cdn){
  var d = locals[cdt][cdn];
  frappe.model.set_value(d.doctype, d.name, "cost_amount", d.qty * d.valuation_rate);

  var total = 0;
  frm.doc.quotesection.forEach(function(d) { total += d.cost_amount; });

  frm.set_value('total_cost', total);
  refresh_field("total_cost");

});

Here total_cost is the field in Sales Invoice which will calculate the qty * valuation rate in the child table. The valuation_rate field is filed in the child table. I need to find the total for the qty*rate and for all rows as they are update and put all the data in the total_cost field.

Please help anyone.

2

Write script for Sales Invoice!

What is quotesectio ?

3

It’s similar to yours.

4

Dear @magic-overflow ,

What i want is to first calculate the amount of cost as in cost*qty and put in the field cost_amount in the child table Sales Invoice Item , then sum up the cost_amount in each row and put in total_cost in the Sales Invoice table.

The once you shared is just to sum up the quantity and give the total quantity .

Please help

5

Thank you @saurabh6790,

i run the following code, still the field is empty. My goal is : first calculate the amount of cost as in cost*qty and put in the field cost_amount in the child table Sales Invoice Item , then sum up the cost_amount in each row and put in total_cost in the Sales Invoice table.

frappe.ui.form.on(“Sales Invoice”, “qty”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “cost_amount”, d.qty * d.valuation_rate);

var total = 0;
frm.doc.items.forEach(function(d) { total += d.cost_amount; });

frm.set_value(‘total_cost’, total);
refresh_field(“total_cost”);

});

6

Did you added valuation rate field in the sales invoice item? Because standard doctype don’t have this field.
If you have added this as a custom field then it seems that the system not set the value in the field. Before above method you have to write the code to set the valuation rate of an item in the field valuation rate

1 Like
7

I think your event fired before valuation_rate has value. Can you try below code? Once Sales Invoice saved, you will see the calculation.

frappe.ui.form.on("Sales Invoice", {
	validate: function(frm) {
		var total = 0, cost_amount = 0;
		$.each(frm.doc.items || [], function(i, d) {
                    cost_amount = 0;
                    cost_amount = d.qty * d.valuation_rate;
                    frappe.model.set_value(d.doctype, d.name, “cost_amount”, cost_amount);
			total += flt(cost_amount);
		});
		frm.set_value("total_cost", total);
	}
});
8

Thank you @magic-overflow,

The following code did my job though :slight_smile:

frappe.ui.form.on("Sales Invoice Item", "qty", function(frm, cdt, cdn){
// cost for total cost per item
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, "cost_amount", flt(d.qty * d.valuation_rate));
});

frappe.ui.form.on("Sales Invoice", "refresh", function(frm, cdt, cdn) {
// code for calculate total and set on parent field.
total_cost_amount = 0;
$.each(frm.doc.items || [], function(i, d) {
total_cost_amount += flt(d.cost_amount);
});
frm.set_value("total_cost_amount", total_cost_amount);
});
1 Like
9

You may have wrong total cost amount when an item removed. You should consider event validate instead.

Check my link, I mentioned above, the reason they chose event validate.