Exact Online Formulas
Invantive Control for Excel comes bundled with several hundreds of formulas to retrieve data from Exact Online directly into your Excel worksheets. They cover the areas:
- Finance
- PSA / projects and timesheets
- Manufacturing
- Logistics and trade
- Use Excel formulas to retrieve any data from Exact Online
This covers large areas of the data frequently used in Excel from Exact Online. However, the total number of available tables is approximately 400, so there are many areas for which no formula is available.
Of course we are happy to add Excel formulas for areas often used by you and other users of Invantive Control for Excel. And of course you can always use the Model Editor to define blocks to retrieve data. But in cases where you don’t have yet access to a formula, you can always resort to a specific formula which can retrieve all data made available through the APIs. The only disadvantage is that it is a little bit harder to write as a formula.
Magic of I_SQL_SELECT_SCALAR
Let us assume that we want to retrieve the bank accounts of an account in a cell. The SQL for this is:
select iban
from AccountBankAccounts
where account_code_attr = ACCOUNTCODE
and code = ACCOUNTCODE /* Server-side field for better performance. */
With the pre-defined Invantive formula I_SQL_SELECT_SCALAR you can retrieve the data directly into a cell. Use the following formula:
I_SQL_SELECT_SCALAR("iban";"accountbankaccounts";"code='90' and account_code_attr='90'";;;;)
And of course, you must replace the semi-colon by commas when you are using an English version of Excel.
The result is the IBAN number of the account 90:
NL42RABO0123409780
Any data element available in the Exact Online tables is available through this formula.
Performance Considerations
When you query the bank account for over 15.000 accounts across 250 companies, you will notice that each calculation takes approximately 200 ms. Across such a large volume, that result in an unacceptable calculation time.
In this scenario, we recommend you to leave out the performance optimization ‘code=90’. This ensures that all data is fetched in one go; the initial lookup will be slower (such as 60 seconds), but the total calculation time will go down from 1 hour to a few minutes.
This is similar to the performance differences between full table scan and index-based queries on traditional database platforms such as Oracle or Teradata.
More Excel formulas for Exact Online
These articles (among others) provide more tips on the use of Excel with Exact Online: