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. Right-click anywhere in the grid and choose Customize View from the pop-up menu to open the Customize View window.

  2. Click the Conditional Formatting tab.

  3. Click New. 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. Click the Condition tab and 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 the operator and select Add Condition from the drop-down list.

        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.

      • ClosedTo add a group of conditions:

        1. Click the operator and select Add Group from the drop-down list.

        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 the Remove this conditionbutton in the corresponding row. To remove a group of conditions, click the operator, and then click Remove.

    • 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.

      • 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. Click the Style tab and 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.

    • To specify text color, select the Text color check box and choose the desired color from the drop-down list.

    • 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, Oblique, 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, select or clear the Enabled check box.

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.