Runtime functions provided

The following functions are provided.

Function Description Usage
and

Returns true where both logical arguments are true. If either argument is false, returns false.

 and(boolean1, boolean2)
avg Returns the average value of a set of values. avg([set of values])
contains Returns true where string contains the element. contains(string,element)
convertToDate Convert the string to date. convertToDate(stringToConvert)
count Returns the number of rows in which a control that has a value (not blank) appears within a repeating section. count(control)
currency Returns the formatted currency string for the specified value. currency(value)
currentRowNumber Returns the row number of the Repeating Section that contains the control. currentRowNumber(control)
date Returns a new date constructed from the specified parameters. date(day, month, year, hours, minutes)
dateAddDays Retuns the date with the specified number of whole days added to it. dateAddDays(date, daysToAdd)
dateAddHours Returns the date with the specified number of whole hours added to it. dateAddHours(date, hoursToAdd)
dateAddMinutes Returns the date with the specified number of whole minutes added to it. dateAddMinutes(date, minutesToAdd)
dateAddMonths Returns the date with the specified number of whole months added to it. dateAddMonths(date, monthsToAdd)
dateDiffDays Returns the number of whole days between date1 and date2. dateDiffDays(date1, date2)
dateDiffHours Returns the number of whole hours between date1 and date2. dateDiffHours(date1, date2)
dateDiffMinutes Returns the number of whole minutes between date1 and date2. dateDiffMinutes(date1, date2)
endsWith Returns true where element is at the end of string. endWith(string, element)
equals Returns true where two items are of equal value. equals (value1, value2)
formatDate

Returns the date in the specified format.

The format specified in FormatDate can include either a combination of syntax elements, such as month and year, or single-character format specifiers, such as the short date pattern.

The following syntax elements are available for use with FormatDate.

Element Description Example return from
"​6/15/2009 1:45:30 PM"
(in US date format)

"dd"​

​Day of month

15

"ddd"​

​Day of week (Single Letter)

M

"dddd"​

​Day of week (Full)

Monday

"MM"​

​Month (Numeric)

6

"MMM"​

​Month (Short)

Jun

"MMMM"​

​Month (Full)

June

"yyyy"​

​Year

2009

"hh"​

​Hours (12 Hour)

1

"hh"​

​Hours (24 hour)

13

"mm"​

​Minutes

45

"tt"​

​AM or PM

PM

The following single-character format specifiers are available for use with FormatDate.

Format Specifier Description Example return from
"​6/15/2009 1:45:30 PM"
(in US date format)

"d"​

​Short date pattern

6/15/2009

"D"​

​Long date pattern

Monday, June 15, 2009

"M", "m"​

​Month/day pattern

June 15

"s"​

​Sortable date/time pattern

2009-06-15T13:45:30

"t"​

​Short time pattern

1:45PM

"T"​

​Long time pattern

1:45:30PM

"Y", "y"​

​Year month pattern

June, 2009
formatDate(date, format)
greaterThan Returns true where value1 is greater than value2. greaterThan(value1, value2)
greaterThanOrEqual Returns true where value1 is greater than or equal to value2. greaterThanOrEqual(value1,value2)
If Determines whether or not a condition is met and returns one value if true, or another value if false. If(logical_test, value_if_true, value_if_false)
inArray Returns true where the collection contains the entire specified element. inArray(collection, element)
isDate Returns true where the value is a valid date. isDate(value)
isNullOrEmpty Returns true where value has no data content. isNullOrEmpty(value)
isNumeric Returns true where the value is a valid number. isNumeric(value)
length Returns the length of a string. length(string)
lessThan Returns true where value1 is less than value2. lessThan(value1,value2)
lessThanOrEqual Returns true where value1 is less than or equal to value2. lessThanOrEqual(value1,value2)

lookup

Retrieves the value from the specified column ("output column") within a SharePoint list ("list title") for use in a formula.

For more information about this function, see Lookup runtime function.

lookup("list title", "column to filter on", "value to filter on", "output column")
max Returns the maximum value found in a set of values. max([set of values])
min Returns the minimum value found in a set of values. min([set of values])
not Returns the logical reverse of the argument. not(boolean)
or Returns true where either logical argument is true. Returns false when both values are false. or(boolean1, boolean2)
parseLookup Parse the value of a lookup control. parseLookup([array or single lookup value], [optional bool showText])
replace Replace part of a string that matches a regular expression pattern (replacePattern) with the specified replacement. replace(textToModify, replacePattern, replacement)
round Returns a value to the nearest number based on the specified number of decimal places. Round(number, [optional numberOfDecimalPlaces])
rows Returns the number of rows a control appears in within a repeating section. rows(control)
startsWith Returns true where element is at the beginning of the value. startsWith(string, element)
subString Returns a part of the string from the character position start for the specified length in characters. The first character is position 0. subString(string, start, length)
sum

Returns the result of all the values in a set being added together.

Note: To add values in a Repeating Section control, omit the square brackets.
Example:  sum(MyRepeatingSectionControl)

 sum([set of values])
toLower Converts a string to lower case. toLower(string)
toTitleCase Converts a string to title case (first letter of each word capitalized). toTitleCase(string)
toUpper Converts a string to upper case. toUpper(string)
trim Removes any leading and trailing spaces from a string. trim(string)

userProfileLookup

Returns the specified SharePoint Online user profile property for the specified user. The profile property is identified by internal name while the user is identified by UPN (User Principal Name; usually the same as the user's email address).

Common internal names of user profile properties: "FirstName", "LastName","HomePhone", "Manager", "Office", "PreferredName", "WorkEmail". Example of valid email address: "john.smith@acme.onmicrosoft.com".

To specify an email address, either insert the reference Common > Current User (Email) or pass the appropriate substring from a People control (trim "i:0#.f|membership|" from the returned value).

Note: For this function to run, the administrator must allow application access to user profiles. See Manage forms.

userProfileLookup("UPN","InternalPropertyName")

Related information

Insert References