Query by formula

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: 

• The following formula is used to search for records in which the last seven characters of the Phone number field are equal to “2524444”.
  
   Substring([Emp]Phone number;4;7)="2524444"
• The following formula finds people born on the current date regardless of the year:
  
   (Day of(Current date)=Day of([Emp]Birthdate)) & (Month of(Current date)=Month of([Emp]Birthdate))
• The following formula divides annual sales by the cost of living and finds records whose calculated value is greater than 1,000:
  
   ([Stats]Annual Sales/[Stats]CostOfLiving)>1000

However, the formula:

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:

• : 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.
  

• : 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.
  

Through this new functioning, query by formula criteria can be combined with standard query criteria:

Due to their integration into the standard query editor, queries by formula benefit from the same functionality as standard queries: 

• choice of result processing by means of the Selection action menu (Selection action menu),
• addition to the Recent queries menu (Recent queries menu),
• saving and loading by means of disk files (see Edit menu).