Query editor

The standard Query editor is a general-purpose editor that can be used to create simple or compound queries. You can create compound searches linked with the And, Or, or Except conjunctions. For example, you can use the Query editor to perform a query for all employees who are over 60 years old or who have an income in excess of $45,000.

You have the choice of searching through the current selection of records or all the records in the table. The other three search methods always search the entire table. You can save queries to disk and open them when you want to repeat the query. The Query editor remembers your last query. You can edit the query or clear it and enter a new query. You can search in fields of the current table as well as fields of related tables. 

The Query editor contains the following areas:

• Criteria area: This area displays the query as you create it or after you load it from a disk file.
• Available Fields menu: This menu allows you to select the table or tables from which you want to display fields in the Fields list. You can display fields from the Master table, the Related tables, or All tables.
• Fields list: This area displays a hierarchical list of the fields in the selected table or tables. Indexed fields are shown in boldface.
• Comparison Operator area: This area displays a list of comparison operators.
• Value area: You enter the value for which you want to search in this area.
• Conjunction buttons: This area contains three buttons that correspond to conjunction operators you can use to join the current simple query to the previous simple query.
• Query in selection button: This button performs the query only on the records in the current selection.
• Query editor buttons: You use this area to save your queries, load other queries from disk, cancel the query, or execute the query.

To create a query in the standard editor:

1. In the Design environment, choose the subcommand Query > Query... from the Records menu, or click on the “Query” button in the tool bar.
   4D displays the Query editor, highlighting the first row of the Criteria area. You can resize the query editor window by clicking on the lower right corner.
2. Select the table(s) whose fields you want to use for the query.
   You can display the Master Table, the tables related to it or all the tables.
   
   When you select Related Tables or All Tables, an icon appears to the left of the names of related fields or tables (+ symbol inside a square under Windows, arrow under Mac OS). Clicking on this icon will expand the list of fields for the table.
3. Click on the name of the field to be used in the search condition.
   4D displays in the criteria area the field name preceded by the name of the table to which it belongs.
   The comparison operators area is highlighted. By default, 4D places the “is equal to” comparison operator after the field name.
4. If you want to use another comparison operator instead, simply click on the one desired.
   4D places it next to the field name in the criteria area.
   .
5. Type the value you want to search for in the Value area.
   In a Text or Alpha field you can use the wildcard character (@) at the end of the value to request a “Begins with” search.
   If the field you selected is associated with a choice list, 4D displays the list and prompts you to select a value. If the field you selected is a Boolean field, 4D displays a pair of radio buttons.
6. If you want to add another simple query, click Add Line.
   4D adds a new line using the “And” conjunction operator. If you want to add a line among those already created, use the Insert Line button. 4D then duplicates the contents of the selected row.
7. If you want to use the “Or” or “Except” operator, click the desired conjunction operator button.
   By default, when you add a line, 4D places the And operator in the corresponding area.
   
8. Repeat steps 2 through 4 to build the second simple query.
   When you build a compound query, 4D evaluates the simple queries in the order in which they appear in the Query editor (i.e., from top to bottom). There is no precedence among the conjunctions. In other words, And does not have priority over Or. Therefore, if you use more than two simple queries when building the compound query, the order in which you enter the simple queries can affect the results of the query.
   As you build the compound query, you can modify existing parts of the query by clicking the line you want to change and clicking a new field or operator, or typing a new value.
   You can remove a simple query by selecting the line and clicking Del Line. In a compound search condition, you can remove all the lines by clicking Clear All. Be careful, deleting lines cannot be undone. 
9. (Optional) To save the query to disk, click the Save button and enter a filename in the create-file dialog box
   For more information, refer to the next section. You do not need to save your query to perform the search.
10. Click Query to search the entire table.
    OR
    Click Query in Selection to restrict the query to the current selection.

If you perform the same query often, you may want to save it to disk. When you save queries to disk, you only need to create them once. In subsequent uses of the Query editor, you can simply load the desired query from disk and click Query or Query in Selection to perform it.To save a query to disk, click Save... in the query editor after specifying your criteria. 4D displays a standard Save file dialog box where you can enter a file name and choose its location on the disk. The file extension for 4D queries is “.4DF”.

To load a saved query, simply click Load... in the Query editor and select the query file (extension “.4DF”). 4D loads your query into the Query editor. When you load a file, it replaces any query that previously appeared in the Query editor.

When you write a query, you tell 4D how to compare the value you specify to the contents of the database. For example, the query, “Last Name equals ‘Smith’” uses the “is equal to” comparison operator. It tells 4D to compare the values in the Last Name field to the string “Smith.”

Comparisons involving alphanumeric values are not case-sensitive. A search on the last name “Smith” will find records containing “smith,” “SMITH,” “sMith,” and so on.

Queries using the Contains and Does Not Contain operators are always sequential queries.

The Contains Keyword operator is available for fields of the Alpha and Text type only.

For more information about this type of query, refer to Comparison Operators in the 4D Language Reference manual.

To make queries easier to specify, 4D has a wildcard character (@) that can replace one or more characters in a search involving an Alpha or Text field. For example, if you are looking for all occurrences of the name “Belmondo” in a field, you may specify the search value in several ways: 

Note: You can combine the wildcard with “Contains Keyword” type query only when it is placed at the end of the word being searched for. For example, the search condition “Notes contains keywords ‘anti@’” is completely valid.

You can search on one or more fields. A query on one field is called a simple query. For example, the search “Last name equals ‘Smith’” is a simple query. When you do a simple query, 4D examines the contents of one field when searching the database. 

UA query on two or more fields is called a compound query. When you do a compound query, you combine separate queries using a conjunction operator. The conjunction operator tells 4D how to combine the results of the individual queries. There are three conjunction operators:

• And: This operator finds all the records that meet the two conditions simultaneously. For example, the query “Find all the employees who work in the engineering department and who make over $50,000” will find the records of only those engineering employees who make over $50,000.
• Or: This operator finds all the records that meet either of two simple queries. For example, the query “Find all the employees who work in the engineering department or who make over $50,000” will find the records of all the people in the engineering department, as well as all the people who make over $50,000 regardless of the department in which they work.
• Except: This operator is the equivalent of “not.” The query “Find all the engineers except those who make over $50,000” will exclude the engineers making more than $50,000.

The conjunction operators let you create compound queries such as “Find the salespeople in New York or California and who have commission rates in excess of 30 per cent and who had sales volume less than $20,000.” The figure below shows this query being specified in the Query editor:

When this query is executed, 4D finds all the New York and California salespeople who may be getting high commissions for low volume sales. Additional examples of the uses of comparison and conjunction operators are given for each search method.