Formula Syntax Reference

The following table displays the functional reference for creating formulae along with their available arguments.

Function Name	No. of Required Arguments	Argument Data Type	Return Type
Numeric Function
Abs	1	Number	Number
Ceil	1	Number	number
Floor	1	Number	Number
Naturallog	1	Number	Number
Base10log	1	Number	Number
Max	Multiple	All Number	Number
Min	Multiple	All Number	Number
Sqrt	1	Number	Number
String Functions
Len	1	String	Number
Find	3	String: String: Number	Number
Concat	Multiple	All String	String
Contains	2	String: String	Boolean
Startswith	2	String: String	Boolean
Endswith	2	String: String	Boolean
Lower	1	String	String
Upper	1	String	String
Trim	1	String	String
Substring	3	String: Number: Number	String
Replace	3	String: String: String	String
Tostring	1	Any data type	String
DateTime Functions
Newdate	6	Number:Number:Number:Number:Number:String	Datetime
Datepart	1	Datetime	String
Timepart	1	Datetime	String
Adddate	3	Datetime: Number: String	Datetime
Subdate	3	Datetime: Number: String	Datetime
Now	0	-	Datetime
Datecomp	2	Datetime: Datetime	Number
Dayofmonth	1	Datetime	Number
Hour	1	Datetime	Number
Minute	1	Datetime	Number
Month	1	Datetime	Number
Year	1	Datetime	Number
Weekday	1	Datetime	Number
Boolean Functions
If	3	Boolean: Generic: Generic	Generic
And	Multiple	All Boolean	Boolean
Or	Multiple	All Boolean	Boolean
Not	1	Boolean	Boolean

Note

String constants should be enclosed within single quotes.
Generic implies any data type – Number, String, Datetime (including normal date) or Boolean.

Blank value computation

Note: This option is being released in a phased manner. It will soon be released to all users.

When you create a formula field, you can decide how blank values in participating fields are to be interpreted.

This is available under Blank value preference and you can pick one of the following options:

Consider blank values as empty.
Consider blank values as 0 for integers and decimals, “(empty) for strings.

The first option ‘Consider blank values as empty’ will be selected by default when you create a new formula field.

Consider the following example:

Rent is the formula field.
The formula used is: Number of units * Price of a unit * Period of storage required.
Number of units, Price of a unit, and Period of storage are all participating fields.
Period of storage is blank.

a) If Consider blank values as empty is chosen for the Rent field:
Period of storage is interpreted as empty, and the formula will not compute the result. So, Rent will remain blank or empty.

b) If Consider blank value as 0 for integers and decimals, ” for strings is chosen for the Rent field:
Period of storage is interpreted as 0, and the formula will compute the result based on this value.