Hi everyone, through this query, I am getting the output as shown in the screenshot. For journal entries like JE000072 and JE000208, both debit and credit lines are being displayed for the particular vendor, which aligns with what’s in the system. Similarly, for JE003150, only the debit line is shown (which is correct), and for JE001283, the credit line is correctly displayed for the respective vendor. However, my requirement is this: In cases where a journal entry (JV) has both debit and credit lines for the same vendor, I want to display only one line — specifically, the credit line — in the results. Could you please guide me on how to achieve this? Your help would be greatly appreciated.

SELECT

BUILTIN.DF(TL.entity) AS entity,

T.TranDate AS trandate,

T.Type AS type,

T.TranID AS tranid,

T.Memo AS memo,

BUILTIN.DF(TAL.account) AS account,

SUM(TAL.debit) AS debit,

SUM(TAL.credit) AS credit

FROM

Transaction T

INNER JOIN

TransactionLine TL ON TL.Transaction = T.ID

INNER JOIN

TransactionAccountingLine TAL ON TAL.Transaction = T.ID

AND TAL.TransactionLine = TL.ID

WHERE

TL.entity = 2590

AND T.Posting = 'T'

AND T.type != 'ItemRcpt'

AND T.Trandate BETWEEN '01/01/2024' AND '31/12/2024'

AND  (

T.Type != 'VPrep'

OR (T.Type = 'VPrep' AND TAL.debit IS NOT NULL AND TAL.credit IS NULL)

)

AND  (

T.Type != 'VendPymt'

OR (T.Type = 'VendPymt' AND TAL.debit IS NOT NULL AND TAL.credit IS NULL)

)

GROUP BY

BUILTIN.DF(TL.entity),

T.TranDate,

T.Type,

T.TranID,

T.Memo,

BUILTIN.DF(TAL.account)

ORDER BY

T.TranDate ASC;

SELECT MAX(trandisplayname), SUM(debit), SUM(CREDIT), MAX(name) FROM (Insert your query here......) GROUP BY name For more information please DM me.

Does you journal have always 2 lines only?

It can be one or more than based on the vendor name. I just grouped your data based on the vendor name. You may apply more things based on your requirement