Party Balance in Query Report

ERPNext Reports
1

Dears
from where can i get Supplier balance Dr or CR and get it into query report ?? Which tab and column ??

2

I could not understand if you wish to find individual general ledger entries for your custom query report or you wish to find the balance via some report.

from where can i get Supplier balance Dr or CR and get it into query report

that will be tabGL Entry. These entries are used in other reports like General Ledger. To know more, simply type GL Entry in your search bar in ERPNext and it will show you the entire list if you are permitted to view them. To view schema, go to bench mysql and execute desc tabGL Entry.

However I think you can get Balance in General Ledger report. you can filter it by Party = Supplier, then choose the Supplier. But I’m not sure if my assumption is correct or not.

image
image1171×559 35.4 KB

3

Thank you for your reply @root13F

so what can i do to get the balance ??

should i get sum(dr) - sum(cr) where party = supplier and part_type = “supplier” then print them as balance

4

sorry I don’t know much about it. However I think you should check out the code of one of these reports like General Ledger and you should find that formula there.

5

I mean that report

select
tabPurchase Invoice Item.parent AS “Purchase Invoice:Link/Purchase Invoice:110”,
tabPurchase Invoice.supplier_name AS “Supplier::100”,
tabPurchase Invoice.status AS “Status::100”,
tabPurchase Invoice Item.item_code AS “Item:Link/Item:110”,
tabPurchase Invoice Item.item_name AS “Item Name::110”,
tabPurchase Invoice.grand_total AS “grand_total::Float:100”,
tabPurchase Invoice.paid_amount AS “paid_amount::Float:100”,
tabPurchase Invoice.outstanding_amount AS “outstanding_amount:Float:100”
from tabPurchase Invoice Item join tabPurchase Invoice on tabPurchase Invoice.name =
tabPurchase Invoice Item.parent
where
tabPurchase Invoice.status != “Cancelled”

Now i need last column to be the outstanding party balance from where can i get it ??