Need help to calculate child table value considering main doctype field

Frappe Framework Custom Script
1

Hi,

I have a custom doctype and a custom child table in the doctype. I want to calculate the child table field

Finance = Conversion Rate * FOB USD

The Conversion Rate is a field of the main doctype, and finance and conversion rate are child table fields. The main doctype name is LC Costing, and table name is LC Costing Items

I tried this custom script; didn’t work.

frappe.ui.form.on("LC Costing Items", "finance", function(frm, cdt, cdn) {
    var d = locals[cdt][cdn];
    frappe.model.set_value(cdt, cdn, "finance", d.fob_usd * d.conversion_rate);
    refresh_field("finance");
});

image
image1180×484 16.8 KB

2

To access the values from the parent doc use:
frm.doc.fieldname

Then after setting the child field
frm.refresh_field("table fieldname")

3 
frappe.ui.form.on("LC Costing Items", "finance", function(frm, cdt, cdn) {
    var d = locals[cdt][cdn];
    frappe.model.set_value(cdt, cdn, "finance", d.fob_usd * frm.doc.conversion_rate);
    frm.refresh_field("finance");
});

So, this would be the code. But that’s not working.

4

There is slight issue with your syntax and by table fieldname, I meant table name in the parent document . Please try the following.

frappe.ui.form.on("LC Costing Items", "field in the child table you want to trigger on change": function(frm, cdt, cdn) { var d = locals[cdt][cdn]; frappe.model.set_value(cdt, cdn, "field in child table you want to update", new value); frm.refresh_field("name of the table in the parent document"); });

5

When do you want to perform this action.

What value do you to change?

6

I want this to happen real time.

And,
Parent doc field name = conversion_rate
Child doc field name 1 = fob_usd
Child doc field name 2 = finance [read only]

Calculation is finance=fob_usd * conversion_rate

Now, changing any of the value will recalculate the finance value.
Thanks for your help.

7

Great. This code is working fine if I change the child field value.

frappe.ui.form.on("LC Costing Items", "fob_usd", function(frm, cdt, cdn) { 
    var d = locals[cdt][cdn]; 
    frappe.model.set_value(cdt, cdn, "finance", d.fob_usd * frm.doc.conversion_rate); 
    frm.refresh_field("finance"); 
    });

What shall I add here so that it also recalculates if I change parent doc field value. i.e

conversion_rate

?

8

For coversion_rate change

frappe.ui.form.on("LC Costing", "conversion_rate":function(frm) { 
    for ( let i in frm.doc.name_of_the_child_table){
        frm.doc.name_of_the_child_table[i].finance = frm.doc.name_of_the_child_table[i].fob_usd * frm.doc.converion_rate;
    }
    frm.refresh_field("name of the child table not Child Doctype name"); 
});
9

Child table name is “LC Costing Items” so after changing the code to frm.refresh_field(“lc_costing_items”);

Getting this while changing the conversion rate.

image
image906×217 8.36 KB

10

I made a typo. it shoud be frm.doc.conversion_rate. see if this is the issue. try the below code

frappe.ui.form.on("LC Costing", 
    conversion_rate: function(frm) { 
        for ( let i in frm.doc.lc_costing_items){
            frm.doc.lc_costing_items[i].finance = frm.doc.lc_costing_items[i].fob_usd * frm.doc.convesrion_rate;
    }
    frm.refresh_field("lc_costing_items"); 
});

frappe.ui.form.on("LC Costing Items", 
    fob_usd: function(frm, cdt, cdn) { 
        var d = locals[cdt][cdn]; 
        frappe.model.set_value(cdt, cdn, "finance", d.fob_usd * frm.doc.conversion_rate); 
        frm.refresh_field("lc_costing_items"); 
    });
1 Like
11 
frappe.ui.form.on("LC Costing", "conversion_rate", function(frm) { 
    for ( let i in frm.doc.lc_costing_items){
    frm.doc.lc_costing_items[i].finance = frm.doc.lc_costing_items[i].fob_usd * frm.doc.conversion_rate;
    }
    frm.refresh_field("lc_costing_items"); 
});
frappe.ui.form.on("LC Costing Items", "fob_usd", function(frm, cdt, cdn) { 
    var d = locals[cdt][cdn]; 
    frappe.model.set_value(cdt, cdn, "finance", d.fob_usd * frm.doc.conversion_rate); 
    frm.refresh_field("finance"); 
});

Voila, I made a minor change to your code, and it works like a charm. Thank you so much!

I found help from the forum to calculate for child table fields or main doctype fields, but mixing them was what I needed. Thanks again. This solves my present issue.

12

@Mohammad_Ahmad_Zulfi,

In my Project doctype, I added a child table named MPS Project Assets. I want to filter the Assets which are linked with the doctype project.

I also added a link field, “Project Asset” in the Project doctype to test if my custom scripts works.

The main doctype filters are working, but the child table filters are not working.

Here’s my code,

frappe.ui.form.on("Project", {
	setup: function(frm) {
		frm.set_query("project_asset", function() {
			return {
				filters: [
					["Asset", "project", "in", frm.doc.name]
				]
			};
		});
	}
});

// MPS Project Assets is a child table, and mps_asset field is linked to Asset

frappe.ui.form.on("MPS Project Assets", {
	setup: function(frm) {
		frm.set_query("mps_asset", function() {
			return {
				filters: [
					["Asset", "project", "in", frm.doc.name]
				]
			};
		});
	}
});

The first part is working, not the second part and I guess that’s because it’s a child table field. Could you help me on this? I’d appreciate it really.

13

Try the event form_render instead of setup in the child table if you want to update when ever you open the row.

or if you want to set query to child table on setup of parent doc follow the instructions in this link.
To set the query for child table you also have to mention the table field name
https://frappeframework.com/docs/user/en/api/form#frmset_query

14 
frappe.ui.form.on("Project", {
	setup: function(frm) {
		frm.set_query('mps_asset', 'asset', () => {
            return {
                filters: {
                    project: 'Test project'
                }
            };
        });
	}
});

I have tried to change the event from setup to form_render or onload. Setup event rather makes the form blank. Nothing seems to work on the child table field “mps_asset”.