Applying conditional formatting to a data grid column
This section details the procedures for applying conditional formatting to a data grid column, covering:
The v10 Web Application enables you to apply a color to a cell of a column of data in the data grid based on the value of the data in that cell, depending on a defined condition (formula), in order to better visualize data and give at-a-glance clues about the certain data. The conditional formatting feature can for instance be used to define thresholds for salary increases or bonuses, highlight specific attributes from a payee or provide warnings about gaps and discrepancies.
When a conditional formatting rule is applied to a data grid column, all users who can see the column in the data grid will see the conditional formatting when it is applied, and will be able to preview the definition of the rule(s) applied to the field.
To be able to define conditional formatting rules on data grid columns, you must be an administrator with super admin rights, be the owner of the grid, or have been granted data grid edition rights. For more information about your permissions on a specific data grid, consult with your system administrator.
Multiple conditional formatting rules can be defined in a given data grid.
When conditional formatting rules are applied to both editable and non editable fields, you can distinguish editable fields by their rounded shape, as illustrated in the figure below. In this example, conditional formatting is applied to the "code_product" and "Id_product" fields, but only the values in "code_product" are editable.
Conditional formatting on editable fields
Formatting rule vs Formatting condition
The conditional formatting feature of the v10 Web Application interface uses two different, but complimentary notions:
Conditional formatting rule: a conditional formatting rule applies one defined format on the cells in one column of the data grid, based on one or several conditions (formulas). It is possible to use several conditional formatting rules on a single data grid column. If you do this, then the OR operator will be applied between the different rules.
Conditional formatting condition: a conditional formatting condition is a single formatting formula, composed of a criterion (data grid column), an operator and a value. It is possible to define several conditions within a single rule. When this is the case, the AND operator is applied between the conditions and only the cells that satisfy all of these conditions will be formatted, using a single color.
Viewing a conditional formatting rule
You can at any time view the definition of the conditional formatting rule(s) applied to the data grid columns using the dedicated panel in the interface.
This is an note/information message.
You can view the definition of the conditional formatting rule(s) even if you do not have edit rights on them; in this case, the contents of the conditional formatting panel will be read-only. For more information about edit rights on conditional formatting, refer to Applying conditional formatting to a data grid column.
To view the definition of an existing conditional formatting rule, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the column on which the rule is applied.
Hover the cursor of your mouse over the header of the column. Three dots are displayed at the right of the header.
Click the
icon. A contextual menu is displayed, as illustrated in the following figure:
Data header conditional menu
Click Conditional Formatting in the contextual menu. The "Conditional Formatting Rules" panel is opened on the right of the application window, as illustrated in the following figure:
Conditional formatting rules panel
The panel contains the rules already defined for the selected field.
This is a tip message.
Text You can view the conditional formatting rules applied to other fields in the data grid directly from the panel, without having to go back to the grid, using the Column Name drop-down list:
If you have edit rights on the conditional formatting rules, the drop down list contains a first sub-level listing the fields on which conditional formatting is currently applied, and then a second sub-level listing all other fields.
If you do not have edit rights on the conditional formatting rules, the drop-down list lists only the fields on which conditional formatting is currently applied.
Creating a conditional formatting rule
You can create conditional formatting rules on all types of fields in a data grid, with all available operators by field type.
To create a conditional formatting rule on a given field of a data grid, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the column on which you want to apply the conditional formatting rule.
Hover the cursor of your mouse over the header of the column. Three dots are displayed at the right of the header.
Click the
Click Conditional Formatting in the contextual menu. The "Conditional Formatting Rules" panel is opened, as illustrated in the following figure:
Conditional formatting rule properties
If a conditional formatting rule already exists on the column, you will need to click the New Rule button.
-
In the "Format Cells If" section, define the rule condition:
In the criterion field, select the reference field for the condition. By default, the field for which you are defining the rule is selected, but you can base the formatting on a different column
Select the operator that you want to use for your rule. Keep in mind that available operators depend on the data type of the selected field.
-
In the value field (3rd field), select the field with which you want to make a comparison (dynamic conditional formatting). The system filters the drop-down list to the fields in the data grid which are of the same type as the field selected in the criterion field (1st field).
Alternatively, you can click the toggle icon to switch to input field and enter a value manually (static conditional formatting). For more information about static and dynamic conditional formatting, see Static Conditional Formatting vs Dynamic Conditional Formatting.
If you want, define another condition to be used in the rule. For more information about the use of several conditions in a rule, see Formatting Rule vs Formatting Condition.
In the "Select a Color" section, pick the color you want to apply to the field cells when the condition is met. If you want, you can enter a custom color using the Custom Hex field.
Click the Apply button located in the lower-right corner of the panel. The conditional formatting rule is saved and applied to the relevant field.
Repeat steps 6-10 if you want to apply another conditional formatting rule to the field. If you do so, the OR operator will be applied between the rules and different colors will be applied in the data grid column. For more information about the distinction between formatting rules and conditions, see Formatting Rule vs Formatting Condition.
Click the ESC icon in the upper-right corner of the panel to return to the data grid.
This is a tip message.
The custom color picker accepts all valid hexadecimal codes. To help you find the color you are looking for, you can use resources such as this.
This is a warning message.
A priority order is applied when several conditional formatting rules are applied to a single data grid field. This order is used to determine which formatting should be applied in case of an overlap between two rules. For more information about the priority order, refer to About the Use of Multiple Conditional Formatting Rules on a Single Data Grid Field. To change the priority order applied to the rules in a field, see Changing the Priority Order of Conditional Formatting Rules.
Static conditional formatting vs Dynamic conditional formatting
The v10 application enables you to create two different types of conditional formatting rules in the system:
Static conditional formatting: a static conditional formatting condition is created by selecting a criterion (field) and an operator, and then manually defining a static value that will determine the formatting of cells in the field
Dynamic conditional formatting: a dynamic conditional formatting condition is created by selecting a criterion (field) and an operator, and then selecting another field in the data grid as the value for the condition in order to set the formatting based on a comparison between the two fields.
The procedure to create static and dynamic conditions is identical. You choose the type that you want to use when creating the condition within the rule (see Creating a Conditional Formatting Rule). By default, the system offers you to use the dynamic conditional formatting feature.
About the use of multiple conditional formatting rules on a single data grid field
The v10 application allows you to create multiples conditional formatting rules on a single data grid field. This is particularly useful when you want to apply thresholds or create non exclusive formatting rule.
The main difference between using multiple rules on a single data grid field and using several conditions on the field is the type of operator that is applied:
When multiple conditions are used in a single rule, only the cells that satisfy all the conditions will be highlighted (i.e., the conditions are linked by the restrictive AND operator)
When multiple rules are applied to a single field, cells that satisfy any of the rules will be highlighted according to the defined priority order (i.e. the rules are linked by the inclusive OR operator).
Therefore, the use of multiple rules is recommended to create non-inclusive ranges, or rules that do not have a logical link between them.
In addition to the operator, the use of multiple conditional formatting rules on a single field is governed by the concept of priority order. Considering that the OR operator is applied between the rules, the system will highlight cells in the field that satisfy any of the defined rules. However, it may be the case that several rules overlap. In order to help the system determine which rule should be applied in case of an overlap, a priority, determined by the order of the rules is applied, from the top down. By default when creating rules on a given data grid field, any new rule is placed below already existing rules, meaning that the new rule would fall below in the order of priority. You can still change the order of priority at any time. For more information about the manage of conditional formatting rule priority order, see Changing the priority order of conditional formatting rules.
Adding a condition to a conditional formatting rule
You have the possibility to apply several conditions (formulas) to the same conditional formatting rule. This feature is to be used when you want to apply the color the cells in a data grid column according to a complex combination of conditions.
When several conditions are defined for a single rule, the AND operator is applied between the conditions. This means that only the cell that satisfy all the defined conditions will be highlighted in the data grid (therefore, it is restrictive). Considering this principle, the use of multiple conditions in a single rule should be restricted to the definition of an inclusive range (date or number), to the definition of a complex rule with dynamic formatting and a logical link between the various legs of the rule. For more information about the distinction between conditional formatting rules and conditions, see Formatting rule vs Formatting condition.
To add a condition to a conditional formatting rule, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the column on which you want to apply the conditional formatting rule.
Hover the cursor of your mouse over the header of the column. Three dots are displayed at the right of the header.
Click the
Click Conditional Formatting in the contextual menu. The "Conditional Formatting Rules" panel is opened.
Locate the rule to which you want to add a condition, and then click the
icon to open the rule definition.
Click the Add Condition button, located under the existing condition(s) in the "Format Cells If" section. A new condition line is added, as illustrated in the following figure:
Conditional formatting with multiple conditions
-
Define criterion, operator and value for the new rule condition:
In the criterion field, select the reference field for the condition. By default, the field for which you are defining the rule is selected, but you can base the formatting on a different column
Select the operator that you want to use for your rule. Keep in mind that available operators depend on the data type of the selected field.
In the value field (3rd field), select the field with which you want to make a comparison (dynamic conditional formatting). The system filters the drop-down list to the fields in the data grid which are of the same type as the field selected in the criterion field (1st field).
Alternatively, you can click the toggle icon to switch to input field and enter a value manually (static conditional formatting). For more information about static and dynamic conditional formatting, see Static conditional formatting vs Dynamic conditional formatting.
Repeat steps 7-8 if you want to add more conditions to the rule. Keep in mind that the more conditions you define, the fewer records will match all defined conditions.
Click the Apply button located in the lower-right corner of the panel. The conditional formatting rule is saved and all defined conditions are applied to the relevant field.
Editing the properties of a conditional formatting rule
Provided that you have the proper access rights, you can edit the definition of an existing conditional formatting rule. To do so, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the data column (field) whose conditional formatting you want to edit.
Hover the cursor of your mouse over the column header and then click the
Click Conditional Formatting in the menu. The "Conditional Formatting Rules" panel is displayed.
Locate, in the panel, the rule that you want to edit and then click the
Conditional formatting rule edition
The current definition of the rule is displayed.
-
Edit the definition of the rule according to your needs:
Change the conditions in the "Format Cells If" section (see Adding a condition to a conditional formatting rule and Editing the conditions of a conditional formatting rule)
Change the highlight color of the rule (see Creating a conditional formatting rule).
Click Apply. You changes are saved and the new definition of the rule is applied immediately to the data grid.
Editing the conditions of a conditional formatting rule
Provided that you have the necessary permissions, you can edit the condition(s) defined in a conditional formatting rule. To do so, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the data column (field) whose conditional formatting you want to edit.
Hover the cursor of your mouse over the column header and then click the
Click Conditional Formatting in the menu. The "Conditional Formatting Rules" panel is displayed.
Locate, in the panel, the rule that you want to edit and then click the
-
In the "Format Cells If" section, perform the required changes:
You can change the actual definition of a condition using the criterion, operator and value fields.
You can add another condition using the Add Condition button (see Adding a condition to a conditional formatting rule).
You can delete an existing condition by clicking the
icon located at the right of the relevant condition.
Click Apply in the lower-right corner of the panel. The new definition of the rule conditions is saved and immediately applied to the data grid.
Changing the priority order of conditional formatting rules
You can change the priority order of the rules defined for a given data grid field in order to modify the behavior applied by the system in case of an overlap between several rule conditions (for more information about the use or multiple conditional formatting rules and their priority order, see About the use of multiple conditional formatting rules on a single data grid field).
To change the order of the rules, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the data column (field) whose conditional formatting you want to edit.
Hover the cursor of your mouse over the column header and then click the
Click Conditional Formatting in the menu. The "Conditional Formatting Rules" panel is displayed.
Locate, in the list of rules, the rule that you want to move in the priority order.
Click the box corresponding the rule.
-
Click the
icon located at the left of the box, than then drag and drop the rule box in the desired location. Keep in mind that the priority is top to down, so the first one in the list is the most important, then the second, etc.
The new priority order is saved as soon as you drop the box in the new location, and is applied immediately to the data grid.
Deleting a conditional formatting rule
To delete a conditional formatting rule defined on a data grid field, proceed as follows:
Open the data grid in which you want to work (see Viewing a Data Grid in About data grids in the v10 Application).
Locate, in the grid, the data column (field) whose conditional formatting you want to edit.
Hover the cursor of your mouse over the column header and then click the
Click Conditional Formatting in the menu. The "Conditional Formatting Rules" panel is displayed.
Locate, in the list of rules, the rule that you want to delete.
Click the
icon located at the right of the rule box. A confirmation message is displayed.
Click Delete. The rule is deleted and the changes are immediately applied to the data grid.