| | Report Designer - Common data joins for Sage Accounts |
| Resolution | Bank
| Join | Type |
|---|
| Bank information on a supplier 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 |
Invoice 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 |
|---|
| Show the nominal code a transaction is posted to 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 that are assigned to a project, change the join type to Inner. | Parent Outer. | | If the above join makes the report run slowly and if the items on each purchase order are all assigned to the same project, you can use this join instead. | PURCHASE_ORDER.PROJECT_ID to PROJECT.PROJECT_ID
This join doesn't work if any purchase orders are assigned to multiple projects. | 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 can only be joined to the AUDIT_HEADER.INV_REF_NUMERIC variable. The nominal report uses the AUDIT_JOURNAL table.
| Join | Type |
|---|
| Purchase order information on a supplier report | AUDIT_HEADER.INV_REF_NUMERIC to PURCHASE_ORDER.ORDER_NUMBER | Parent Outer | | 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 you should 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 can only be joined to the AUDIT_HEADER.INV_REF_NUMERIC variable. 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, you can use this rax text join in The raw text editor.
[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're using Despatch to fully despatch the orders within Sales Order Processing, 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, project 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 information on a nominal report | AUDIT_JOURNAL.DETAILS to STOCK.DESCRIPTION | Parent Outer | | 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 which are 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 which are assigned to a fund, or to add fund criteria, change the join type to Inner. | Parent Outer. |
[BCB:19:UK - Sales message :ECB]
|
|