Choice column

Dynamic choice column

An admin can create a set of Choice columns and associate rules to link them as Dynamic Choice columns. A selection in one column restricts values in another column to pre-defined ('mapped') values.
For example, when a user adds a record, the selection in a '
Country
' entry can filter the options available in a '
Team
' entry.
The filter and default values apply if:
  • A user adds a record (in the iSheets module or in a form).
  • A user uses inline editing in the iSheets module.
  • Values are imported to the iSheet from an Excel file.
How to create a Rule
A
Rule
 filters the values available for each column, based on the values already selected. Rules for columns with many choices may be managed as an Excel file and imported into a Rule.
Configure a Choice column for dynamic choices
From the
Manage iSheets
 page, select
Manage columns
 for an iSheet
with two or more related Choice columns
 (or create a set of Choice columns in an iSheet).
In the
Manage columns
 window, click
Rules
 and select
Create rule
.
note
To create a rule, you need to have at least two Choice columns.
The Choice columns must already contain the choices required for that column. The Primary Choice column must use Drop-down Menu or Radio Buttons, not a multiple-selection checkbox.
The
Add new rule
 window opens:
Enter a name for the rule, then select the
Primary column
 and the related
Secondary column
.
note
Each rule creates a mapping for a pair of Choice columns; you must create a separate rule for each pair. If you are unable to select a pair of Choice columns, look for a rule that already maps those columns.
Value mapping
Value mapping defines which values are linked, and you can either import a set of mapped values from an Excel file or map them directly in this window.
Select
Value mapping
 to create a mapping.
Enter one or more
Value selections
.
At the top of this section, define the
Default secondary value
,
if no Primary value
 or a
Primary value without defined Secondary values
 is selected. If no default is selected, the column remains blank.
Under
Value selection
, select a value from the
Primary
 column to map, then select all related values in the
Secondary
 list. You may set a
Default secondary value
 that is used
if the value in the Primary drop-down
 is selected.
note
The default secondary value must be one of the selected secondary values.
Selected values are shown under the Secondary drop-down.
After you have selected mapped values for the Primary value, select
Value mapping
 again and map values for the next Primary value. Continue until you have created all maps required for the selected pair of columns.
Select
Save rule
 to finish the mapping.
tip
If you create a rule with many value mapping definitions, we recommend that you periodically select
Save rule
.
note
Multiple related columns may be mapped with additional rules. For example, a 'Country' column can filter the available options in a 'Team' column, and both these selections can filter options in a third 'Project' column. Two rules are required, one for the Country and Team pair, and another for the Team and Project pair.
Note that there is currently a limit of 50 rules for each iSheet.
Importing mapped pairs with an Excel file
For longer maps, you can use the Export Template option to download a template, add the necessary values, and import the data.
Once the Primary and Secondary column data is ready, click
Export Template
 to download the Excel file.
You will now have a pre-defined column data sheet.
The downloaded template contains two columns:
Primary Value
 and
Secondary Values
.
In the Excel sheet, enter the Primary value in the first column and list each corresponding Secondary value in the second column, repeating the Primary value on each row where a new Secondary value is entered.
note
Each entry must match the choices defined in the Choice column.
Save
 the Excel file.
In the
Add new rule
 window, select
Import from Excel
 and either browse or drag the file to the
Import
 window.
After the file is uploaded, click
Next
 to start the file validation process.
Click
Import
 to continue, then
Close
.
note
If the file fails validation, please verify that the Excel file contains the correct values and layout, and then retry.
The imported Value mapping is added to the
Add new rule
 page. Review the mapping and add default values if required.
Click
Save rule
 to keep the imported mapping.
Managing rules
Saved rules are added to the
Manage rules
 window:
Select
More actions
 to
Edit
 or
Delete
 a rule.
note
The rules are applied when records are added manually, with bulk import, or with the API.

Selecting values in an iSheet

When Dynamic Choice rules are created, users can only select values from the predefined mapping when choosing from mapped choice columns.
Add record
If a user selects Add record for an iSheet that provides dynamic choice options, the available options are filtered based on the form entries as the user completes it.
For example, if 'UK' is selected, the default value mapped to 'UK' is automatically added to the 'projects' selection.
note
If no default option is defined in the mapping, the 'projects' selection remains empty.
The user may select the projects drop-down to see other values mapped to 'UK'.
note
In this example, 'Climate Series' is the only mapped value.
When the user selects 'USA', the default USA project is automatically selected, and the project options are filtered to display only valid USA projects.
Inline edit
If Inline edit is enabled, users can click on a value in the iSheets module to open a list of available options. In this example, the country and projects columns have a mapping rule that links projects to countries.
If the country is set to India, only projects mapped to India are available for selection.
In this case, the user can select the 'Electric Range' project because it was mapped to 'India' in the mapping rule. If the user changes the country column to 'Japan', different mapped values appear, such as 'Dynamic Force Engine' or 'Mirrorless Bodies'.
Bulk upload to iSheet
If a user adds records with the bulk upload from Excel option, values for dynamic choice columns follow the mapping rules:
  • If a primary value is provided but the secondary column values are missing or incorrect, the default value is applied to the secondary column in the iSheet.
  • If no default value is defined, the column remains blank.

This article applies to:

  • Product: HighQ
  • Subject: iSheets

Related Content