Skip to Content
0

Cannot view crystal report field

Dec 28, 2016 at 06:56 AM

90

avatar image

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!

sap5.jpg (72.3 kB)
sap6.jpg (66.3 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Vitaly Izmaylov
Dec 28, 2016 at 06:48 PM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Edy Simon Dec 29, 2016 at 01:15 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Gonzalo Gomez Dec 29, 2016 at 09:29 AM
0

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

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

Share
10 |10000 characters needed characters left characters exceeded
Zuber Kazi Dec 31, 2016 at 08:09 AM
0

Hi Nikka,

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

Regards,

Zuber Kazi

Share
10 |10000 characters needed characters left characters exceeded