Set Query Options

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:

1. Set Query dialog:  See instructions below for this recommended query tool that is easy to use and contains universal settings throughout EBMS.

2. 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.

Advanced Query Webinar

Set Query Insider Series

Tools that use Set Query

This powerful each option is available on the following tools:  

1. Report prompts:  Click on the Set Query button as shown below:
   
   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.  

2. 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.

3. 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.

4. Purchase dialog query:  Select Inventory > Purchasing to open the following dialog:
   
   Review Purchasing > Inventory Purchasing Dialog for more details on this dialog.

5. App queries:  Select an app on the Labor > Workers > App Settings tab and 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

6. Recurring Billing Process:  Query option
   
   Review [Sales] Recurring Billing > Process for details on this billing process.

7. 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:

Use the Set Query

Clock on the Advanced, Query, Set Query button on any of the tools listed above to open the following Set Query dialog:

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.

Database Field

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.

Operator

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.

Value

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:

And/Or

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.

EXAMPLES

Query:

Database field: STATE

Operator - is exactly

Value - PA

And/or - Ignore this column for this query

Result:

Will list only the customers within the state of Pennsylvania.

Query:

Database field - TYPE

Operator - contains

Value - RT

And/Or - And (go to next line)

Database field - L. Name

Operator - Starts with

Value - D

Result:

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.

Use Advanced Formula Option

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:

1. Database expressions:  Review Database Expressions for database expression syntax and functions.

2. 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:

Copying Queries

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.