GETTABLE
In addition to parameters in PARAM, column A, and row 1, Get Tables can be defined in the template. A Get Table query is a SQL query or a data view specified in the template that retrieves any customized information directly from the database and inserts it into the template workbook in separate data sheets.
Get Tables can be used in many different ways in the template workbook; however, the most common reason for using Get Table is to retrieve descriptions connected to object values that are used in the template. For example, in a result report showing accounts, a Get Table would be defined in the workbook to retrieve the account descriptions for each account.
Each Get Table creates a worksheet in the book, filled with the dataset returned by the query. Every time the template is generated, the Get Table query refreshes the dataset so the returned data is always the latest. This data table(s) can thereafter be referred to with the CF_GETDESCR function in order to insert object value descriptions in the generated document. Instead of fetching these columns from the database, it is faster and more advantageous to use the CF_GETDESCR to insert descriptive values.
In the Data Sheets right panel in Template Design, the different Get Table queries can be viewed and managed for a template. In this panel you can:
- Add new queries by selecting the + button in the Data Sheets heading (to the right).
- Edit existing queries by selecting the pen icon.
- Delete get table queries by selecting the trash can icon.
- Refresh the data returned by the query in each data sheet by selecting the refresh icon.
Below is a list of GETTABLE sheet input parameters:
GETTABLE Sheet | Input Parameters | Example |
|---|---|---|
In column B | Sheet Name | Define the name of the data sheet tab that should be created and filled with the data returned by the query or data view. |
In column C | Data source | Define the data source towards which the SQL query should run. Leave empty if it should be towards the Bizview database. |
In column D | SQL query/Data View ID
| Define the actual SQL query or data view ID.
|
In column E | Include headers | Optional input parameter. By default, headers will be included in the data set. Therefore, this input parameter should only be used if headers should be excluded. |
In column F | Synchronized | By default, all gettable queries are run asynchronously, which, from a performance standpoint is the best. If for some reason your gettable query is dependent on the result of another gettable query, then you can define TRUE in the Synchronized (column F) column in the GETTABLE sheet for those gettable queries that are dependent on each other. If this parameter is used, then you also need to make sure that the queries in the GETTABLE sheet is in the order that you want them to be executed. |
Below an example of the GETTABLE sheet with different queries to retrieve data into different data sheets in the workbook:
As an example of the above GETTABLE query for ACCOUNT:
The data set will be inserted in the new worksheet ACCOUNT. The data range will also be named "ACCOUNT". It is that named range that should be used in the CF_GETDESCR formula to retrieve the account descriptions to the template:
- The first column in the SQL query is the object value ACCOUNT and this is the value that need to be found in the column left of the CF_GETDESCR formula in the template.
- The second column in the SQL query is the value that will be returned by the CF_GETDESCR formula.
