Report Designer - Common data joins for Sage Accounts
Summary
A list showing some of the most commonly used data joins in Sage 50 Accounts.
Description
Before using the Join Editor it's important to understand How data joins work, to ensure your report shows the correct information.
NOTE: Take a backup of your reports and layouts before using any of the data joins. Use our article to find Which files to include in a Sage 50 Accounts backup.
Resolution
▼ Bank
| Join | Type | |
|---|---|---|
| Bank information on a supplier report | AUDIT_HEADER.BANK_CODE to BANK.ACCOUNT_REF | Parent Outer |
| Bank information on a customer report | AUDIT_HEADER.BANK_CODE to BANK.ACCOUNT_REF | Parent Outer |
▼ Customer
| Join | Type | |
|---|---|---|
| Sales ledger information on a nominal report. | AUDIT_JOURNAL.ACCOUNT_REF to SALES_LEDGER.ACCOUNT_REF
Don't use this join if you have a customer and supplier with the same account reference | Parent Outer |
| Sales ledger information on a financial report | AUDIT_SPLIT.ACCOUNT_REF to SALES_LEDGER.ACCOUNT_REF Don't use this join if you have a customer and supplier with the same account reference | Parent Outer |
| Sales ledger information on a bank report | AUDIT_SPLIT.ACCOUNT_REF to SALES_LEDGER.ACCOUNT_REF Don't use this join if you have a customer and supplier with the same account reference | Parent Outer |
| Payment method on a customer report | SALES_LEDGER.PAYMENT_METHOD_ID to PAYMENT_METHOD.UNIQUE_ID | Parent Outer |
▼ Invoices and delivery addresses
| Join | Type | |
|---|---|---|
| Invoice information on a customer statement or report | AUDIT_HEADER.INV_REF_NUMERIC to INVOICE.INVOICE_NUMBER This join only works if you update the Audit trail with the invoice numbers rather than order numbers. You can set this in Invoice Defaults. | Parent Outer |
| INVOICE information on the Goods Despatched Note. | GDN_ITEM.GDN_NUMBER to INVOICE.GDN_NUMBER | Inner |
| Invoice information on a stock transactions report | STOCK_TRAN.REF_NUMERIC to INVOICE.INVOICE_NUMBER If you add the INVOICE table to your stock transaction report, you can't then add the SALES_ORDER or PURCHASE_ORDER table to the same report. | Parent Outer |
| Delivery address information on an invoice layout | INVOICE.DEL_ADDRESS_1 to SALES_DEL_ADDR.ADDRESS_1 If the customer has two delivery addresses with identical information on the first line, use DEL_ADDRESS_2 instead. | Parent Outer |
▼ Nominal
| Join | Type | |
|---|---|---|
| Display the nominal code used on a transaction on the Nominal Activity. | AUDIT_JOURNAL.SPLIT_NUMBER to AUDIT_SPLIT.SPLIT_NUMBER | Parent Outer |
| Nominal information on an invoice report | INVOICE_ITEM.NOMINAL_CODE to NOMINAL_LEDGER.ACCOUNT_REF | Parent Outer |
| Nominal information on a purchase order layout or report | POP_ITEM.NOMINAL_CODE to NOMINAL_LEDGER.ACCOUNT_REF | Parent Outer |
| Nominal information on a purchase ledger report | PURCHASE_LEDGER.DEFAULT_NOM_CODE to NOMINAL_LEDGER.ACCOUNT_REF | Parent Outer |
| Nominal information on a transactional report, for example, bank or customer | AUDIT_SPLIT.NOMINAL_CODE to NOMINAL_LEDGER.ACCOUNT_REF Use an Inner join first, then if the report doesn't run, change it to Parent Outer. | Inner |
| Nominal information on a transactional project report | AUDIT_SPLIT.NOMINAL_CODE to NOMINAL_LEDGER.ACCOUNT_REF | Parent Outer |
▼ Project
| Join | Type | |
|---|---|---|
| Project information on an invoice report or layout | INVOICE_ITEM.PROJECT_ID to PROJECT.PROJECT_ID | Parent outer |
| Project information on a stock transaction report | STOCK_TRAN.TRAN_NUMBER to PROJECT_TRAN.STOCK_TRAIL_ID | Parent outer |
| PROJECT_TRAN.PROJECT_ID to PROJECT.PROJECT_ID | Inner | |
| Project information on a sales order report or layout | SOP_ITEM.PROJECT_ID to PROJECT.PROJECT_ID | Parent outer |
| POP_ITEM.PROJECT_REF to PROJECT.REFERENCE If you only want to show orders assigned to a project, change the join type to Inner. | Parent Outer. | |
| Use this join if the above join makes the report slow and if the items on each purchase order are part of the same project. | PURCHASE_ORDER.PROJECT_ID to PROJECT.PROJECT_ID This join doesn't work for multi-project purchase orders. | Parent Outer. |
| Project information on a bank, customer, supplier, nominal or financial report | AUDIT_SPLIT.TRAN_NUMBER or AUDIT_JOURNAL.TRAN_NUMBER to PROJECT_TRAN.AUDIT_TRAIL_ID | Parent outer |
| PROJECT_TRAN.PROJECT_ID to PROJECT.PROJECT_ID | Inner | |
| PROJECT_TRAN.COST_CODE_ID to PROJECT_COST_CODE.COST_CODE_ID | Parent outer |
▼ Purchase order, delivery and GRN
NOTE: The INVOICE, SALES_ORDER and PURCHASE_ORDER tables only join to the AUDIT_HEADER.INV_REF_NUMERIC variable. The nominal report uses the AUDIT_JOURNAL table.
| Join | Type | |
|---|---|---|
| Purchase order information on a project report | POP_ITEM.ORDER_NUMBER to PURCHASE_ORDER.ORDER_NUMBER | Inner |
| Delivery address information on a purchase order layout | PURCHASE_ORDER.DEL_ADDRESS_1 to PURCHASE_DEL_ADDR.ADDRESS_1 To show the delivery address from the supplier record: If the supplier has two delivery addresses with identical information on the first line, use DEL_ADDRESS_2 instead. | Parent Outer |
| PURCHASE_ORDER.DEL_ADDRESS_1 to COMPANY_DEL_ADDR.ADDRESS_1 To show the delivery address in Company Preferences. | Parent Outer | |
| POP_ITEM information on a GRN report | GRN_ITEM.ORDER_ITEM,ORDER_NUMBER to POP_ITEM.ITEM_NUMBER,ORDER_NUMBER | Inner |
| GRN information on a purchase order report | For this join, edit an existing report by clicking Purchase Order processing, then click Reports, then click Goods received. | |
| Purchase order information on a stock transactions report | STOCK_TRAN.REF_NUMERIC to PURCHASE_ORDER.ORDER_NUMBER If you add the PURCHASE_ORDER table to your stock transaction report, you can't then add the INVOICE or SALES_ORDER table to the same report. | Parent Outer |
▼ Sales order, delivery and GDN
NOTE: The INVOICE, SALES_ORDER and PURCHASE_ORDER tables joins to the AUDIT_HEADER.INV_REF_NUMERIC variable only. The nominal report uses the AUDIT_JOURNAL table.
Join | Type | |
|---|---|---|
| Delivery address information on a sales order layout or delivery note | SALES_ORDER.DEL_ADDRESS_1 to SALES_DEL_ADDRESS.ADDRESS_1 If the customer has two delivery addresses with identical information on the first line, use DEL_ADDRESS_2 instead. | Parent Outer |
| Delivery address information on a goods despatched note | The standard Goods Despatched Note contains the SALES_DEL_ADDR table, however, if delivery addresses don't appear correctly, use the raw text editor to add it. [SALES_ORDER] INNER JOIN [GDN_ITEM] ON SALES_ORDER.ORDER_NUMBER = GDN_ITEM.ORDER_NUMBER LEFT OUTER JOIN [SALES_DEL_ADDR] ON SALES_ORDER.DEL_ADDRESS_1 = SALES_DEL_ADDR.ADDRESS_1 AND SALES_ORDER.ACCOUNT_REF = SALES_DEL_ADDR.ACCOUNT_REF INNER JOIN [STOCK] ON GDN_ITEM.STOCK_CODE = STOCK.STOCK_CODE INNER JOIN [SOP_ITEM] ON GDN_ITEM.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER AND GDN_ITEM.ORDER_ITEM = SOP_ITEM.ITEM_NUMBER INNER JOIN [SALES_LEDGER] ON GDN_ITEM.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF CROSS JOIN [LETTER_TYPE] CROSS JOIN [COMMUNICATION_ADDRESS] STATIC JOIN [COMPANY]. | |
| GDN_ITEM information on a delivery note | SOP_ITEM.ITEM_NUMBER,ORDER_NUMBER to GDN_ITEM.ORDER_ITEM,ORDER_NUMBER This data join only works if you use Despatch to fully despatch the sales orders, rather than using Amend. It doesn't work with any part despatch. | Inner |
| SOP_ITEM information on a GDN report | GDN_ITEM.ORDER_ITEM,ORDER_NUMBER to SOP_ITEM.ITEM_NUMBER,ORDER_NUMBER You can't show non-despatched SOP items or message lines on the GDN report. | Inner |
| Add the SALES_LEDGER, STOCK, CURRENCY and COUNTRY_CODE tables to the Despatch Note | SALES_ORDER.ACCOUNT_REF to SALES_LEDGER.ACCOUNT_REF | Inner |
| SOP_ITEM.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer | |
| SALES_LEDGER.CURRENCY to CURRENCY.NUMBER | Inner | |
| SALES_LEDGER.COUNTRY_CODE to COUNTRY.CODE | Inner | |
| Sales order information on a customer report | AUDIT_HEADER.INV_REF_NUMERIC to SALES_ORDER.ORDER_NUMBER | Parent Outer |
| Sales order information on an invoice layout | INVOICE.ORDER_NUMBER_NUMERIC to SALES_ORDER.ORDER_NUMBER | Parent Outer |
| Sales order information on a stock transactions report | STOCK_TRAN.REF_NUMERIC to SALES_ORDER.ORDER_NUMBERIf you add the SALES_ORDER table to your stock transaction report, you can't then add the INVOICE or PURCHASE_ORDER table to the same report. | Parent Outer |
▼ Supplier
| Join | Type | |
|---|---|---|
| Purchase ledger information on a remittance layout | REMITTANCE.ACCOUNT_REFPURCHASE_LEDGER.ACCOUNT_REF | Inner |
| Purchase ledger information on a financial report | AUDIT_SPLIT.ACCOUNT_REF to PURCHASE_LEDGER.ACCOUNT_REF Don't use this join if you have a customer and supplier with the same account reference. | Parent Outer |
| Purchase ledger information on a nominal report | AUDIT_JOURNAL.ACCOUNT_REF to PURCHASE_LEDGER.ACCOUNT_REF Don't use this join if you have a customer and supplier with the same account reference. | Parent Outer |
| Purchase ledger information on a bank or financial report | AUDIT_SPLIT.ACCOUNT_REF to PURCHASE_LEDGER.ACCOUNT_REF Don't use this join if you have a customer and supplier with the same account reference. | Parent Outer |
| Payment method on a supplier report | PURCHASE_LEDGER.PAYMENT_METHOD_ID to PAYMENT_METHOD.UNIQUE_ID | Parent Outer |
| Purchase ledger information on an invoice | STOCK.PURCHASE_REF to PURCHASE_LEDGER.ACCOUNT_REF This data join shows the supplier currently set on the product record. It's not retrospective and has no link to the original purchase order. | Parent Outer |
▼ Stock
| Join | Type | |
|---|---|---|
| Stock allocation information on a product report | STOCK.STOCK_CODE to STOCK_ALLOCATION.STOCK_CODE This join shows stock allocated through the Allocations option in the product record. . It doesn't include allocations from SOP. To include products with no allocations, you must set the join to Parent Outer. | Inner. |
| Stock information on a goods despatched note | SOP_ITEM.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer. |
| Stock information on a project report | STOCK_TRAN.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer. |
| Stock information on a purchase order report | POP_ITEM.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer. |
| Stock information on the Stock Allocations Per Project report | STOCK_ALLOCATION.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer. |
| Stock information on a sales order report | SOP_ITEM.STOCK_CODE to STOCK.STOCK_CODE | Parent Outer. |
| Stock tax rate information on a product report | STOCK.TAX_CODE to TAX_CODE.TAX_CODE | Inner. |
▼ Other
| Join | Type | |
|---|---|---|
| Audit Trail information on a nominal transactions report | AUDIT_JOURNAL.TRAN_NUMBER to AUDIT_SPLIT.TRAN_NUMBER | Inner |
| CIS information on a supplier report | PURCHASE_LEDGER.ACCOUNT_REF to CIS_SUBCONTRACTOR.SUPPLIER_REFN | Inner shows only suppliers who are subcontractors. Parent Outer shows all suppliers. |
| Department information on a bank or customer report | AUDIT_SPLIT/JOURNAL.DEPT_NUMBER to DEPARTMENT.NUMBER | Inner |
| EC VAT Description information on an invoice layout | INVOICE_ITEM.EC_VAT_DESCRIPTION_ID to EC_VAT_DESCRIPTION.UNIQUE_ID | Parent Outer. |
| Fund information on reports that use the AUDIT_SPLIT table | AUDIT_SPLIT.FUND_ID to FUND.FUND_ID To show only transactions assigned to a fund, or to add fund criteria, change the join type to Inner. | Parent Outer. |
| Fund information on reports that use the AUDIT_JOURNAL table | AUDIT_JOURNAL.FUND_ID to FUND.FUND_ID To show only transactions assigned to a fund, or to add fund criteria, change the join type to Inner. | Parent Outer. |
Solution Properties
- Solution ID
- 200427112216175
- Last Modified Date
- Fri Apr 24 13:06:21 UTC 2026
- Attributes
-
Product DetailsModules: Report Writer
- Views
- 0