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_TABLEformula (press ctrl+shift+a to see the arguments or use the function wizard). TheI_SQL_SELECT_TABLEfunction 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 thatI_SQL_SELECT_TABLEuses 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.