AFAS via Invantive Control for Excel

Questions
,
1

I’ve recently asked support if getting AFAS data via Invantive Control is possible. The answer was that it’s not directly supported with standard formulas.

Eventhough it’s not supported is it possible to retreive data from AFAS via Invantive Control in Excel?
I’ve looked into the old youtube videos and tried things similar to the link below, but I can’t get a hold of it.

2

There is currently no set of pre-defined formulas for AFAS and it is not expected that these will become available in the near future. AFAS has no standardization yet on the APIs like Nmbrs or Exact given the dynamic nature of the AFAS connectors. There have been talks about uploading GetConnector definitions from vendors, but that has not sufficiently moved forward given the high volume of installations of Invantive Control and differences between AFAS instances.

You can retrieve the data from AFAS into Excel in various ways. The two that spring into mind are:

  • Create an Invantive Cloud database with AFAS data container and use Excel with Power Query to retrieve the data.
  • Use the I_SQL_SELECT_TABLE formula (press ctrl+shift+a to see the arguments or use the function wizard). The I_SQL_SELECT_TABLE function is also used by the Data Analysis in the Building Blocks menu for use with Exact Online. Note that Excel has limitations in terms of length. You may need to circumvent these Excel constraints by introducing a named range, just as Data Analaysis on Exact Online automatically does. On Excel forums you will find more tips when you run into length constraints for Excel formulas. Note that I_SQL_SELECT_TABLE uses Excel arrays/matrixes which may require learning some advanced Excel skills.
  • Use a block with a free format query. These can be defined from the Model Editor, add a block, enter the query and set the location in the tab Presentation. This video although dates introduces the concept.

In general:

  • When you are in a Power Query or Power BI environment: use the first option.
  • For advanced Excel users working with a list of predefined maximum size and real-time needs: use I_SQL_SELECT_TABLE.
  • For high volumes of data such as with risk models for Palladium at Risk or Crystal Balls or very wide variations in size: use blocks.
3

Thanks, I’ll try these for now.

1 like