QUERY BY FORMULA

version 11.2 (Modified)

QUERY BY FORMULA ({aTable}{; }{queryFormula})

QUERY BY FORMULA looks for records in table. QUERY BY FORMULA changes the current selection of aTable for the current process and makes the first record of the new selection the current record.

QUERY BY FORMULA and QUERY SELECTION BY FORMULA work exactly the same way, except that QUERY BY FORMULA queries every record in the entire table and QUERY SELECTION BY FORMULA queries only the records in the current selection.

Both commands apply queryFormula to each record in the table or selection. The queryFormula is a Boolean expression that must evaluate to either TRUE or FALSE. If queryFormula evaluates as TRUE, the record is included in the new selection.

The queryFormula may be simple, perhaps comparing a field to a value; or it may be complex, perhaps performing a calculation or even evaluating information in a related table. The queryFormula can be a 4D function (command), or a function (method) or expression you have created. You can use wildcards (@) in queryFormula when working with Alpha or text fields as well as the "contains" (%) operator for keyword queries. For more information, please refer to the description of the QUERY command.

If queryFormula is omitted, 4D displays the Query dialog box.

When the query is complete, the first record of the new selection is loaded from disk and made the current record.

These commands are optimized and can more particularly take advantage of indexes. When the type of query allows it, these commands execute queries equivalent to the QUERY command. For example, the statement QUERY BY FORMULA([mytable]; [mytable]myfield=value) will be executed just like QUERY([mytable]; [mytable]myfield=value), which will allow the use of indexes. 4D can also optimize queries containing parts that cannot be optimized, by first executing the optimized parts and then combining the results with the rest of the query. For example, the statement QUERY BY FORMULA[mytable];Length(myfield)=value) will not be optimized. On the other hand, QUERY BY FORMULA([mytable];Length(myfield)=value1 | myfield=value2) will be partially optimized.

These commands by default carry out "joins" like SQL. This means that it is not necessary for a structural automatic relation to exist between table A and table B in order to execute a statement of the type QUERY BY FORMULA([Table_A];[Table_A]field_X = [Table_B]field_Y) (see example 3).

If they exist, the relations defined in the Structure editor are not used as a rule. However, these commands will use automatic relations in the following cases:

- If the formula cannot be broken down into elements of the { field ; comparator ; value} form

- If two fields of the same table are compared.

Note: For compatibility reasons, it is possible to deactivate the joins mechanism, either globally via the database Preference (converted databases only) or per process using the SET DATABASE PARAMETER command.

4D Server: Beginning with version 11 of 4D Server, these commands are run on the server, which optimizes their execution. Keep in mind that when variables are called directly in queryFormula, the query is calculated with the value of the variables on the client machine. For example, the statement QUERY BY FORMULA([mytable];[mytable]myfield=myvariable) will be run on the server but with the contents of the myvariable variable of the client machine.

On the other hand, this principle is not applied for formulas using methods that, themselves, call variables (the values of the variables are evaluated on the server). In this context, it may be advisable to use the "Execute on server" method attribute, which allows the method to be executed on the server while passing parameters (variables) to it (see the Design Reference manual).

In previous versions of 4D Server, these commands were executed on client machines. For compatibility's sake, this functioning is maintained for databases converted to version 11. A compatibility preference and a selector of the SET DATABASE PARAMETER command can nevertheless be used to adopt the functioning of version 11 (execution on the server) in converted databases

1. This example finds the records for all invoices that were entered in December of any year. It does this by applying the Month of function to each record. This query could not be performed any other way without creating a separate field for the month:

   QUERY BY FORMULA ([Invoice]; Month of ([Invoice]Entered) = 12) ` Find the invoices entered in December

2. This example finds records for all the people who have names with more than ten characters:

   QUERY BY FORMULA ([People]; Length ([People]Name)>10) ` Find names longer than ten characters

3. This example activates SQL joins for a specific query by formula:

   $currentVal:= Get database parameter(QUERY BY FORMULA Joins) 
   SET DATABASE PARAMETER(QUERY BY FORMULA Joins;2) `Activate SQL joins
      `Query all the lines of "ACME" client invoices even though the tables are not related
   QUERY BY FORMULA([invoice_line] ; [invoice_line]invoice_id = [invoice]id & [invoice]client = "ACME")
   SET DATABASE PARAMETER(QUERY BY FORMULA Joins;$currentVal) `We re-establish the current settings

QUERY, QUERY BY SQL, QUERY SELECTION, QUERY SELECTION BY FORMULA.