Calculation on Child Table custom fields

ERPNext
, ,
1

How to calculate custom fields, link sub-total, Total, VAT 15% and Contingency 10 % from Child Table?

calcu
calcu524×628 12.6 KB

2

Hi @Mulatu_mekonnen,

Here check its syntax, please.

frappe.ui.form.on("Child Table Name", "event_name", function(frm, cdt, cdn) {
    var doc = locals[cdt][cdn];
    frappe.model.set_value(cdt, cdn, 'total_value_field_name', doc.value1 + doc.value2);
});

Child Table Name: eg. Sales Invoice Item
event_name: Form Scripts
total_value_field_name: Total value set in field

More details about script for check it, please.

Thank You!

3

@Mulatu_mekonnen What I have understood from your post is that you have a child table and some custom fileds in the main form. You want to calculate the sub-total, total, vat and contingency based on the child table.
If so, please answer the following questions:

  1. What is the name and label of the Child Table? Ex: name=items & label=Items
  2. What is the calculation that must be done on each row of the child table that It’s sum is the sub-total? Ex: participants * unit_price
  3. Is the total equals sub-total + vat + contingency?
  4. What is the field name of sub-total, total, vat and contingency? Ex: sub_total, total, vat_amount, contingency_amount
4

@kid1194 Thank you, here is the clarifications for the doctype

  1. What is the name and label of the Child Table? Ex: name= Budget List & label=Allocated Budget
  2. What is the calculation that must be done on each row of the child table that It’s sum is the sub-total? Ex: Number of Training Days * Number of Participants * Unit Price
  3. Is the total(grand_total) equals sub-total + vat + contingency: Yes
  4. What is the field name of sub-total, total, vat and contingency? Ex:number_of_training_days, number_of_participants,unit_price,total_price,vat,contingency,grand_total
5

@NCP Thank you for help
where i should apply the script in child table or in main doctype?

6

Hi @Mulatu_mekonnen,

Please check it.

image
image992×552 30 KB

Thank You!

7

@Mulatu_mekonnen To clarify things:

  1. Main form belongs to Budget List doctype
  2. Child table linked to Allocated Budget doctype
  3. Child table field name in main form is allocated_budget

Example of Child Table Field Name

Example of Child Table Field Name
Example of Child Table Field Name1260×603 110 KB

If the points above are correct then the code that you are looking for is this.

function runCalculation(frm, doc) {
    var total_price = 0;
    $.each(doc.allocated_budget, function(row) {
        total_price += (row.number_of_training_days * row.number_of_participants) * row.unit_price;
    });
    var vat = (total_price / 100) * 15,
    contingency = (total_price / 100) * 10;
    frm.set_value({
        'total_price': total_price,
        'vat': vat,
        'contingency': contingency,
        'grand_total': total_price + vat + contingency
    });
}
frappe.ui.form.on("Budget List", {
    validate(frm) { runCalculation(frm, frm.doc); }
});
frappe.ui.form.on("Allocated Budget", {
    allocated_budget_add(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); },
    allocated_budget_remove(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); }
});

The code updates the total_price, vat, contingency and grand_total when you add or remove a row from Child Table.
It also update the fields before saving the form.

Note: If this post was the answer you were looking for, please mark it as solution for other users to be able to find it easily.

8

Capture
Capture1358×571 96.7 KB

Thank You, but still can’t solve it. also i don’t understand this " 1. Child table linked to Allocated Budget doctype" and where i put the script in main doctype or child doctype? i attached image for your references. Thanks

9

@Mulatu_mekonnen The photo you posted is different from what you have explained in an earlier post.

To answer your questions:

  1. The child table belongs to Allocated Budget doctype
  2. You put the code in the main doctype which is Budget List

If you are still confused, here is an example:
These are the fields of Quotation doctype form, as you can see, the details of the items child table is:

Now, based on the information you provided earlier:

  • The main form is Budget List doctype form
  • The field name of the child table is allocated_budget
  • The doctype of the child table is Allocated Budget
  • The fields of the main doctype that you want to calculate has the following fieldnames: total_price, vat, contingency and grand_total
10

Still no working,

code
code1063×493 38.3 KB

sample
sample1059×486 29.2 KB

main form
main form1319×626 69.8 KB

function runCalculation(frm, doc) {
var total_price = 0;
$.each(doc.allocated_budget, function(row) {
total_price += (row.number_of_training_days * row.number_of_participants) * row.unit_price;
});
var vat = (total_price / 100) * 15,
contingency = (total_price / 100) * 10;
frm.set_value({
‘total_price’: total_price,
‘vat’: vat,
‘contingency’: contingency,
‘grand_total’: total_price + vat + contingency
});
}
frappe.ui.form.on(“Budget List”, {
validate(frm) { runCalculation(frm, frm.doc); }
});
frappe.ui.form.on(“Allocated Budgets”, {
allocated_budget_add(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); },
allocated_budget_remove(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); }
});

i did everything based on the information you provided. can you please check
Here is child table Doctype also

child table Doctype
child table Doctype1308×517 37.7 KB

11

@Mulatu_mekonnen We had some misunderstanding going on between us but the images you posted helped clarifying things out.

There are things you need to know:

  1. The name of the main doctype

In the code, the main doctype is Budget List, while in the images it is Budget Lists.This is one of the reasons why the code isn’t working.

  1. The name of the child doctype

In the code, the child doctype is Allocated Budget, while in the images it is Allocated Budgets.This is another reason why the code isn’t working.

  1. The Apply to field in the client script

The code must be applied to Form, while in the images it’s applied to List. This is another reason why the code isn’t working.

That being said, here is what you have to do:

  1. In the main doctype Budget Lists, add a field of type Button below contingency and set its label as Calculate Values so the fieldname becomes calculate_values. This button will run the calculation manually when clicked.
  2. In the client script, change the Apply to field from List to Form
  3. Use the following code
function runCalculation(frm, doc) {
    var total_price = 0;
    $.each(doc.allocated_budget || [], function(row) {
        total_price += (row.number_of_training_days * row.number_of_participants) * row.unit_price;
    });
    var vat = (total_price / 100) * 15,
    contingency = (total_price / 100) * 10;
    frm.set_value({
        'total_price': total_price,
        'vat': vat,
        'contingency': contingency,
        'grand_total': total_price + vat + contingency
    });
    frm._isCalculated = true;
}
frappe.ui.form.on("Budget Lists", {
    calculate_values(frm) { runCalculation(frm, frm.doc); },
    validate(frm) { if (!frm.isCalculated) runCalculation(frm, frm.doc); }
});
frappe.ui.form.on("Allocated Budgets", {
    allocated_budget_add(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); },
    allocated_budget_remove(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); }
});

Finally, this misunderstanding is my fault since I should have asked for the screenshots since the beginning. I’m sorry for the misunderstanding and for making this post very long :sweat_smile:.

12

@kid1194 Thank you so much for your support. when i lick a button it shows me "

image
image1077×409 20.4 KB

" NaN Error.

13

@Mulatu_mekonnen Maybe because the values are numerical strings. Give the following code a try.

function runCalculation(frm, doc) {
    var total_price = 0;
    $.each(doc.allocated_budget || [], function(row) {
        total_price += (parseInt(row.number_of_training_days) * parseInt(row.number_of_participants)) * parseFloat(row.unit_price);
    });
    var vat = (total_price / 100) * 15,
    contingency = (total_price / 100) * 10;
    frm.set_value({
        'total_price': total_price,
        'vat': vat,
        'contingency': contingency,
        'grand_total': total_price + vat + contingency
    });
    frm._isCalculated = true;
}
frappe.ui.form.on("Budget Lists", {
    calculate_values(frm) { runCalculation(frm, frm.doc); },
    validate(frm) { if (!frm.isCalculated) runCalculation(frm, frm.doc); }
});
frappe.ui.form.on("Allocated Budgets", {
    allocated_budget_add(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); },
    allocated_budget_remove(frm, cdt, cdn) { runCalculation(frm, locals[cdt][cdn]); }
});
1 Like