Query by formula

Use the Query by Formula editor to find records based on the results of a calculation or data manipulation. You use the Formula editor to write the expression based on which the query will be performed. 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.

The Query by Formula command is useful for writing queries that involve operations such as the following:

• operations or evaluations on alphanumeric strings,
• date computations,
• arithmetic computations.

Here are some examples of the uses of the Query by Formula editor: 

• 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 use this method in the formula. You can save formulas to disk and load saved formulas into the Formula editor.

The following figure shows a query (search) formula in the Formula editor: 

For a detailed description of the Formula editor, see Formula editor.

To use the Query by Formula editor:

1. In the Design environment, choose Query > Query by Formula from the Records menu.
   OR
   Choose Query by Formula... from the menu associated with the “Query” button in the tool bar. 
2. Use the Formula editor to build a formula.
   OR
   Click the Load button to retrieve an existing formula from disk.
   The expression you write must equal either TRUE or FALSE for each record.
   If you build a formula in the Formula editor, you can either type your function or use the list of operators and functions. As you enter values, the editor checks the syntax used. If any syntax errors occur, a message describing the error will appear in the window.
   If you click the Load button, 4D displays an open-file dialog box where you can select a file. When you load a file, it replaces any formula that appears in the Formula editor.
   Note: The formula file extension is “.4FR”.
3. (Optional) If you want to save your formula to disk, click the Save... button.
   4D displays a dialog box that can be used to specify the name and location of the file. Click on the Save button to save the formula.