The query dialog consists of the following four columns:A query is used to limit the number of selections in a given list. An example would be to list only the customers in the state of Pennsylvania. This is accomplished by querying on the state entry to only allow those with the value of "PA". Queries are useful both in search lists and reports. EBMS uses the same query engine for both functions.
List Example
Select Sales > Customers. A customer lookup list appears on the window. To query from the entire list, select the All option on the lookup list dialog or select the folder of customers you want to display. Press the query button and the following Set Query dialog will appear.
The query dialog consists of the following four columns:
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.
Complete the following steps to identify the database name of any field within a window:
1. Go to the window that displays the specific information such as a customer window.
2. Right click on the desired field within the window and press the <Shift> and <Ctrl> buttons while selecting the What’s This? Option on the context menu.
3. The What’s This dialog will show the database File and Field names.
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 data that the field is compared to. If the information compares to the value according to the operator, the record will be listed; otherwise it will not.
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).
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 then set the And/Or entry to And. If the field is required to only match any of the list of values then set the And/Or entry to Or. Note that if you are using a combination or 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.
The Use Advanced Formula option is used to do advanced queries. Enter a query using the steps listed above and then click the Use Advanced Formula button. 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.
Queries can be used within reports as well. Click on the Set Query button on any print dialog to set a query within a report.