Need help trying to figure the correct way to join these two tables for a Debtors ageing report model extracting data from SAGE X3.
First scenario I joined these two tables by INV_0 which shows all doc types. When I test these amounts to show INV_0 it seems to associate to NUMDUD_0 on the other table. Any idea how to join them? What this require any other tables?
Second scenario I left joined the Sinvoice table to the Gaccdudate and found that I can only see the doc types SAINV and SACRN. No GEN01 or REC01 types seem to be appearing or could be found by search box.
With Regards to second scenario , there must be no row keys in first table that have a matching row key in the second table that have Doc type GEN01 or REC01, by the nature of LEFT JOIN on Rows matching Key in Left table are kept.
Try an OUTER JOIN (TABLENAME) and see
Vineeth Pujari If a post helps to resolve your issue, please accept it as a Solution.
With Regards to second scenario , there must be no row keys in first table that have a matching row key in the second table that have Doc type GEN01 or REC01, by the nature of LEFT JOIN on Rows matching Key in Left table are kept.
Try an OUTER JOIN (TABLENAME) and see
Vineeth Pujari If a post helps to resolve your issue, please accept it as a Solution.
This seems to work to keep all document types intact. Thank you. Would you suggest using both your suggestions as I'm still not able to "bind" or associate the NUM_0 and NUMDUD_0 to pull all in. Sure there is not a need for an additional table such as PAYMENTD?
Please note that sometimes transactions in GACCDUDATE will not have a corresponding transaction in SINVOICE
It is possible to create invoices directly from the general ledger. in that event no transaction will exist in sub ledger. This is why the reports also run off GACCDUDATE which is your open item table.
Sometimes the business will choose to take on outstanding AP and AR transactions as only open items rather than a full take on of the outstanding invoices in sub ledger.
