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 ?
Hello NCP
There are some errors in this code, i just try… Please guide
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!
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
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 .
It works well for rounded total , 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!
thank you
It works
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
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!