Cannot view crystal report field

[former_member236650]

Hello Experts!

I need help with my query. When generating a CREDIT MEMO form, we have some missing fields. Below is the query we used for the A/R CREDIT MEMO form.

ALTER View [dbo].[APP_CM] as

select distinct

a.CardName, a.Address, a.DocEntry, a.DocDate, a.DocNum, b.LicTradNum, c.ItemCode, c.Dscription, SUM(c.PriceBefDi) 'Base', a.DocTotal, a.VatSum, e.docnum 'Inv No.', e.docdate ' Inv.Date', SUM(f.PriceBefDi) 'InvBase', e.VatSum 'Inv Vat', e.DocTotal 'Inv Total', g.Account, g.Debit, g.Credit, h.AcctName, i.prjname, c.project, a.comments, c.ocrcode3, SUM(c.GTotal) [GTotal]

from ORIN a left join OCRD b on a.CardCode=b.CardCode left join RIN1 c on a.DocEntry=c.DocEntry left join CRD1 d on a.CardCode=d.CardCode left join INV1 f on c.BaseEntry = f.docentry AND c.BaseLine = f.LineNum and c.BaseType = f.ObjType left join OINV e on f.docentry = e.docentry left join JDT1 g on a.transid = g.TransId left join OACT h on g.Account = h.acctcode left join OPRJ i on c.project = i.prjcode

--where a.docnum = 1000002941

group by a.CardName, a.Address, a.DocEntry, a.DocDate, a.DocNum, b.LicTradNum, c.ItemCode, c.Dscription, --SUM(c.PriceBefDi) 'Base', a.DocTotal, a.VatSum, e.docnum , e.docdate , --SUM(f.PriceBefDi) 'InvBase', e.VatSum , e.DocTotal, g.Account, g.Debit, g.Credit, h.AcctName, i.prjname, c.project, a.comments, c.ocrcode3

When running the query, the results return a NULL value. But when looking through SAP, this CREDIT MEMO was referenced from an AR INVOICE.

Thank you!

[former_member324899]

Hi Nikka,

There is no issue with query. Can you please share relationship map of that credit note.

Regards,

Zuber Kazi

[gonzalogomez]

In crystal reports exist an option that allows nulls in the reports.

In File-Options-Reports..you will see Allow Nulls....etc...

[edy_simon]

Hi Nikka,

Your joining even though not precise but looks ok and should work.
Are you querying the correct database?
Can you query only the RIN1 and see what is the value of BaseType, BaseEntry and BaseLine.
Separately, query the INV1 using the BaseType, BaseEntry and BaseLine you got from the prev query ?

Regards
Edy

[vitaly_izmaylov]

I would review the table linking with your DBA. Sould Left join be used or inner join. It could be a linked field value mismatch. There are 8 tables in your query, are the NULL values coming from the same table?