Wanting to create a Gross Profit Percentage field and need it to display an automatically calculated ratio

Hello,

I have made a custom field within Sales Order Items which is titled “Gross Profit Percentage” which I want to automatically update and display a ratio of Gross Profit to Billed Amt. I have attempted to write a custom script to accomplish this but have not had any success and my field consistently just reads 0%. Attached is an image of my current custom script, any help would be appreciated.

frappe.ui.form.on(‘Sales Order’, {
refresh(frm) {
// your code here
}
});

frappe.ui.form.on(‘Sales Order Item’, {
refresh(frm) {
// add a trigger on field “custom_field1”
cur_frm.cscript.gross_profit = function(doc, cdt, cdn) {

      doc.gross_profit_percentage = flt(doc.gross_profit)/flt(doc.billed_amt)*100; 
    
      
      refresh_field('items');
    };
    
    
    cur_frm.cscript.billed_amt = cur_frm.cscript.gross_profit;
}

});

Thank you

@Ryan123 Try the following…

frappe.ui.form.on('Sales Order Item', {
    gross_profit: function(frm, cdt, cdn) {
        update_gross_profit_percentage(frm, cdt, cdn);
    },
    billed_amt: function(frm, cdt, cdn) {
        update_gross_profit_percentage(frm, cdt, cdn);
    },
});
function update_gross_profit_percentage(frm, cdt, cdn) {
    let row = local[cdt][cdn];
    row.gross_profit_percentage = flt(flt(
        flt(row.gross_profit) / flt(row.billed_amt)
    ) * 100);
    frm.refresh_field('items', cdn, 'gross_profit_percentage');
}

Hello,

Thanks for your reply, however unfortunately it doesn’t seem to work.


I set the value to just 100 for now to see if it at least assigns the value to the field properly, but even that does not work and the field simply displays ‘0.00%’

I do have the field configured as a ‘percent’ data type, would this potentially cause an issue? should I simply leave it as ‘Data’?

Thanks again for your help, I will continue attempting to get this to work.

@Ryan123 I don’t think that it should matter if fieldtype is Percent or not…

FYI, the code will not work in quick entry and may not work during table inline changes…

Hi again, and thank you for the feedback.

So I have managed to get the script working using your above code, however I have made a few observations and would like to know if you know of any workarounds. First of all, i had to change the fields which act as ‘flags’ to run the custom function from ‘gross_profit’ and ‘billed_amt’. The reason for this is because these fields are automatically updated when a change is made to the ‘rate’ field, and this does not seem to trigger the function. Therefore I made ‘rate’ the flag field and this caused the gross_profit_percentage custom field to be properly updated and calculated! However, the down side is that it appears none of the other fields dependent on ‘rate’ are updated (such as gross_profit and ‘net_rate’).

Would you happen to have encountered this before? Any ways for me to trigger the function from an auto-update read-only field? or at least trigger the function so that when I update rate is also updates all its other dependent fields?

Since the fields are in a child table, you can’t frm.set_value to update the values of gross_profit and billed_amt which will trigger the gross profit percentage function…

So in order to update those fields and still trigger the gross profit percentage function, you should use the following code…

I’m assuming that the rate field is also in the child table…

// value = the value calculated for gross_profit
// fieldtype = the fieldtype of gross_profit field, you can set it as '' if the field is not of type Link or Dynamic Linkk
frappe.model.set_value(cdt, cdn, 'gross_profit', value, 'fieldtype', false);

sorry I do not fully understand. Attached is a screenshot of all the fields i am talking about (all within the items child table)


When i enter the value for the ‘rate’ field, it updates things such as ‘amount’, ‘net rate’ etc… but this updating is not done via a custom script from me, but just something ERPnext does by default. When i implement my custom script for updating gross profit percentage, ERPnext stops updating these fields. why is this? is this something i can fix?

I see…

There is a workaround that you can use…

I hope that this code works well…

frappe.ui.form.on('Sales Order', {
    onload: function(frm) {
        frappe.model.on('Sales Order Item', '*', function(fieldname, value, doc) {
            if (doc.parent !== frm.docname || ['gross_profit', 'billed_amt'].indexOf(fieldname) < 0) return;
            update_gross_profit_percentage(frm, doc);

        });
    },
});
function update_gross_profit_percentage(frm, doc) {
    let value = flt(flt(
        flt(doc.gross_profit) / flt(doc.billed_amt)
    ) * 100); 
    frm.fields_dict.items.grid.set_value('gross_profit_percentage', value, doc);
}

Hello, thank you again for the assistance, however it seems this workaround has not worked.

the other fields updated by changing the value of ‘rate’ are not updating. I am very confused by why my custom script seems to be ‘overriding’ these updates

How about we add some log messages to the browser console so you can see what is happening…

frappe.ui.form.on('Sales Order', {
    onload: function(frm) {
        frappe.model.on('Sales Order Item', '*', function(fieldname, value, doc) {
            console.log('Sales Order Item Change', fieldname, value, doc);
            let docname = frm.doc.name || frm.docname;
            if (
                (doc.parent && doc.parent !== docname)
                || ['gross_profit', 'billed_amt'].indexOf(fieldname) < 0
            ) return;
            update_gross_profit_percentage(frm, doc);

        });
    },
});
function update_gross_profit_percentage(frm, doc) {
    let value = flt(flt(
        flt(doc.gross_profit) / flt(doc.billed_amt)
    ) * 100);
    console.log('Gross profit percentage', value);
    frm.fields_dict.items.grid.set_value('gross_profit_percentage', value, doc);
}

Post a screenshot of the console after you give it a try…