How to configure Set Query and Use Advanced Formula
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 two 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 Advanced Query section at the end of this page for more details on this advanced query option.
Tools that use Set Query
This powerful each option is available on the following tools:
-
Report prompts: From File > Reports, double-click on a report name to open the Print and Report Options page. Click on the Set Query button as shown below:
Note that all advanced queries within a report use different syntax than all other query examples in this list. Report queries use the advanced Crystal Reports syntax. Review 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 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 after the first option for "Check this box to change existing data along with the folder defaults" is enabled.
Review Edit Defaults, Filter Down Data, and Globally Change Data for details on the filter down process. -
Purchasing query: Select Inventory > Purchasing to open the following dialog. Click the Query button in the middle of the page.
Review Inventory Purchasing Page for more details on this dialog. -
App queries: Go to Labor > Workers and double-click on a worker name to open the worker record. Go to the worker record App Settings tab, select an app from the list, and click the Properties button.
-
MyDispatch: From MyDispatch Properties, go to the Set Query button.
Review MyDispatch > EBMS User Configuration for details on the MyDispatch Properties. -
MyTime: From MyTime Properties, go to the Set Query button.
Review MyTime Desktop Settings for steps to set up queries in the MyTime app.
-
-
Recurring Billing Process: Go to Sales > Recurring Billing and select the Query option.
Review Recurring Billing Process for details on this billing process. -
Batch Processing query: Go to Sales > Batch Processing and select Queries on the top of the page. Multiple query buttons are used to query the batch.
Review Batch Billing Overview for details on configuring the invoice queries.
The Task dialog also has query options, but the setup methods are different than the standard Set Query dialog. Review Creating Task Queries for details on creating task queries within the task module.
The set query option consists of multiple levels:
Use the Set Query
Click on the Advanced, Query, or 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. (Database Field, Operator, Value, And/Or) 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. Review all available database field names 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 directly compare the City field with a value of Lancaster, select the operator "is exactly" or "contains." The operator tells the query how to filter information. 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 must 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: (Leave empty. This column is unnecessary 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 vice 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:
- Database Expressions: Review Database Expressions for database expression syntax and functions.
-
Crystal Reports 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 dialogs but CANNOT be copied between a dialog and a report. For example, an advanced formula can be copied between EBMS tasks and worker record App Settings tab but CANNOT be copied between the task query in EBMS and a task report.