Issue with Row Balances in Script Report

[flexy2ky]

Hello Community, I am trying to create a script report to fetch a customer’s ledger transaction balances and I wrote these report scripts:

def fetch_customer():
    logged_in_user = frappe.session.user
    member = frappe.db.get_value("Member", {"email_id": logged_in_user}, "name")
    linked_customer = frappe.db.get_value("Member", {"name": member}, "customer")
    return linked_customer

def execute(filters=None):
    columns, data = [], []

    columns = [
        _("Posting Date") + ":Date:100",
        _("Account") + ":Link/Account:350",
        _("Debit") + ":Currency:150",
        _("Credit") + ":Currency:150",
        _("Balance") + ":Currency:150"
    ]

    linked_customer = fetch_customer()

    sd_account = frappe.db.get_value("Custom Settings", None, "special_deposit_account")

    where_conditions = ["party_type = 'Customer'", "is_cancelled = 0"]

    if linked_customer:
        where_conditions.append(f"party = '{linked_customer}'")

    if sd_account:
        where_conditions.append(f"account = '{sd_account}'")

    where_conditions_str = " AND ".join(where_conditions)

    data = frappe.db.sql(f"""
        SELECT posting_date, account, debit, credit
        FROM `tabGL Entry`
        WHERE {where_conditions_str}
        ORDER BY posting_date
    """, as_dict=1)

    data_with_balance = get_result_as_list(data)


    return columns, data_with_balance


def get_balance(row, balance, debit_field, credit_field):
    balance += row.get(debit_field, 0) - row.get(credit_field, 0)

    return balance

def get_result_as_list(data):
    running_balance = 0
    
    for d in data:
        running_balance = d.get("debit", 0) - d.get("credit", 0)
        d["balance"] = running_balance
    
    return data

Everything looks good at first glance but the row balances do not follow the convention of showing the balance based on the previous row balance and current row credit and debit. each row balance is isolated. But the balance in the total row is correct. If I modify this running_balance = d.get("debit", 0) - d.get("credit", 0) and make it running_balance += d.get("debit", 0) - d.get("credit", 0) then the row balances will be correct but the total balance becomes a sum of the row balance which is wrong. I can’t seem to figure out what I might be doing wrong so any help will be appreciated.

[NCP]

Hi @flexy2ky,

Hmm ,

you had a small mistake in how you calculated these running balances. You reset the balance for each row, which led to incorrect results. When you fixed it by adding running_balance += d.get("debit", 0) - d.get("credit", 0) , the row balances became correct, but the total balance was wrong.

So please try it and replace it with your get_result_as_list method and check it.

def get_result_as_list(data):
    running_balance = 0

    for d in data:
        d["balance"] = running_balance + d.get("debit", 0) - d.get("credit", 0)
        running_balance = d["balance"]

    return data

Thank You!

[flexy2ky]

@NCP Thanks for your suggested fix. I implemented as suggested but the result is same as the first scenario. Here’s a screenshot of the result with your suggested function:

As you can see, the balance is a sum of all row balances which is wrong even though the row balances per balance history is correct. but if I implement it this way:

def get_result_as_list(data):
    running_balance = 0
    
    for d in data:
        running_balance = d.get("debit", 0) - d.get("credit", 0)
        d["balance"] = running_balance
    
    return data

I get this:

in which case the total balance is correct but the row balances, while correct per row, is incorrect per balance history.

[flexy2ky]

Bumping this up in case someone has an idea of what I should do.

[flexy2ky]

Bumping again.