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.
If you select SQL Filter, you can write a SQL statement that performs the filter.
On the filter window, a "Quick" Filter checkbox is now available.
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.
Quick Filters appear in a pane to the right of the grid. In this illustration, there are six filters available.
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.
-
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.
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.
To hide a filter, click Hide. To hide the entire filter pane, click Hide Filters.
Note that even when filters or the filter pane are hidden, activated filters remain active.
To redisplay the filter pane, click Show More Filters. To redisplay a hidden filter, click Show.
To remove a filter from the pane, click 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.
To begin creating a Quick Filter:
-
From any grid, open the Filter window.
-
Select SQL Filter.
The SQL Filter window opens. -
Select "Quick" filter.
The contents of the SQL Filter window change as shown in this illustration.
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.)
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.
-
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.
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.
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.
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.
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.
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.
-
In the Filter Caption field, enter a title for the filter.
-
In the Behavior field, select Checklist.
-
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.
-
-
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.
To complete your filter, add information in this order:
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.
-
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.
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.
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:
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:
|
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
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
Use Field or Use SQL |
|
DataType |
|
Value |
|
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"}
]
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
Use Field or Use SQL |
|
DataType |
|
Value |
|
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"
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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.
-
In the Filter Caption field, enter a title for the filter.
-
In the Behavior field, select Numeric.
-
In the Short Caption field, enter text that will appear in the filter field to describe the value that can be entered.
-
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.
-
-
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.
To complete your filter, add information in this order:
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.
-
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.
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 | 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.
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
Use Field or Use SQL |
|
DataType |
|
Value |
|
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": ">=",
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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.
-
In the Filter Caption field, enter a title for the filter.
-
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.
-
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.
To complete your filter, add information in this order:
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.
-
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.
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 | 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 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 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 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 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 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.
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
Use Field or Use SQL |
|
DataType |
|
Value |
|
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"
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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.
-
In the Filter Caption field, enter a title for the filter.
-
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.
-
In the Short Caption field, enter text that will appear in the filter field to describe the value that can be entered.
-
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.
To complete your filter, add information in this order:
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.
-
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.
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 |
|
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 |
|
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 |
|
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 |
|
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.
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:
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
Use Field or Use SQL |
|
DataType |
|
Value |
|
Lookup radio button selection
|
|
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"
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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.
-
In the Filter Caption field, enter a title for the filter.
-
In the Behavior field, select noParam.
-
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:
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.
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 |
---|---|
|
n/a TruckMate creates this value automatically. |
|
Filter Caption |
|
Short Caption |
|
Behavior |
|
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",
}
]
}
-
Move your mouse over this code example. A Copy button appears in the upper right corner.
-
Click Copy.
-
In the SQL Filter window, paste the code into the User SQL field.
-
Select "Quick" filter if it is not already selected.
-
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"
}
]
}
-
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. -
Select "Quick" filter if it is not already selected.
-
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:
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.
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:
|
No authorized routine named "IN" of type "FUNCTION" having compatible arguments was found. |
Checklist |
Either of the following:
|
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:
|
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:
|
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. |