How to get Sales Order Number for a given Invoice Number...

Question

I'm trying to figure out how to relate Sales Order Number to Invoice Number in the data (for extract and reporting). There is a screen in the front-end that does this titled "Sales - Looking up Invoice xxxxxxx". It shows both the Invoice Number and the Sales Order number at the top right, so I know there's a way to relate these two, but I can't find it in the DataDict or by looking through the tables. 
 There is a way that *almost* works: 
 titrec to titlu via sSource=sSource1 (which contains the Invoice Number), then titlu to tsalordr via sPONum=sSONum (which contains the Sales Order number). 
 This works much of the time, but not always. There is a specific Invoice number I've looked up in the front-end which shows me the Invoice Number and Sales Order number, but that same Invoice Number doesn't exist in titrec.sSource (nor does the corresponding SO Number exist in titlu.sPONum), so my approach above doesn't find a match. Sage must be using something else to relate these two, but I don't know what it is. 
 Does anyone know the "official" way to do this?

Richard S. Ridings · Accepted Answer

Please bear in mind that the SQL statement I gave you is limited in function and not suitable for all uses. If I were doing this myself, I would likely create an aliased table from the sales orders and another for the invoice lookup in some manner and then link them together. That way I could link the customer ID that is associated with the invoice and the customer ID associated with the sales order as well, etc. The exact manner of creating the SQL statement would be determined by the final use of the data and exact data I needed. 
 JasonCC said: You show nJournal=8 in your query. What is that doing exactly? 
 You mentioned the DataDict.pdf file above. If you read the titrec table for the nJournal field, you will see an asterisk in the description that relates to the info below the field list. The DataDict does not always contain all information we need, but in this case, it shows you that those tables include the purchase, credit and debit note lookups, etc. 8 = Sales. 
 JasonCC said: Is the only sure way to do it to use an INNER JOIN so you only show sales order records that also have an invoice record? 
 Based on what you have described as your needs, then yes, an INNER JOIN would be better. However, as I mentioned above, the exact SQL statement may not just link actual tables together, it may require using aliased tables linking different SELECT statements (basically subqueries). This will allow you to link customers directly to their orders and invoices. It is possible to have an order number on an invoice for two different customers be the same number. They don't have to originate from a sales order. However, the SQL I gave you will link the same order number 200 for both Ashburton and Askew to the same sales order 200 that is only for Ashburton. 
 I have used different WHERE statements over the years for different purposes depending on how my clients user their file and what they want in the reports. 
 WHERE (`tsalordr`.`bReversed` =0 AND `tsalordr`.`bCleared` = 0) 
 WHERE (`tsalordr`.`bQuote` = 0 AND `tsalordr`.`bCleared` = 0) 'bQuote = 1 means it is a quote not sales order 
 These are just two I was able to pull from my code quickly. 
 JasonCC said: I see you use bReversed=0, but if I use that (and even add nFilled<>0) I still get Sales Order records that have no corresponding Invoice. 
 On the surface, that just tells me that the order has not been turned into an invoice yet and you can still see it on the Pending Sales Order report in Sage 50.

Sage 50 Canada

How to get Sales Order Number for a given Invoice Number...

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING