AX1468

ComboBox refresh variable

ComboBox refresh variables prompt users to select a value from a searchable drop-down list (the combo box). There are several different options to define the list of values for the combo box:

Using a ComboBox data source defined within the file. ComboBox data sources support defining separate labels and values. This means that what displays to end users in the combo box can be different than what is placed in the [SelectedValue] column for use in impacting the data query. For example, end users might select "friendly" country names (the labels) but the corresponding country code (the values) is placed in the [SelectedValue] column.
Using a table column. You can specify a table column to display the values from that column in the drop-down list.
Using a picklist table. You can specify a picklist table to display the values from that picklist in the drop-down list. This option provides special support to automatically display the picklist value to users, but write back the corresponding code as the selected value.
Using an Axiom Query. You can define an Axiom query in the file, and designate this query as the source for the combo box. The values returned by the query display in the drop-down list. The query runs "in the background" when the user interacts with the combo box.

When using a ComboBox with a table column or an Axiom query, you can also use RelatedColumnValue variables. These are special variables that do not display to users. Instead, they are used to return values from related columns so that these values can be referenced in titles and other areas of the file. For example, if you are prompting users to select an account, then you can use a RelatedColumnValue variable to return the description of that account from the Acct.Description column. For more information, see RelatedColumnValue refresh variable.

Variable behavior

The variable displays as a drop-down list with a searchable entry box. The user can scroll the list and select the value directly, or type into the box to find a particular value.

Desktop Client: Example ComboBox refresh variable

Web Client: Example ComboBox refresh variable

The drop-down lists may be limited to displaying some number of values (the specific display limit depends on the source of data and the client environment). For example, in some cases the drop-down list will only display the first 100 values. However, all values can be found by using the search box. The search matches on the primary value, any description columns, and any additional columns included in the display format.

The values in the drop-down list are sorted as follows:

ComboBox data source: Values are presented in the same order as the data source.
Table column: Values are sorted based on the display format if defined, otherwise based on the value column.
Picklist table: Values are sorted based on the string value of the picklist by default. If a display format is defined, values are sorted based on the display format.
Axiom query: Values are sorted according to the Axiom query Data Sort setting.

Variable properties

This section explains how to complete a variable row in the RefreshVariables data source when defining a ComboBox variable. Some data source columns do not apply in this case and are not discussed here. If any inapplicable columns are present in the data source, they should be left blank on rows that define ComboBox variable types. If you are using the Data Source Assistant to complete the variable properties, then only the applicable columns will be shown in the task pane.

For more information on the RefreshVariables data source in general, see Defining refresh variables.

General variable properties

All refresh variables use a common set of general properties such as the variable name, display name, and whether the variable is enabled or required. Any special considerations for ComboBox variables are noted.

Column Tag	Description
[Name]	The name of the variable. This name identifies the variable row in the data source, and is also used as the variable display name to users if a separate display name is not defined in the `[DisplayName]` column. The name should not contain any non-alphanumeric characters such as question marks or periods. If you want the variable name that displays to users to include non-alphanumeric characters, use the display name. The name cannot be dynamic; it must remain static because it is used to identify the variable. If you are configuring a dependent variable and you need the name to change based on the selection of the parent variable, then you must make the display name dynamic instead of the name.
[DisplayName]	Optional. The display name of the variable. If defined, the display name will be used instead of the name when the variable displays to users in the refresh dialog or filter panel. For example, you might want to define the variable name as "Acct" but use a display name of "Account" or "Select an account".
[VariableType]	Specifies the variable type. Enter `ComboBox` to allow the user to select a value from a searchable drop-down list.
[IsEnabled]	Specifies whether the variable is enabled (True/False). If `True`, then the variable will be included in the refresh dialog or filter panel. If blank or `False`, then the variable will not be included in the refresh dialog or filter panel. This evaluation is determined when the dialog or panel is opened. If the variable is flagged as dependent using the `[DependsOn]` column, then the evaluation will be performed again after a value has been selected for the parent variable.
[SelectedValue]	The user's selected value for the variable will be placed in this cell. When setting up the file to use the variable value, point your formulas to this cell. If you want to define a default value for the variable, use the `[DefaultValueonOpen]` field rather than entering the default value here.
[IsRequired]	Optional. Specifies whether the user must enter a value for this variable (True/False). If `True`, then the user must specify a value for this variable in order to perform the refresh. If blank or `False`, then the user can leave the variable blank. The file should be configured so that it refreshes appropriately if the variable is left blank. The display of required and optional variables depends on the environment. In the Desktop Client, the text (optional) follows the name of optional variables. In the Web Client, required variables that do not yet have a selected value are indicated with a red bar along the side of the variable field.
[DependsOn]	Optional. Specifies that the variable is dependent on another variable. To make a variable dependent on another variable, enter the name of the "parent" variable. A variable should be flagged as dependent if the parent variable must be completed before the user can enter a value for the dependent variable. Dependent variables can be updated dynamically in response to the selected value for the parent variable. For more information, see Using dependent refresh variables.
[DefaultValueonOpen]	Optional. Specifies a default value to be copied to the `[SelectedValue]` field on open. When the user opens the document and uses the refresh variables, the value specified here will be the initial selected value. This allows you to define a default value without needing to place the value directly in the `[SelectedValue]` field, where it could get overwritten by user selection and inadvertently saved. NOTE: If you need to use a formula to determine the default value, the formula may not get calculated before the value is copied to the `[SelectedValue]` field. In this case you must still define the value in the `[SelectedValue]` field directly. The default value is copied regardless of whether the variable is currently enabled or not. This allows you to set a default value for dependent variables that may not be initially visible, but will be visible based on the parent variable selection. The default value must be valid in the context of the variable type and configuration. If the default value is not a value that could be entered or selected for the variable, validation errors or refresh errors may occur.
[ClearSelectedValueonSave]	Optional. Specifies whether the selected value is cleared when the file is saved (True/False). If `True`, then the selected value is cleared when the file is saved. You should set this to True if you want to ensure that the variable always starts off with no value after saving. If blank or `False`, then the selected value is left as is when the file is saved. You should set this to False if you want to be able to set a "default value" for the variable, or if you want the last-selected value to be retained after saving the file.
[ClearSelectedValueonOpen]	Optional. Specifies whether the selected value is cleared when the file is opened (True/False). If `True`, then the selected value is cleared when the file is opened. You should set this to True if you want the variable to start with no value when the file is opened. If blank or `False`, then the selected value is not cleared when the file is opened. You should set this to False if you want the last-saved value to be retained when opening the file. If you enable this option and also define a default value using `[DefaultValueonOpen]`, then the current selected value is first cleared, then the default value is copied to the `[SelectedValue]` field.
[GroupName]	Optional. Defines a group name under which the variable will display. This option only applies to refresh variables used with Axiom forms; it will be ignored when using refresh variables with spreadsheet files in the Desktop Client. If a group name is specified, then the variable will be displayed within an expandable / collapsible grouping in the Web Client filter panel (or when the form is viewed as a web tab within the Desktop Client). For more information, see Defining refresh variables for Axiom forms.
[CollapseOnOpen]	Optional. Specifies whether the group that this variable belongs to should start out collapsed when the form is opened (True/False). By default, groups start out as expanded. This option only applies to refresh variables used with Axiom forms, and only if the variable has a defined group name. The option is ignored when using refresh variables with spreadsheet files in the Desktop Client. If one variable in the group is set to True, then the group is collapsed, regardless of whether other variables in the group may be set to False (or blank).

The remaining columns depend on whether you are using a ComboBox data source, a table column, a picklist table, or an Axiom query.

NOTE: The [AllowMultiSelect] property cannot be used with ComboBox variables. Only Grid variables support selection of multiple values.

Variable-specific properties (ComboBox data source)

The following additional properties apply to ComboBox variable types, when using a ComboBox data source:

Column Tag Description

Column Tag	Description
[DataSourceName]	The name of the ComboBox data source to provide the list of values for the variable. You must define a ComboBox data source within the file in order to use this option. The name of the data source is defined within the ComboBox tag. For example, if the tag is `[ComboBox;MyName]`, then you would enter `MyName` as the data source name. The ComboBox data source has two property columns: `[Label]` and `[Value]`. The labels display to users in the refresh dialog or filter panel. When a user selects a label, the corresponding value is placed in the `[SelectedValue]` column. For more information, see Creating a ComboBox data source for the variable.
[PlaceholderText]	Optional. Specifies placeholder text to display within the combo box until a value is selected. If blank, then the default text "Choose a value" is used.
[AutoQuoteString]	Optional. Specifies whether the string value is placed in single quotation marks when it is written to the `[SelectedValue]` column (True/False). If omitted or blank, the default setting is False, which means the string value is not quoted. This option is intended to make it easier to create filters based on the selected value, when the selected value is a string and therefore must be wrapped in single quotation marks. For example: `Dept.VP='Smith'`. NOTE: The values in the ComboBox data source are always considered to be strings and will be quoted if this option is enabled, even if the values are actually numbers.

[DataSourceName]

The name of the ComboBox data source to provide the list of values for the variable. You must define a ComboBox data source within the file in order to use this option.

The name of the data source is defined within the ComboBox tag. For example, if the tag is [ComboBox;MyName], then you would enter MyName as the data source name.

The ComboBox data source has two property columns: [Label] and [Value]. The labels display to users in the refresh dialog or filter panel. When a user selects a label, the corresponding value is placed in the [SelectedValue] column.

For more information, see Creating a ComboBox data source for the variable.

[PlaceholderText]

Optional. Specifies placeholder text to display within the combo box until a value is selected. If blank, then the default text "Choose a value" is used.

[AutoQuoteString]

Optional. Specifies whether the string value is placed in single quotation marks when it is written to the [SelectedValue] column (True/False). If omitted or blank, the default setting is False, which means the string value is not quoted.

This option is intended to make it easier to create filters based on the selected value, when the selected value is a string and therefore must be wrapped in single quotation marks. For example: Dept.VP='Smith'.

NOTE: The values in the ComboBox data source are always considered to be strings and will be quoted if this option is enabled, even if the values are actually numbers.

The following properties do not apply to ComboBox variables when using a ComboBox data source: ListChoices, ColumnName, AdditionalColumns, ColumnFilter, AllowMultiSelect, DisplayFormat, Hierarchies, UseAsQuickFilter, MinDate, MaxDate, TooltipColumn, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Variable-specific properties (table column)

The following additional properties apply to ComboBox variable types, when using a table column:

Column Tag	Description
[ColumnName]	The column to provide the list of values for the variable. Enter a fully-qualified Table.Column name such as `Acct.Acct`. Multi-level lookups can be used. You can specify any column from any client-defined table in your system. System tables such as Axiom.Aliases are not supported for use with refresh variables and cannot be used. When using columns with lookups (including multi-level lookups), the final lookup table is considered the primary table. For example, if you specify `GL2019.Dept`, this is the same as specifying `GL2019.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 (GL2019 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, `GL2019.Dept` returns only the departments used in the GL2019 table, whereas `Dept.Dept` returns the full list of departments defined in the Dept table. If the value column is a key column or a validated column, then the corresponding descriptions automatically display with the column values in the drop-down list, unless a display format is defined.
[ColumnFilter]	Optional. Specifies a filter criteria statement to limit the list of values displayed to the user. You can type in the filter statement manually, or right-click the cell and use Axiom Wizards > Filter Wizard. 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.
[PlaceholderText]	Optional. Specifies placeholder text to display within the combo box until a value is selected. If blank, then the default text "Choose a value for ColumnName" is used.
[DisplayFormat]	Optional. Defines a display format for the items in the list, and specifies additional columns to display. By default, items in the list are displayed as: KeyColumn - DescriptionColumn If you want to specify a different format and/or use additional columns, then you can indicate the display format here. Use fully qualified Table.Column syntax and place column references in curly brackets. For example, you could indicate something like: {Acct.Acct} - {Acct.Description} ({Acct.Category}) This would display account items in the following format: 8000 - Facilities (Overhead) 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. If a display format is defined, the items in the list are sorted based on the display format instead of the value column. NOTE: This is the only way to display additional columns in the list. The `[AdditionalColumns]` property does not apply to ComboBox variables. Additional columns included in the display format are searchable within the list.
[TooltipColumn]	Optional. Specifies a column that defines tooltip text for each value shown in the list. When a user hovers over a value in the list, the corresponding text from this column is shown in a tooltip. If the value column uses a lookup, then the tooltip column must be resolvable from the primary table, or must use a fully qualified path from the starting table.
[AutoQuoteString]	Optional. Specifies whether the string value is placed in single quotation marks when it is written to the `[SelectedValue]` column (True/False). If omitted or blank, the default setting is False, which means the string value is not quoted. This option is intended to make it easier to create filters based on the selected value, when the selected value is a string and therefore must be wrapped in single quotation marks. For example: `Dept.VP='Smith'`. NOTE: This option only applies if the value column is a string column.

The following properties do not apply to ComboBox variables when using a table column: ListChoices, AllowMultiSelect, DataSourceName, DisplayFormat, Hierarchies, UseAsQuickFilter, MinDate, MaxDate, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Variable-specific properties (Picklist table)

Although you can specify a picklist table column using the [ColumnName] field, the [DataSourceName] field provides special support for picklist tables. You can specify just the picklist table name, and the combo box will automatically display the picklist value to users for selection, while using the corresponding code as the selected value.

Column Tag	Description
[DataSourceName]	Specifies the picklist table to define the list of values. Use the following syntax: Picklist: TableName For example, Picklist: Category uses the picklist table named Category. When a picklist table is the data source, this is effectively the same as designating the Code column of the picklist table as the source column. However, instead of displaying the integer codes to users, by default the drop-down list displays the string values to users. Users select a string value from the list, but the selected value is the corresponding code.
[ColumnFilter]	Optional. Specifies a filter criteria statement to limit the list of values displayed to the user. You can type in the filter statement manually, or right-click the cell and use Axiom Wizards > Filter Wizard.
[PlaceholderText]	Optional. Specifies placeholder text to display within the combo box until a value is selected. If blank, then the default text "Choose a value for ColumnName" is used.
[DisplayFormat]	Optional. Defines a display format for the items in the list. By default, items in the list are displayed using just the value in the Value column of the designated picklist table. The code and description are not displayed. If you want to specify a different format and/or use additional columns, then you can indicate the display format here. Use fully qualified Table.Column syntax and place column references in curly brackets. For example, you could indicate something like: {Category.Value} - {Category.Description} This would display items in the following format: New - Use this category for new requests The display format can use any column from the picklist table. If a display format is defined, the items in the list are sorted based on the display format instead of the value column.
[TooltipColumn]	Optional. Specifies a column in the picklist table that defines tooltip text for each value shown in the list. When a user hovers over a value in the list, the corresponding text from this column is shown in a tooltip. For example, you could specify Description to display the contents of the Description column as tooltips.

The following properties do not apply to ComboBox variables when using a picklist table: ListChoices, AdditionalColumns, AllowMultiSelect, Hierarchies, UseAsQuickFilter, MinDate, MaxDate, AutoQuoteString, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Variable-specific properties (Axiom query)

The following additional properties apply to ComboBox variable types, when using an Axiom query:

Column Tag	Description
[DataSourceName]	The name of the Axiom query to provide the list of values for the variable. The sheet where the query is defined must also be specified, for example: `Sheet2!AQList` For more information on how to set up this query for use with a refresh variable, see Setting up an Axiom query for the variable.
[ColumnFilter]	Optional. A filter criteria statement to limit the list of values displayed to the user. You can type in the filter statement manually, or right-click the cell and use Axiom Wizards > Filter Wizard. This property can be used in addition to (or instead of) the Data Filter on the Axiom query itself.
[PlaceholderText]	Optional. Specifies placeholder text to display within the combo box until a value is selected. If blank, then the default text "Choose a value for ColumnName" is used (where ColumnName is the first column in the Axiom query's field definition).
[DisplayFormat]	Optional. Defines a display format for the items in the list, and specifies additional columns to display. By default, items in the list are displayed as: KeyColumn - DescriptionColumn If you want to specify a different format and/or use additional columns, then you can indicate the display format here. Use fully qualified Table.Column syntax and place column references in curly brackets. For example, you could indicate something like: {Acct.Acct} - {Acct.Description} ({Acct.Category}) This would display account items in the following format: 8000 - Facilities (Overhead) Any additional columns included here must also be in the field definition of the Axiom query. NOTE: This is the only way to display additional columns in the combo box. The `[AdditionalColumns]` property does not apply to ComboBox variables. Additional columns included in the display format are searchable within the list.
[TooltipColumn]	Optional. Specifies a column that defines tooltip text for each value shown in the list. When a user hovers over a value in the list, the corresponding text from this column is shown in a tooltip. The specified column must also be present in the Axiom query field definition.
[AutoQuoteString]	Optional. Specifies whether the string value is placed in single quotation marks when it is written to the `[SelectedValue]` column (True/False). If omitted or blank, the default setting is False, which means the string value is not quoted. This option is intended to make it easier to create filters based on the selected value, when the selected value is a string and therefore must be wrapped in single quotation marks. For example: `Dept.VP='Smith'`. NOTE: This option only applies if the value column of the Axiom query (the first column in the field definition) is a string column.

The following properties do not apply to ComboBox variables when using an Axiom query: ListChoices, ColumnName, AdditionalColumns, AllowMultiSelect, Hierarchies, UseAsQuickFilter, MinDate, MaxDate, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.

Example data source

The following screenshot shows examples of ComboBox variables, one of each type.

Creating a ComboBox data source for the variable

If you are defining refresh variables for use in a form-enabled file, then you can use the data source wizard to add the data source. Right-click in the cell where you want to start the data source, then select Create Axiom Form Data Source > Combo Box.

If you are defining refresh variables for use in a spreadsheet Axiom file, then you must manually create the data source.

The tags for the Combo Box data source are as follows:

Primary tag

[ComboBox;DataSourceName]

The DataSourceName identifies this data source so that it can be assigned to a Combo Box component. Data source names must be unique within a file and must start with a letter. Names can only contain letters, numbers, and underscores. Names are validated when the file is saved; an invalid name will prevent the save.

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

[ComboItem]: Each row flagged with this tag defines an item to display in the combo box.

Column tags

[Label]

The display name for each item in the list. Labels should be unique. If multiple rows have the same label, then the first value with that label is used.

[Value]

The corresponding value for each label. This can be the same value as the label, or a different value.

For example, in a list of colors, both the label and the value can be the text Blue. Or, the label text can be Blue while the value is a numeric color code. Separating the label from the value allows you to display "friendly" text to end users but use any value as the selected value.

NOTES:

The primary tag must be placed in the first 500 rows of the sheet.
Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula. For more information, see Using formulas with Axiom feature tags.

The following example shows how a ComboBox data source might look in a file:

In this example, if the user selects the label "Consolidated" from the combo box, the value "All" will be placed in the [SelectedValue] column.

Setting up an Axiom query for the variable

When the user interacts with the combo box to select an item, the specified Axiom query is run in memory only (meaning, no values are populated within the sheet where the query is configured). The results of the query are used to populate the list.

The Axiom query should be set up as follows:

The first column in the field definition is the value column for the variable—meaning the values to be placed in the [SelectedValue] column of the RefreshVariables data source. If the value column is a key column, then the second column in the field definition is the description column for the key values.
The field definition of the query must also contain any additional columns used in the [DisplayFormat] property, as well as any columns used by associated RelatedColumnValue variables. These columns must be placed after the key and description columns. All columns in the field definition must be contiguous (no blank cells in between).
It is recommended to use fully qualified Table.Column names in the field definition for the Axiom query. If you define a display format for the variable or use a dependent RelatedColumnValue variable, both of those features require the columns to be fully qualified, and they must match the field definition entries exactly.
The Axiom query data filter can be used to filter the list of values. If desired, you can also (or alternatively) use the [ColumnFilter] property of the RefreshVariables data source.
Although the query itself must be active, all refresh behavior options for the Axiom query should be set to Off (such as Refresh on file open, Refresh on manual refresh, etc.), unless you also want the query to run at those times for reasons other than the combo box.
No Axiom query settings that impact the display in the sheet will apply to the combo box. This includes spreadsheet sorting (use data sort instead), in-sheet calc method formatting or formulas, and data range filters. The only Axiom query settings read from the sheet are the field definition entries. One way to think of it is that the values for the drop-down list are basically the same values that you see when using the Preview Axiom Query Data button on the Sheet Assistant.
System tables such as Axiom.Columns are not supported for use in refresh variables, and cannot be used in the Axiom query.