AX1629

Using picklist variables for file groups

You can use picklist variables in file groups to:

• Identify and reference the picklist tables associated with the file group. Picklist tables are special reference tables that are used to define lists of values from which users can make selections.
• Define picklist variable properties that can be used to impact how the picklist is used in the file group.

Picklist variables are special kinds of table variables. Each picklist variable must resolve to a picklist table name. Picklist variables behave like table variables when using features such as cloning a file group or creating a file group scenario, meaning that new tables will be created as needed.

Picklist variable values can be returned in files by using the GetFileGroupVariable function as normal. Using this approach, you can dynamically return the names of the current picklist tables for the file group, and use those tables as needed. Additionally, picklist variable properties can be returned in files by using the GetFileGroupVariableProperty function, such as to return whether the picklist variable is flagged as required. You can then configure the file to behave as needed depending on the variable property.

All picklist variable creation and configuration takes place on the Picklist Variables sub-tab in the file group properties. To access this area:

1. On the Axiom tab, in the Administration group, click Manage > File Groups.

2. In the Axiom Explorer dialog, navigate to the file group that you want to edit, then right-click the file group and select Edit.

   TIP: You can also access this dialog by right-clicking a file group in the Explorer task pane.

3. Select the Variables tab, then select the Picklist Variables sub-tab.

From this area, you can create new variables, edit existing variables, and delete variables. Only administrators or users with the Administer File Group permission can access this area.

NOTE: If Restrict saves to tables defined below is enabled on the Table Variables sub-tab, then it is not possible to save data to the target table of a picklist variable. In most cases, this behavior does not cause any issues because it is typically not necessary to save data to picklist tables from within a file group. However, if you do need to do this, then you must define a table variable that resolves to the picklist table, and configure that variable to allow saving data. This table variable can be used in addition to the picklist variable or instead of the picklist variable, depending on whether you need the advanced configuration options of picklist variables.

Creating picklist variables

You can create new picklist variables as needed.

To create a picklist variable:

1. On the Picklist Variables sub-tab, click New.

2. In the Edit Picklist Variable dialog, complete the following fields:

   Item	Description
   Variable Name	The name of the variable. The name should be brief, yet self-explanatory. For example, Category to define the name of the target picklist table that holds the list of categories for users to select.
   Variable Value	The value of the variable. You can type in a value, and/or you can reference another file group variable to define the value. To reference another file group variable within the value, enter the variable name in curly brackets. You can also use the Insert variable tool to insert any user-defined general variable within the file group, or to insert a built-in variable such as {FileGroupYear}. Alternatively, you can use the table chooser to the right of the box to choose a table and set the variable value to that specific table name. For example, if the variable name is Category for the picklist table that holds capital planning categories, then the variable value might be CP_Category or CP{FileGroupYear}_Category (if each year of planning uses different categories). The latter example would resolve to CP2020_Category if the file group year is 2020.

   Once the variable value has been defined, the Resolved Picklist Name shows the picklist table name that the variable resolves to. If the resolved table name does not match an existing table, a warning icon is displayed. This may be expected if the target table has not yet been created.

   

   Example picklist variable

3. If the variable value resolves to an existing picklist table, then you can optionally click show optional picklist configuration to expose the advanced picklist settings. In this section, you can configure the following:

   • Picklist Column Configuration: You can optionally associate this picklist variable with an existing column in the plan code table that has a lookup relationship to the picklist table. If the plan code table does not already have a column with a lookup relationship, you can create a new column. For more information, see Associating a picklist variable with a column in the plan code table.

   • Picklist Enablement: If a Picklist Enablement Column has been defined for the file group, you can specify the values in that column for which this picklist variable should be enabled. For more information, see Dynamically enabling picklists.

   • Picklist Properties: You can specify whether the picklist variable should be flagged as required. For more information, see Required variables.

4. Click OK to close the Edit Picklist Variable dialog and add the new variable to the Picklist Variables grid.
5. Click Apply or OK in the Edit File Group dialog to save your changes.

Associating a picklist variable with a column in the plan code table

You can optionally associate a picklist variable with a validated column in the plan code table. This feature serves two purposes:

• You can use the GetFileGroupVariableProperty function to return the associated column, and then reference that column as needed in file group components such as plan file templates and utilities.
• Axiom Software can automatically create the validated column for you if it does not already exist on the table.

To associate a picklist variable with a column, use the Picklist Column Configuration section in the Edit Picklist Variable dialog. If this section does not already display in the dialog, click show optional picklist configuration to expose the advanced variable settings.

Complete the following properties to associate the variable with a column:

Item	Description
Associate variable with a picklist column in the plan table	Select this check box to enable the ability to associate a column with the picklist variable. If necessary, you can clear this check box to remove an existing association. Clearing the check box only affects the variable properties; it will not delete or modify the column in the plan code table. An existing association may be removed automatically if the variable value changes (so that the variable now resolves to a different picklist table), or if the plan code table is modified so that the associated column is deleted or no longer looks up to the target picklist table.
Picklist Column	If the plan code table already contains a validated column that looks up to the target picklist table, that column is listed here by default. If the plan code table contains multiple eligible columns, the field becomes a drop-down list where you can select the desired column. If the plan code table does not contain any eligible columns (or if you do not want to use the existing columns), click create new column to create a new column on the plan code table. The Picklist Column field is automatically populated with a column name using the format PicklistTable_Col. You can modify this name as needed. When you save the variable and exit the Edit File Group dialog, a new integer column will be created on the plan code table using this name. The new column has a lookup to the target picklist table.
Default Picklist Column Value	Specify the default value for the associated column in the plan code table. By default, this is set to the default value for the target picklist table. You can select any value in the picklist table if you want the default value for this column to be something different. When you save, Axiom Software will set the default value in the associated column to the corresponding code for the selected value. For example, if the default picklist column value is left at N/A, then the default value of the associated column will be set to the corresponding code of 0. If new data is saved to the plan code table and no value is specified for the associated column, the default value is used.

In the following example, the Priority picklist variable is associated with a column in the plan code table. That table has an existing column named Priority with a lookup to the CP_Priority picklist table, so that column is selected by default.

In the plan file template and other files, you can use the GetFileGroupVariableProperty function to return the associated column name. For example:

Dynamically enabling picklists

Your plan files may be designed so that certain picklists only apply to plan files that meet a certain criteria. You can use picklist variables to dynamically enable or disable certain picklists as follows:

• You can optionally specify a Picklist Enablement Column on the plan code table that holds values to determine whether certain picklists should be enabled or not for each plan file. For example, a column named Type that specifies the type of capital request.

• For each picklist variable, use the Picklist Enablement section to specify which values in the enablement column the picklist should be enabled for. Certain picklists may only apply to certain types.

• In the plan file template (or in other files as needed), you can use the GetFileGroupVariableEnablement function to return whether a picklist variable is enabled for a specified value in the picklist enablement column. You can then use formulas to dynamically show or hide the picklist. For example, you could hide a combo box that references the picklist if the current plan file is assigned to a type for which the picklist variable is not enabled.

Picklist Enablement Column

The Picklist Enablement Column is specified at the top of the Picklist Variables sub-tab, and applies to all picklist variables. To specify a column, click Select to open the Column Chooser dialog. You can select any column on the plan code table that is a validated column with a lookup to a picklist table.

In this example, the column Type is the specified picklist enablement column. The values in this column can be used to dynamically enable or disable certain picklist variables per plan file.

Picklist Enablement for each picklist variable

Once a picklist enablement column has been designated for the file group, you can specify which values in that column each picklist variable should be enabled for. By default, each picklist variable is enabled for all values in the picklist enablement column.

NOTE: Specifying enablement values for a variable simply updates the variable properties, so that you can return the enablement status using the GetFileGroupVariableEnablement function. Axiom Software does not provide any built-in functionality to enable or disable the variable within plan files. It is up to the file designer to return the enablement status and then build the desired solution as needed—for example, to hide a combo box that uses a certain picklist if the picklist variable is disabled.

To select the enablement values for a picklist variable, use the Picklist Enablement section in the Edit File Group Variable dialog. If this section does not already display in the dialog, click show optional picklist configuration to expose the advanced variable settings. This section is only present in the dialog if a picklist enablement column has been specified for the file group at the top of the Picklist Variables tab.

To specify enablement values for the variable:

1. Click configure enablement.

   

2. In the Choose Value dialog, select the values for which you want this variable to be enabled, and then click OK. This dialog displays all values in the picklist that the picklist enablement column looks up to.

   To continue the previous example, the picklist enablement column Type looks up to the Type picklist table. The Choose Value dialog shows the values in the Type picklist table.

   

   NOTE: When you first access this dialog for a variable, no values are selected. This means that the variable is enabled for all values. Once you select one or more values and save the changes, the variable is now enabled for only those values.

When you return to the Edit Picklist Variable dialog, the Picklist Enablement section is updated to reflect the number of enabled values.

If you need to modify the enabled values later, you can do any of the following:

• Click configure enablement to open the Choose Value dialog again, and edit the selected values.
• Click clear selected values to clear all selected values, which means the variable is enabled for all values (the default behavior when no values are selected).
• Clear the Enable this picklist variable check box to make the variable disabled for all values.

Returning the enablement status

In the plan file template and other files, you can use the GetFileGroupVariableEnablement function to return the enablement status of the variable for a given value. For example:

For example, if the plan file is form-enabled, then you may have a Select tag that points to the picklist table associated with the variable, so that the user can select a value from that picklist. If the function returns True, the row with the Select tag shows as normal. if the function returns False, you can dynamically hide the [Row] tag, so that the user is not prompted to select a value for this picklist.

The same basic principles apply to a spreadsheet plan file, except in this case you would probably set up dynamic view tags so that the applicable row or column associated with the picklist variable could be hidden in the spreadsheet.

The GetFileGroupVariableProperty function can also be used to return the list of enabled values, instead of returning the enablement status of a specific value.

Required variables

You can optionally flag a picklist variable as required, so that you can enforce selection from the target picklist table. For example, you might want to:

• Set up custom save validation so that the user cannot save data unless they have selected a non-default value from the picklist.

• Set up a picklist refresh variable so that it is a required variable, meaning the user must select a value in order to refresh data.

To flag a picklist variable as required, select the Is required check box in the Picklist Properties section of the Edit Picklist Variable dialog. If this section does not already display in the dialog, click show optional picklist configuration to expose the advanced variable settings.

NOTE: Enabling the Is required check box simply updates the variable properties, so that you can return the required status using the GetFileGroupVariableProperty function. Axiom Software does not provide any built-in functionality to require the picklist value when saving data to the database or performing other actions. It is up to the file designer to return the required status and then build the desired solution as needed—for example, to enable custom save validation if the picklist variable is flagged as required.

In the plan file template and other files, you can use the GetFileGroupVariableProperty function to return whether the variable is flagged as required. For example:

Managing existing picklist variables

To edit a picklist variable, select the variable in the grid and then click Edit. When editing a variable, keep in mind the following:

• Generally speaking, you should not change a variable name once any file group components have been created that reference the name, such as plan file templates, drivers, or utilities. If you change the name, you will need to manually find and update any GetFileGroupVariable references to the old name in file group components.

• If you change the variable value so that it resolves to a different table, be sure you understand the impacts of the change within the file group. Changing the variable value may impact processes such as the data being queried into plan files, and the tables being saved to. It may also impact the associated picklist column, if set. The ability to modify file group behavior by changing variable values is the purpose of file group variables, but you should take care to ensure you are setting the values as appropriate for the file group.

• If you modify any of the advanced variable properties such as the associated column, picklist enablement, and required status, this may affect the behavior of the variable in existing file group components. Make sure you understand how the variable is being used before making any changes to the properties.

To delete a picklist variable, select the variable in the grid and then click Delete. Before deleting a file group variable, you should be absolutely sure that the variable is not needed. Deleting an in-use variable will cause GetFileGroupVariable functions that reference the variable to return an error.

Editing product placeholder variables

Clients using installed product systems may have access to "placeholder" picklist variables that were installed by the package. These placeholder variables do not have a defined value and do not currently resolve to any picklist tables. It is intended that you can create custom picklist tables as needed, and then update these placeholder variables to reference your custom tables. File group components will then use your custom picklist tables.

When editing a placeholder variable to reference a custom picklist table, note the following:

• The advanced configuration section of the variable properties is automatically visible. It is not necessary to click the link to expose it.

• The Picklist Column Configuration section is not optional for placeholder variables. When you save the variable, Axiom Software will automatically create an associated column in the plan code table that looks up to the target picklist.

For more information on using picklist variables with your installed product, see the separate documentation provided for the product.