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
Project information on a purchase order report or layout - Excluding GRN reports	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.