Custom script to get customer balance on Sales Invoice

Frappe Framework Custom Script
11

Hi, I tried this but dint work.Could you Kindly illustrate, I would like to have this script in a custom jinja print format.

12

Hi
Can we fetch account balance in Jinja Template ?

13

@Ratanak How can I fetch the customer’s Credit Limit or even better Remaining Credit where

remaining_credit = credit_limit - outstanding_balance

14

did you create a custom field called: customer_balance?
the custom field can be any name you prefer, just replace the “customer_balance” in the custom script above with the name of your custom field.

15

You’ll have to create a custom field for “remaining_credit”, another custom field to fetch “credit_limit” from Customer, then write another custom script to calculate the remaining_credit.

16

I don’t know of any way you can do that in Jinja Template.
The only way I know is create a custom field and use the custom script below to fetch the customer balance and print the custom field in your Jinja Template.
Also even if you could do that in Jinja Template, it is not recommended as the value won’t saved in the database as it would a custom field

17

Your solution would look something like this, provided you have the following custom fields:

  1. remaining_credit

  2. customer_balance (use the script above to fetch)

  3. credit_limit (fetch it from Customer)

     frappe.ui.form.on('Sales Order', {
         validate: function(frm, cdt, cdn) {
         	// make calculation on the fields
             var b = flt(frm.doc.credit_limit);
             var c = flt(frm.doc.customer_balance);
     		var a = b - c;
     		frm.set_value('remaining_balance', a);
     		frm.refresh_field('remaining_balance');
         }
     });
1 Like
18

Thank you. I tried to fetch credit limit from Customer doctype, but credit_limit is a child table field of Customer Credit Limit table.

I was unable to fetch the credit limit, let alone calculate the remaining credit. I had created three custom fields , customer_balance, credit_limit and remaining_limit.

19

How can I fetch customer’s credit limit in sales invoice?

20

You would need to google custom script for fetching field from child table.

21

This link might help as well:

22

thank you , i was searching for this calculation ,

frappe.ui.form.on(‘Task’, {
validate: function(frm, cdt, cdn) {
// make calculation on the fields
var b = flt(frm.doc.rate_per_fedden);
var c = flt(frm.doc.treated_area);
var a = b * c;
frm.set_value(‘total_quantity’, a);
frm.refresh_field(‘total_quantity’);
}
})

23

Do we have script in client side?

24

The script is client side script, in the Custom Script Doc Type.

1 Like
25

how to use this method for email notifications to get closing balance of customer on submission of payment entry.

I tried using this in Message section of new notification:

{{ erpnext.accounts.utils.get_balance_on(date= frappe.datetime.nowdate(), party_type= doc.customer, party= doc.customer) }}

But its not working. Kindly guide me.

1 Like
26

is it possible to use it in jinja?

27

@Ratanak,

I couldn’t make it work. I tried to find a custom script to fetch customer’s credit_limit into a custom field called customer_credit_limit in Sales Invoice doctype.

Tried to scroll through various forum post to fetch child table’s value into a single custom field on another doctype, apparently, there aren’t any similar post.

28

Found the solution from here Cur_frm.add_fetch - #5 by PAMPERO

This will fetch the first’s row’s value. We can use this for any types of child table value fetching into a single field of another doctype.

frappe.ui.form.on("Sales Invoice", "customer",
function(frm) {
    frappe.call({
        "method": "frappe.client.get",
        args: {
            doctype: "Customer",
            name: frm.doc.customer
        },
        callback: function (data) {
            frappe.model.set_value(frm.doctype,
                frm.docname, "credit_limit",
             data.message.credit_limits[0].credit_limit
               );
        }
    });
});
1 Like
29

Have you Find any solution? How to implement it

30

Here is script to get customer balance on invoice using html

 {% if doc.customer %}
      {% set ledger_entries = frappe.get_all("GL Entry", filters={"party_type": "Customer", "party": doc.customer}, fields=["SUM(debit) - SUM(credit) as balance"]) %}
      {% if ledger_entries %}
        {% set balance = ledger_entries[0].balance %}
        <p><b>Customer Balance:  {{ frappe.format_value(balance, { "fieldtype": "Currency" }) }}</b></p>
      {% endif %}
    {% endif %}
1 Like