sql query to get DocNum and NumAtCard from order in the invoice

alex oosterom

Hello everybody.

If you are good with query's; please read my question.

A customer of us wants on his invoice layout de order nr's (docnum) that are related to the articles on his invoice.

He always creates first the order then the delivery and then the invoice.
To get the docnum from the order on his invoice i wrote the following query:
SELECT T0.[DocNum] as ODLNDocNum,T2.*,T3.* FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN ORDR T2 ON T1.BaseDocNum=T2.DocNum INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry

I named my query 'InvoiceOrderInput

The next thing i did was that i connected the query to the lay-out definition like below:
Query Query2 Linkfield1 Linkfield2
SWA_LD_Document:ROW ...'InvoiceOrderInput' BaseDocNum ODLNDocNum


This works good! the only thing is that when the customer has 2 different order on 1 delivery, he gets wrong order document numbers on his invoice.

Have anyone a idea to let this thing work???
If it is with a totally different way it's alright with me.

Thank you!

Philipp Knecht

Hi Alex

Basically your approach is Ok.
Just some Tipps.
I would recommend to use the BaseEntry - Field in the RDR1,DLN1,INV1,... - Table.
(Use then DITINCT if you get too many rows)
Also you should restrict your Query somewhere with ... [%DocEntry] = DocEntry ...

hth