BP Balance

[Former Member]

How to get by Crystal report the BP balance ? if I get column balance it's not true.

Giuseppe

[former_member201110 · Accepted solution]

Hi Giuseppe,

The JDT1 table contains all the information you need to calculate the current balance for a BP. The ShortName field will contain the BP Code for any transaction type posted against the BP.

For example, the following query will return the balances for all BPs:

select
	T0.CardCode, T0.CardName, T0.CardType,
	isnull(sum(T1.Debit - T1.Credit), 0) as Balance
from
	OCRD T0
	left outer join JDT1 T1 on T0.CardCode = T1.ShortName
	and T0.CardType in ('C', 'S')
group by
	T0.CardCode, T0.CardName, T0.CardType

Kind Regards,

Owen