AX1455

RelatedColumnValue refresh variable

RelatedColumnValue refresh variables can be used to bring in additional column values based on the user's selection for a parent variable. This is a special variable type that does not display to the user—it is only used to return values that can then be referenced elsewhere in the file.

For example, imagine that you have a Grid variable for the user to select an account from Acct.Acct. The report will be filtered by that selected account, so you want to display both the account number and the account description in the report title. You can define a RelatedColumnValue variable that returns the associated value from Acct.Description for the selected account. You can then reference both values in the report title.

The RelatedColumnValue variable provides an alternative to using GetData functions to return these associated values. Avoiding use of unnecessary GetData functions can improve file performance.

The following variable types can be parent variables for a RelatedColumnValue variable:

Grid variables
ComboBox variables using a table column
ComboBox variables using an Axiom query

The parent variable must use a key column of a reference table as its value column in order to bring in related column values.

Variable behavior

RelatedColumnValue variables do not display to users on refresh. Instead, they are automatically populated based on the user's selection for a parent variable. Once the user has completed the selections for the other refresh variables and those selections are written to the data source, the [SelectedValue] column for the RelatedColumnValue variable is also populated with the appropriate value from the specified column for the variable.

For example, imagine that you have the following configuration:

In this example, the RelatedColumnValue variable named Description depends on the Grid variable named Account. The Description variable is configured to return the value for Acct.Description based on the selected account for the Account variable.

When the Refresh Variables dialog is presented to the user (or the Web Client filter panel), only the Account variable displays:

Once the user selects an account and clicks OK, the selected account is placed in the [SelectedValue] column for the Account variable, and the associated description from Acct.Description is placed in the [SelectedValue] column for the Description variable. In the following example, the user selected account 5000, and the description of that account is "Travel."

Variable properties

This section explains how to complete a variable row in the RefreshVariables data source when defining a RelatedColumnValue 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 RelatedColumnValue 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 RelatedColumnValue variables are noted.

Column Tag	Description
[Name]	The name of the variable. Although the variable name is still required for RelatedColumnValue variables, it is for descriptive purposes only, to indicate the purpose of the variable to other file designers. RelatedColumnValue variables do not display in the Refresh Variables dialog, so therefore end users will not see the name unless they view the data source. The separate display name does not apply to RelatedColumnValue variables.
[VariableType]	Specifies the variable type. Enter `RelatedColumnValue` to return a related column value for the selected item of a parent variable.
[IsEnabled]	Specifies whether the variable is enabled (True/False). If `True`, then the variable will be included in the refresh process, meaning a value will be returned for the variable. If blank or `False`, then the variable will not be included in the refresh process, meaning no value will be returned for the variable.
[SelectedValue]	The related column value will be placed in this cell. When setting up the file to use this value, point your formulas to this cell. The related column value is not selected by the user; instead it is determined based on the selected value for this variable's parent variable. For example, if the parent variable points to `Acct.Acct` and the user selects account 1600, then this variable uses that account value to look up the appropriate related column value. If you want to define a default value for the variable, use the `[DefaultValueonOpen]` field rather than entering the default value here. NOTE: If multi-select is enabled for a parent Grid variable, and a user selects multiple values for that variable, then multiple related column values will be returned as well. These values will be presented in a comma-separated list, in the same order as their parent values.
[DependsOn]	Specifies the parent variable that this variable depends on. Enter the name of an eligible Grid or ComboBox variable. This property is required for RelatedColumnValue variables. The parent variable determines the column value to use when looking up the related column value. The parent variable must be enabled. RelatedColumnValue variables are only valid if the parent variable uses the key column of a reference table as its value column. If the parent variable points to a non-key column, then it is not possible to look up a unique value for a related column.
[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 related column value is cleared out when the file is saved (True/False). If `True`, then the value is cleared when the file is saved. If blank or `False`, then the value is left as is when the file is saved. NOTE: The RelatedColumnValue variable should use the same setting as its parent variable, so that the parent value and the related column value are both cleared or retained as a set.
[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.

NOTE: The [DisplayName], [GroupName], and [CollapseOnOpen] properties do not apply to RelatedColumnValue variables because these variables do not display to users.

Variable-specific properties

The following additional properties apply to RelatedColumnValue variable types:

Column Tag Description

Column Tag	Description
[ColumnName]	Specifies the related column from which to look up a value, based on the selected value for the parent variable. Enter a fully-qualified Table.Column name such as `Acct.Description`. If the parent variable uses the key column of a reference table, then the related column can be any column from the same table as the parent variable, or from a table that the parent variable has a lookup relationship with. If the parent column is a key column such as `Dept.Dept`, then the related column can be any other column in that table such as `Dept.Description`, `Dept.Region`. It can also be `Dept.Region.RegionType`, assuming that `Dept.Region` looks up to `Region.Region`. If the parent column is a grouping column such as `Dept.Region`, then related columns are not supported. It is not possible to look up to `Dept.Region.RegionType` in this case. You would have to set the parent variable to `Region.Region` or to `Dept.Region.Region` in order to look up a value from the RegionType column in the Region table. If the parent variable is a ComboBox variable that uses an Axiom query, then the related column must be present in the field definition of that query.
[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 related column is a string column.

[ColumnName]

Specifies the related column from which to look up a value, based on the selected value for the parent variable. Enter a fully-qualified Table.Column name such as Acct.Description.

If the parent variable uses the key column of a reference table, then the related column can be any column from the same table as the parent variable, or from a table that the parent variable has a lookup relationship with.

If the parent column is a key column such as Dept.Dept, then the related column can be any other column in that table such as Dept.Description, Dept.Region. It can also be Dept.Region.RegionType, assuming that Dept.Region looks up to Region.Region.
If the parent column is a grouping column such as Dept.Region, then related columns are not supported. It is not possible to look up to Dept.Region.RegionType in this case. You would have to set the parent variable to Region.Region or to Dept.Region.Region in order to look up a value from the RegionType column in the Region table.

If the parent variable is a ComboBox variable that uses an Axiom query, then the related column must be present in the field definition of that query.

[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 related column is a string column.

The following properties do not apply to RelatedColumnValue variables: PlaceHolderText, ListChoices, AdditionalColumns, ColumnFilter, AllowMultiSelect, DataSourceName, DisplayFormat, Hierarchies, UseAsQuickFilter, MinDate, MaxDate, TooltipColumn, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.