The Invantive drivers make all 1.100 usable tables available for Exact Online in real-time.
The Exact Online tables are available for use in SQL, Power BI Desktop, Power BI Service, Power Query, Azure Data Factory, Qlik, Tableau and other popular packages.
The steps to link Exact Online with Power BI can be found in Connect Power BI to Exact Online, 180 days free.
Recommended Tables
The following tables are recommended for use on Exact Online reports and analysis. Tables have been selected from the over 1.100 tables available for both best real-time performance as well as providing excellent coverage for many questions (note that many more detailed tables exist, see data model at the end):
| Area | Table name | Contents |
|---|---|---|
| General | SystemDivisions | available Exact Online companies |
| General | AccountsIncremental | customers, suppliers and prospects |
| General | ItemsIncremental | articles |
| General | ItemGroups | article groups |
| General | TransactionLinesIncremental | financial transactions in any ledger |
| Accounting | BalanceLinesPerPeriod | balance per company, general ledger account and financial period |
| Accounting | AROutstandingItems | open sales invoices |
| Accounting | APOutstandingItems | open purchase invoices |
| Accounting | GLAccountsIncremental | general ledger accounts |
| Accounting | BudgetScenarios | budget scenarios |
| Accounting | Budgets | budgeted amounts |
| Projects | ProjectsIncremental | projects |
| Projects | PjtTimeTransactionsIncremental | hours on projects |
| Projects | ProjectWBSIncremental | work breakdown structure |
| Trade | SupplierItems | suppliers |
| Trade | BatchNumbers | batch number |
| Trade | SerialNumbers | serial numbers |
| Trade | WarehouseTransfers | warehouse transfer |
| Trade | WarehouseTransferLines | lines of warehouse transfer |
| Trade | StockPositionsIncremental | article stock positions |
| Trade | StorageLocationStockPositionsIncremental | detailed stock positions |
| Trade | GoodsDeliveryLinesBulk | outgoing goods |
| Trade | GoodsReceiptLines | incoming goods |
| Manufacturing | ShopOrders | shop orders |
| Manufacturing | Workcenters | work centers |
| Manufacturing | MfgSalesOrderLines | manufacturing sales order lines |
| Manufacturing | MfgTimeTransactions | work center time consumption |
| Sales | SalesOrderLinesIncremental | sales orders and their lines |
| Sales | SalesInvoiceLinesIncremental | invoices and invoice lines |
| Purchase | PurchaseOrderLinesIncremental | purchase orders and their lines |
| Payroll | Employees | employees |
| Payroll | Employments | employments |
| Payroll | PayrollTransactions | payroll transactions |
Note that Exact Online has a mixed ledger with everything becoming real-time a financial transaction).
Note also that for transaction tables such as TransactionLinesIncremental and SalesOrderLinesIncremental the header is stored as line number 0. For more details see Special line number such as 0 and 9999 on financial transactions in Exact Online.
Currently most often used tables
A few Exact Online tables were the most used over the past year. The table below shows which Exact Online tables are the most popular and will help you selecting tables.
The currently most often used tables deviate from the recommended tables, since many of the now recommended tables didn’t exist when people developed their reports.
| Position | Table name | Description |
|---|---|---|
| 1 | TransactionLinesIncremental | Journal entries and lines |
| 2 | DocumentAttachmentsIncremental | Document attachments |
| 3 | SalesOrderLinesIncremental | Sales orders and lines |
| 4 | AccountsIncremental | Accounts |
| 5 | SalesInvoiceLinesIncremental | Sales invoices and lines |
| 6 | PjtTimeTransactions | Time on Projects |
| 7 | ReportingBalance | Balances |
| 8 | GLAccountClassificationMappings | General Ledger account mappings |
| 9 | XML.AccountClassifications | Account classification |
| 10 | SalesEntries | Sales |
The full list of over 1.100 tables is available online:
A graphical representation of the relationships between tables in Exact Online is available online: