4D v16.3

Query by formula

Home

 
4D v16.3
Query by formula

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:

 [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:

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



See also 

Query editor

 
PROPERTIES 

Product: 4D
Theme: Searching records

 
HISTORY 

 
ARTICLE USAGE

4D Design Reference ( 4D v16)
4D Design Reference ( 4D v16.1)
4D Design Reference ( 4D v16.3)