Query
Written by Anthony Hodge
Updated at August 11th, 2025
Table of Contents
Request data from the source database in order to populate the action grid. If the data exists (based on the query criteria) it will be immediately loaded and displayed. Save queries to MyViews to be recalled instantly whenever you need to re-apply them.
A Query allows you to retrieve only the data you intend to work with. Of course the greater the volume of records requested by the query, the longer it will take to load the action grid (100 records would load much faster than 10,000). Once records are loaded into the action grid, they may be sorted, filtered, and sub-totaled for various types of analysis.
Queries provide access to the entire database, unlike Work-in-Process action grids (which are always pre-loaded by the system). More detailed information on this topic, is provide in the Action Grids overview.
Don't confuse "Query" with "Filter". They use similar criteria but perform completely different functions. A Query is used only to request and retrieve records from the database whereas a Filter is used only to narrow down the number of records that have already been loaded into the action grid.
Your Query criteria can also be recorded (along with Filter, Sort, and Column settings) to be invoked instantly via MyViews. See Saving to a View below.
Query Console
When you launch a Query-based action grid for the first time it will usually start out by displaying the query console. Unless you set up a default View to load data automatically, Query-based action grids remain empty until you compose and submit a query. You also have the option to perform a new query to reload the action grid at any time. To invoke the query console from the action grid menu, select the Preferences tab, then click 
.
|
|
The query console is used to define criteria for populating the action grid with data. For most Query-based action grids, this console is designed to retrieve transaction records (orders, receipts, probills, etc.). Each field in the console corresponds to a column in the action grid. Enter search criteria into these fields to request data from the database.
|
|
|
Submits query and populates the grid with data from the database. |
|
|
Submits query and adds the results to the currently-loaded data (if any). |
|
|
Clears all fields of query criteria. |
Composing a Query
- The colored fields at the beginning of the console have been optimized to produce quicker results when accessing the database. If you compose a query using other columns, retrieval will be much slower (depending on the criteria and/or the size of the database.)
- All previous query criteria is retained in the console until overwritten or cleared via the
button. - The Time to Load value (in the action grid Information Bar) indicates how long it took to retrieve data from the database.
- This type of query is acceptable to be saved in a View to be invoked instantly via MyViews. See Saving to a View below.
Note: There is a slightly different console for retrieving inventory records (product numbers, lot sets, conveyances, etc.), described below.
Data Types
The criteria entered must relate to the data elements of the column specified:
| Text | Most data elements in the grid are defined as alphanumeric strings. Text data can be in upper and lowercase, but filter results will not be case sensitive. Examples include names, addresses, descriptions. |
| Numbers | Numeric data elements represent quantities, percentages, and money. Columns containing numeric values (weight, quantities, etc) are displayed in green, and they always show the total value at the bottom of the grid. Examples include weights, counts, fuel charge, revenue. |
| Dates | Dates are displayed in the format "yyyy-mm-dd". Examples include ship date, expiry. |
Variable Dates
For date columns you may enter specific dates from the calendar popup, but you also the option to select variable dates from the variable date dropdown; e.g., Today, Next Day, etc.
Selecting the variable date option lets you define criteria that will change based on the current date. It is most useful for saving a query to a View, particularly when you need refreshed data every time the View is to be invoked. For example, selecting the Today variable as your From Date will always base the start point of the range to whatever "today's date" is. The different types of date variables are listed below:
| Last Day (Yesterday) | One day before the current date. |
| Today | The current date |
| Next Day (Tomorrow) | One day after the current date. |
| Next Sunday or Today | Next coming Sunday (or if the current date is a Sunday). |
| Next Monday or Today | Next coming Monday (or if the current date is a Monday). |
| Next Tuesday or Today | Next coming Tuesday (or if the current date is a Tuesday). |
| Next Wednesday or Today | Next coming Wednesday (or if the current date is a Wednesday). |
| Next Thursday or Today | Next coming Thursday (or if the current date is a Thursday). |
| Next Friday or Today | Next coming Friday (or if the current date is a Friday). |
| Next Saturday or Today | Next coming Saturday (or if the current date is a Saturday). |
| Last Calendar Month Beginning | First day of the previous month; e.g., June 1st, if the current month is July. |
| Last Calendar Month Ending | Last day of the previous month; e.g., June 30th, if the current month is July. |
| This Calendar Month Beginning | First day of the current month. |
| This Calendar Month Ending | Last day of the current month. |
| Next Calendar Month Beginning | First day of the following month; e.g., August 1st, if the current month is July. |
| Next Calendar Month Ending | Last day of the following month; e.g., August 31st, if the current month is July. |
| Last Calendar Year Beginning | First day of the previous year; e.g., January 1st, 2012, if the current year is 2013. |
| Last Calendar Year Ending | Last day of the previous year; e.g., December 31st, 2012, if the current year is 2013. |
| This Calendar Year Beginning | January 1st of the current year. |
| This Calendar Year Ending | December 31st of the current year. |
| Next Calendar Year Beginning | First day of the following year; e.g., January 1st, 2014, if the current year is 2013. |
| Next Calendar Year Ending | Last day of the following year; e.g., December 31st, 2014, if the current year is 2013. |
| Last Fiscal Period Beginning | First day of the previous fiscal period (month) based on datesset in GL90. |
| Last Fiscal Period Ending | Last day of the previous fiscal period (month) based on date set in GL90. |
| This Fiscal Period Beginning | First day of the current fiscal period (month) based on date set in GL90. |
| This Fiscal Period Ending | Last day of the current fiscal period (month) based on date set in GL90. |
| Next Fiscal Period Beginning | First day of the following fiscal period (month) based on date set in GL90. |
| Next Fiscal Period Ending | Last day of the following fiscal period (month) based on date set in GL90. |
| Last Fiscal Year Beginning | First day of the previous fiscal year based on date set in GL90. |
| Last Fiscal Year Ending | Last day of the previous fiscal year based on date set in GL90. |
| This Fiscal Year Beginning | First day of the current fiscal year based on date set in GL90. |
| This Fiscal Year Ending | Last day of the current fiscal year based on date set in GL90. |
| Next Fiscal Year Beginning | First day of the following fiscal year based on date set in GL90. |
| Next Fiscal Year Ending | Last day of the following fiscal year based on date set in GL90. |
Note: The Variable Date option does not exist in versions prior to VL 13.010.
Operators / Wildcards
The following special characters are used for building text and numeric query criteria:
| Symbol | Data Type Usage | Description | ||
| String | Numeric | Date | ||
| / | Yes | No | No | All column entries that contain text/strings |
| - | Yes | No | No | All column entries that contain no text/strings |
| < | No | Yes | No | Less than value |
| > | No | Yes | No | Greater than value |
| <= | No | Yes | Yes | Less than or equal to value |
| >= | No | Yes | Yes | Greater than or equal to value |
| + | No | Yes | No | Joins two numeric evaluations |
| ; | Yes | No | No | Denotes OR function for multiple text/strings |
| * | Yes | Yes | Yes | Denotes a wildcard. Matches all values. |
Note: In versions prior to VL 13.010, only the * asterisk wildcard is permitted. See Legacy VL.
Query Tips
You only need to define criteria for the columns you are interested in. The more columns you query, the more refined (specific) your results will be. Most of the time you can get the results you want based on the contents of a single column. Note that the colored fields at the beginning of the console have been optimized to produce quicker results when accessing the database. If you compose a query using multiple columns, retrieval will be much slower (depending on the criteria and/or the size of the database.)
Following are a few pointers and examples to get you started creating and invoking filters.
Practice Makes Perfect
If no records are found when you submit a query don't immediately assume that you have made a mistake. It could be that your criteria is invalid for the column selected, but it could also mean that there simply aren't any matching records. Just try another search using different criteria or a different column.
Once You Perfect a Query
When you finally get the results you want, it might be a good idea to record your query criteria to a View so you won't need to retype it over and over again. See Saving to a View below.
Matching Text / Strings
When evaluating text, the query reads from the left. Text criteria can be upper and/or lowercase.
| Wal* |
To retrieve records based on column entries that begin with a particular word or phrase simply type the text you want to match followed by an asterisk. Usually you only need to enter the first few letters and the query will find all records that have matching entries within the column selected. For example, "COS*" will find "COSTCO Dallas", "COSTCO Monroe TWSP", "COSTCO College Park", etc.
| / |
Enter a single / slash to select only those records that contain some data within the column selected.
| - |
Enter a single - dash to select only those records that contain no data within the column selected.
| Grec;4 Seas;Roma/Vistar |
To retrieve records based on column entries that match more than one word or phrase, separate the criteria using a ; semi-colon. Usually you only need to include the first few letters of each word or phrase and the filter will find all records that have matching entries within the column selected. For example, the expression "Grec;4 Seas;Roma/Vistar" returns all instances of "Greco & Sons Inc", "4 Seasons Distribution", and "Roma/Vistar".
| *Vistar |
To retrieve records based on column entries that end with a particular word or phrase, place an asterisk before the text you want to match. For example, "*Vistar" will find all records that have matching entries that end with Vistar within the column selected.
| *Food* |
To retrieve records based on column entries that may include a particular word or phrase anywhere in the string, use two asterisks, one before and one after the text. For example "*food*" will find all records that have matching entries such as "A&B Foods", "SYSCO Food Service" or "Food Wholesalers" within the column selected.
Working with Numeric Values
If you are not searching for specific values, use numeric operators to define the range of numbers from which to select. For example, where X represents a number:
<X finds values less than X.
>X finds values greater than X
<=X finds values less than or equal to X
>=X finds values greater than or equal to X
Note: Although some text columns appear to contain a list of numbers (Orders, Probills, etc.) they are not numeric and will not accept numeric operators for filtering. Only the green columns in an action grid contain numeric values.
| 365 |
To retrieve records based on column entries that contain a specific number, simply type in the number you want to match. The filter will find all entries that match the number exactly.
| <10000 |
To retrieve records based on column entries that contain numeric values that are less than a certain number, type a < less than sign followed by the number. For example, "<10000" will find all records that contain values that are less than 10000 within the column selected.
| >10+<60 |
To retrieve records based on column entries that contain numeric values within a range, enter two expressions joined by a + plus sign. The two expressions represent the numbers at opposite ends of the range. For example, ">10+<60" will find all records that have entries that fall within the range 11-59 (greater than 10 and less than 60).
Date criteria can be used for matching a specific date or a date range. All dates are selected using the popup calendar and are displayed in the format "yyyy-mm-dd". Date ranges assume the format:
>= from date + <= to date
To find entries for a specific date, select the same date for both the From Date and To Date fields via the calendar popup.
| From Date | 2010-08-12 |
| To Date | 2010-08-12 |
To find entries that fall within a date range, select the From Date and To Date fields via the calendar popup.
| From Date | 2010-03-10 |
| To Date | 2010-03-15 |
For example if a filter on the "Ship Date" column was defined with afrom date of 2010-03-10 and a to date of 2010-04-15, it would find all records that shipped between March 10th and April 15th inclusive.
Of course, the Query lets you replace the actual date values with one of several types of variable dates; e.g., Today, Next Day, etc. See Variable Dates for more information.
Inventory Query Action Grids
There is a slightly different console (from the standard transaction query) for retrieving inventory records (product numbers, lot sets, conveyances, etc.). This type of query is described below.
|
|
Submits query and displays results organized by lot set. Each unique data element will be shown on a new row; i.e., the same product code may appear several times to display |
|
|
Submits query and displays results by merging records based on common components; e.g., expiry date. |
|
|
Invokes a secondary console for extending the query to product-specific criteria; e.g., size, color, batch, etc. Note that partial entries (via the asterisk wildcard) are not accepted in product-specific criteria. |
|
|
Clears all fields of query criteria. |
.png){kind=spacer}
Saving to a View
To save the current query criteria for future use, open the Preferences tab, select .png)
, then click the .png)
button. The View created will include any Filter, Sort, and Column settings used to define the currently-displayed action grid. For Query-based action grids, saving a View also includes the option to be invoked with or without preset query criteria. See MyViews for more information.
Note: This functionality does not exist in versions prior to VL 13.010. See Legacy VL.