Skip to Content
avatar image
Former Member

Cannot view crystal report field

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Dec 28, 2016 at 06:48 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 29, 2016 at 01:15 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 29, 2016 at 09:29 AM

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 31, 2016 at 08:09 AM

    Hi Nikka,

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

    Regards,

    Zuber Kazi

    Add comment
    10|10000 characters needed characters exceeded