BITransSplits - Sage 50 Accounts Audit Header & Transaction Splits

by Hugh Johnson, Posted on 4/17/2018 11:00:28 AM

 ODBC, Power BI Desktop, OData, Transaction Splits

BITransSplits - Sage 50 Accounts Audit Header & Transaction Splits

Sage 50 Accounts OData feed for Power BI

This article describes the BITransSplits table in the Suntico Sage 50 Accounts OData feed for tools like Microsoft Power BI.  The table is a flattened combnation of the Sage 50 Accounts ODBC tables AUDIT_HEADER and TRANS_SPLITS.

This table provides all of the transaction splits of of all of your posted Sage 50 Accounts transactions going back over the last c. 800 days.  Excluded are any deleted transactions.

This table is essential if you want to analyse any of the following Sage 50 Accounts data:

  • Accounts payable
  • Accounts receivable
  • Nominal transactions
  • Purchase Invoices
  • Departmental Profit & Loss
  • Imported Sales transactions (via CSV)*
  • Sales transactions created via the Batch Input screens in Sage 50 Accounts*

*Sales transactions that are imported into Sage 50 Accounts via CSV file, or created through the Batch Invoices screen go straight to the Transactions audit log and do not appear in the Sage 50 Accounts Invoices list.

BITransSplits table

NominalRecordIDTextKey to BINominalRecords table
SupplierIDTextKey to BISuppliers table
CustomerIDTextKey to BICustomers table
AccountRefTextAccount for the other side of the transaction
TypeTextSage 50 Transaction Type
DateDate/TimeTransaction date
InvRefTextReference (e.g. Invoice #)
DetailsTextTransaction line description
ExtraRefTextExtra reference field from Sage
NetAmountDecimalNet amount of the transaction line
GrossAmountDecimalGross amount of the transaction line
OutstandingDecimalOutstanding amount of the transaction line
ForeignRateDecimalExchange rate of the transaction line
TranSplitIDTextUnique identifier for the record
DepartmentNameTextName of the Sage 50 Department
DeptNumberTextNumber of the Sage 50 Department
TranNumberWhole NumberTransaction Split number from Sage
DueDateDate/TimeDue Date for the transaction

BITransSplits relationships

The BITransSplits table in the OData feed has relationships with a number of other tables as shown below.

Note: the Dates table is not part of the data feed, but is easily created within your model.

To try our this OData feed for your Sage 50 Accounts data, why not create your own Suntico BI trial account?



Copyright © 2019,
Accounting Insights Ltd.
All Rights Reserved.

Our Office :
Unit 4, Roof Garden Offices,
Clarehall Shopping Centre,

+353 1 567 4069

[email protected]