Filtering data in a grid

You can use filters to reduce the number of records shown in a grid. This makes it easier for you to locate the information you need. Filters are conditions that serve as restrictions. When you apply filters, the system hides the records that do not meet the filter criteria you have entered. You can quickly enter filters and clear them when you are done. By default, filtering features are not visible in grids.

image1 See it in action

To enable the filtering feature in a grid, follow these steps:

  1. Open the window that contains the grid you want to filter.

  2. Right-click anywhere in the data grid.
    A shortcut menu opens.

  3. Select Show Filter Row.

    Immediately below the row of column headings, a filter row comes into view.

Depending on the type of data grid, you will see one of these filter rows. You can tell the difference by the presence or absence of the image2 Condition icon.

image3 image4

Components of the filter row:

image2

Displays the list of operators for a conditional filter

image5

Text entry area where you enter the value for a filter

When the data is filtered, this text area displays the filtering element.

image6

If you move your pointer over the text box, you can see the condition applied to the filter.

image7

image8

Displays the values available for filtering and provides access to other filtering options

image9

Clears the filter currently in use

  1. Only data rows that meet your conditions appear in the grid.

  2. Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

When you have [.NetMisc]ShowFilterRowChangesFromDefault=Y in your TTS50, you can quickly spot filters applied to a grid. The setting causes a green fill color to appear on the row numbers when the grid is filtered. Saving or resetting the layout removes the green fill color.

image10

Applying text filters

Use filtering to limit the records by data in a text column, such as company or status.

If the filter row is not visible, do the following:

  1. Right-click anywhere in the data grid.

  2. From the shortcut menu, select Show Filter Row.

A listed value

If the filter looks like this: You can do this:

image11 image12

  • Click the drop-down arrow and select one of the items listed.

  • Click the drop-down arrow and enter the alphanumeric characters that you want to use as your filter for the column.

    The system will limit the grid to records having data that start with the letters you entered.

image13

  1. Click the drop-down arrow.
    By default, the (All) checkbox is selected.

  2. Clear the (All) checkbox to clear all the options.

  3. Select those items that you want to be included in the grid.

Blanks or non-blanks

If the filter looks like this: You can do this:

image14

Click the drop-down arrow and then select:

  • (Blanks) to display any data rows that are blank in this column.

  • (NonBlanks) to eliminate any data rows that are blank in this column.

image15

Blanks

To set the filter to find only records with no data in the column:

  1. Click the drop-down arrow.

  2. Clear all the selections.

  3. Select (Blanks).

image16

Non-blanks

To set the filter to find only records that have data in the column:

  1. Click the drop-down arrow.

  2. Clear the (Blanks) checkbox.

Conditional filter

Use this option to expand the filtering options in the data grid.

  1. In the filter row of the appropriate column heading, do one of the following:

    • For filter rows that include the image17 Conditions icon:

      Click Conditions.
      A list of conditions appears.

      image18

    • For filter rows that do not include the image17 Conditions icon:

      Click the drop-down arrow and select Text Filters.
      A list of conditions appears.

      image19

  2. Select the appropriate condition.
    The cursor moves to the text entry area.

  3. Enter the text you want.
    Only rows that match your criteria are shown.

Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

Applying number filters

Use filtering to limit the records by data in a numeric column, such as charge amount or miles.

A listed value

If the filter looks like this: You can do this:

image20

Click the drop-down arrow and select one of the items listed.

image21

  1. Click the drop-down arrow.
    By default, the (All) checkbox is selected.

  2. Clear the (All) checkbox to clear all the options.

  3. Select those items that you want to be included in the grid.

Blanks or non-blanks

If the filter looks like this: You can do this:

image22

Click the drop-down arrow and then select:

  • (Blanks) to display any data rows that are blank in this column.

  • (NonBlanks) to eliminate any data rows that are blank in this column.

image23

Blanks

To set the filter to find only records with no data in the column:

  1. Click the drop-down arrow.

  2. Clear all the selections.

  3. Select (Blanks).

image24

Non-blanks

To set the filter to find only records that have data in the column:

  1. Click the drop-down arrow.

  2. Clear the (Blanks) checkbox.

Conditional filter

Use this option to expand the filtering options in the data grid.

  1. In the filter row of the appropriate column heading, do one of the following:

    • For filter rows that include the image17 Conditions icon:

      Click Conditions.
      A list of conditions is shown.

      image25

    • For filter rows that do not include the Conditions icon:

      Click the drop-down arrow and select Number Filters.
      A list of conditions is shown.

      image26

  2. Select the appropriate condition.
    The list of conditions closes and the cursor moves to the data entry area.

  3. Enter the value you want.
    Only rows that match your criteria are shown.

Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

Applying date filters

Use filtering to limit the records by data in a date column, such as book date, earliest or latest arrival date.

If the filter looks like this: You can do this:

image27

image28

  1. In the filter row of the date column heading, click the equal sign and select the operator you want to use.

    Note: Unless you want to specify an exact time, do not select Equals.

  2. In the filter row of the same column heading, click the drop-down arrow and do one of the following:

    • Manually enter the date.

    • Select the date from a calendar.

Notes:

  1. Today’s date is marked with an outline.

  2. Your selected date is highlighted.

  3. Your selected date is entered into the filter field with the selected operator.

  4. Scroll forward or backward to another month by clicking on the left or right arrows.

image29

image30

  1. In the filter row of the date column heading, click the drop-down arrow.
    A drop-down menu with a checklist opens.

  2. As appropriate, click the + and - icons to expand and collapse the drill-down list of years, months, and days.

  3. Use the following options to determine which items will be included in your filter:

    • Clear the (All) checkbox to clear all the options, and then select those items that you want to be included in the grid.

    • Select and deselect items one at a time by clicking their checkboxes.

    • Click (All) to include every item in the filter. Clicking (All) a second time will deselect all items.

    • Click (Blanks) to set the filter to limit the list to records with no data in the selected column.

    • Clear the (Blanks) checkbox to eliminate any data rows that are blank in this field.

  4. Click OK.

image31

image32

Apply date filters by periods of time:

  1. In the filter row of the date column heading, click the drop-down arrow.

  2. Select Date Filters.

  3. Select the period of time.

    The data in the column is filtered and the selected period of time is shown in the filter row.

    image33
Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

Applying custom filters

There may be times when you need to create a filter that involves conditions. A grid’s filter row provides access to the Custom Filter window. The window allows you to add as many conditions as needed. You can specify whether all conditions must be met, or whether at least one condition must be met.

image34

Three entries define a condition:

  • The column that contains the data that will be restricted

  • The operator used to do the comparison, such as = (equals) or > (greater than)

  • The value to which the column’s data will be compared

You can use the Custom Filter window to:

  • Apply multiple conditions to a filter

    This illustration shows how you could set up a condition on the BillToCity column that would filter only those records where the BillToCity was equal to Cincinnati OR Cleveland OR Columbus.

    image35

  • Base the result on another column in the grid

    This illustration shows how you could set up a condition on the Shipper column. It filters the records to show only those with a shipper company ID that is the same as the BillTo company ID.

    image36

    1. In the filter row of the appropriate column, open the Custom Filter window.

      If the filter looks like this: Do this:

      image37

      Click the drop-down arrow and select (Custom).

      image38

      Click the drop-down arrow and, according to the type of data in the column, select:

      • Text Filters > Custom Filter

      • Number Filters > Custom Filter

      • Date Filters > Custom Filter

    2. In the Filter based on field, select the condition’s type.

      • All
        All conditions must be met to include the data row in the filtered list. This is the same as the Boolean AND condition. This is the default value.

        This sample condition would include all records that are greater than or equal to 155 miles, AND less than 825 miles in the TotalMiles column.

        image39

      • Any
        At least one of the conditions must be met to include the record in the list. This is the same as the Boolean OR condition.

        This illustration shows a filter to return all rows when the shipper ID is BASBEA OR SELSAN.

        image40

    3. Select an operator.

    4. Enter or select the value or column name.

    5. Click OK.
      When you position your pointer over the filter row, the conditions you selected are displayed.

      image41

      image42

  1. To remove a row from the Custom Filter window, select the row, and then click Delete.
    No warning or confirmation message is shown.

  2. If you add more rows than can display in the window, a scroll bar appears. You can also increase the size of the window to display all condition rows without scrolling.

  3. You can select multiple conditions to delete.

    image43

  4. Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

Clearing filters

You can use the icons in the filter row to clear any applied filters in a particular column or the filters in all columns.

If the filter row is not visible, do the following:

  1. Right-click anywhere in the data grid.
    A shortcut menu opens.

  2. Select Show Filter Row.

  • To clear the filter criteria in a particular column, click the image44 Clear icon in the column.

    image45

  • To clear the filter criteria in all the columns, click the image44 Clear All icon in the upper-left corner of the grid.

    image46

Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.

Hiding the filter row

If you no longer want to use it, you can hide the filter row.

  1. Right-click anywhere in the data grid.
    A shortcut menu opens.

  2. Select Hide Filter Row.

  1. If you filter the data and then hide the filter row, the restriction is still applied. Only those data rows that met your conditions are displayed in the grid.

  2. Changes you make to the display remain in effect only until you close the window. To keep them, save the modified layout.