The Set Query tool will filter a list based on almost any values within the database. One or more user defined conditions must be true for the record to be included within the queried list. The flexibility of the query operators allows the user to define the content of various look up lists, reports, and other queried records within EBMS. There are two2 levels of query options within EBMS:
Set Query dialog: See instructions below for this recommended query tool that is easy to use and contains universal settings throughout EBMS.
Advanced query: This advanced tool is recommended for more flexibility than the Set Query option. Reference the section at the end of this page for more details on this advanced query option.
This powerful each option is available on the following tools:
Report prompts: Click
on the Set Query button as
Note that all advanced queries within a report use different syntax than queries all other examples in this list. Report queries use the advanced Crystal syntax.
Review Reports > Printing a Report for more information on the report prompt dialog.
Lookup lists such as customer,
vendor, product, worker, and other lists: Select the Find function within any list and
then click on the Advanced
option within the Find window:
Review Features > Find Button within a List Dialog for details on the Find button search.
Filter down data process: EBMS
includes a Filter Down option within the
Set Defaults feature. The Advanced
button appears (2) if the top change existing
data option (1) is enabled.
Review Features > Edit Defaults, Filter Down Data, and Globally Change Data for details on the filter down process.
Purchase dialog query: Select
Inventory > Purchasing to open the following dialog:
Review Purchasing > Inventory Purchasing Dialog for more details on this dialog.
App queries: Select an app on theand click Properties.
MyDispatch: Set Query button
Review [Labor] MyDispatch > Setup > EBMS User Configuration for details on the MyDispatch Properties.
MyTime: Set Query button
Review [Labor] MyTime > Setup > MyTime Desktop Settings for steps to setup queries in MyTime
Recurring Billing Process:
Review [Sales] Recurring Billing > Process for details on this billing process.
Batch Processing query: Multiple
query buttons are used to query the batch.
Review [Sales] Invoices > Batch Processing for details on configuring the invoice queries.
The task dialog also has query options but the setup methods are different than the Set Query dialog. Review [Labor] Tasks > Customizing Task Views > Creating Queries for details on creating task queries within the task module. The set query option consists of multiple levels:
Clock on the Advanced, Query,
Set Query button on any of the tools listed
above to open the following Set Query
The query dialog consists of the following four columns as shown above. Review the following sections for details on each setting.
Reference the last section on this page for details on the Use Advanced Formula option.
This column is used to set the name of the field in the database that needs to be compared with the value. You can review all the database field names available by clicking the down arrow to the right of the database field entry. The technical database field can be found by completing the steps explained in EBMS Tables and Fields.
The Tree_ID Database Field should be used when queuing categories or folders.
Select the desired Operator. For example, to compare the City field with a value of Lancaster you would select the operator "is exactly" or "contains." Click on the down arrow to list all available operators.
The Value is the comparison data. If the database information compares to the Value according to the operator, the record will be listed; otherwise, it is filtered.
NOTE: The information inserted within the Value field is case sensitive. The query will only list information that is in the exact same case (upper-case or lower-case).
Records can be queried by categories or folders by selecting the Tree ID Database Field option as shown below:
The Value must contain the folder's Tree ID value found on the folder. This
value MUST be prefixed with spaces to create a 5 character Value. For
example, the 3 digit number but be prefixed with 2 spaces. Open
the folder properties to find the Tree ID value
as shown below:
This column does not need to be set if the query consists of only one line. If the query consists of multiple lines and you require the record to match every value, set the And/Or entry to And. If the field is required to only match any of the list of values,set the And/Or entry to Or. Note that if you are using a combination of Ands and Ors in a particular query, the advanced formula option is required.
Database field: STATE
Operator - is exactly
Value - PA
And/or - Ignore this column for this query
Will list only the customers within the state of Pennsylvania.
Database field - TYPE
Operator - contains
Value - RT
And/Or - And (go to next line)
Database field - L. Name
Operator - Starts with
Value - D
All customers in which the type field contains "RT" and the Last name starts with the letter D will be listed. By using the operator "contains," the type field can be used to query a number of different groups of customers. If John Doe's type contains "RT," "WH2," and "WH3," then John is listed in the query.
The Use Advanced Formula option located at the bottom of the Set Query dialog is used to configure advanced queries as shown below.
The advanced formula is more flexible than the Set Query options. Enter
a query using the steps listed above and then click the Use
Advanced Formula button to view the equivalent database expression.
Note that the syntax between this code is not compatible so a database
expression cannot be copied between a dialog and a report or vise versa.
Notice that the query becomes an expression. Insert parentheses, multiple fields, or equations into the expression area. When completed, click on the Check Formula button to verify the expression. EBMS uses the following code types for advanced formula expressions:
Crystal report syntax code: Review SAP Crystal Reports for information on Crystal syntax.
The following is the same Set Query code as shown above but using the Crystal syntax:
A user may wish to use the same query in multiple locations. An advanced formula can be copied between the dialogs but CANNOT be copied between a dialog and a report. For example an advanced formula can be copied between EBMS tasks and the Worker > App Settings but CANNOT be copied between the task query in EBMS and a task report.