You can use a Query by Formula to find records based on the results of a calculation or data manipulation. The expression you write must equal either TRUE or FALSE for each record. You can use any function in the language as well as any project methods specifically designated by the developer.
A Query by Formula is useful for composing queries that involve operations such as the following:
- operations or evaluations on alphanumeric strings,
- date computations,
- arithmetic computations.
Here are some examples of queries by formula:
However, the formula:
[Stats]Annual Sales/[Stats]CostOfLiving
is incorrect because it returns a numeric value, not TRUE or FALSE.
You can only write formulas that are one logical line long. That is, you cannot press the Carriage return key on the keyboard and write a second line. The editing area, however, will wrap to the next line if the statement is too long. If you need to use a formula that is more than one line, write it as a project method and call this method in the formula.
Queries by formula are written in the standard query editor, where a query by formula is an advanced query mode.
To define a query by formula, display the Query editor and then Alt+click (Windows) or Option+click (OS X) on the add line button
.
Note: For a single-line query by formula, delete the first line added by default.
The added line then includes additional menus:
![](../../picture/1333949/pict1333949.en.png)
: displays a hierarchical list of all the database tables and fields whose type is compatible with a query by formula.
: displays a hierarchical list of all the operators that can be used in a query by formula.
![](../../picture/1333955/pict1333955.en.png)
: displays a list of 4D functions available by default in the context of a query by formula. You can include project methods in this list using the SET ALLOWED METHODS command.
![](../../picture/1333968/pict1333968.en.png)
Through this new functioning, query by formula criteria can be combined with standard query criteria:
![](../../picture/1333970/pict1333970.en.png)
Due to their integration into the standard query editor, queries by formula benefit from the same functionality as standard queries: