Sum column in child table and show total in parent field

mcd.steven · November 25, 2020, 4:51am

The above example works well and even after a few years its the best complete solution for child summing to parent, thanks Mohammed.

Mohammed_Redha · November 25, 2020, 1:13pm

@mcd.steven You Are most welcome

Vesper_Solutions · January 22, 2021, 1:03pm

@Mohammed_Redha
Thanks so much mate. You’re a lifesaver, I have been crawling the forums for a week now and your code works like a charm.

Vesper_Solutions · January 22, 2021, 4:03pm

@Mohammed_Redha I have a query. Using your code, I am adding up amounts for each row to add up to a grand total. My code is:

frappe.ui.form.on(“PM Invoice”, {
amount:function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.items.forEach(function(d) { total += d.amount; });
frm.set_value(“grand_total”, total);
refresh_field(“grand_total”);
},
pminvoice_remove:function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.items.forEach(function(d) { total += d.amount; });
frm.set_value(“grand_total”, total);
refresh_field(“grand_total”);
});

where amount is the amount field for the child table and grand_total is the sum of all amounts from child table and items is the field name of the table. However I also want to calculate the amount for child table row by this logic

amount = hours * hourly_rate

where hours and hourly_rate are fields of the child table. I want to calculate the amount by this and then add all the amounts to form a grand total. So what should I add to the code?

wisharya · October 3, 2021, 12:00pm

I used the same template for my use-case

frappe.ui.form.on('PF Trainee Training Attendance Record', {
attendance_score:function(frm, cdt, cdn){
   var d = locals[cdt][cdn];
   var total = 0;
   frm.doc.attendance_table.forEach(function(d) { total += d.attendance_score; });
   frm.set_value("total_attendance", total);
   refresh_field("total_attendance");
 },
attendance_table_remove:function(frm, cdt, cdn){
  var d = locals[cdt][cdn];
  var total = 0;
  frm.doc.attendance_table.forEach(function(d) { total += d.attendance_score; });
  frm.set_value("total_attendance", total);
  refresh_field("total_attendance");
  	}
});

Parent doctype name: Training Candidates
Parent total field: total_attendance
Child table name: PF Trainee Training Attendance Record
Child table name in parent doctype: attendance_table
Column to be sum name in child table: attendance_score

But the output is different, It concatenates the values instead of addition:

Here are some snaps from the doctype and child table doctype for references:

Any help would be appreciated. Thanks.

alonbr · October 3, 2021, 1:11pm

I would do something like:

frappe.ui.form.on('Training Candidates', {
    before_save(frm) {
        var attendance_tbl = frm.doc.attendance_tabel;
        var total_attendance = 0;
		$.each(attendance_tbl, function(index, row){
			total_attendance = total_attendance + row.attendance_score;
			frm.set_value('total_attendance', total_attendance);
    		});
    });
    }
});

This way, as you save the form your total_attendance will calculate itself.

And one more thing: are you sure attendace score is a float? should it not be an integer?

wisharya · October 3, 2021, 3:43pm

Thanks a lot . It worked. And yes It should be integer instead if float.

lubem · March 10, 2022, 10:22am

can you apply this to Material Request and get Total amount using a custom field?

Mohammed_Redha · March 16, 2022, 9:25pm

Yes, you can do that

lubem · March 17, 2022, 11:54am

below is the custom code I created but not working.
frappe.ui.form.on(“Material Request Item”,{
amount: function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.items.forEach(function (d) { total += d.amount; });
cur_frm.set_value(“total_amount”, total);
refresh_field(“total_amount”);
},

items_remove: function(frm, cdt, cdn){
    var d = locals[cdt][cdn];
    var total = 0;
    frm.doc.items.forEach(function (d) { total += d.amount; });
    cur_frm.set_value("total_amount", total);
    refresh_field("total_amount");
}

});

lubem · March 17, 2022, 11:57am

The remove function for row is working amount summation is not working.
frappe.ui.form.on(“Material Request Item”,{
amount: function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.items.forEach(function (d) { total += d.amount; });
cur_frm.set_value(“total_amount”, total);
refresh_field(“total_amount”);
},

items_remove: function(frm, cdt, cdn){
    var d = locals[cdt][cdn];
    var total = 0;
    frm.doc.items.forEach(function (d) { total += d.amount; });
    cur_frm.set_value("total_amount", total);
    refresh_field("total_amount");
}

});

Mohammed_Redha · March 18, 2022, 1:45pm

You should set the trigger on item_code instead of the amount

Try this code:

frappe.ui.form.on('Material Request Item',{
    item_code: function(frm, cdt, cdn){
    var d = locals[cdt][cdn];
    var total = 0;
    frm.doc.items.forEach(function (d) { 
        total += d.amount; 
        });
        cur_frm.set_value('total_amount', total);
        refresh_field('total_amount');
},

items_remove: function(frm, cdt, cdn){
    var d = locals[cdt][cdn];
    var total = 0;
    frm.doc.items.forEach(function (d) { total += d.amount; });
    cur_frm.set_value('total_amount', total);
    refresh_field('total_amount');
    }
});

lubem · March 18, 2022, 2:18pm

Thanks. it works now

KhanhNguyenNgoc · May 21, 2022, 2:02am

Hi Modhammed, I have try your code and add a function to set up fomular for the table. But it only works , when I put them together it didn’t work. Pls told me what I do wrong. Here is my script.

frappe.ui.form.on(“Chi tiet de nghi”, {
qty:function(frm, cdt, cdn) {
const rowSelected = frm.doc.detail.find(row => row.name === cdn);
rowSelected.amount = rowSelected.qty * rowSelected.rate;
},
rate:function(frm, cdt, cdn) {
const rowSelected = frm.doc.detail.find(row => row.name === cdn);
rowSelected.amount = rowSelected.qty * rowSelected.rate;
},
})
frappe.ui.form.on(“Chi tiet de nghi”, {
amount:function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.detail.forEach(function(d) { total += d.amount; });
frm.set_value(“real_amount”, total);
refresh_field(“real_amount”);
},
detail_remove:function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.detail.forEach(function(d) { total += d.amount; });
frm.set_value(“real_amount”, total);
refresh_field(“real_amount”);
},
})

its_satya · May 27, 2022, 7:33am

Mohammed_Redha:

frappe.ui.form.on("sum test table", {
   qty_11:function(frm, cdt, cdn){
   var d = locals[cdt][cdn];
   var total = 0;
   frm.doc.table_2.forEach(function(d) { total += d.qty_11; });
   frm.set_value("total_01", total);
   refresh_field("total_01");
 },
   table2_remove:function(frm, cdt, cdn){
   var d = locals[cdt][cdn];
   var total = 0;
   frm.doc.table_2.forEach(function(d) { total += d.qty_11; });
   frm.set_value("total_01", total);
   refresh_field("total_01");
   	}
   });

Its Working!!
Thanks a lot, Sir this is the best script for Sum column in child table and show total in parent field

Rida_Batool · July 21, 2023, 7:31pm

frappe.ui.form.on(‘Wheat_Purchase’,{
qty: function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.bags_info.forEach(function (d) {
total += d.qty;
});
cur_frm.set_value(‘total_amount_for_bags’, total);
refresh_field(‘total_amount_for_bags’);
},

bags_info_remove: function(frm, cdt, cdn){
var d = locals[cdt][cdn];
var total = 0;
frm.doc.bags_info.forEach(function (d) { total += d.qty; });
cur_frm.set_value(‘total_amount_for_bags’, total);
refresh_field(‘total_amount_for_bags’);
}
});

parent doctype = wheat purchase
column whose sum is require = qty
child table name = bags_info
parent field in which i want to show sum = total_amount_for_bags

this code isn’t working, im using erpnext14