Crystal Report OUtgoing payment

[Former Member]

Hi

SELECT T2.[DocTotal] as 'Invoice total' ,T2.[DocNum] as'Invoice no',T2.[DocDate] as 'invoice date',t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate ,t4.U_NAME  

FROM [dbo].[OVPM]  T0 INNER JOIN [dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry left join VPM1 t3 on

T0.DocNum = t3.docnum left join OUSR t4 on T0.UserSign=t4.USERID where T0.[Docentry]={?DocKey@} group by T2.[DocTotal],T2.[DocNum] ,T2.[DocDate] ,T2.doctotal,t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate,t4.U_NAME   Order BY T2.[DocNum]

The above code diplays outing payments details when the invoice is selected but when payment on account and account tab is selecte the report is showing blank.

please suggest what to include in the above code so that it dispalys details when payment on account and account tab is selected.

Attached screen shots for three scenarios.

Regards,

Karthik

[Former Member · Accepted solution]

Hi

when you use inner join to vpm2 you are creating this situation

try to use left join

SELECT T2.[DocTotal] as 'Invoice total' ,T2.[DocNum] as'Invoice no',T2.[DocDate] as 'invoice date',t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate ,t4.U_NAME  

FROM [dbo].[OVPM]  T0 left JOIN [dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry left join VPM1 t3 on

T0.DocNum = t3.docnum left join OUSR t4 on T0.UserSign=t4.USERID where T0.[Docentry]={?DocKey@} group by T2.[DocTotal],T2.[DocNum] ,T2.[DocDate] ,T2.doctotal,t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate,t4.U_NAME   Order BY T2.[DocNum]

shachar

[Former Member · Accepted solution]

Hi

used left join but still no result

karthik

[Former Member · Accepted solution]

try this

(you had another inner join to opch )

shachar

SELECT T2.[DocTotal] as 'Invoice total' ,T2.[DocNum] as'Invoice no',T2.[DocDate] as 'invoice date',t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate ,t4.U_NAME  

FROM [dbo].[OVPM]  T0 left JOIN [dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum left JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry left join VPM1 t3 on

T0.DocNum = t3.docnum left join OUSR t4 on T0.UserSign=t4.USERID where T0.[Docentry]={?DocKey@} group by T2.[DocTotal],T2.[DocNum] ,T2.[DocDate] ,T2.doctotal,t0.Comments,t0.CardName,T3.[CheckSum],t0.doctotal

,T3.[CheckNum],t0.DocNum,t0. CreateDate,t4.U_NAME   Order BY T2.[DocNum]

[Former Member · Accepted solution]

Thank you shachar the issue is resolved.

Regards,

Karthik