Using Query Builders to populate Grid Lists

Query Builder functionality is available on all browse windows and lookup lists in On Key. This functionality enables you to do the following:

You can access the Query Builder by clicking the Query Builder tab in any browse window or lookup list.

How to... add columns to a browse window or lookup list grid

  1. Open the Query Builder for the browse window or lookup list.

  2. In the Columns pane, expand the hierarchical structure as required, and then select the check box next to the column you want to include in the grid.

  3. Click the Apply button at the bottom of the Query Builder to add the selected columns to the grid window.

How to... add a filter to a browse window or lookup list grid

  1. Open the Query Builder for the browse window or lookup list.

  2. In the Columns pane, select the column that you want to apply the filter on, and then click the Add () button to add the select column to the Filter Criteria pane.

  3. Complete the following fields for the filter criteria:

    Field

    Description

    Operator

    Select an operator. Possible operators include:

    • is equal to
    • Is not equal to
    • Is smaller than
    • Is smaller than or equal to
    • Is greater than
    • Is greater than or equal to
    • Is like
    • Is not like
    • Is null
    • is not null

    Notes:

    • Null refers to values that are blank or empty.
    • The 'like' operators require the use the '%' wildcard. These operators are used to specify a portion of the filter value. For example, if you want to add a filter that only displays staff members with first names that start with 'Ab', use 'Ab%' as the filter value.

    User Formula - Date-based columns only

    Enables you to specify whether you want to use a dynamic date range filter. A dynamic date range filter lets you specify a custom date or time range using an offset from the current date and time.

    If you select this check box, you must provide the filter value using the following formula: Now() +/- Days/Hours(x)

    • Example 1: To filter records for the previous two weeks (14 days), use  Now()-Days(14)
    • Example 2: To filter records for the previous 36 hours, use Now()-Hours(36)
    • Example 3: To filter records for the next 3 days, use Now()+Days(3)

    Value

    Specify the value that you want to use to filter the data.

    Notes:

    • The 'like' operators require the use the '%' wildcard. For example, if you want to add a filter that only displays staff members with first names that start with 'Ab', use 'Ab%' as the filter value.

    State

    Depending on the user’s access rights the possible states are:

    • User Compulsory; set for all users.
    • User Defined; selected by a user on an ad hoc basis

  4. Click the Apply button at the bottom of the Query Builder to apply the filter.

© 2016 Pragma