Get Customer Ledger Balance Into New Sales Invoice

ERPNext Selling/CRM
1

I’m not sure exactly how to write the custom script to get a customer’s ledger balance into a new Sales Invoice. Is there a function/hook for doing this?

2

So I finally figured it out, mostly after reviewing the documentation here: Client side scripting · webnotes/wnframework Wiki · GitHub

I eventually realized that to react to selection/changes of value in any field on the form, the script should start with: cur_frm.cscript.<field_name> where <field_name> is the name for the field (without the angle brackets: “<>”) as it is in the “Customize Form” view for that DocType. This field_name can also be a field you inserted to customize the DocType.

So in my custom script, I wanted the outstanding ledger balance to change and be displayed based on the currently selected customer. I wanted this balance value to be displayed in a field I inserted in the Sales Invoice DocType, named “outstanding_balance”, which is of type “Read Only” so it displays as a non-editable value display.

So, the client script should call the server-side method erpnext.accounts.utils.get_balance_on, passing the arguments required for the method with args: {date: doc.posting_date, party_type: 'Customer', party: doc.customer}. You may notice that i excluded the “account” parameter in the args as defined for the method. Instead I passed in the “party_type” and “party” parameters, assigning them the values of “Customer” and dynamically retrieving the customer’s name as the “party” using the doc.customer code.

The callback directive points to the function which is executed when the server-side code execution completes and returns a result as the “r” argument in the function. The “r” would contain a property called “message”, which should represent the customer’s ledger balance value.

I then assign this message value returned from the server into the “outstanding_balance” custom field (as mentioned earlier, this is a custom field I inserted in the Sales Invoice doctype). I used an in-built ERPNext function called “format_currency” method to format the value appropriately as a money value, using the currency set up by the user.

Finally, the ERPNext “refresh_field” function updates/displays the “outstanding_balance” field accordingly.

Below is the full code I used. I inserted this in the “Set Up > Custom Script” form, selecting “Sales Invoice” DocType.

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');
		}
	});
}
7 Likes
3

@ugommirikwe thanks for the detailed help :smile:

You can contribute to examples here:

4

@rmehta I will do that :slight_smile:

5

Hello @ugommirikwe

I used your script and it works for me too…nice!!

I tried to put the outstanding balance field on the invoice emailed to the customer and I realised it will not include the current invoice balance.

So if the customer was owing $1500 before and I raised a new invoice of $500, I want the new balance of $2000 to reflect and not the old $1500.

Any ideas on how to do this??

Regards

6

thanks for shareing :heart_eyes:
it worked for me, great job :slight_smile:

7

Hi, I’ve been trying to achieve this. Could you do it? How?

@rmehta @ugommirikwe , Can you enlighten on this please?