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")

(missing or bad snippet)

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.

(missing or bad snippet)

Performance considerations

Generally speaking, GetData functions should only be used when absolutely necessary. If there is another reasonably feasible way to query the data—such as using an Axiom query or a data lookup—the other query option should be used instead of GetData functions.

However, when it is necessary to use GetData functions, keep in mind the following performance considerations:

  • When used by itself, the GetData function is a non-volatile function. Use caution when linking GetData functions to volatile functions—for example, by referencing a cell that contains a volatile function. This configuration 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 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).

  • Avoid embedding another GetData function within the parameters of a GetData function. This configuration triggers multiple server calls and recalculations to resolve the "parent" GetData function, which can impact performance.

  • Avoid embedding multiple GetData functions within another function, such as an IF function. This type of construction results in each GetData function being handled using separate server calls, which can impact performance. Although it seems counter-intuitive, it is more performant to extract the GetData functions into separate cells and then reference those cells within the IF function. Although this means that both GetData functions are always processed, they will now both be processed within the same server call.

Common examples

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

This example returns the sum of all data for benefit accounts in column M1 of the GL2021 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("GL2021.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 GL2021.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'","GL2021")

=GetData("M1",,"GL2021")

This example returns the sum of all data in M1. This example could also be written as: =GetData("GL2021.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'","GL2021",,,,,"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, GL2021.

See also