Highlighting records

You can highlight records with different background colors based on custom conditions. As an alternative or in addition to background color, records can be highlighted by text formatting such as font style, font color, size, etc.

You set up highlighting by defining conditional formatting rules. A rule consists of a highlighting condition, a highlighting format (background color and/or text properties) and a highlighting scope (row, column or first column). Conditional formatting rules are listed on the Conditional Formatting tab of the Customize View window. If a record satisfies a condition, it is highlighted using the format defined in the rule. If a record satisfies the condition of two or more rules, it is highlighted in accordance with the first (uppermost) rule in the list.

To create a rule:

  1. Select View > Customize View from the main menu to open the Customize View window.

  2. Click the Conditional Formatting tab.

  3. Click Add. The Conditional Formatting Rule window opens.

  4. Type in a rule name.

  5. Select the scope for conditional formatting: Row, First column or Column.

  6. UnderCondition , define a highlighting condition in either of two modes:

    • Design Mode - allows to use the graphical Filter Builder. The Design mode is set by default. You can change the mode by clicking Switch to SQL Mode.

      • ClosedTo add a condition:

        1. Click Add Condition.

        2. Define the condition: choose the field to compare, the comparison operator and value to compare with (if applicable to the chosen operator). For details, see Using operators in the Filter Builder. If the comparison operator is either like or not like, in the value you can use the wildcards: "_" representing any single character or "%" representing any sequence of characters.

        To define the value, you can also use the following placeholders: <Current User>, <Current User Organization>, and <Current User Location>, which will be replaced with the respective values from the Full Name, Organization, and Location fields of the current user’s Person record.

      • ClosedTo add a group of conditions:

        1. Click Add Group.

        2. In the new group row, choose the operator to combine the conditions within the group (the default operator is AND, you can also choose OR, NOT AND, or NOT OR). For details, see Using operators in the Filter Builder.

        3. Define the conditions in the group.

      • ClosedTo remove a condition or a group of conditions:

        • Click Delete Row in the corresponding row.

    • SQL Mode - allows you to directly type in your SQL WHERE clause (without the word WHERE). This mode is recommended for advanced SQL users only. You can change the mode by clicking Switch to Design Mode. However, all the data entered in the SQL Mode will be lost.

Your SQL WHERE clause can include the following placeholders: <Current User>, <Current User Organization>, and <Current User Location>. Before the filter is applied, the placeholders will be automatically replaced with the respective data from the Full Name, Organization, and Location fields of the current user’s Person record.

      • Examples of SQL WHERE clauses for the Incident s grid:

      Meaning WHERE clause

      Highlight overdue Incident s

      (DATEDIFF(n, [Due_Date], [Completed_Date]) >0) OR ([Completed_Date] is NULL) AND(DATEDIFF(n, [Due_Date], GETDATE()) >0)

      Highlight overdue Incident s which status is neither Closed nor Resolved

      ([Status] <> N'Resolved') and ([Status] <> N'Closed') and (DATEDIFF(n, [Due_Date], GETDATE()) >0)

      Highlight unassigned Incident s

      ([Status] = N'Unassigned') and (([Assignee] ='') or ([Assignee] is NULL))

      • Examples of SQL WHERE clauses for the Computers grid:

      Meaning WHERE clause

      Highlight leased Computers with expired Lease Return date

      [Lease] = '1' AND (DATEDIFF(n, [Lease_Return_Date], GETDATE()) >0)

      Highlight out-of-warranty Computers

      (DATEDIFF(n, [Warranty_Exp], GETDATE()) >0)

  1. In the Apply Style section, define the background color for the records that satisfy the rule condition. As an alternative or in addition to background and text color, you can specify font formatting for those records.

    • To specify background color, select the Background color check box and choose the desired color from the drop-down list. Alternatively, you can click the ellipsis button and select one of standard colors or define a custom one using the Color window.

    • To specify text color, select the Text color check box and choose the desired color from the drop-down list. Alternatively, you can click the ellipsis button and select one of standard colors or define a custom one using the Color window.

    • To specify font name, select the Font namecheck box and choose the desired name from the drop-down list.

    • To specify font size, select the Font sizecheck box and enter the desired size in the corresponding field.

    • To specify font style, select the Font stylecheck box and apply desired styles by selecting the Bold, Italic, Strikeout or Underline check boxes.

NOTE: You can preview your highlighting format at the bottom of the window.

  1. Click OK.

To enable or disable a rule:

  • On the Conditional Formatting tab, right-click the rule and select Enable orDisable from the pop-up menu.

To modify a rule:

  1. On the Conditional Formatting tab, double-click the rule to modify. The Conditional Formatting Rule window opens.

  2. Modify the rule as needed and click OK.

To copy a rule:

  1. On the Conditional Formatting tab, select the rule and click Copy.

  2. In the Conditional Formatting Rule window, specify the parameters as needed and click OK.

To change the priority of a rule:

  • The Conditional Formatting tab displays rules in order of priority: if a record satisfies the condition of more than one rule, the first (uppermost) rule in the list is applied. To change a rule priority, select that rule and move it to the desired position by clicking Move Up or Move Down.

To remove a rule:

  • On the Conditional Formatting tab, select the rule and click Delete.