Custom script for calculating expected margin in Quotation

Frappe Framework Custom Script
,
1

Hello,
I would like to display an information about expected margin when creating Quotation. So I tried to write this custom script.

Margin is calculated as a sum of (QuotationItem.Qty * (QuotationItem.Rate - QuotationItem.Valuation_rate) for every item in Quotation.
I have created custom field valuation_rate in Quotation Item doctype and fetching its value from linked Item doctype (via Fetch From field).
I have created custom field gross_margin in Quotation doctype to keep actual value of expected margin.

Then I added this script to Quotation doctype:

frappe.ui.form.on('Quotation',  {
    validate: function(frm) {
        var gm = 0;
        $.each(frm.doc.items,  function(i,  d) {
           gm += (flt(d.qty) * (flt(d.rate)-flt(d.valuation_rate)));
        });
        frm.doc.gross_margin = gm;
    } 
})

First question is, is it a good approach to achieve this goal?

Secondly, the trigger I have used (validate) is probably not suitable as I have to save document to trigger calculation. What trigger shall I use to recalculate it after every change of any quotation items amount or qty?

And the last question, is it possible to fetch valuation_rate directly from Item (linked to QuotationItem via item_code) without creating that intermediate custom field valuation_rate in QuotationItem doctype? Something like “QuotationItem.item_code.valuation_rate” ?

Thanks a lot for any help/ideas/comments.

2

Still no ideas …?

3

If anybody would found this feature useful, here is my solution. I am not developer and not sure if it is the best way to achieve what I wanted, but looks like it is working well:

In doctype Quotation Item created read only custom field valuation_rate and fetching value from linked Item. (did not find a way how to do it without this intermediate field)
In doctype Quotation Item created read only field valuation_rate and fetching value from linked Item. (for displaying expected margin in Quotation form)

In Quotation doctype created this custom script:

// recalc on item qty changed
frappe.ui.form.on("Quotation Item", "qty", function(frm, cdt, cdn) {
  var items = frm.doc.items;
  var gm = 0;
  for(var i in items) {
    gm += flt(items[i].qty)*(flt(items[i].rate)-flt(items[i].valuation_rate));
  }
  frm.set_value("gross_margin",gm);
});

// recalc on item created
frappe.ui.form.on("Quotation Item", "item_code", function(frm, cdt, cdn) {
  var items = frm.doc.items;
  var gm = 0;
  for(var i in items) {
    gm += flt(items[i].qty)*(flt(items[i].rate)-flt(items[i].valuation_rate));
  }
  frm.set_value("gross_margin",gm);
});

// recalc on item remove
frappe.ui.form.on("Quotation Item", "items_remove", function(frm, cdt, cdn) {
  var items = frm.doc.items;
  var gm = 0;
  for(var i in items) {
    gm += flt(items[i].qty)*(flt(items[i].rate)-flt(items[i].valuation_rate));
  }
  frm.set_value("gross_margin",gm);
});
1 Like
4

@martin42 Good job, Thanks for sharing your code :grinning:
I just optimized the code with less number of lines:

frappe.ui.form.on(‘Quotation Item’, {
recalc: function(frm, cdt, cdn) {
var items = frm.doc.items;
var gm = 0;
for (var i in items) {
gm += flt(items[i].qty) * (flt(items[i].rate) - flt(items[i].valuation_rate));
}
frm.set_value(“gross_margin”, gm);
},
qty: function(frm, cdt, cdn) {
frm.trigger(“recalc”, cdt, cdn);
},
rate: function(frm, cdt, cdn) {
frm.trigger(“recalc”, cdt, cdn);
},
item_code: function(frm, cdt, cdn) {
frm.trigger(“recalc”, cdt, cdn);
},
items_remove: function(frm, cdt, cdn) {
frm.trigger(“recalc”, cdt, cdn);
}
});

1 Like
5

@Mohammed_Redha Thanks a lot for your help! Looks much better…

Dont you know if is it possible to fetch valuation_rate directly from linked Item via item_code without creating custom field valuation_rate in QuotationItem doctype? It would simplify it and could be useful also in another cases.

6

@mohammed_redha I found one issue with the script, event item_code triggers recalc, but it calculates all the fields except the one just added. Is there any solution to include also newly added field?

7

Hi mohammed_redha
Thank you for optimizing the code, but please can you tell me where we can put this code?

8

@zahidshuja You can add it in custom script doctype in your instant:

https://docs.erpnext.com/docs/user/manual/en/customize-erpnext/custom-scripts

or in JS folder inside your custom App

9

thankx