View source for Formula Expressions

===About Formula Expressions===
Formula expressions give you the ability to calculate dynamic values and apply dynamically-generated criteria to a search.

Formula expressions are used in these areas of the platform:

:{| border="0"
|
* Default [[Field]] values
* [[Layout Rule#Settings]]
* [[Computed Fields]] in Reports
* [[Formula Fields]]
* [[Macro Visibility Criteria]]
* [[Rule Execution Criteria]]
* [[Rule Actions]] to add or update a record
* [[Validations#Create the Validation Criteria|Validation Criteria]]
| valign="top"|
* [[Custom Access Criteria]]
* Input mapping specified for: 
** [[Web Service]] definition
** [[External Data Source#Settings]]
** [[External Lookup#Lookup Service Configuration]]
** [[External Lookup#Search Service Configuration]]
** [[Process Model#Execute Web Service]] 
|}

===Building a Formula Expression===

A formula expression is essentially equivalent to a spreadsheet formulas. 
Formula expressions are built from ''fields'', ''operators'', ''functions'', and ''values''. 

Typically, formula expressions are built using the buttons and drop down lists provided in the [[Formula Builder]], but they need not be. A fully-formed expression can simply be typed or pasted into the builder's expression-box text area.

====Values====
:A number, date, or ''string'' you enter into the expression:
:* A string or date needs to be enclosed in single quotes. For example: <tt>'ABC Company'</tt>.
:* You specify an ''empty string'' using two single quotes with nothing between them: <tt><nowiki>''</nowiki></tt>

====Fields====
:The name of an object [[Fields|Field]], a [[Computed Fields|Computed Field]], or a [[Referential Field]] (to reference a field in a related object).

====Functions====
:One of the built-in [[Formula Functions]]. Functions can nest to arbitrary depth.
:For example, to create a conditional expression, start by selecting the logical <tt>IF</tt> function from the drop down (which gives you a template), or else type in the conditional expression in a format like this:
:: <tt>IF (test, value-if-true, value-if-false)</tt>

{{Note|<br>Unlike [[Filter Expressions]], and/or logic in a Formula Expression is added using the functions <tt>AND(boolean_expression_1, boolean_expression_2)</tt> and <tt>OR(boolean_expression_1, boolean_expression_2)</tt>.
:''Learn more:'' [[Formula Functions#Logical Functions]]}}

====Operators====
:{{:Operators in Formula Expressions}}

===Examples===
Here are some examples of typical Formula Expressions.

====Simple Formula Expressions====
In a reservations application, there are fields for Creation Date, Check In Date, and Check Out Date. A formula field can calculate the number of days between the Creation Date and Check In Date. To create such a formula field, use the date fields and the [[DATECOMP]] formula function. DATECOMP will give a positive result when the later date is the first operand. It will give a negative result if the later date is the second operand. 

{| border="1" cellpadding="5" cellspacing="0"
!Formula Expression
!Description
|-
|<tt>DATECOMP(check_in_date, creation_date)</tt>||Compare the future Check In Date and the reservation Creation Date, and return the difference in the number of days
|-
|<tt>DATECOMP(TODAY(), check_in_date)</tt>||To build a complex expression, nest the expressions. This example compares Today's Date to the Check In Date and returns the difference in the number of days. ([[TODAY|<tt>TODAY</tt>]] returns the current date.)
|-
|<tt>IF((DATECOMP(check_in_date, TODAY())<2), 'Rush', 'Standard')</tt>||A more complex example uses the logical formula function <tt>IF</tt> to return one of two text results: 'Rush' if the Check In Date is less than two days from today, and 'Standard' if the Check In Date is two days or more away.
|}

====Complex Formula Expressions====
{{:Complex Expressions}}