AX1472

GetData data lookup

You can use GetData rows in a DataLookup data source to return data from a table, given a column name, a criteria statement, and a table name.

The GetData data lookup supports the same query parameters as the GetData function, and can be used as a substitute for this function to improve file performance. The data lookup is intended to be used in cases where the queried value is not expected to change during the current file session, and therefore the value only needs to be queried once (or only after specific events).

To create a GetData data lookup, add a [GetData] row to a DataLookup data source and add the appropriate parameter columns. For more information on creating the DataLookup data source, see Creating DataLookup data sources.

GetData parameters

[GetData] rows use the following parameter columns. Within the DataLookup control row, these parameter names must be placed in square brackets—for example, [ColumnName]. The parameters can be placed in any order.

NOTE: If the entry for any parameter depends on a value returned by another data lookup row, then that row must be in a different data source and executed before this row is executed. For more information, see Dependent data lookups.

Parameter	Description
ColumnName	The name of the column to be queried. You can use an actual column name, a column alias, or a calculated field name. If the column name is fully qualified (Table.Column), then you can omit the TableName parameter. NOTE: If you are querying a system table—such as Axiom.Aliases—then you must enter only the column name here. Do not use fully qualified syntax with a system table. See the system table example below.
FilterCriteria	When querying data tables, the criteria statement specifies the records to be summed. If no criteria statement is specified, GetData returns the sum of the entire column. The criteria statement can be based on the data table, or on a lookup reference table that the data table links to (including multiple levels of lookup). When querying reference tables, the criteria statement typically specifies an individual record to be returned. The criteria statement can be based on the reference table, or on another reference table that it links to (including multiple levels of lookup). It is recommended to use fully qualified Table.Column syntax in the filter.
TableName	The name of the table to be queried. Note the following: If the column name is an alias, then the table name can be omitted. Alternatively, you can specify "alias" as the table name, or specify the name of the table that the alias points to. If the column name in the ColumnName parameter is fully qualified (Table.Column), then the table name can be omitted.
NoDataDefaultMessage	Optional. A custom message to use as the return value if the query does not return any data. NOTE: Returning no data is not considered an error for purposes of the IsError column. IsError will return False when the no data message is displayed.
IgnoreSheetFilter	Optional. Specifies whether sheet filters are ignored for this query. If FALSE, then sheet filters are applied to this query. False is the default value if this parameter is not specified. If TRUE, then sheet filters are ignored for this query. All sheet filters are ignored, including temporary filters applied by Quick Filter and GetDocumentHyperlink, and multipass filters for file processing.
InvalidQueryMessage	Optional. A custom message to use as the return value if the database query is invalid.
AlternateAggregation	Optional. Specifies an alternate aggregation type for the returned data. In most cases this should be omitted to use the default aggregation for the column—for example, to sum data columns. The available aggregation types are the same as when using alternate aggregations with an Axiom query field definition. For more information, see Specifying an alternate aggregation method for a field definition. NOTE: When querying a system table (such as Axiom.Aliases), only Min and Max are supported. Other alternate aggregations are not supported and will return the same value as when using no alternate aggregation.
ConversionTarget	Optional. Specifies a conversion target so that the query returns converted data. This parameter only applies if the specified column and table have been configured for data conversions. You can specify a value directly (for example, "CAD"), or you can look up a value from a grouping column in a lookup reference table (for example, `DEPT.TargetCur`). In either case, the target must be present in the "to" column of the relevant conversion table. If the table is not configured for data conversions, an error results. If the specified conversion target is invalid, a zero value is returned. Data conversions will also return zero values if the conversion configuration for the table has invalid values, or if the conversion table itself has no valid rate entry for the relevant conversion and period (for example, blank entries in a rate column). For more information, see Data conversions and Querying data using data conversions.
Result	The Result column is where the return value for the row is placed when the data lookup is executed. You can reference this cell to use the return value in other areas of the file. This column must be present in order for the data lookup to be valid.
IsError	Optional. Indicates whether the return value for the data lookup was an error. If TRUE, the return value is an error. This may be an Axiom #ERR code, a specific error message, or a custom error message defined in the data lookup (such as the GetData invalid query message). If FALSE, the query executed successfully. The IsError column can be helpful if you need to set up formulas with error trapping. Instead of using the ISERROR Excel function, you can use a construction such as: =IF(Info!$M$10=True, "", Info!$L$10) Where the IsError column is in M10 and the Result column is in L10. If the data lookup returns an error, this function returns blank instead of displaying the error.

Remarks

A user's table filters (as defined in Security) are always applied to GetData queries. Therefore, the value returned may vary depending on the user's table filters.
If the column queried by GetData is a Boolean data type, then True values are returned as 1, and False values are returned as 0.
If the column queried by GetData is a hyperlink columnA string column in the database that has been flagged as containing hyperlink data (either web URLs or Axiom file path). The column is flagged by defining a Hyperlink Label in the column properties, then the raw value in the column is returned. The value is not auto-converted to a link (like when using Axiom queries). You can use the raw value returned by GetData in a variety of ways—for example, in a Hyperlink function, in a GetDocument function, in a Hyperlink component (forms), or in a HREF tag (forms).
If you are using GetData queries in form-enabled files that will be open in a shared form instance (via use of embedded forms), then you may want to set the IgnoreSheetFilter parameter to True even if the sheet is not using sheet filters. This allows any duplicate GetData queries within the files to leverage the shared GetData cache. If IgnoreSheetFilter is False, then the GetData query is cached on a per sheet basis and the result cannot be shared with other sheets (regardless of whether the sheet actually has a defined filter).
GetData can be used to query data from Axiom system tables, to return system information. For more information on which tables can be queried, see System tables.

Example

The following screenshot of a DataLookup data source shows several GetData examples. Some optional parameter columns are omitted for space.

For more examples of GetData use, see GetData function. The same examples work for both approaches. To use a function example in a DataLookup data source, you would place the applicable function parameters in the corresponding parameter columns.