on 08-14-2020 8:15 AM
Hi all,
Im trying to get my query to display Quotes with linked invoice numbers (if they exist or not) where I will then filter from there.
I have the following query but for some reason i cant get an invoice number to show for any of the records, some of which i know exist. I've change the join around a few times but hasnt seem to fixed it...im sure its simple but im not seeing it.
SELECT DISTINCT T0.[DocNum], T0.[CardName], T0.[DocDate], T0.[DocTotal],T0.[DocStatus], T3.DocNum,T7.DocNum FROM OQUT T0 INNER JOIN QUT1 T1 ON T1.DocEntry = T0.DocEntry LEFT JOIN RDR1 T2 on T2.[BaseEntry] = T1.[DocEntry] AND T2.[BaseLine] = T1.[LineNum] LEFT JOIN ORDR T3 ON T2.[DocEntry] = T3.[DocEntry]
left outer join DLN1 T4 on T4.BaseEntry = T2.DocEntry and T4.BaseLine = T2.Linenum
left outer join ODLN T5 on T4.DocEntry = T5.DocEntry
left Outer join INV1 T6 on T6.BaseEntry = T3.DocEntry and T6.BaseLine = T2.Linenum and T4.BaseType = 15
OR (T6.Basetype=17 and T6.BaseEntry=T2.DocEntry and T6.BaseLine=T2.LineNum)
left join OINV T7 ON T7.DocEntry = T6.DocEntry WHERE T0.[Series] = 78 AND T0.[CANCELED] = 'N' ORDER BY T0.[DocDate] DESC
Any help will be appreciated as always.
Just a follow up question as well out of curiosity. This query will show multiple lines for some records because the sales order has been modified and doesn't match the original quote. Is there a way to circumvent this?
Regards,
Nick
Ended up using the delivery num as my reference point without needing to go all the way to invoice. If delivery num existed then it wouldnt show those records.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nick,
When the link between Quote and invoice is not direct, this query is going to be very difficult to write. This is because the path from a quote to an invoice, can go different ways:
Quote-->Invoice
Quote-->Delivery-->Invoice
Quote-->Sales Order-->Delivery-->Invoice
Quote-->Sales Order-->Invoice
They are all linked in reverse order, and your follow question reveals that there can be multiple links from line to line.
So please first try to reverse the order of your tables in the FROM clause. To avoid multiple records per quote, you could try the solution I used in your other question, and combine all target document numbers into a single column.
Regards,
Johan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.