Try ERPNext Buy Support Partners Foundation Foundation Members

Child Table Calculation

Hello all,

I created 1 Child Table with 4 columns, now i want to calculate total (sum) of value in 1 particular column and display the result of this column’s total in other filed (which not the part of child table).

How can achieve this, i followed few topics from this forum but nothing will help me. :disappointed:

Any help will be appreciate.

Thanks,
Hardik Gadesha

1 Like

You can use a custom script to listen to an event, say maybe when a user keys in a value in the field which will then trigger some code to sum up the 4 fields and put the result into another field.

Something like this would work:

frappe.ui.form.on("Sales Order Item", "rate", function(frm, cdt, cdn) {
    var item = locals[cdt][cdn];
    var result = a + b + c;
    item.result_field = result;
});
1 Like

Thanks @bohlian

Below is my scenario.

Here i want to calculate sum of data field in material weight column and display this sum to total weight field.

How can i do this ?

Thanks

frappe.ui.form.on("Child Doctype Name", "material_weight_field_name", function(frm, cdt, cdn) {

   var material_details = frm.doc.child_table_field_name
   var total = 0
   for(var i in material_details) {
	total = total + material_details[i].material_wight_field_name
	}

	frm.set_value("total_weight_field_name",total)
});

Update field and child table name correcty

2 Likes

Thanks @Hafees_Kazhunkil.

I will check this, whether it’s working for me or not. :slight_smile:

I have few queries : On which event it will execute ?
: Child Table Doctype means what is the name of child doctype of name of the doctype where i use this child table ?
: Material details menas table filed name in this doctype ?

Hello @Hafees_Kazhunkil.

It gives 0 output after i input data in material weight column, and after move to 2nd raw, it would not change any thing and output remains same 0. :disappointed:

Here is my code :

frappe.ui.form.on(“Material Table”, “material_weight”, function(frm, cdt, cdn) {

var material_details = frm.doc.material_weight;
var total = 0
for(var i in material_details) {
total = total + material_details[i].material_wight
}

frm.set_value(“total_weight”,total)

});

Material Table = child table doctype
material_weight_field_name = material_weight ( field name in child table)
Child_table_field_name = ?? (not getting)
total_weight_field_name = total_weight

I have created
child table doctype = Material Table
Child table field names = material_name, khana_no, material_weight, moisture
current doctype where i use child table = Body Template
table filed name in Body Template form = material

Please Guide me.

frappe.ui.form.on("Material Table", "material_weight", function(frm, cdt, cdn) {

   var material_details = frm.doc.material;
   var total = 0
   for(var i in material_details) {
	total = total + material_details[i].material_weight
	}

	frm.set_value("total_weight",total)

});

please try

1 Like

@Hafees_Kazhunkil It gives NaN in total_weight field. I have to wright this script in Body Template.js . Right ?

please share your doctype screen shot of both child and parent.field type for weight must be integer or float.

write script in custom script module

@Hafees_Kazhunkil

Child Table SS :

Body Tamplate SS :

To update custom script go to

setup->custom script

select doctype as Body Template
paste above script in script column then save.

then try
it will calculate total on material weight event

1 Like

Thanks @Hafees_Kazhunkil .

It’s working for me now.

I have other queries as well.

As shown in above screen shot of my table. I want to calculate moisture weight as per moisture entered in moisture column. Like

Material weight = 5, moisture = 10% [ MW * Moisture] = 0.5
Material weight = 10, moisture = 10% [ MW * Moisture] = 1
Material weight = 15, moisture = 10% [ MW * Moisture] = 1.5

Total weight = 5+10+15 = 30
Total Moisture weight = 3 Like wise

How it will be calculated ?

Create a new float field Total Moisture weight in Body Template

frappe.ui.form.on("Material Table", "material_weight", function(frm, cdt, cdn) {

   var material_details = frm.doc.material;
   var total = 0
   var total_moisture_weight = 0
   for(var i in material_details) {
	total = total + material_details[i].material_weight
   total_moisture_weight = total_moisture_weight + ((material_details[i].material_weight*material_details[i].moisture)/100)
	}

	frm.set_value("total_weight",total)
   frm.set_value("total_moisture_weight",total_moisture_weight)

 });

please check.event field given as material weight

Thanks For your help @Hafees_Kazhunkil

I stuck at another point. I successfully done this code and it’s working for me after some changes. Now my questions is like, i fetch this table and all the calculated values in another doctype. But now if after fetching all the values from table, i want to add new raw in destination table with some values and with addition of this new raw, i want modification in fetched total values.

For ex. I fill Table A, with adding new raw, it will calculate some data and gives result to total field. I will fetch this Table A to Table B and also fetch total. Now i will add new raw in Table B with some values. Now i want to modify total automatically after adding new raw in Table B. total was fetched from doctype containing Table A. How to achieve this ?

Regards,
Hardik Gadesha

Hello @Hafees_Kazhunkil

I have successfully calculate values of child tables and display it’s sum to parent doctype field. Now if i remove raw from child table. than how can i subtracts this raw value of child table from parent total field ?

Thanks,
Hardik Gadesha

frappe.ui.form.on("Material Table", "child_table_name_remove", function(frm, cdt, cdn) {

   var material_details = frm.doc.material;
   var total = 0
   var total_moisture_weight = 0
   for(var i in material_details) {
	total = total + material_details[i].material_weight
   total_moisture_weight = total_moisture_weight + ((material_details[i].material_weight*material_details[i].moisture)/100)
	}

	frm.set_value("total_weight",total)
   frm.set_value("total_moisture_weight",total_moisture_weight)

 });

use event as child_table_name_remove

Thanks @Hafees_Kazhunkil

Its Working for one table :slight_smile: . But not for other one :disappointed:

Here is my code for other table

frappe.ui.form.on(“Chemical Table”, “chemical_details_remove”, function(frm, cdt, cdn) {
var chemical_details = frm.doc.chemical_details;
var chemical_weight = 0
for(var k in chemical_details) {
chemical_weight = chemical_weight + chemical_details[k].chemical_weight;
}
cur_frm.set_value(“chemical_weight”,chemical_weight)
});

Can you help me ?

Hello @community,

I have a query in child table calculation. I have created following table

I want to calculate Net (%) of particular material w.r.t total net weight. (Here 5940 kg) . So, here, all net weight of particular material is 1980 kg. So Net (%) of each should be 33.33 %. But when ever i add new material entry with it’s respective weight and moisture, then it calculates it’s net (%) but did not change previously calculate net (%) of other materials. It Means, 100 % Net will be modified after entry of second material and so on.

This is My code for calculation.

frappe.ui.form.on(“Material Table Weigh Bridge”, “moisture”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “net_weight”, d.material_weight - (d.material_weight * (d.moisture/100)));
var material = frm.doc.material;
var total = 0
var net_weight = 0
for(var j in material) {
total = total + material[j].material_weight
net_weight = net_weight + material[j].net_weight;
cur_frm.set_value(“total_weight”,total);
cur_frm.set_value(“net_weight”,net_weight);
var Moisture_Pers = ((frm.doc.total_weight - frm.doc.net_weight) * 100) / frm.doc.total_weight;
cur_frm.set_value(“moisture_weight”,Moisture_Pers);
}
frappe.model.set_value(d.doctype, d.name, “net”, (material[j].net_weight /frm.doc.net_weight) * 100);
});

/ Material table calculation, on event of material weight /

frappe.ui.form.on(“Material Table Weigh Bridge”, “material_weight”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “net_weight”, d.material_weight - (d.material_weight * (d.moisture/100)));
var material = frm.doc.material;
var total = 0
var net_weight = 0
for(var j in material) {
total = total + material[j].material_weight
net_weight = net_weight + material[j].net_weight;
cur_frm.set_value(“total_weight”,total);
cur_frm.set_value(“net_weight”,net_weight);
var Moisture_Pers = ((frm.doc.total_weight - frm.doc.net_weight) * 100) / frm.doc.total_weight;
cur_frm.set_value(“moisture_weight”,Moisture_Pers);
}
frappe.model.set_value(d.doctype, d.name, “net”, (material[j].net_weight /frm.doc.net_weight) * 100);
});

Any Help will be appreciate. :slight_smile:

Thanks,
Hardik Gadesha

I think this line should inside for loop.

Thanks For your efforts. But I tried it as well.

This line inside For loop will give same result. As d.doctype and d.name stores current row values, so it changes value of that particular row, at which we change material weight or moisture value. It does not change all table value.

What should i do for this ?