How to create progress bar In Sales Invoice

Hello Everyone,
I try to create custom progress bar (% Remain) in sales invoice to show the percentage of grand total and outstanding amount but i unable to update this custom field automatically.
I wanted to show how much % of amount remain against that particular sales invoice when we do payment entry against that invoice then automatically we can view the updated progress bar which we can view in sales order.
I try client script to calculate percentage but it doesnt work. I am just beginner. Anyone have any idea about it ?

Hi @Shraddha33,

Please share your script with the field name?

Thanks.

Hello NCP

There are some errors in this code, i just try… Please guide :slight_smile:

frappe.ui.form.on(‘Sales Invoice’, {
refresh(frm) {

	// your code here
    function compute('Sales Invoice', 'per_remain'){
    if (doc.outstanding_amount && doc.base_grand_total)
    { //it's check if the fields contains a value
      doc.remain = (doc.outstanding_amount / doc.base_grand_total) * 100;
    }
                                    }
            cur_frm.cscript.base_grand_total = compute;

              cur_frm.cscript.outstanding_amount  = compute;
        	
               }

})

Thanks in advance

Hi @Shraddha33,

In this case, the custom/client script does not work because when the outstanding amount updates then your per remain not to update.

auto-trigger does not work in a custom/client script, it’s worked on the server-side.

So better way you can make a query report and set a condition in a report.

Thank You!

1 Like

Hi,
We are working on Frappe Cloud, I don’t know about query report and all. Please guide me.

Hi @Shraddha33,

If you are a system admin then you can make a query report.

Please check it.
https://frappeframework.com/docs/v13/user/en/guides/reports-and-printing/how-to-make-query-report

Thanks.

I have basic understanding of SQL queries. Can you please help me about this

Hi @Shraddha33,

Please apply and check it:

Query Code:

Select
    `tabSales Invoice`.name as `Invoice No:Link/Sales Invoice:120`,
    (`tabSales Invoice`.outstanding_amount * 100) / `tabSales Invoice`.rounded_total as `Per Remain (%%):Percentage:150`
    
    From `tabSales Invoice`
    
    Where
    `tabSales Invoice`.docstatus = 1

You can set a field as per your according in a query report.

If do not understand then check this topic.

Thank You!

Hi Shradha

You can also try server scripting for the same

Best

Hi Nihantra,
Thanks for solution :blush:
I will try this …

Hello @NCP and @Ranbir,
We are using FC … So I just have basic understanding of programming. Can you please help me for server scripting.

Hello @NCP,
Thanks for solution :blush:.
It works well for rounded total :+1:, but when I disable rounded total that time it shows me nothing…
Then how to rectify this .
Also I want to import this values in ‘Per Remain’ field by import option, I make this field as ‘allow on submit’ but this field is unedited after submit.
Waiting for your positive response.
Thanks in advance

Hi @Shraddha33,

If disable rounded total then select grand total.
After applying then check its report.

Thanks.

Hello @NCP,
thanks for your response.
but if i want to apply both the condition like select rounded total if null then choose grand total.
and ho

I want to import this values in ‘Per Remain’ field by import option, I make this field as ‘allow on submit’ but this field is unedited after submit.

how can i edit custom field after submit

Hi @Shraddha33,

In case, you can use the below query.

CASE 
        WHEN `tabSales Invoice`.rounded_total = 0 THEN (`tabSales Invoice`.outstanding_amount * 100) / `tabSales Invoice`.grand_total
        ELSE (`tabSales Invoice`.outstanding_amount * 100) / `tabSales Invoice`.rounded_total
        END AS `Per Remain (%%):Percentage:150`

More details for check SQL for: SQL CASE Expression

It’s very difficult to implement because of field change for use js and method calling in py so both sides use to implementation. so I will think.

Thank You!

1 Like

thank you
It works :slight_smile:

Per Remain
I just want to edit this field without coding then how can i

It’s not possible.

Thanks.

No problem.
Thanks for your prompt reply :slight_smile:

Hi @Shraddha33,

If not enable the server script then enable it in FC.

Then Apply the server script like when submitting your payment entry then update the sales invoice field per remain:
But set your field to allow after submitting for Per remain

for d in doc.get('references'):
    test_name = frappe.db.get_value("Sales Invoice",{'name':d.reference_name},'name')
    if test_name:
        test_doc = frappe.get_doc("Sales Invoice",test_name)
        if test_doc.rounded_total == 0:
            test_doc.remain = (test_doc.outstanding_amount * 100) / test_doc.grand_total
        else:
            test_doc.remain = (test_doc.outstanding_amount * 100) / test_doc.rounded_total
        test_doc.save()

Please set the variable your according.
Then check it.

Hope you do understand.

Thank You! :slight_smile:

1 Like

hello @NCP,
Thanks for your reply.
Its works. :smiley: