I would like to be able to link (SQL join) from the “G_L Entry” table to “Sales_ Invoice_ Line” table.
Is this possible?
I have written a test query using the Document No, but this query returns excess rows from the Sales_Invoice_Line table because there can be multiple rows with the same Document No, but different Line_No in the Sales_Invoice_Line table.
Is there another table that can be used to connect these tables?
Thanks for your help,
Are you trying to write a stored procedure on SQL? and for what purpose?
Linking a Sales Invoice Line to a GL Entry table is not a good idea, because you will not always be able to generate a direct link.There is no row information on the G/L Entry. And if you have a sales invoice of let’s say 5 rows with the same item and price, then there will be no way to know which is which.
The reason for this is to be found in the history of the sales invoice line and header tables. These tables were originally just created as a temporary table, for as long as you needed to be able to print the sales invoice and when printed you could deleted. And that’s actually what the user can do today (unless you remove the code or permission to do so). When posted and printed, then the sales invoice header/line tables can be deleted. All the “important” information are stored in different tables (g/l entry, customer ledger entry, item ledger entry etc.).
The real problem is that you will have a ‘many to many’ relationship if you use the Document No. (The only thing that could link the tables).
That is not a good thing as you have no way to relate any specific G/L Entry to any specific Sales Invoice Line.