Skip to Content
0
Jul 28, 2020 at 12:17 AM

Get sales quote number to sales order query + order lines on a single line

85 Views Last edit Jul 28, 2020 at 12:21 AM 3 rev

Hi all,

This is a 2 part question. The first half is probably something really simple but i cant see why it's not working (it works going from quote to sales order but not the other way). I need this to also show the T5.DocDate of the linked sales quotation but i cant get it to display.

SELECT DISTINCT T0.DocNum,T0.[CardCode], T0.[CardName],T1.FirstName,T1.LastName,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T5.DocDate
 FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN QUT1 T4 ON T4.BaseEntry = T3.DocEntry AND T4.BaseLine = T3.LineNum
LEFT JOIN OQUT T5 ON T5.DocEntry = T4.DocEntry
WHERE T0.series = '73' AND T0.DocStatus = 'O'

The second half is a bit more complicated and i dont know if it can be done. I need to display all the line items (itemcode and quantity) from a sales order on each details line in Crystal reports. Example below

As always, any help is appreciated.

Attachments

info.jpg (46.0 kB)