AX2356

GetData function

The GetData function queries the database and returns data from a table, given a column name, a filter criteria statement, and a table name. The GetData function can be used to return any type of data.

IMPORTANT: If it is possible to use an Axiom query or a data lookup instead of GetData functions to return a set of data, it is always recommended to do so for faster performance. Use of GetData functions in a file can significantly impact the performance of that file, especially when large numbers of functions are used.

Syntax

GetData("ColumnName", "FilterCriteria", "TableName", "NoDataDefaultMessage", IgnoreSheetFilter, "InvalidQueryMessage", "AlternateAggregation", "ConversionTarget")

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.

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.

All non-numeric entries must be placed in double quotation marks, unless you are using cell references to reference the text held in another cell. However, the Boolean values TRUE and FALSE do not need to be in quotation marks.

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.

  • The GetData Function Wizard is available in Axiom files to assist you in building GetData functions. Right-click in any cell and then select Axiom Wizards > GetData Function Wizard.

  • The GetData function is a non-volatile function. Use caution when linking GetData functions to volatile functions (for example, referencing a cell that contains a volatile function) as this will make the GetData function behave like a volatile function, which may impact performance and cause unexpected calculation behavior. In particular, functions that return a different result on every calculation (such as NOW) should not be used within GetData functions.

  • 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.

Common examples

=GetData("M1","ACCT.AcctGroup='Benefits'","GL2019")

This example returns the sum of all data for benefit accounts in column M1 of the GL2019 table. You could return the same result by using a fully qualified column name in the first parameter and omitting the table name in the third parameter: =GetData("GL2019.M1","ACCT.AcctGroup='Benefits'")

=GetData("CYA1","ACCT.AcctGroup='Benefits'")

This example returns the same data as the first example, assuming that CYA1 is an alias name for GL2019.M1. Since CYA1 is an alias name, the table name can be omitted. However, you could also write this function as =GetData("CYA1","ACCT.AcctGroup='Benefits'","Alias") or =GetData("CYA1","ACCT.AcctGroup='Benefits'","GL2019")

=GetData("M1",,"GL2019")

This example returns the sum of all data in M1. This example could also be written as: =GetData("GL2019.M1") or =GetData("CYA1")

=GetData("Description","ACCT=1000","ACCT")

This example queries a reference table to return a single value—in this case, the description for Acct 1000 (for example, "Cash"). This function could also be written as: =GetData("ACCT.Description","ACCT=1000")

Optional parameter examples

=GetData("CYA1","ACCT.AcctGroup='Benefits'",,"Query returned no data",,"Invalid query")

This example defines custom messages to display to the user if the query returns no data or if the query is invalid. Note that the unused third and fifth parameters are delimited with commas.

=GetData("CYA1","ACCT.AcctGroup='Benefits'",,"Query returned no data",True,"Invalid query")

This example ignores any sheet filter defined on the Control Sheet. For example, if the sheet filter were DEPT=2000, then this function would ignore the filter and return the data for all departments.

=GetData("DEPT",,"DEPT",,,,"DistinctCount")

This example uses alternate aggregation to return the count of unique departments in the DEPT table.

=GetData("M1","ACCT.AcctGroup='Benefits'","GL2019",,,,,"GBP")

This example uses the conversion target to return converted data. Instead of returning data "as is" from the M1 column, the data will be converted per the conversion configuration set up for this table. In this example, the data is being converted from its "base" currency (say, U.S. dollars) to British pounds.

System table example

=GetData("TableName","Alias='CYA1'","Axiom.Aliases")

This example queries the system table Axiom.Aliases. It will return the name of the table that the CYA1 alias is currently assigned to—for example, GL2019.

See also