From the specifics of your question, it sounds like you understand some SQL so I did a quick example below.  You mentioned the field sSource in table titrec but that doesn't exist and sSource1 (which does exist in titrec and is the invoice number), does not exist in titlu.  I don't know if this is part of your problem or not but it is a possible reason why the linking isn't working.

Below is some quick SQL that shows some linking between the invoice lookup information and the sales order header table.  I did not fully test it but it should be accurate.  However, it really depends on what you are looking for whether this is your ultimate solution.  You asked for

JasonCC said:

I'm trying to figure out how to relate Sales Order Number to Invoice Number

This information is in the titrec table in titrec.sSource1 (invoice number) and titlu.sPONum (sales order number assuming an order was converted to an invoice).  Because you mentioned the sales order table, I've linked it here as well.  Even though I have used a Left Join to the sales order table, the selection of only pulling non-reversed sales orders, forced the statement into an Inner Join.  Therefore it only shows those invoices that have sales orders.  With this code below you would also have a problem if the same sales order is used for multiple customers.

Hopefully you can modify it to suit your needs.

SELECT
    DATE(`titrec`.`dtJournal`) AS `InvoiceDate`
    , `titrec`.`sSource1` AS `InvoiceNumber`
    , `titlu`.`sName` AS `InvoiceCustomer`
    , `titlu`.`sPONum` AS `InvoiceOrderNumber`
    , DATE(`tsalordr`.`dtSODate`) AS `SalesOrderDate`
    , `tsalordr`.`sName` AS `SalesOrderCustomer`
    , `tsalordr`.`sSONum` AS `SalesOrderNumber`
FROM
    `simply`.`titrec`
    INNER JOIN `simply`.`titlu` 
        ON (`titrec`.`lId` = `titlu`.`lITRecId`)
    LEFT JOIN `simply`.`tsalordr` 
        ON (`titlu`.`sPONum` = `tsalordr`.`sSONum`)
WHERE (`titrec`.`nJournal` =8
    AND `titrec`.`bReversed` =0
    AND `tsalordr`.`bReversed` = 0);

Yes, sorry. I meant sSource1. That was a typo so that's not the cause of the problem (unfortunately) because I am using sSource1 in my test queries.

I tried your query, which is basically doing the same thing I am (more or less), and it returned no results for the invoice number in question.

I found a handful of records in titrec where the invoice number was in sSource2 or sSource3, but even checking those fields doesn't find this invoice number.

A simple SELECT * FROM titrec WHERE sSource1='xxxxx' (the invoice number in question) returns no results.

Likewise, SELECT * FROM titlu WHERE sPONum = 'zzzzz' (the SO Number which corresponds to the invoice number I'm looking for) also returns no results. However, if I query that same SO Number using sSONum in tsalordr, it is there.

And I can go to the Invoice Lookup screen in the front-end and type in the invoice number in question and it will display it along with the corresponding SO Number. It has to be somewhere. I just can't figure out where.

Generally I ignore sSource2 and sSource3.  2 is usually the payment method (eg. VISA) or cheque number (dependent on setting under the Setup menu).  sSource3 I think is only used in Make Other Payments in the Payables module.  I never use that module and I have rarely had to do reports on that info (maybe once in over 25 years).

Reviewing your latest message, it starts out leading me to believe what I mentioned in my first post.

JasonCC said:

it returned no results for the invoice number in question.

This leads me to believe what I said here is what is going on.

Richard S. Ridings said:

Therefore it only shows those invoices that have sales orders. 

In other words, if an invoice was not created by a sales order then my SQL statement will not show it.

But because you can go to the Sage 50 user interface, open the Sales data entry screen and search an invoice and get the invoice with the sales order number, it means it can be found.  It is possible to enter a sales invoice and enter your own number in the Order Number field, so it may not have been created from a sales order and wouldn't be seen from my statement.

The rest of your post is indicating to me that you may be looking at two different databases.  The file you have open in Sage 50 may not be the same file you are linked to in your reporting software.  Are you sure you are pointing to the exact same MySQL port on the exact same server?

Run this statement to see if you get the same company name showing in Sage 50 on the Home screen.

SELECT scompname FROM simply.tcompany;

Can you find the sales order number in tsalordr.sSONum?  If so, what does tsalordr.nFilled indicate?  0, 1 or 2?  If 2, then it was converted to an invoice with no remaining backorders (in one or more invoices). 1 = partial conversion, 0 = not converted to an invoice.

Well, this was user error (mainly, mine). I had a script that was copying off the tables so I wouldn't be hammering the Sage production db with all of my tests and I forgot to release an update to the script, so a couple of the tables were stale. Thanks anyway, as you've confirmed for me the method of relating Invoice Number to Sales Order (titlu to titrec to salordr), but I did have a couple of detail questions.

You show nJournal=8 in your query. What is that doing exactly?

Also, is there a set of criteria that will only pull "legit" sales records from tsalordr? 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. 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?

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.

Richard S. Ridings said:

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

Yes, this is my intention. These initial tests are just proof-of-concept to make sure I can relate the two sources directly in the tables (I'm actually trying to tie this all to the Journal Entry tables as well). I will ultimately be using views to pull the various extracts once it comes to that.

I think I'm good for now, but I'm sure once I get down into the finite details I'll have more questions. At least I know where to go.