Try ERPNext Buy Support Partners Foundation

Custom script to get customer balance on Sales Invoice

#1

I was looking through the forum with regards to custom script to get customer outstanding balance on to Sales Invoice and came across this post:

get customer balance
by using this code

cur_frm.cscript.customer = function(doc) {
	return frappe.call({
		method: "erpnext.accounts.utils.get_balance_on",
		args: {date: doc.posting_date, party_type: 'Customer', party: doc.customer},
		callback: function(r) {
			doc.outstanding_balance = format_currency(r.message, erpnext.get_currency(doc.company));
			refresh_field('outstanding_balance', 'accounts');
		}
	});
}

I have successfully pull customer balance onto Sales Invoice everytime a customer is selected. However, if I am to click Get Items button and pull items from Sales Order, the customer outstanding balance will become zero, and I have to select the customer again in order for the customer balance to appear correctly.

I’m wondering if there’s any way to keep this from happening?

1 Like
How to show the customer's previous balance in the sales invoice
Showing Outstanding Amount of customer in sales order
#2

Anyone out there can help me with this issue? I still can’t populate the customer balance on Sales Invoice when I pull items from Delivery Note.

#3

Add your function to refresh listener.

frappe.ui.form.on("Your doctype", {
  refresh: function(frm, cdt, cdn) {
    // your code here
  }
})
1 Like
#4

I added the event trigger as you suggested, but it doesn’t seem to work. Below is the code I use

frappe.ui.form.on(“Sales Invoice”, {
refresh: function(doc) {
return frappe.call({
method: “erpnext.accounts.utils.get_balance_on”,
args: {date: doc.posting_date, party_type: ‘Customer’, party: doc.customer},
callback: functionÂź {
doc.customer_balance = r.message;
refresh_field(‘customer_balance’, ‘accounts’);
}
});
}
});

I’m not sure where to put the function within the function. Sorry, I am very new to programming.

#5

Can anyone help me with this particular case.

How do I write a validate custom script of the above function. I am at a loss as to where to put the “Validate” trigger for this function to get custom balance when I save the document.

frappe.ui.form.on("Sales Invoice", "validate", function(doc){
			frappe.call({
			method: "erpnext.accounts.utils.get_balance_on",
			args: {date: doc.posting_date, party_type: 'Customer', party: doc.customer},
			callback: function(r) {
			doc.customer_balance = r.message;
			refresh_field('customer_balance', 'accounts');
	}
    });
});

When I click save, it doesn’t do anything

#6

I’ve figured out the way to validate this:

cur_frm.cscript.custom_validate = function(doc) {
return frappe.call({
	method: "erpnext.accounts.utils.get_balance_on",
	args: {date: doc.posting_date, party_type: 'Customer', party: doc.customer},
	callback: function(r) {
		doc.customer_balance = r.message;
		refresh_field('customer_balance', 'accounts');
	}
});

}

1 Like
#7

Also, with the script described above, it will only fetch the customer balance on the date same as Sales Invoice posting date. If you want the current customer balance, you’ll need to set the arg: {date: frappe.datetime.nowdate() }

So the script would be like this:

cur_frm.cscript.custom_validate = function(doc) {
return frappe.call({
	method: "erpnext.accounts.utils.get_balance_on",
	args: {date: frappe.datetime.nowdate(), party_type: 'Customer', party: doc.customer},
	callback: function(r) {
		doc.customer_balance = r.message;
		refresh_field('customer_balance', 'accounts');
	}
});
2 Likes
#8

Hellow,
How do you get this script to work in a Custom Print format?

#9

You’ll have to create a custom field to show the customer balance and then use the custom field in your print format

#10

could u make a small video for help

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

Frappé Technologies Pvt Ltd, Mumbai