Sum fields amount From Child Table V7

[Randy_Lowery]

Hello i need some help with custom scrip, i have created a custom table with quantity and money denomination for sales register day closure it looks like this

Scrips to calculate the value total are:

frappe.ui.form.on(“Denominaciones”, “denominacion”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “valor”, d.denominacion * d.cantidad);
});

and

frappe.ui.form.on(“Denominaciones”, “cantidad”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “valor”, d.denominacion * d.cantidad);
});

What scrip should i use to get sum of all rows ( Valor ) value into a field called balance_real

I tried this scrip from another post without luck.

frappe.ui.form.on(“Denominaciones”, “cantidad”, function(frm, cdt, cdn){
var d = locals[cdt][cdn];
frappe.model.set_value(d.doctype, d.name, “valor”, d.denominacion * d.cantidad);

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

frm.set_value(‘balance_real’, total);

});

Any help is much appreciated have a nice day…

[cpurbaugh]

Link to related thread for crossreferencing:

Calculate a value for custom field

Hi I have a custom field deposit_required on DocType Quotation which should be 60% of the grand total deposit_required = grand_total*0.6 The following script has updated all existing quotes but when I start a new quote I can’t get the value for deposit_required cur_frm.cscript.deposit_required = function(doc) { rate_calc = (doc.grand_total / 100 * 60); doc.deposit_required = rate_calc; refresh_field(“deposit_required”); } Appreciate any help.

[Randy_Lowery]

Thanks @cpurbaugh i already read it before posting my question. I understand how to calculare fields within a row and how to calculate within a doctype but iam trying to sum fields from rows into a field in master doctype but cant find any reference in thar thread. thanks in advance

[cpurbaugh]

I think what you will have to do is write a ‘for’ loop to do this. I’ll work on this tomorrow, because it’s something I need as well. As for the link, I was just putting it there so that anyone coming across either thread sees the link.

Your value adding functions look correct.

frappe.ui.form.on("Denominaciones", {
    "denominacion": function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
    }
});

frappe.ui.form.on("Denominaciones", {
    "cantidad": function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
    }
});

frappe.ui.form.on("Denominaciones", {
    "cantidad": function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        for (i = 0; i < [DONOTYETKNOW]; i++) {
	    total += d.valor;
        }
        frm.set_value('balance_real', total);
    }
});

The part [DONOTYETKNOW] should be how many lines there are in the child table.

I’ll update this post as I look deeper into this.

Different possible solution (From a thread detailing my problem with the same thing, from a while back):

Mathematical Operations

@cpurbaugh, In my case this worked even keeping the doctype as child table frappe.ui.form.on(“Packing Materials”, “ordered”, function(frm,cdt,cdn) { var d = locals[cdt][cdn]; frappe.model.set_value(d.doctype, d.name, “amount”, d.rate * d.ordered); frm.fields_dict[“amount”].grid.grid_rows_by_docname[cdn].amount.refresh(); alert(frm.doc.amount); });

[Randy_Lowery]

Good day . @cpurbaugh the code almost work the only thing is that it multiply the last line for the [DONOTYETKNOW] value for example: if i input 9 as the total of denomination lines it will get the last value and multiply by 9 and updates the balance_real value. i already saw this How to get sum of field from child table but i cant get it to work maybe you’ll get it … thanks again.

[cpurbaugh]

frappe.ui.form.on("Denominaciones", {
    "cantidad": function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
        var total = 0;
        frm.doc.denominaciones.forEach(function(d) { total += d.valor; });
        frm.set_value('balance_real', total);
    }
});

Ya, this does seem to be the same as what you posted in your original post, and it seems like it should work. Can you give me the following information:

Parent DocType:
Child Table DocType:
Child Table fieldname (in parent doctype):

Also, I’m assuming I have the actual calculations correct:

valor = denominacion * cantidad
balance_real = valor * number of lines

[Randy_Lowery]

yes it should but is the first option i tried without any luck.

Parent DocType: Arqueo de Caja
Child Table DocType: Denominaciones
Child Table fieldname (in parent doctype): Denominaciones

valor = denominacion * cantidad (true)
Balance_real= is the sum of al ‘valor’ values for example:

        Denominacion               Cantidad                       Valor

line 1 : 500 … … 2 … 1000
line 2 : 100 … 5 … 500

Balance real at this point should be 1500.

[cpurbaugh]

I think I’ve found your problem. You need to put the script in the “Arqueo de Caja” custom script file and point the script at “Arqueo de Caja”.

frappe.ui.form.on("Arqueo de Caja", {
    "cantidad": function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
        var total = 0;
        frm.doc.denominaciones.forEach(function(d) { total += d.valor; });
        frm.set_value('balance_real', total);
    }
});

[Randy_Lowery]

Yes im working on parent doc, theres how the scrips looks like, the funtion from calculating the table lines are working but still i cant ge any value on my balance real field

[cpurbaugh]

Try this:

frappe.ui.form.on("Denominaciones", {
    denominacion: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
        frm.doc.denominaciones.forEach(function(d) { total += d.valor; });
        frm.set_value('balance_real', total);
    }
});

frappe.ui.form.on("Denominaciones", {
    cantidad: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "valor", d.denominacion * d.cantidad);
        frm.doc.denominaciones.forEach(function(d) { total += d.valor; });
        frm.set_value('balance_real', total);
    }
});

[cpurbaugh]

@Randy_Lowery Please try this and get back to me, I only have an hour before I have to go offline, and I’d like to see this work for you today.

[Randy_Lowery]

i haven’t had any luck with the code definitely this line :

frm.doc.denominaciones.forEach(function(d) { total += d.valor; });

isnt updating the value because i tried the same scrip with : for (i = 0; i < [DONOTYETKNOW]; i++) your first postand at least is calculating something. ( the las updated line multiplied by the [DONOTYETKNOW] value.

as for now this is all thats works

for calculating fields within a row.

if i instead use this

frappe.ui.form.on(“Denominaciones”, {
cantidad: function(frm, cdt, cdn) {
var d = locals[cdt][cdn];
var total = 0;
frappe.model.set_value(d.doctype, d.name, “valor”, d.denominacion * d.cantidad);
frm.doc.denominaciones.forEach(function(d) { total += d.valor; });
frm.set_value(‘balance_real’, total);
}
});

theres no calculation at all at row level and total field

also tried this one

from this thread Calculate Total Qty of items table of Sales Invoice - #11 by helenelollipops

And still isnt working, but thank for been patient and pointing me out possible solutions.

[cpurbaugh]

It’s wierd, because I’ve now got it working on my end. I’ll give you all of the information on my side and we’ll see if that helps I guess.

Functions:

frappe.ui.form.on("Testbed Child", {
    len: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "area", d.len * d.wid);
        frm.doc.table.forEach(function(d) { total += d.area; });
        frm.set_value('total_area', total);
    }
});

frappe.ui.form.on("Testbed Child", {
    wid: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "area", d.len * d.wid);
        frm.doc.table.forEach(function(d) { total += d.area; });
        frm.set_value('total_area', total);
    }
});

Parent DocType: Testbed DocType A
Child Table Doctype: Testbed Child

Calculation within table: len * wid = area
Calculation outside of table: sum of all areas
Sum field: total_area

[Randy_Lowery]

thats good news thanks!

[cpurbaugh]

see above post.

[cpurbaugh]

frappe.ui.form.on("[CHILD TABLE DOCTYPE]", {
    [CHILDTABLEMULTIPLE1]: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "[CHILDTABLEPRODUCT]", d.[CHILDTABLEMULTIPLE1] * d.[CHILDTABLEMULTIPLE2]);
        frm.doc.[CHILDTABLEFIELDINPARENT].forEach(function(d) { total += d.[CHILDTABLEPRODUCT]; });
        frm.set_value('[PARENTDOCTYPETOTALFIELD]', total);
    }
});
frappe.ui.form.on("[CHILD TABLE DOCTYPE]", {
    [CHILDTABLEMULTIPLE2]: function(frm, cdt, cdn) {
        var d = locals[cdt][cdn];
        var total = 0;
        frappe.model.set_value(d.doctype, d.name, "[CHILDTABLEPRODUCT]", d.[CHILDTABLEMULTIPLE1] * d.[CHILDTABLEMULTIPLE2]);
        frm.doc.[CHILDTABLEFIELDINPARENT].forEach(function(d) { total += d.[CHILDTABLEPRODUCT]; });
        frm.set_value('[PARENTDOCTYPETOTALFIELD]', total);
    }
});

[Randy_Lowery]

just to make sure with my values should look like this:

frappe.ui.form.on(“Denominaciones”, {
denominacion: function(frm, cdt, cdn) {
var d = locals[cdt][cdn];
var total = 0;
frappe.model.set_value(d.doctype, d.name, “valor”, d.cantidad * d.denominacion);
frm.doc.table.forEach(function(d) { total += d.valor; });
frm.set_value(‘balance_real’, total);
}
});

frappe.ui.form.on(“Denominaciones”, {
cantidad: function(frm, cdt, cdn) {
var d = locals[cdt][cdn];
var total = 0;
frappe.model.set_value(d.doctype, d.name, “valor”, d.cantidad * d.denominacion);
frm.doc.table.forEach(function(d) { total += d.valor; });
frm.set_value(‘balance_real’, total);
}
});

still isnt working

[cpurbaugh]

instead of frm.doc.table.foreach it should be frm.doc.denominaciones.foreach

[cpurbaugh]

@Randy_Lowery

[Randy_Lowery]

well definetly ill try crating a new table with your fields for testing since i cant figure it out with my own

n o