Server script to update ledger closing balance in the customer doctype

ERPNext
1

Problem Statement: Update ledger closing balance of a Customer to Customer document using server script triggered on save of GL Entry

Script used:

Server Script
Script Type: Document Event
Reference Document Type: GL Entry, Journal Entry, or Payment Entry

def update_customer_outstanding_balance(doc, method):
    try:
        # Only proceed if the document is related to a customer and the company is defined
        if doc.party_type == "Customer" and doc.party and doc.company:
       # Fetch the total outstanding balance for the customer filtered by company, including opening balance
            outstanding_balance = frappe.db.sql("""
                SELECT SUM(debit - credit) AS outstanding_balance
                FROM `tabGL Entry`
                WHERE party_type = 'Customer'
                AND party = %s
                AND company = %s
                AND is_cancelled = 0
                AND (is_opening = 'Yes' OR is_opening IS NULL) -- Include opening entries
            """, (doc.party, doc.company), as_dict=True)

  

            # If balance exists, update custom_outstanding field in Customer doctype
            if outstanding_balance and outstanding_balance[0].outstanding_balance is not None:
                frappe.db.set_value('Customer', doc.party, 'custom_outstanding', outstanding_balance[0].outstanding_balance)
                frappe.logger().info(f"Updated custom_outstanding for {doc.party}: {outstanding_balance[0].outstanding_balance}")
            else:
                # Set to 0 if no balance is found
                frappe.db.set_value('Customer', doc.party, 'custom_outstanding', 0)
                frappe.logger().info(f"Set custom_outstanding to 0 for {doc.party}")
2 
frappe.ui.form.on('Sales Invoice', {
    customer: function(frm){
    	frappe.call({
			method: "erpnext.accounts.utils.get_balance_on",
			args: {date: frm.doc.posting_date, party_type: 'Customer', party: frm.doc.customer},
			callback: function(r) {
			    console.log("r",r)
			    frm.set_value("customer_previous_balance",r.message)
				// frm.doc.customer_previous_balance = format_currency(r.message, erpnext.get_currency(frm.doc.company));
				refresh_field('customer_previous_balance');
			}
		});
    }
})

this was created in sales invoice … similar can be used for customer with update on load
Also have used field customer_previous_balance to display same

1 Like
3

Worked perfectly with following changes:

frappe.ui.form.on('Customer', {
    refresh: function(frm) {
        // Ensure customer field exists
        if (frm.doc.customer_name) {
            frappe.call({
                method: "erpnext.accounts.utils.get_balance_on",
                args: {
                    date: frappe.datetime.now_date(), // Use current date or other relevant date
                    party_type: 'Customer',
                    party: frm.doc.customer_name // Correct field for customer
                },
                callback: function(r) {
                    if (r.message && frm.doc.custom_outstanding !== r.message) {
                        // Update the field if the value is different
                        frm.set_value("custom_outstanding", r.message);

                        // Automatically save the form after updating the custom_outstanding field
                        frm.save();
                    }
                }
            });
        }
    }
});
1 Like