AX1185

RelatedColumnValue calc method variable

RelatedColumnValue calc method 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 and then place them in designated calc method locations.

For example, imagine that you have a Grid variable for the user to select an account from Acct.Acct. You want to display both the account number and the account description in the inserted calc method. You can define a RelatedColumnValue variable that returns the associated value from Acct.Description for the selected account. Both values will then be placed in the calc method.

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. 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 calc method variables and the calc method is inserted, the relative location for the RelatedColumnValue variable is also populated with the appropriate value from the specified column for the variable.

For example, imagine that you are prompting users to select an account to place in the calc method, and you also want to place the account description in the calc method. To do this, you first set up the parent variable to display a list of accounts. Then, you create a RelatedColumnValue variable to return the account description, and configure it as dependent on the parent Account variable.

When the user inserts this calc method and the Calc Method Variable dialog displays, only the Account variable is visible.

When the calc method is inserted, Axiom finds the specified column for the RelatedColumnValue variable—in this example, Acct.Description—and returns the value for the selected account. Both values are then placed into the specified relative locations within the calc method.

Variable properties

This section explains how to complete a variable row in the CalcMethodVariables 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. For more information on the CalcMethodVariables data source in general, see Defining calc method variables using a CalcMethodVariables data source.

General variable properties

All calc method 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. 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]	This property does not apply to RelatedColumnValue variables, because these variables do not display to the user.
[VariableType]	Specifies the variable type. Enter `RelatedColumnValue` to return a related column value for the selected item of a parent variable.
[RelativeLocation]	The location to place the variable value when the calc method is inserted, relative within the calc method. The location is specified as follows: `<ColumnLetter><CalcMethodRow>`. For example: If the value should be placed in column B of the calc method, within the first row of the calc method, enter the following: `B1`. If the calc method is a single-row calc method, the calc method row is always 1. If the value should be placed in column B of the calc method, but within the third row of the calc method, enter the following: `B3`. The entry for the calc method row must be valid within the context of the current calc method. For example, if the current calc method is a three-row calc method, then the only valid entries for the row are 1, 2, and 3. If you attempt to specify B4 for the three-row calc method, the entry is invalid. 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. 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.
[IsEnabled]	Specifies whether the variable is enabled (True/False). If `True`, then the variable value will be placed in the relative location when the calc method is inserted. If blank or `False`, then no value will be queried for the variable. This evaluation is determined when the Calc Method Variables dialog 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]	This property does not apply to RelatedColumnValue variables. The user never selects a value.
[IsRequired]	This property does not apply to RelatedColumnValue variables. The variable never displays to the user. The queried value is always placed in the calc method as long as the variable is enabled.
[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.
[InsertOnly]	This property does not apply to RelatedColumnValue variables, as it effectively inherits the insert only property of the parent variable.

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`. The column for the parent variable must be a key column of a reference table or a validated column. If the parent variable column is 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. For example, if the parent variable column is `Dept.Dept`, then the related column can be any other column in that table such as `Dept.Description` or `Dept.Region`. It can also be `Dept.Region.RegionType`, assuming that `Dept.Region` looks up to `Region.Region`. If the parent variable column is a validated column, then the related column can be any column from the lookup table. For example, if the parent variable column is `Dept.Region`, then the related column could be `Dept.Region.RegionType`. 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 calc method location (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'`. This option is not commonly used in calc method variables. 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.

The column for the parent variable must be a key column of a reference table or a validated column.

If the parent variable column is 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. For example, if the parent variable column is Dept.Dept, then the related column can be any other column in that table such as Dept.Description or Dept.Region. It can also be Dept.Region.RegionType, assuming that Dept.Region looks up to Region.Region.

If the parent variable column is a validated column, then the related column can be any column from the lookup table. For example, if the parent variable column is Dept.Region, then the related column could be Dept.Region.RegionType.

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 calc method location (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'. This option is not commonly used in calc method variables.

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, MinDate, MaxDate, TooltipColumn, PrimaryTable, LimitColumn, MinValue, MaxValue, StepFrequency.