Conditional columns allow you to create forms that show relevant fields based on the business logic of your use case or project. Columns may be displayed or hidden based on values entered in other columns in the item.
The Column condition settings section allows you to define the rules that will trigger this conditional behaviour when a user is entering data into an iSheet item.
For example, an iSheet might have a Country choice column. If a user selects "United Kingdom", the iSheet will display a "County" picklist. If a user selects "United States" or "Australia", the iSheet instead displays the relevant "State" picklist.
If a user selects "Australia" from the "Country" choice list, the "State (AU)" column displays:
If the user selects "United Kingdom" instead, the "County (UK)" column would display:
Multiple conditions may be combined to determine if a column should display. For example, if a user selects "New York" from the "State" column and "New" from the "Lease Type" column, the multiple line text column called "New York Lease Notes" will appear for the user to enter more information:
Configuring column conditions
First, determine if a column should be hidden until the conditions defined are true, or displayed at first and subsequently hidden if the conditions defined are true. By default, the former option, Display column, is selected since it is the more common to hide conditional columns until the conditions defined are true. However, if the field should be displayed by default and only hidden under certain circumstances, change this setting to Hide field.
Next, add conditions to trigger the display (or hiding) of the column based on the business logic of the use case:
- Select a column to drive the conditionality. Only certain column types, listed below, may be used in conditions:
- Single line text
- Multiple line text (plain text only)
- Choice
- Numbers
- Dates and time
- User lookup
- Choose the appropriate operator, such as is equal to or contains. The type of column selected determines the available operators
- Type in the value that should trigger the condition
- Click Add to add the condition
Condition operators
When creating a conditional statement, consider how the value entered in the condition field should trigger the condition. For example, the condition may be satisfied if the value of the field is equal to a certain value.
The type of column that is selected determines which operators are available to define the condition trigger. Text fields, such as single line text, multiple line text, choice, and user lookup (including system generated fields like Created by and Modified by), may use any of these operators:
- is equal to
- is not equal to
- begins with
- contains
Number and Date and time may use the following operators:
- is equal to
- is not equal to
- is greater than
- is less than
- is greater than or equal to
- is less than or equal to
.png)
Text
All text matching is case insensitive.
The contains operator will match any value in the list of choices, including a substring.
For example, we have a choice column which includes all US States, and we define the following condition:
US State contains New
The condition will be met if the state selected either "New York", "New Jersey" or "New Mexico".
If the choice field allows multiple selections, selecting any one of the values containing New will trigger the condition.
However, if the condition is based on multiple possible values in the same text or choice field, for example if US State equals New York or California, then create two separate conditions:
US State equals New York
OR
US State equals California
And connect these conditions using the boolean OR. More information is below in the section on multiple conditions.
Dates and Numbers
Date and time column conditions provide a date picker to use to select the date condition value:
If a date value triggers a condition by falling within a certain date range, create two conditions:
Termination Date is greater than or equal to 01 Oct 2019
AND
Termination Date is less than or equal to 31 Oct 2019
Combine the conditions with the boolean AND. In the above example, if a Termination Date entered falls during the month of October 2019, the condition will be triggered and a column displayed or hidden. A numeric range can be created in the same way.
Alternatively, a condition could be triggered if a number column value exceeds a defined amount. For example, if a budget estimate entered exceeds a certain threshold, an additional column could be displayed, perhaps for notes about the reason for the higher than usual budget.
The condition would be as follows:
Budget estimate is greater than 10000
Multiple conditions
You may combine multiple conditions so that a column only displays when one or more of the conditions are met. For example, in a "new client intake" iSheet, we have a column for a budget estimate that is required under one or both of these circumstances:
- for matters with a fee cap, OR
- for matters involving new clients.
Then select OR when adding those conditions.
Alternatively, the budget estimate may only be required when both of these conditions are met:
- for fixed fee matters, that also involve
- new clients.
In this case, select AND when adding those conditions.
If adding multiple conditions, you may need to use the arrows to adjust the order of conditions, particularly if AND and OR operators are used in combination. See the Advanced concepts section below for more details.
To rearrange the order of conditions, select the checkbox next to a condition and click the up or down arrow to the right. Arrows are also useful when a new condition is added and needs to be sorted appropriately.
Negative conditions
Negative condition options are available in Column configuration and also View configuration.
The 'is blank' and 'is not blank' options are not available for system generated columns as these columns are never blank. 'Does not contain' and 'Does not begin with' are available for system generated columns.
These options are displayed at the bottom of Column settings:
The options displayed at the bottom within the View settings are:
Removing and modifying conditions
To remove a condition, click the checkbox next to the condition and select Remove.
It is not possible to modify an existing condition. Instead, remove the existing condition, create a new condition, and add the new condition to the list of conditions. If there are multiple conditions, reorder the conditions using the arrows if necessary.
Display of conditional columns
Conditional columns display according to their conditional rules when adding, editing or viewing items.
The same is true for file and folder metadata iSheets. Conditional columns display (beside the file in the viewer) according to their rules when viewing or editing metadata, or if modifying the metadata, via More actions > Edit details > Metadata.
However, if a view includes conditional columns, these columns will always be displayed in the table view, as in the example below. Conditional columns will also appear in the search form, if they are configured to be searchable.
Best practice
Group multiple conditional columns in a section for a cleaner display. The section (and section title) will not appear unless at least one field inside the section is visible. However conditional fields will always appear in the iSheets search form, provided that they have are searchable, irrespective of whether the condition to show it has been satisfied.
Advanced concepts
Order of operations for more than two conditions and mixed boolean operators
Consider the following condition: We would like a text column called "Conditional example field" to display when the Lease Type is "New" and State (US) is equal to either "California" or "New York". We may try to configure the condition as follows:
However upon testing the condition, the behaviour does not match the business logic required. Instead, the "Conditional example field" appears when Lease Type equals Renewal and State (US) equals New York.
The order of the conditions has grouped the three conditions based on the AND operator.
Expressed another way, this is our desired condition:
Lease Type is equal to New AND ( State (US) is equal to California OR State (US) is equal to New York )
But this is the conditional behaviour we get:
( Lease Type is equal to New AND State (US) is equal to California ) OR State (US) is equal to New York
Reversing the order, as below, will not produce the desired conditionality either:
In this case, the boolean AND groups the second and third conditions together, as follows:
State (US) is equal to California OR ( State (US) is equal to New York AND Lease Type is equal to New )
In order to achieve the desired conditionality, we must do the following:
In this case, we repeat the Lease Type condition to ensure the conditional logic we are after is achieved:
( State (US) is equal to California AND Lease Type is equal to New ) OR ( State (US) is equal to New York AND Lease Type is equal to New )
How to combine multiple conditions with the "is not equal to" operator
You may choose to use the "is not equal to" operator if you have a choice column with dozens of value choices, and would like a column to appear for all of those options except one. For example, to display a column for all states in the US except Alaska, configure the conditional column as follows:
Display field when State (US) is not equal to Alaska
If you need to combine more than one of these "negative" conditions, that is, multiple conditions where a column "is not equal to" a value, you must choose the appropriate boolean connector to achieve the desired conditionality.
Consider the following scenario. You would like a conditional column to appear under all circumstances except for when the state selected is Alaska or Hawaii. You may initially configure the conditions as follows:
Display field when
State (US) is not equal to Alaska
OR
State (US) is not equal to Hawaii
However, upon configuring this conditional column and testing it, you will discover that the conditional column appears no matter which state is selected, including Alaska or Hawaii. Combining is not equal to conditions with the OR operator is the equivalent of asking to display the column, except for when state is not equal to both Alaska and Hawaii, which is impossible given that this column is a single select column.
Alternatively, to achieve the desired conditional behaviour, use the following configuration:
Display field when
State (US) is not equal to Alaska
AND
State (US) is not equal to Hawaii
With this configuration, if either Alaska or Hawaii is selected, the column will not display. Under all other conditions, the column will display.
