QUERY BY FORMULA

version 3

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

QUERY BY FORMULA looks for records in table. QUERY BY FORMULA changes the current selection of table 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 4th Dimension function (command), or a function (method) or expression you have created. You can use wildcards in queryFormula when working with Alpha or text fields.

Note: 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 always perform a sequential search, not an indexed search. QUERY BY FORMULA and QUERY SELECTION BY FORMULA are slower than QUERY when used on indexed fields. The query time is proportional to the number of records in the table or selection.

4D Server: The server does not execute the query formula. Each record is sent to the local workstation and the query formula is evaluated on the workstation. This makes the command less efficient with 4D Server than the QUERY command.

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

QUERY, QUERY SELECTION, QUERY SELECTION BY FORMULA.