It is so hard to find useful information for SAP. I hope this helps. I created this document in SAP Business One 9.2 and Crystal Reports 2011.
Database Expert
If you think you can find a good relationship between ORDR, RDR1 and RDR10…good luck.
I needed to create a command to do it for me.
Create your command using this SQL join. If there are more fields you would like to use or less, you can modify your command whenever you want.
SELECT *
FROM
(
SELECT
docnum, linenum, visorder, itemcode, dscription, 0 as ordernum, OpenInvQty,
U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime
FROM
rdr1 INNER JOIN ordr
ON rdr1.docentry=ordr.docentry
UNION
SELECT
docnum, aftlinenum, visorder, '', cast(linetext as nvarchar(500)), rdr10.ordernum,
OpenInvQty, U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime
FROM
rdr10 INNER JOIN ordr
ON ordr.docentry=rdr10.docentry
INNER JOIN rdr1 ON rdr10.docentry =rdr1.docentry AND rdr10.aftlinenum=rdr1.visorder
)
O ORDER BY docnum, visorder, ordernum
That’s it. Do not use any other Tables (it may crash CR). Go ahead and modify the report so that you do not have to go through all records. I.e. filter for open POs only.
* If you want to preview, make sure to create a record select on the DocNum so you do not load all PO’s.
** Use Parameter field DocKey@ and link it to your DocNum in the formula workshop before importing to SAP B1.
