Customer Total Open Credit field in a BAQ

ERP 10
1

Has anyone had success in getting the Total Open Credit field into a BAQ?
Field help has it as Customer.TotOpenCredit , but I was unable to find in the selection of fields.
Thank you.

2

When the DB field is empty in field help is usually means that this is calculated on the fly and isn’t in the database in that form. So for you to get that, you would have to calculated it yourself.

image

3

I did this long ago, before SubQueries existed. Here are the calculations that I performed, in SQL Views and then brought them all back into 1 to use as an External BAQ.

Invoice Balance

SELECT        Company, CustNum, SUM(InvoiceBal) AS TotInvBal
FROM            Erp.InvcHead
GROUP BY Company, OpenInvoice, CustNum
HAVING        (OpenInvoice = 1)

Current Orders

SELECT        Company, CustNum, SUM(DocExtPriceDtl) AS Total
FROM            dbo.OrderDtl
WHERE        (OpenLine = 1) AND (NeedByDate <= GETDATE())
GROUP BY Company, CustNum

Future Order

SELECT        Company, CustNum, SUM(DocExtPriceDtl) AS Total
FROM            dbo.OrderDtl
WHERE        (OpenLine = 1) AND (NeedByDate > GETDATE())
GROUP BY Company, CustNum

Shipped Not Invoiced

SELECT        Erp.ShipDtl.Company, Erp.ShipDtl.CustNum, SUM((Erp.ShipDtl.OurInventoryShipQty + Erp.ShipDtl.OurJobShipQty) * dbo.OrderDtl.DocUnitPrice) AS Prc
FROM            Erp.ShipDtl INNER JOIN
                         dbo.OrderDtl ON Erp.ShipDtl.Company = dbo.OrderDtl.Company AND Erp.ShipDtl.OrderNum = dbo.OrderDtl.OrderNum AND Erp.ShipDtl.OrderLine = dbo.OrderDtl.OrderLine
WHERE        (Erp.ShipDtl.Invoiced = 0) AND (Erp.ShipDtl.ReadyToInvoice = 1)
GROUP BY Erp.ShipDtl.Company, Erp.ShipDtl.CustNum
2 Likes