Quick Filters

In Dispatch and Multi Mode Dispatch, you can use filters to determine the types of data that appear in the grids. If your setup allows, you can create your own filters.

Typically, you can filter with the help of specific fields on a window, like the one shown in this illustration.

Trip Filters window

If you select SQL Filter, you can write a SQL statement that performs the filter.

SQL Filter window with highlighted User SQL field content

On the filter window, a "Quick" Filter checkbox is now available.

Quick Filter checkbox highlighted at the top of the Filter window

Some of the major differences between Quick Filters and traditional SQL filters include:

  • Performance
    On average, Quick Filters perform significantly better than SQL filters. They provide more filter options while taking up less memory.

  • Language
    Quick Filters are written in (mostly) JSON and follow a different format than SQL filters. TruckMate contains a special Quick Filter Builder as well as an option to write your own JSON filters.

  • Usability
    At any time, you can activate multiple Quick Filters for one or more grids. You can also quickly change your filter data and view new results.

These topics describe how to use and create Quick Filters. If you need additional assistance, contact Trimble Transportation Support.

How Quick Filters appear in TruckMate grids

Currently, in Dispatch, Quick Filters are available in these grids:

Carriers

Load Request

Chassis

Misc Equipment

Containers

Power Units

Drivers

Trailers

Freight Bills

Trips

Currently, in Multi Mode Dispatch, Quick Filters are available in these grids:

Carriers

Misc Equipment

Chassis

Outbound

Containers

P&D

CrossDock

Pickups

Deliveries

Planning

Doors

Power Units

Drivers

Trailers

Inbound

Trips

Load Request

Yard

Quick Filters are also available in the Discounts/Minimums tab on the Vendor Information window in Rate Maintenance.

This illustration shows the Trips grid in Dispatch.

Sample Trips grid with active filter pane

Quick Filters appear in a pane to the right of the grid. In this illustration, there are six filters available.

Detail of the filter pane

Using Quick Filters

To add a Quick Filter to a grid, do either of the following:

  • In the Filter window, select "Quick" Filter and click Apply.

    Apply button highlighted in the Filter window

  • Right-click on the grid. In the shortcut menu, all available Quick Filters appear as individual options. Select the Quick Filter you want to use.

By default, a new Quick Filter is deactivated when it appears on the filter pane. To activate it, select the checkbox next to the filter title. You can activate as many or as few filters as you want.

Filter selected checkbox highlighted

Once the filter is activated, selecting filter options immediately affects what you see in the grid.

Depending on the filter setup, hovering over an option displays a ToolTip containing more information.

Filter with displayed ToolTip

To hide a filter, click Hide button Hide. To hide the entire filter pane, click Hide Filters.

Filter pane with filters hidden

Note that even when filters or the filter pane are hidden, activated filters remain active.

Filter pane with active filter reference

To redisplay the filter pane, click Show More Filters. To redisplay a hidden filter, click image12 Show.

To remove a filter from the pane, click image13 Remove.

Creating a Quick Filter

Instead of SQL, Quick Filters use (mostly) JSON. This illustration depicts a sample Quick Filter written in JSON. Note that there is a parameter that contains some SQL.

User SQL field containing JSON

To begin creating a Quick Filter:

  1. From any grid, open the Filter window.

  2. Select SQL Filter.
    The SQL Filter window opens.

  3. Select "Quick" filter.

    Quick filter button highlighted in SQL Filter window

    The contents of the SQL Filter window change as shown in this illustration.

    Quick Filter Builder window highlighted in SQL Filter window

From here, you have two options:

  • Add your own JSON code

  • Use the Quick Filter Builder to specify filter parameters that TruckMate can use to create JSON code

Add your own JSON

In the User SQL field, add JSON code. If you are not familiar with the basic concepts of JSON, this introduction may help.

Once you have finished, you can do either of the following:

  • Click OK. If there are no errors, your filter is saved and the original filter window redisplays.

  • Click Quick Filter Builder to see your parameters in individual fields. This may help you understand how TruckMate uses the JSON you entered. You can edit them further if you want.

Use the Quick Filter Builder

If you click Quick Filter Builder, the contents of the SQL Filter window change.

In the Main section, at least four fields appear. (If you are creating a filter for use in certain Multi Mode Dispatch freight grids, another field appears.)

Quick Filter Builder "main" header fields

At minimum, the fields that you must complete are:

  • Filter Caption

    This text appears at the top of the filter when it is applied to a grid. In this illustration, the filter caption is Checklist Quick Filter 1030.

    Highlighted filter title in filter pane

  • Behavior

    The way you want the Quick Filter to operate. You have these choices:

You then continue to build the filter by entering data in additional fields. These fields differ depending on the behavior you select.

Each individual filter that you create in this manner can be combined with other filters to create a multi-rule filter. You cannot create multi-rule filters in the Quick Filter Builder.

This illustration shows a completed Quick Filter in the Quick Filter Builder.

Completed Quick Filter Builder fields

Once you have finished building your filter, you can do either of the following:

  • Click OK. If there are no errors, the main Filter window reappears.

  • Click Save and Back to JSON Editor. This redisplays the original SQL Filter window. In the User SQL field, you can see the JSON corresponding to the entries you made. This example shows the JSON corresponding to the filter in the previous illustration.

    image20

    If you want to make changes, you can edit the contents of the field, or click Quick Filter Builder again.

    If you are satisfied with the filter, click OK. If there are no errors, the main Filter window reappears.

Multi Mode Dispatch freight bill grids

If you are creating a Quick Filter for any of these Multi Mode Dispatch grids:

  • CrossDock

  • Deliveries

  • Pickup

you must also select a filter level.

Filter Level field highlighted in Quick Filter Builder header

Regardless of the option you choose, a Fixed SQL field appears in the Quick Filter Builder. The information in the field differs depending on the option you choose.

Data Set

If you choose Data Set (the default), the Fixed SQL field displays information about the grid’s temporary table. If you apply this type of filter to a grid, you see results more quickly, but the grid refreshes more slowly.

Trimble recommends that you use the Data Set option if your grids typically contain 500 records or fewer.

Fixed SQL field displayed above Quick Filter Builder header containing temporary table SQL

Stored Procedure

If you choose Stored Procedure, the Fixed SQL field displays the stored procedure that TruckMate uses to populate the grid. If you apply this type of filter to a grid, you see results more slowly, but the grid refreshes more quickly.

Trimble recommends that you use the Stored Procedure option if your grids typically contain more than 500 records.

Fixed SQL field displayed above Quick Filter Builder header containing stored procedure SQL

Types of Quick Filters

Checklist

A checklist Quick Filter provides a list of filter options. There are two types of checklist Quick Filters you can create:

  • A fixed checklist Quick Filter uses a list of predefined options that you create.

  • A dynamic checklist Quick Filter uses SQL to extract a list of values from a database table.

Regardless of the type of filter, you must start with these steps.

  1. In the Filter Caption field, enter a title for the filter.

  2. In the Behavior field, select Checklist.

  3. In the Data Type field, select the data type of the field on which the filter will operate. Valid options for checklist Quick Filters are:

    • String
      Use this option for any field that supports alphanumeric characters, such as zones, short descriptions, and user fields.

    • Integer
      Use this option for any field that supports numeric values, such as trip number, pieces, and items.

    • Float
      Use this option for any field that supports decimal (or double) values, such as weight, cube, and volume.

  4. If you are creating a filter for certain freight bill grids in Multi Mode Dispatch, select a filter level.

Once you have completed these steps, several additional fields appear in the Quick Filter Builder.

image24

To complete your filter, add information in this order:

  1. Condition

  2. Lookup
    The process differs for fixed and dynamic checklists.

  3. Defaults (optional)

Condition

In the Condition section, select the name of the specific grid field that you want to filter on. You have two ways to do this.

  • Select Use Field and choose a field in the Field Name field.

    The fields available to you depend on the data type you selected.

    If you want, you can also type a field name. However, be aware that field names are prefixed by a table identifier, as shown in this illustration.

    image25

  • Select Use SQL and enter a SQL query. This option provides you with a little more flexibility.

    In your query, you can still only filter on a single field, and that field must match the data type you selected. You must identify this field with a question mark, as shown in this illustration.

    image24

    If you want, you can add more rules to your query. For example, suppose you want the filter to also display trips that have a status of Available and trips that have a trailer assigned. Your query would look like this:

Lookup for fixed checklists

In the Lookup section, select Use List. A grid displays.

For each filter option, enter information as indicated in this table.

Required: *

Column Description Maximum characters

Key *

Value used by TruckMate to look up records

25

If you have a value that is larger than 25 characters, create a dynamic checklist Quick Filter.

Caption

Value displayed on the Quick Filter if the column contains data. If you leave this column blank, the Key value is used.

50

Hint

Value displayed in a ToolTip that appears when you hold the mouse over a specific option. If you leave this column blank, nothing happens when the mouse is over the option

100

This illustration displays a completed list of filter options.

image28

Lookup for dynamic checklists

In the Lookup section, select Use Select SQL.

In the field that appears, enter SQL statements that will generate the list of available filter options dynamically. Follow the same structure that you would for creating a SQL filter.

For example, suppose the field being filtered on is T.CURRENT_ZONE and you want to create a list of current zones that are on active trips. The SELECT statement could look something like this:

image29

If you want to see a preview of the zones that would appear as options with this SELECT statement, select Use List again. The grid displays the applicable zones.

If you create a SQL statement and then preview the SQL results:

  • You must remember to reselect Use Select SQL to save the dynamic checklist Quick Filter. Otherwise, a fixed checklist Quick Filter is created.

  • You cannot change the values in the Caption and Hint columns.

Defaults

If you want, you can specify in the Value field one option that appears by default when the Quick Filter is in the filter pane. The option must be a value that:

  • is in your fixed list or

  • will be returned by your SELECT statement

image30

Examples

When you are satisfied with the filter, you can save it. Doing so displays the JSON corresponding to the selections you made.

Fixed checklist

This illustration shows a sample fixed checklist Quick Filter in the Quick Filter Builder.

image19

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters": [
    {
      "name": "P1",
      "caption": "QF-04: Fixed Checklist",
      "captionShort": "(Not Applicable)",
      "formatSql": "T.CURRENT_ZONE %P1",
      "behavior": "CheckList",
      "dataType": "String",
      "defaultValue": "BCTERM",
      "list":[
        {"val": "BCTERM","caption": "British Columbia Terminal","hint": "Main Terminal Zone"},
        {"val": "V6B 6G1","caption": "Vancouver, BC","hint": "V6B 6G1 Specifically"},
        {"val": "BCPORT","caption": "Port of Vancouver"},
        {"val": "V3G 1A1"}
      ]
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

Use Field or Use SQL

dataType

DataType

defaultValue

Value

list

Each Key/Caption/Hint record in the grid created when Use List was selected

Here is another sample fixed checklist Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters": [
    {
      "name": "P1",
      "caption": "Current Status",
      "formatSql": "STATUS %P1",
      "behavior": "CheckList",
      "dataType": "String",
      "list":[
        {"val": "AVAIL","caption": "Available","hint": "Driver is Available"},
        {"val": "ASSGN","caption": "Assigned","hint": "Driver has been Assigned"},
        {"val": "DISP","caption": "Dispatched","hint": "Driver has been Dispatched"},
        {"val": "UNAVL","caption": "Unavailable","hint": "Driver is Unavailable"}
      ]
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

Dynamic checklist

This illustration shows a sample dynamic checklist Quick Filter in the Quick Filter Builder.

image33

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters": [
    {
      "name": "P1",
      "caption": "QF-05: Dynamic Checklist",
      "captionShort": "(Not Applicable)",
      "formatSql": "T.CURRENT_ZONE %P1",
      "behavior": "CheckList",
      "dataType": "String",
      "defaultValue": "BCTERM",
      "listSql": "SELECT DISTINCT(CURRENT_ZONE) FROM TRIP WHERE ACTIVE_REC='True'"
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

Use Field or Use SQL

dataType

DataType

defaultValue

Value

listSql

SQL statements created when Use Select SQL was selected

Here is another sample dynamic checklist Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "Driver Checklist",
      "formatSql": "STATUS %P1",
      "behavior": "CheckList",
      "dataType": "String",
      "listSql": "SELECT DISTINCT(STATUS) FROM DRIVER"
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

Numeric

A numeric Quick Filter lets you filter on a specific numeric value and apply operators to that value. For example, you could filter on all trips that are carrying 1000 pieces or more.

  1. In the Filter Caption field, enter a title for the filter.

  2. In the Behavior field, select Numeric.

  3. In the Short Caption field, enter text that will appear in the filter field to describe the value that can be entered.

  4. In the Data Type field, select the data type of the field on which the filter will operate. Valid values for numeric Quick Filters are:

    • Integer
      This is the default option for numeric Quick Filters. It is for any field that supports numeric values, such as trip number, pieces, and items.

    • Float
      Use this option for any field that supports decimal (or double) values, such as weight, cube, and volume.

  5. If you are creating a filter for certain freight bill grids in Multi Mode Dispatch, select a filter level.

Once you have completed these steps, several additional fields appear in the Quick Filter Builder.

image36

To complete your filter, add information in this order:

  1. Condition

  2. Defaults (optional)

Condition

In the Condition section, select the name of the specific grid field that you want to filter on. You have two ways to do this.

  • Select Use Field and choose a field in the Field Name field.

    The fields available to you depend on the data type you selected.

    If you want, you can also type a field name. However, be aware that field names are prefixed by a table identifier, as shown in this illustration.

    image37

  • Select Use SQL and enter a SQL query. This option provides you with a little more flexibility.

    In your query, you can still only filter on a single field, and that field must match the data type you selected. You must identify this field with a question mark, as shown in this illustration.

    image38

    If you want, you can add more rules to your query. For example, suppose you want the filter to also display trips that have a status of Available and trips that have a trailer assigned. Your query would look like this:

    image39

Defaults

If you want, you can select the operator that appears by default when the Quick Filter is in the filter pane.

Operator Definition

=

(equals)

Filters on the exact value entered.

For example, if 40 is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is 40.

>=

(greater than or equal to)

Filters on the exact value entered and any values that are greater than the value entered.

For example, if 40 is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is 40 or greater.

<=

(less than or equal to)

Filters on the exact value entered and any values that are less than the value entered.

For example, if 40 is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is 40 or less.

Between

Filters on a range that spans two values entered.

For example, if 40 and 80 are entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is 40, 80, or a number between 40 and 80.

Depending on the operator selected, one or more Value fields appear.

image40

Enter a value or values that appear by default when the Quick Filter is in the filter pane.

Examples

When you are satisfied with the filter, you can save it. Doing so displays the JSON corresponding to the selections you made.

This illustration shows a sample numeric Quick Filter in the Quick Filter Builder.

image41

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters":{
    {
      "name": "P1",
      "caption": "QF-06: Numeric",
      "captionShort": "Weight Value",
      "formatSql": "T.PIECES %P1",
      "behavior": "Numeric",
      "dataType": "Integer",
      "defaultOperation": ">=",
      "defaultValue": "40"
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

Use Field or Use SQL

dataType

DataType

defaultValue

Value

defaultOperation

Operator

Here is another sample numeric Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters":{
    {
      "name": "P1",
      "caption": "Last Trip Completed",
      "captionShort": "Trip Number",
      "formatSql": "LAST_TRIP %P1",
      "behavior": "Numeric",
      "dataType": "Integer",
      "defaultOperation": ">=",
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

Date

A date Quick Filter lets you filter on a specific date or date range and apply operators to that value or range. For example, you could filter on all trips that occurred between November 1 and November 8 of this year.

  1. In the Filter Caption field, enter a title for the filter.

  2. In the Behavior field, select Date.

    By default, the value in the Data Type field is set to DateTime. This option covers all fields that contain date-based and/or time-based data. However, if you want to filter on date-based fields only, you can select Date as the data type.

  3. If you are creating a filter for certain freight bill grids in Multi Mode Dispatch, select a filter level.

Once you have completed these steps, several additional fields appear in the Quick Filter Builder.

image44

To complete your filter, add information in this order:

  1. Condition

  2. Defaults (optional)

Condition

In the Condition section, select the name of the specific grid field that you want to filter on. You have two ways to do this.

  • Select Use Field and choose a field in the Field Name field.

    The fields available to you depend on the data type you selected.

    If you want, you can also type a field name. However, be aware that field names are prefixed by a table identifier, as shown in this illustration.

    image43

  • Select Use SQL and enter a SQL query. This option provides you with a little more flexibility.

    In your query, you can still only filter on a single field, and that field must match the data type you selected. You must identify this field with a question mark, as shown in this illustration.

    image44

    If you want, you can add more rules to your query. For example, suppose you want the filter to also display trips that have a status of Available and trips that have a trailer assigned. Your query would look like this:

    image45

Defaults

If you want, you can select the operator that appears by default when the Quick Filter is in the filter pane.

Operator Definition (examples use mm/dd/yy date format)

Today

Filters on the exact date entered.

For example, if this operator is selected and 10/01/23 is entered, the filter looks for all records for which the value of the grid field specified in the Condition section is 10/01/23.

Yesterday

Filters on the date before the date entered.

For example, if this operator is selected and 10/01/23 is entered, the filter looks for all records for which the value of the grid field specified in the Condition section is 09/30/23.

Tomorrow

Filters on the date after the date entered.

For example, if this operator is selected and 10/01/23 is entered, the filter looks for all records for which the value of the grid field specified in the Condition section is 10/02/23.

Past Date

Filters on the exact date preceding the current date, based on the number entered.

For example, if this operator is selected and:

  • the current date is 10/01/23 * 3 is entered

the filter looks for all records for which the value of the grid field specified in the Condition section is three days earlier-in this case, 09/28/23.

Days Back

Filters on a range of dates starting with the current date and ending on an earlier date that is defined by the number entered.

For example, if:

  • the current date is 10/01/23

  • this operator is selected and

  • 3 is entered

the filter looks for all records for which the value of the grid field specified in the Condition section is 09/28/23, 10/01/23, or a date in between.

Start From Days Back

Filters on all dates starting from an earlier date that is defined by the number entered.

For example, if:

  • the current date is 10/01/23

  • this operator is selected and

  • 3 is entered

the filter looks for all records for which the value of the grid field specified in the Condition section is three days earlier than the current date-in this case, 09/28/23-and later. This includes records with dates that are past the current date, such as 10/02/23.

Future Date

Filters on the exact date following the current date, based on the number entered.

For example, if:

  • the current date is 10/01/23

  • this operator is selected and

  • 3 is entered

the filter looks for all records for which the value of the grid field specified in the Condition section is three days later-in this case, 10/04/23.

Days Forward

Filters on a range of dates starting with the current date and ending on a later date that is defined by the number entered.

For example, if:

  • the current date is 10/01/23

  • this operator is selected and

  • 3 is entered

the filter looks for all records for which the value of the grid field specified in the Condition section is 10/01/23, 10/04/23, or a date in between.

Start From

Filters on the date entered and all later dates.

For example, if 10/01/23 is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is 10/01/23 or later.

Date Range

Filters on a range of dates defined by the dates entered.

For example, if this operator is selected, and the dates 10/01/23 and 10/08/23 are entered, the filter looks for all records for which the value of the grid field specified in the Condition section is 10/01/23, 10/08/23, or a date in between.

Depending on the operator selected, one or more Value fields appear.

image48

Enter a value or values that appear by default when the Quick Filter is in the filter pane.

Examples

When you are satisfied with the filter, you can save it. Doing so displays the JSON corresponding to the selections you made.

This illustration shows a sample date Quick Filter in the Quick Filter Builder.

image49

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "QF-07: Date",
      "captionShort": "(Not Applicable)",
      "formatSql": "T.STATUS = 'AVAIL'
AND
L.LS_TRAILER IS NOT NULL
AND
T.PLAN_DEPART %P1",
      "behavior": "Date",
      "dataType": "DateTime",
      "defaultOperation": "pastDate"
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

Use Field or Use SQL

dataType

DataType

defaultValue

Value

defaultOperation

Operator

Here is another sample date Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "Planned Time of Availability",
      "formatSql": "DELIVER_BY %P1",
      "behavior": "Date",
      "dataType": "DateTime",
      "defaultOperation": "tomorrow"
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

Text

A text Quick Filter lets you filter on a text string that you specify. For example, you could filter on all trips that departed from a particular zone.

  1. In the Filter Caption field, enter a title for the filter.

  2. In the Behavior field, select Text.

    By default, the value in the Data Type field is set to String. This is the only valid value for text Quick Filters.

  3. In the Short Caption field, enter text that will appear in the filter field to describe the value that can be entered.

  4. If you are creating a filter for certain freight bill grids in Multi Mode Dispatch, select a filter level.

Once you have completed these steps, several additional fields appear in the Quick Filter Builder.

image52

To complete your filter, add information in this order:

  1. Condition

  2. Defaults (optional)

  3. Lookup (optional)

Condition

In the Condition section, select the name of the specific grid field that you want to filter on. You have two ways to do this.

  • Select Use Field and choose a field in the Field Name field.

    The fields available to you depend on the data type you selected.

    If you want, you can also type a field name. However, be aware that field names are prefixed by a table identifier, as shown in this illustration.

    image25

  • Select Use SQL and enter a SQL query. This option provides you with a little more flexibility.

    In your query, you can still only filter on a single field, and that field must match the data type you selected. You must identify this field with a question mark, as shown in this illustration.

    image24

    If you want, you can add more rules to your query. For example, suppose you want the filter to also display trips that have a status of Available and trips that have a trailer assigned. Your query would look like this:

Defaults

If you want, you can select the operator that appears by default when the Quick Filter is in the filter pane.

Operator SQL equivalent Definition

Equal

= 'VALUE'

Filters on the exact string entered.

For example, if BCTERM is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section is BCTERM.

StartWith

LIKE 'VALUE%'

Filters on strings that start with the string entered.

For example, if BCT is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section starts with BCT.

EndWith

LIKE '%VALUE'

Filters on strings that end with the string entered.

For example, if ERM is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section end with ERM.

Include

LIKE '%VALUE%'

Filters on strings that contain the string entered.

For example, if CTE is entered, and this operator is selected, the filter looks for all records for which the value of the grid field specified in the Condition section contains CTE.

In the Value field, you can enter a string that appears by default when the Quick Filter is in the filter pane.

image53

Lookup

If you want, you can use SQL to create a list of available filter options dynamically.

In the Lookup section, select Use Select SQL. In the field that appears, enter your SQL statements.

For example, suppose the field being filtered on is T.CURRENT_ZONE and you want to create a list of all terminal zones in the database. The SELECT statement could look something like this:

image54

Examples

When you are satisfied with the filter, you can save it. Doing so displays the JSON corresponding to the selections you made.

This illustration shows a sample text Quick Filter in the Quick Filter Builder.

image55

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "QF-08: Text",
      "captionShort": "Zone ID",
      "formatSql": "T.CURRENT_ZONE %P1",
      "behavior": "Text",
      "dataType": "String",
      "defaultOperation": "StartWith",
      "allowLookup": "True",
      "lookupSql": "SELECT ZONE_ID, SHORT DESCRIPTION
FROM ZONE
WHERE IS_TERMINAL = 'True'"
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

Use Field or Use SQL

dataType

DataType

defaultValue

Value

allowLookup

Lookup radio button selection

  • False
    No Lookup

  • True
    Use Select SQL

lookupSql

SQL statements created when Use Select SQL was selected

Here is another sample text Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "Search For Driver",
      "captionShort": "Driver ID",
      "behavior": "Text",
      "dataType": "String",
      "defaultOperation": "Equal",
      "allowLookup": "True",
      "lookupSql": "SELECT DRIVER_ID, NAME FROM DRIVER"
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

No Parameter

A Quick Filter that has "no parameter" is equal to a standard SQL filter.

  1. In the Filter Caption field, enter a title for the filter.

  2. In the Behavior field, select noParam.

  3. If you are creating a filter for certain freight bill grids in Multi Mode Dispatch, select a filter level.

Once you have completed these steps, a Condition section appears in the Quick Filter Builder.

In the field, enter a SQL query that the system will interpret as a WHERE clause. For example, suppose you want the filter to display trips that have a status of Assigned and have at least one trailer assigned. Your query would look like this:

image58

Examples

When you are satisfied with the filter, you can save it. Doing so displays the JSON corresponding to the selections you made.

This illustration shows a sample no-parameter Quick Filter in the Quick Filter Builder.

image59

The corresponding JSON looks like this:

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "QF-09: NoParam",
      "captionShort": "(Not Applicable)",
      "formatSql": "T.STATUS = 'ASSGN' AND L.LS_TRAILER1 IS NOT NULL",
      "behavior": "noParam"
    }
  ]
}

The data maps to Quick Filter Builder fields as shown in this table:

JSON mapping Quick Filter Builder field

name

n/a

TruckMate creates this value automatically.

caption

Filter Caption

captionShort

Short Caption

behavior

Behavior

formatSql

SQL statements created

Here is another sample no-parameter Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1",
  "parameters":[
    {
      "name": "P1",
      "caption": "Show Only Available Drivers",
      "formatSql": "STATUS = 'AVAIL' AND CURRENT_TRIP = 0",
      "behavior": "noParam",
    }
  ]
}

  1. Move your mouse over this code example. A Copy button appears in the upper right corner.

  2. Click Copy.

  3. In the SQL Filter window, paste the code into the User SQL field.

  4. Select "Quick" filter if it is not already selected.

  5. Click Quick Filter Builder to see the corresponding field value map.

Multi-Rule

If you want, you can combine two or more Quick Filters into a multi-rule Quick Filter. You have two options:

  • You can create each individual filter in the Quick Filter Builder. Once you are satisfied with each filter’s JSON, you can combine the code snippets and paste the result in the User SQL field on the SQL Filter window.

  • You can write the JSON yourself in the User SQL field on the SQL Filter window. Note that you cannot view or edit multi-rule filters in the Quick Filter Builder.

Example

Here is a sample multi-rule Quick Filter that you can try out in the Drivers grid.

{
  "formatSql": "%P1 OR %P2",
  "caption": "Driver Search and Availability",
  "parameters":[

      {
       "name": "P1",
       "caption": "Search For Driver",
       "captionShort": "Driver ID",
       "formatSql": "DR.DRIVER_ID %P1",
       "behavior": "Text",
       "dataType": "String",
       "defaultOperation": "Equal",
       "allowLookup": "True",
       "lookupSql": "SELECT DRIVER_ID, NAME FROM DRIVER"
      },

      {
       "name": "P2",
       "caption": "Planned Time of Availability",
       "formatSql": "DELIVER_BY %P2",
       "behavior": "Date",
       "dataType": "DateTime",
       "defaultOperation": "tomorrow"
      }

]
}

  1. Copy this JSON to the User SQL field in the SQL Filter window.
    Note: In this help topic, you can move your mouse over the upper right corner to display a Copy button.

  2. Select "Quick" filter if it is not already selected.

  3. Click OK.

Quick Filter benefit examples

Flexibility

Suppose you have this SQL filter for the Trips grid in Dispatch:

T.STATUS IN ('DISP', 'DEPARTDOCK', 'LOADEDTOGO')
AND
L.LS_LEG_STAT IN ('PLANNED', 'ACTIVE')
AND
T.PIECES BETWEEN 25 AND 45
AND
T.CURRENT_ZONE IN (SELECT SUBZONE FROM FASTZONE WHERE ZONE_CODE = 'BC')

When applied, the grid displays records for which all of these are true:

  • Trip status is DISP, DEPARTDOCK, or LOADEDTOGO

  • Leg status is PLANNED or ACTIVE

  • Pieces count is between 25 and 45

  • Current zone is anywhere in British Columbia

If you wanted more specific results-for example, a subset of zones in British Columbia-you would need to change the filter or create a new one.

Instead, you could create all the following:

  • A checklist Quick Filter for trip status. You could create:

    • A fixed checklist that uses the specific values (DISP, DEPARTDOCK, LOADEDTOGO), or

    • A dynamic checklist that includes all dispatch, depart dock, or assignment behaviors

  • A dynamic checklist Quick Filter for leg status

  • A numeric Quick Filter for the pieces count. You could restrict valid values to between 25 and 45, or set it to accept any numeric value.

  • A text or no-parameter Quick Filter for the current zone

    • With a text filter, you can specify the exact zone(s) you want to see.

    • With a no-parameter filter, you can see all trips in British Columbia if you want.

The resulting filter pane could look like this:

image64

You still have access to all the rules in the original SQL filter, with additional flexibility to filter on other criteria.

This flexibility also lets you get rid of the SQL filter, which is one less thing to maintain in your environment.

Fewer filters, less maintenance

To accommodate as many different users' needs as possible, you may find you have too many SQL filters in your environment. With Quick Filters, you may be able to decrease your total filter count by at least 60 percent.

Suppose you have multiple SQL filters that involve a date parameter of some kind, such as Pick Up By, Deliver By, or Planned Departure. Each one has a specific purpose:

  • WHERE DELIVER_BY = CURRENT_TIMESTAMP

  • WHERE DELIVER_BY = CURRENT_TIMESTAMP + 1 DAYS

  • WHERE DELIVER_BY = CURRENT_TIMESTAMP + 2 DAYS

You can create a single Quick Filter that covers all of these and more.

Fewer filters mean less maintenance, less testing, an easier learning curve for new users, and more speed and efficiency for experienced users.

Fewer custom screen designs

In some environments, users have taken advantage of TruckMate’s screen design features to provide easy access to commonly-used custom SQL filters. This can create overly complex interfaces, as shown in this example. Each button represents an individual SQL filter.

image65crop

Quick Filters can replace most of these filters, improving application usability and performance.

Error messages

This table lists some of the common error messages you may find when using or creating Quick Filters.

Error message Filter(s) affected Cause

Please Enter the Caption.

All

Filter Caption field is blank

Behavior is not defined

All

Behavior field is blank

Either Select SQL or List of Values should be specified for CheckLists

Checklist

Either of the following:

  • Use List was selected, but no list values were created

  • Use Select SQL was selected, but no SQL statements were added

No authorized routine named "IN" of type "FUNCTION" having compatible arguments was found.

Checklist

Either of the following:

  • Use List was selected, but the Field Name field is blank

  • Use Select SQL was selected, but:

    • no field was identified in the SQL as the field to filter on

    • a field was added, but the question mark was missing

The data types of the operands for the operation "IN" are not compatible or comparable.

Checklist, Numeric

The value in the Data Type field is not supported for the specified filter behavior.

An unexpected token "END-OF-STATEMENT" was found following "BAD SQL". Expected tokens may include: "JOIN <joined_table>".

Checklist

Use Select SQL was selected, but an error exists in the SQL statements that were added.

An unexpected token "=" was found following "STAMP - 1 YEAR AND". Expected tokens may include: "<space>".

Numeric, Text

Either of the following:

  • Use List was selected, but the Field Name field is blank

  • Use Select SQL was selected, but:

    • no field was identified in the SQL as the field to filter on

    • a field was added, but the question mark was missing

A number is assigned to a VARCHAR column.

Numeric

The value in the Data Type field is String, which is not supported for numeric Quick Filters.

Text

The value in the Data Type field is Integer or Float, which are not supported for text Quick Filters.

An unexpected token "TRUNC" was found following "YEAR AND BETWEEN". Expected tokens may include: "<space>".

Date

Either of the following:

  • Use List was selected, but the Field Name field is blank

  • Use Select SQL was selected, but:

    • no field was identified in the SQL as the field to filter on

    • a field was added, but the question mark was missing

The data types of the operands for the operation "BETWEEN" are not compatible or comparable.

Date

The value in the Data Type field is not supported for the specified filter behavior.

The syntax of the string representation of a datetime value is incorrect.

Text

The value in the Data Type field is Date, which is not supported for text Quick Filters.

An unexpected token "SQL" was found following "P - 1 YEAR AND BAD". Expected tokens may include: "END-OF-STATEMENT".

No Parameter

An error exists in the SQL statements that were added.