Dynamics Community Forum Thread Details

Relation between BankChequePaymTrans and specTrans table x++

I need to get TaxWithHoldamount relation to every single line in vendTransOpen table

I create a new Vandor payment journal and I selected two lines with same invoice like (21) and different TaxWithHoldamount then its appear as one line in journal (sum amount and sum withhold amount )then I generate cheque

I created a custom report to get all data based on cheque number

but in my select statement data repeated

when I remove VendTransOpen from join its appear 2 lines but with VendTransOpen its appear 4 lines

so what's wrong ?

while select BankChequePaymTrans
where BankChequePaymTrans.ChequeNum==ChequeNum
join VendTrans
where VendTrans.AccountNum==BankChequePaymTrans.InvoiceAccountNum
join vendtransopen
where VendTransOpen.RefRecId == VendTrans.RecId
join RecId from specTrans
where vendtransopen.TableId == specTrans.RefTableId
&& vendtransopen.DataAreaId == specTrans.RefCompany
&& vendtransopen.RecId == specTrans.RefRecId

{

// Logic

select TaxWithholdTrans
where TaxWithholdTrans.Voucher==BankChequePaymTrans.Voucher
&& TaxWithholdTrans.Invoice==invno && TaxWithholdTrans.TaxWithholdBaseAmount>0
&& TaxWithholdTrans.SpecTransId == spectrans.RecId;

}

Quick Links

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL