Skip to Content
author's profile photo
Former Member

Adding Purchase Orders info to report (crystal)

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.TrackNo AS TrackingNo
, o0.DocNum AS DelivDoc
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
i0.TrackNo IS NOT NULL
GROUP BY o0.NumAtCard
ORDER BY o0.NumAtCard

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Posted on Feb 24, 2017 at 09:45 PM

    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.


    Add comment
    10|10000 characters needed characters exceeded