| | Report Designer - Common data joins for Sage Accounts |
| Resolution | | | Join | Type | | Bank information on a supplier report | AUDIT_HEADER.BANK_CODE to BANK.ACCOUNT_REF | Parent Outer | | | 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 | | | 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 | | | 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 | | | 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. | | If the above join makes the report run slowly and if the items on each purchase order are part of the same project, you can use this join instead. | 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 |
 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 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, 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 | NOTE: The INVOICE, SALES_ORDER and PURCHASE_ORDER tables only joins 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, 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'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 | | | 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 | | | 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. | | | 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. | [BCB:19:UK - Sales message :ECB]
|
|