Try ERPNext Buy Support Partners Foundation

Custom script for calculating expected margin in Quotation

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.

Still no ideas …?

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);
});

@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

@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.

@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?