Skip to Content
0

Adding Purchase Orders info to report (crystal)

Feb 24, 2017 at 01:23 AM

53

avatar image
Former Member

I'm writing a report that includes sales order info + delivery notes. What I'm trying to do is add information from the purchase orders table (I believe it is OPOR). How do I link this table to one or more of the tables I already have? Please see my query below:

SELECT o0.NumAtCard AS OrderNo
,i0.DocNum
,i0.TrackNo AS TrackingNo
,i0.CardName
, o0.DocNum AS DelivDoc
FROM ODLN d0
INNER JOIN DLN1 d1 ON d0.DocEntry = d1.DocEntry
LEFT OUTER JOIN INV1 i1 ON d1.TrgetEntry = i1.DocEntry
LEFT OUTER JOIN OINV i0 ON i1.DocEntry = i0.DocEntry
INNER JOIN ORDR o0 ON d1.BaseEntry = o0.DocEntry
INNER JOIN OITM itm ON i1.ItemCode = itm.ItemCode
WHERE o0.SlpCode = 18 --website AND
o0.CANCELED = 'N' AND
i0.TrackNo IS NOT NULL
GROUP BY o0.NumAtCard
|,i0.DocNum
|,i0.TrackNo
,i0.CardName
,o0.DocNum
ORDER BY o0.NumAtCard

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Brian Dong Feb 24, 2017 at 09:45 PM
0

Hi Vy,

This is a hard question to answer because we don't know your database or the structure. If you are using this SQL in your report, you can add the fields to the Select portion and link the table in the FROM. Whether the join is an Inner or Outer Join will depend on how you want the data presented.

If you are using the Data Expert then add the table to the report and the PO table to your other tables.

This would be more of a question you should be asking your database administrator because they would know what type of join and what fields to link on.

Brian

Share
10 |10000 characters needed characters left characters exceeded