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 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. Lastly, you can perform advanced queries using formulas (see Query by formula).

The editor window contains an edit menu, a selection action menu and a recent queries menu.

Note: The Recent queries menu is displayed when at least one query has already been performed during the session.

The edit menu contains commands for managing the query code.

• Load... and Save...: These commands manage the loading and saving of query files to disk. 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 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". All parameters are saved: query line(s), query action, as well as queries by formula.
  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. 

• Copy formula to Clipboard: Places the code of the formula built in the editing area on the Clipboard. 

• Reset: Deletes all the lines of the query defined in the editor. Warning: deleting lines cannot be undone.

This menu defines the query action to be performed based on the current selection of existing records. 

• Create new selection (default action): 4D searches in all the records of the tables and replaces the original current selection to display the records found.

• Search in selection: 4D only searches in the records of the original current selection and then replaces this selection with the records found.

• Add to selection: 4D searches in all the records of the tables and adds the records found to the original current selection. Any records found that are already part of the current selection are displayed but not duplicated.

• Remove from selection: 4D searches in all the records of the tables and removes any records found from the original current selection.

This menu appears when at least one query has already been performed. It contains the most recent queries made during the session, so users can easily repeat their most common queries. Up to 10 queries are kept. 

Note that any selection actions associated with the queries (Search in selection, Add to selection, etc.) are not saved.

When a query is selected from this menu, its description is displayed in the build area. You can then run it directly or modify it as needed.

The Query editor supports predefined queries. Just like a standard saved query, a predefined query contains a full query definition including all criteria, and can be loaded in the Query editor at any time. Predefined queries can be embedded in deployed applications and are directly listed in a sub-menu of the Query editor.

To define a predefined query:

1. Create a "Queries" sub-folder in the "Resources" folder of your database.
2. Add all saved query files (.4df) in this folder to be used as predefined queries:
   

When at least one .4df query file related to the current table is found in the Resources/Queries folder, a new Load > item is added at the end of the Query editor's Edit menu. This item provides access to all the predefined queries as sub-menu items:

Selecting a sub-menu item loads the corresponding query in the Query editor.

Reminder: Only query files related to the current table are displayed in the sub-menu.

To create a standard query in the editor, just set up a line in the form of "field operator value":

1. To designate the field, use the hierarchical list found to the right of the editing area:
   
   You can also right-click in the editing area and enter the first letter(s) of the table of the field (do not type the "["  character): a text prediction mechanism displays proposals matching what you type:
   
   Once you have set the table, press the right arrow button to validate the proposition and access the list of fields. Enter the first letter(s) of the field or use the up/down arrows to scroll through the fields of the table:
   
   This list displays all the database tables and their fields. If a virtual structure is defined using the SET TABLE TITLES and SET FIELD TITLES commands, it is taken into account.
2. Select a comparison operator from the central menu. The list of operators is updated according to the type of field defined:
   
   In addition to standard comparison operators, the query editor offers extended operators and pre-entered value types to let you quickly perform the most common queries (see Comparison operators).
3. Type the value you want to search for.
   In a Text or Alpha field you can use the wildcard character (@) at the end of the value to specify 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.
4. If you want to specify a multiple query, click on the button to add a line. 4D duplicates the contents of the row corresponding to the button.
   If you want to add a query line using a formula, hold down Alt (Windows) or Option (OS X) while you click on the add button. This point is detailed in the Query by formula section.
5. Select the desired conjunction (And, Or, Except).
   By default, 4D sets the And conjunction next to the line that is added.
   
6. Repeat steps 1 through 3 in order to specify new criteria.
   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 clicking on the next to it. Be careful, deleting lines cannot be undone.
7. Choose the destination of the query using the selection action menu: Create new selection, Search in selection, Add to selection, Remove from selection (see previous section).
8. (Optional) To save the query to a disk file, select Save... from the edit menu.  
9. Click on Query to launch the search.

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.

Here are the operators available for each field type:

(*) For strings, queries are based on the alphabet (where a < b). For example, a query of the type name is after "don" finds Donna, Don Juan, Smith, and so on, but does not find Alves or Dominick.

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 queries. For example, the search condition “Notes contains word(s) ‘anti@’” is 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 is ‘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.

To perform a query combining several criteria, you can click as many times as needed on the add line button .

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.