AX1470
Creating DataLookup data sources
To create a data lookup query, use a DataLookup data source. Each row in the data source defines the parameters for a value that you want to query from the database.
The DataLookup data source supports several different row types that can be used to query different types of data. For example, you can use a [GetData] row to query data from any client-defined table, or a [GetFileGroupProperty] row to return information about a file group. You can mix row types in the same data source as needed.
Each sheet in the file can have multiple DataLookup data sources. The parameters on a row in one data source can depend on values returned by another data source—much like the parameters of one function can depend on values returned by other functions. However, when using dependent data sources, you must make sure that the data sources are executed in the appropriate order.
To create a DataLookup data source:
-
Right-click the cell in which you want to start the data source, then select Axiom Wizards > Insert Data Lookup Data Source > Insert TagName.
The tag name specifies the type of row tag that you want to add to the data source. For example, if you want to use a
[GetData]row, you would select Insert Get Data Tag. You can use multiple different tags in each data source, but you must choose one to start with.The current cell and any necessary cells below and to the right must be blank in order to insert the data source.
The wizard adds the primary tag, all required column tags for the selected row tag, and one row tag. The row containing the DataLookup tag becomes the control row, and the column containing the DataLookup tag becomes the control column. The following screenshot shows an example data source after the initial insertion, with no parameters defined yet:
From here, you can complete the data source as follows:
-
You can optionally name the data source and specify an order. This is done by adding parameters to the primary
[DataLookup]tag. To do this, you must manually edit the tag using the appropriate syntax. For more information, see DataLookup tag syntax. -
You can manually complete the parameters for the row tag by filling out the cells as appropriate, or you can use the Data Source Assistant to complete the parameters. The parameters vary depending on the type of row you are defining. For more information on the row types and column parameters, see Defining data lookup rows and columns.
-
You can add more rows to the data source manually, or you can use the Data Source Assistant to add rows. For more information, see Using the Data Source Assistant to add or edit data lookups.
NOTE: Once you have set up the data source, remember to configure the data lookup execution options as needed so that the query is run when you expect. If the query is unnamed, you may not need to do anything further, but if the query is named then you must configure a way to trigger the execution. For more information, see Executing data lookups.
The primary DataLookup tag uses the following syntax:
[DataLookup;DataSourceName;Order]
| Parameter | Description |
|---|---|
|
DataSourceName |
Optional. The name of the data source. For example: [DataLookup;FileGroup] The name of this data source is FileGroup. You can reference this name when using features that list data lookups to execute. A data source should only be left unnamed if you want it to be executed every time the file is refreshed. If instead you want to control the execution to only certain times, then you should give the data source a name. For more information, see Executing data lookups. Multiple data sources can have the same name. If you list that name to be executed, all eligible data sources with the same name will be run. The reserved name |
|
Order |
Optional. Defines the processing order for the data source, among other data sources with the same name. Unnamed data sources are considered as having the same name for this purpose. Specify a whole number starting from 1. For example: [DataLookup;FileGroup;2] If the file contains multiple data sources with the name FileGroup, this data source will be executed second. If no order number is specified, then the data source is executed before any ordered data sources with the same name. Multiple data sources can be assigned the same order number. In this case, the rows of the data sources will be combined and executed in the same batch. When specific data source names are listed to execute, the overall order is determined by the order they are listed. For example, if DataLookups to execute on open is set to For more information, see Dependent data lookups. |
NOTES:
-
The primary DataLookup tag must be located in the first 500 rows of the sheet.
-
The DataLookup tag can be placed within a formula, as long as the starting bracket and identifying tag are present as a whole within the formula. For more information, see Using formulas with Axiom feature tags.
Defining data lookup rows and columns
DataLookup data sources support the following row tags to query data. These tags must be placed in the control column, below the [DataLookup] tag.
| Tag | Description |
|---|---|
|
Returns data from a table, given a column name, a criteria statement, and a table name. |
|
|
Returns information about an installed product feature. |
|
| Returns a specified property for a file group. | |
| Returns the value of a specified variable for a file group. | |
|
Returns whether a picklist variable is enabled for a specified value in the picklist enablement column. |
|
|
Returns information about a file group variable, especially picklist variable properties. |
|
| Returns values from the plan code table, for the current plan code (of a plan file). | |
| Returns a specified security permission for a user. | |
| Returns information about a specified table. | |
| Returns a specified property for a user. |
These row tags correspond to existing Axiom functions and return the same type of data. For example, if you would have used a GetData function to return the data, then you should use a [GetData] row tag within the DataLookup data source.
Each row tag has a set of query parameters that should be placed as column tags in the control row. For example, [GetData] rows use a parameter of [ColumnName], to specify the database column from which to return data. For more information on the valid parameter columns for each row type, as well as data source examples, see the links in the previous table.
The parameter columns work as follows:
-
All column tags must be placed to the right of the primary tag. Columns can be in any order within the control row.
-
To omit a parameter for a particular query row, simply leave the cell blank. If a parameter is optional and you are not using it in any of the query rows, you can omit that column tag from the control row entirely.
-
DataLookup data sources can contain different types of rows. The control row should contain the parameters necessary for all rows in the data source. Each row will only use the parameter columns that are relevant to that row; all other columns in the data source will be ignored for that row. If two separate row types use the same parameter name, then the same parameter column can be used for both types. For example, if the data source contains a
[GetFileGroupProperty]row and a[GetFileGroupVariable]row, both rows can use the same[FileGroup]column.
When the data lookup is executed, the result of each query row is always placed in the [Result] column for the data source. This is the one parameter column that is always required and is used by all query rows. Other areas of the file can reference the result cell to use the value returned for the query row.
Row and column tags do not need to be contiguous. Blank cells and cells with non-tagged text will be ignored in the control row and control column. Axiom Software will continue reading the control row and control column for tags until it encounters a tag that is not valid for use in the DataLookup data source, or until the end of the spreadsheet used range.
For information on using the Data Source Assistant to create or edit DataLookup data sources, see Using the Data Source Assistant to add or edit data lookups.
Example conversion of an Axiom function to a DataLookup data source
Imagine you have a GetData function as follows:
To return the same value using a DataLookup data source, you would create a data source with a [GetData] row, and complete the columns that correspond to the function parameters.
When the data lookup is run, the result is placed in the [Result] column. If the data lookup returns an error, then the error text is placed in the [Result] column, and the [IsError] column returns True.
