AX2357

GetDataElement function

Launches the Choose Value dialog where the user can select a value (such as an account number), and places the selected value in a designated cell. The function parameters determine which values are available for selection in the dialog.

To launch the wizard, the user double-clicks on the cell containing the GetDataElement function.

NOTE: GetDataElement is not for use in Axiom forms. If you want users to be able to select values from within an Axiom form, you can use the Combo Box component or a Formatted Grid component (Select tag).

Syntax

GetDataElement("DisplayText", "TableColumn", "TargetCell", "Filter", "AdditionalColumns", "UseDataSource", "HideKey")

Parameter	Description
DisplayText	The display text for the cell containing the GetDataElement function. For example, the text could be something like "Select an account" or "Double-click to select an account." The DisplayText displays as normal text in the cell. If you want the text to appear like a hyperlink on a web page, you must manually apply the font formatting to the cell.
TableColumn	The table column from which the user will select a value. You must use fully qualified Table.Column syntax, or use an alias name. When using columns with lookups (including multi-level lookups), the final lookup table is considered the primary table. For example, if you specify GL2021.Dept, this is the same as specifying GL2021.Dept.Dept, so the Dept table is the primary table. Any columns listed in filters and as additional columns must be resolvable from the primary table, or must contain a fully qualified path from the starting table (GL2021 in this example). When using columns with lookups, the starting table impacts the list of items to be returned from the value column. For example, GL2021.Dept returns only the departments used in the GL2021 table, whereas Dept.Dept returns the full list of departments defined in the Dept table. Alternatively, you can specify the name of a DataElements data source defined within the file. To do this, you must also set the value of the UseDataSource parameter to TRUE.
TargetCell	Optional. The target cell in which to place the selected value. If no target cell is specified, then the selected value is placed in the cell containing the GetDataElement function. This value replaces the function, meaning that it is no longer possible to launch the Choose Value dialog from this location. NOTE: Remember to place the target cell location in quotation marks, such as "C25". If you omit the quotation marks and use just C25, then the entry is interpreted as a cell reference within the function—meaning that Axiom will attempt to read the target cell location from the cell reference. If C25 is empty, the function will behave as if no target cell was specified. If C25 contains content other than a cell location, the function will return an error when it is double-clicked.
Filter	Optional. A filter criteria statement to limit the list of values displayed in the Choose Value dialog. Use standard filter criteria syntax. If the value column uses a lookup, then the column in the filter criteria statement must be resolvable from the primary table, or must use a fully qualified path from the starting table. This parameter does not apply if you are using a DataElements data source.
AdditionalColumns	Optional. One or more columns in the primary table, or a related table, to display along with the value column for information purposes. Separate multiple column names with commas. Any columns listed should use fully qualified Table.Column syntax. If the value column uses a lookup, then any additional columns must be resolvable from the primary table, or must use a fully qualified path from the starting table. Column-only syntax is only allowed for columns directly on the primary table. In the Choose Value dialog, additional columns display with the column name only (the table is not listed). NOTES:   If the value column is a key column or a validated column, then the corresponding descriptions automatically display with the column values. It is not necessary to separately specify the description column here. This parameter does not apply if you are using a DataElements data source.
UseDataSource	Specifies whether the function should treat the second parameter of the function as a table column name or as a DataElements data source. If FALSE (default), then the second parameter is treated as a table column name. If the entry does not match a table column name defined within Axiom, the function returns an error. If TRUE, then the second parameter is treated as a DataElements data source name. The fourth and fifth parameters are ignored. For more information on using a DataElements data source, see Using a DataElements data source with GetDataElement.
HideKey	Specifies whether the key column is hidden in the dialog. Only applies when the specified value column is a key column of the table. If FALSE (default), then the key column is shown in the dialog. If TRUE, then the key column is hidden in the dialog. Users make their selection based on other columns in the table, and then the key column value is inserted into the designated cell. This is intended for situations where the user needs to select a value from the key column, but the code that users recognize is held in a different column of the table.

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.

Remarks

• In cases where a target cell is specified, the current value in that cell is automatically selected in the list when the Choose Value dialog is launched.

• The Axiom Double-Click setting does not need to be enabled for the sheet in order to use GetDataElement.

• The Choose Value dialog uses either the full Axiom grid format or a "simple view" format depending on the information shown in the dialog. If only one column is shown (including concatenated descriptions, if applicable), then the simple view is used. If multiple columns are shown, then the full grid view is used.

  The number of records available to the dialog depends on the view being used. Simple view is limited to showing 5,000 records, but the search box can be used to find any record returned by the query. Full grid view is limited to showing 10,000 records by default, for both display and search (this limit can be adjusted using the system configuration setting MaxChooseValueRows).

• GetDataElement is a non-volatile function.

Examples

All of these examples use a table column. For an example of using a DataElements data source, see the following section.

=GetDataElement("Select an account","ACCT.ACCT")

This example opens the Choose Value dialog in "simple view" and displays all values of the ACCT.ACCT column. Once a value is selected, it is placed in the current cell, replacing the GetDataElement function.

=GetDataElement("Select an account","ACCT.ACCT","C25","Acct.Category='Revenue'")

In this example, the optional filter parameter is used to filter the list of ACCT values to only show Revenue accounts. Additionally, the selected value will be placed in cell C25 instead of the current cell. This GetDataElement function can continue to be used to change the account value placed in cell C25.

=GetDataElement("Select a department","DEPT.DEPT",,,"VP,Region,Region.RegionType")

This example opens the Choose Value dialog in full grid view, in order to display the additional columns specified in the fifth parameter. The columns VP and Region are on the target DEPT table, whereas Region.RegionType is used to bring in a column from a related table.

=GetDataElement("Select a region","GL2021.DEPT.Region","C25")

This example uses a multi-level lookup. In this case, the dialog will only show regions based on departments used in the GL2021 table, instead of all regions.

=GetDataElement("Select a request","Request.RequestID","C25",,"Request.RequestCode",False,True)

In this example, the source column is being hidden using the HideKey parameter. The key column is an identity column, so the RequestCode column contains a more meaningful code that is built up for each item in the table. Users will use the RequestCode to select an item from the dialog, and then the appropriate key column value will be written to the specified cell.

Using a DataElements data source with GetDataElement

To define a custom list of values for use with GetDataElement, you must create a DataElements data source within the file. This data source works the same way as data sources for Axiom form components. The data source has a primary tag to identify the control row and control column, and then a series of row and column tags to flag the data.

The tags for the DataElements data source are as follows:

Primary tag

[DataElements;DataSourceName;DialogTitle]

DataSourceName defines the name to be placed in the second parameter of the GetDataElement function. For example, if the data source defines a list of available status values, the name might be Status.

DialogTitle is optional, and defines text to display in the title bar of the GetDataElement selection dialog. If this parameter is omitted, the title of the dialog is "Choose Value."

The placement of this primary tag defines the control column and the control row for the data source.

• All column tags must be placed in this row, to the right of the tag.
• All row tags must be placed in this column, below the tag.

Row tags

[Header]

The Header row contains the text to display as the header of each column in the GetDataElement selection dialog.

[Row]

Each row flagged with this tag defines an item that can be selected in the GetDataElement selection dialog.

Column tags

[Column]

Each column flagged in the data source will display in the GetDataElement selection dialog, using the header title displayed in the Header row. For example, you might have one column for status names and one column for descriptions of the status values.

[Value]

The Value column contains the value that will be placed in the target cell for GetDataElement.

The following is an example DataElements data source:

You can type the data source tags manually, or use the data source wizard. To use the wizard, select any list items that you have already entered into the spreadsheet, then right-click and select Axiom Wizards > Create Data Element Data Source. You can also right-click an empty cell to add the tags first and then enter the list items.

To use a DataElements data source, the GetDataElement function should be set up as follows:

• The second parameter must indicate the data source name instead of a table column name.
• The fourth and fifth parameters do not apply and should be left blank.
• The sixth parameter must be set to True. This tells Axiom to use a data source instead of a table column.

For example, the GetDataElement function might look as follows:

When a user double-clicks the GetDataElement cell, the selection dialog displays as follows:

When a user selects an item in the dialog and clicks OK, the corresponding value from the Value column in the data source is placed in the target cell.

NOTE: When using a data source, the Choose Value dialog is always in full grid view instead of simple view.