Applying conditional formatting

Using conditional formatting, you can customize how cells display data in TMW Back Office data grids. You can even assign a grid characteristic (such as a color) to cells that contain data elements that meet special criteria. Conditional formatting allows you to call attention to important information. These visual alerts speed up users' ability to make decisions, thereby enabling them to use the grids to perform tasks more efficiently.

There are four types of conditions:

  • Operator conditions
    Use an Operator condition to change the appearance of a cell based on the information in that cell.

  • Formula conditions
    Use a Formula condition to change the appearance of a cell based on the information in another column.

  • True conditions
    Use a True condition to apply your cell formatting changes to every cell in a column.

  • Condition Groups
    Use a Condition Group to change the appearance of a cell based on more than one condition. The component conditions, joined by "AND" or "OR," can be the Operator, True, or Formula type. If you are using TMW Back Office version 17.10 or later, you can nest Condition Groups within Condition Groups.

You must retrieve data before applying conditional formatting to a column.

The following videos demonstrate how to use this functionality in TMW Back Office version 17.10 and later. Similar examples are described in Examples of conditional formatting.

image1 See it in action: Highlighting specific entries in a column

image1 See it in action: Color-coding column data

Overview of the conditional formatting windows

While applying conditional formatting, you will use these windows.

Add Condition/Appearance Mappings

The Add Condition/Appearance Mappings window is the starting point for all conditional formatting.

The window appears as shown in this illustration.

image3

Formula Builder

The Formula Builder opens when you select image4 Formula Builder.

image5

Create Condition Group

You create Condition Groups in the Add Condition/Appearance Mappings window.

image7

Adding conditional formatting

To apply conditional formatting to a column of data, follow these steps.

  1. Right-click in the column heading to apply the conditional format.
    A shortcut menu opens.

  2. Select Conditional Format.
    The Add Condition/Appearance Mappings window opens. A single condition row appears in the grid by default.

  3. In the Select a condition type field for the condition row, select the condition type you want.

    image15

    • Operator
      Select Operator to add a condition based on an arithmetic operator (such as equal to, greater than, or less than) or on a true condition.

      image16

    • Formula
      Select Formula to format one column based on a condition in another column. You can enter the formula in the field provided, or you can click image4 Formula Builder to open the Formula Builder window and build your formula there.

      image17

      image18

    • True
      Select True to format all items in the column, regardless of the contents.

      image19

    • Condition Group
      Select Condition Group to add two or more conditions for a particular style change. Each condition in a group can be Operator, Formula, True, or Condition Group type.

      To create an "AND" condition group in which all of the conditions in the group must be met, select All in the Apply field.

      To create an "OR" condition group in which any of the conditions in the group can be met, select Any in the Apply field.

      image20

  4. Click image21 Styles to display a list of styles.

    image22

  5. Select the style that will determine how column information will appear in the grid when your condition or conditions are met. Do one of the following:

    • To select an existing style, click on the style icon.

    • To select a style used recently, click on a style icon in the Recent section.

    • To create a new style, follow these steps:

      1. Click Custom Style. The Custom Style Properties window opens.

        image23

      2. Recently-created styles appear in the Recent Styles section. To work with one of these styles, select it in the list.

      3. Enter style properties in the fields provided.

        Field Description

        Font Color

        Fill

        RGB values corresponding to the color of the font text or fill area. You can enter values in the fields or select from these color palettes:

        Custom

        image24

        Web

        image25

        System

        image26

        Note: When selecting colors, it might be important to maintain consistency from one computer to the next. If this is the case, it is recommended that you select colors from the Web color palette, rather than the System palette.

        Font

        Text font. You can choose from any of the fonts installed on your computer.

        Size

        Font size. You can choose from any of the font sizes installed on your computer.

        Style

        Font style. Options are:

        image27 Italic

        image28 Bold

        image29 Strikethrough

  6. Click OK.
    The Styles button changes to reflect the edits made.

  7. To add another condition, click Add Condition and repeat Steps 3-6.

  8. Once you have added all your conditions, decide how the system will apply them to the column by selecting a value in the Apply field.

    • If you want all conditions applied ("AND"), select All.

    • If you want any of the conditions applied ("OR"), select Any.

      image30

  9. Click OK.

Adding an icon to a condition

First available in TMW Back Office: 2021.3

You can add an icon to an existing conditional format. An icon can provide a quick, easy-to-understand cue that a particular condition is met.

You can only add a conditional icon to a column that already has a conditional format applied.

  1. Open the grid that contains the column you want to format.

  2. If the column you want to add the icon to does not have a conditional format applied already, add one now.
    Note: You can add icons only to columns that already have a conditional format applied.

  3. Right-click the header of the column to which you want to add an icon.

  4. Select Conditional Format Appearance.
    The Grid Conditional Formatting Appearance Customizer window opens.

  5. Select the Image row.

  6. Click the ellipses icon at the far right of the row.

    image37
    The Appearance Image Editor window opens.

  7. Click the ellipses icon next to the right of Select From File/Resource…​

    image38b
    The Open window opens.

  8. Select the image file.

    image39

  9. Click Open.

  10. Click OK in the Appearance Image Editor window.
    If the rendered size of the image file exceeds 10 KB, a message displays. It reads, "Image render size of the selected file cannot exceed 10KB."

    image43

    Click OK and repeat Steps 7-10, selecting an image that is less than 10 KB in size.

  11. Click OK in the Grid Conditional Formatting Appearance Customizer window.
    The icon appears in the grid.

    image40

  12. Save the layout if you want to use this conditional formatting in future sessions.

Deleting an icon from a condition

There are two ways you can delete a conditional format icon.

  • To delete the icon and the condition to which it is attached:

    1. Delete the condition to which it is attached.

    2. Save the layout.

  • To delete the icon and retain the condition to which it is attached:

    1. Right-click the header of the column from which you want to remove an icon.

    2. Select Conditional Format Appearance.
      The Grid Conditional Formatting Appearance Customizer window opens.

    3. On the Image row, select Bitmap.

      image41

    4. Press the DELETE key.
      The system removes the image reference.

      image42

    5. Click OK.
      The icon is removed from the grid.

    6. Save the layout.

Editing a condition

To add or change a condition or its formatting features, follow these steps.

For Operator and True conditions, you can make changes directly in the window. This is true regardless of whether the condition exists in a Condition Group.

For Formula conditions, you can make changes directly in the window or click image4 Formula Builder to make changes in the Formula Builder window. This is true regardless of whether the condition exists in a Condition Group.

  1. Click image21 Styles.

  2. Do one of the following:

    • Select a new predefined style by clicking on its style icon.

    • Select a recently used style by clicking on it in the Recent section.

    • If you used a predefined style and you want to create a custom style, click Custom Style and make changes on the Custom Style Properties window.

    • If you used a custom style and want to edit it, click Edit and make changes on the Custom Style Properties window.

    • If you used a custom style and want to replace it with the most recently used style in the Recent section, click Remove.

  3. If multiple conditions exist, you can rearrange the order by doing either of the following:

    • Click the Move Up and Move Down buttons.

    • Click on the area to the left of a condition and drag it to where you want it. Red arrows appear on either side of the condition as you drag it.

      image34

  4. Click OK.

Deleting a condition

To delete a condition from a column’s format, follow these steps.

  1. Right-click in the column heading.
    A shortcut menu is shown.

  2. Select Conditional Format.
    The Add Condition/Appearance Mappings dialog box is shown.

  3. Delete the condition.

In the row containing the condition you want to delete, click image36 Delete Condition.

  1. If you want to remove all conditional formatting from the column, select and delete each row, one at a time.

  2. Click OK.