Documentation

Use the value picker for selecting values for conditions and/or parameters. The value picker looks like a regular text box. Once you click on the box, a value builder window is opened below, with options to select columns and/or functions.

Depending on the value type of the column/parameter for which the value is being entered, the picker will check your input or disallow invalid entry. The picker allows one of the following data types:

1. Text – any input is allowed.
2. Number – the entered value must be a number or the calculation result must produce a number
3. Date – the entered value must be a valid date column name or the calculation result must produce a date
4. Boolean – the entered value must be a Boolean (yes/no)

Value Calculations

You can enter scalar values, such as “Title” or “1”, column values in the form of column name surrounded by square brackets (such as “[Title]”), functions (such as “$Month([Created])”) or arithmetic operators (“+”, “-“,”*” or “/”).

When entering a text value, all the different parts of the value are concatenated into one string, such as “[Title] 1” may produce “My title 1”.

When entering a number, each part is evaluated as a number and the arithmetic operations are performed on those numbers. For example, “[Amount]/[Count]” can produce “6/2”, which results in “3”. Note that only simple calculations are performed; you should not use any parentheses or advanced mathematical symbols. Do not include any spaces.

When entering a date, the first part is evaluated as a date. Additionally, you can include additional parts, evaluating as a number and either “+” or “-“ between them. Do not include any spaces.

When calculating, the specified number of days is subtracted or added to the date. For example, “[Due Date]-[Warning Period]” may result in “12/12/2011-7”, which will produce “12/5/2011”.

When entering a Boolean, you must either enter Yes/No (or similar values) or select a yes/no column. No arithmetic operations are supported.

NOTE: do not use spaces within your formulas, e.g. [Amount]-5 is correct, but [Amount] - 5 will not work.

Picker Pop-up

The window presents a selection of available columns and functions for building the value expression. It is presented each time you click within the field.

Functions

Use functions in conditions and/or data entry fields to produce values based on text and column values. Most functions accept parameters, such as text, column names or other functions.

• Year – takes date/time as parameter and produces number from the Year part of that date, e.g. “$Year([Created])” -> “$Year(12/12/2010)” -> “2010”.
• Month – takes date/time as parameter and produces number from the Month part of that date, e.g. “$Month([Created])” -> “$Month(12/12/2010)” -> “12”.
• Day – takes date/time as parameter and produces number from the Day part of that date, e.g. “$Day([Created])” -> “$Day(12/12/2010)” -> “12”.
• Weekday - takes date/time as parameter and produces named of the weekday, e.g. “$Weekday([Created])” -> “$Weekday (12/12/2010)” -> “Monday”. The weekday name will be in the language of the site.
• Hour – takes date/time as parameter and produces number from the Hour part of that date/time, e.g. “$Hour([Created])” -> “$Hour(12/12/2010 12:00 PM)” -> “12”.
• Minute – takes date/time as parameter and produces number from the Minute part of that date/time, e.g. “$Minute([Created])” -> “$Minute(12/12/2010 12:00 PM)” -> “0”.
• WeekNumber – takes date/time as parameter and produces order number of the containing week in the year, “$WeekNumber ([Created])” -> “$WeekNumber (1/1/2010 12:00 PM)” -> “1”.
• Today – [Today], returns the date of when the action is performed. You can also use this function as input for the above functions.
• Now - [Now] returns the date and time of when the operation is executed.
• Current User – [Me] or [Current User], returns the display name of the user performing the action.
• Lookup – for any lookup column returns the column value of an additional column given the selected lookup value for the list item. For example, suppose your column lookups a list of states and shows the state name abbreviation, such as IL or NY. Additionally, the Title column in the States list contains the full name of that state, Illinois or New York. By adding the following function $Lookup([State]|[Title]) – you can return the full name (from the column Title) for the value selected in the column State of the current item.

NOTE: When looking up value from a parent item of Associated Item, Associated Task or Associated Document content type, the first parameter should be omitted, e.g. $Lookup([Title]). Note that such lookup always return text, no matter what the actual data type is.

NOTE: if the column name given as the second parameter exists both in the current list and the lookup list, make sure both internal and display names of the field match or that the column does not exist by the same in the current list. Otherwise it will cause the function not to return values.

• Extract – extract part of text based on the extraction pattern to be used in setting column values and in item query conditions. The system will attempt to convert the extracted value into the matching value type, such as integer. If your extracted value is not matching that value type, the import action will fail.
  The entered value should be as follows: $Extract([Column]|?prefix^suffix), where
  • $Extract – function name
  • Column – name of the column to get the value from (such as Subject), surrounded by square brackets
  • ?prefix^suffix - the extraction pattern, where
    • ?  - means that the extraction should be somewhere in the middle of the text (if you do not include the question mark, the system will attempt to extract the value from the beginning of the text)
    • prefix - text immediately preceding the value. This text can be blank if no prefix is needed and the extracted value is at the very beginning of the text.
    • ^ - the text to extract
    • suffix – text immediately following the value. If no suffix is specified, the value is truncated at the first space or line break. Use \r as end-of-line character to include the value up to the end of the current line. Use \e as end-of-file character to include the value up to the end of content.

For instance, if your column A value is expected to be in the format “Your approval for purchase request #11” and you need to update a list item with ID 11 using the first word from the message body, you need the following pattern for the ID value: $Extract([A]|?request #^), that will produce “11”, which can be compared to the ID column value of the list. For the body, use $Extract([Body]|^) to extract the first word from the message body. 

Note: This will work if your email body is in plain text, if your emails are sent as HTML you will need to combine this function with a $StripHTML([Body]) to get the first text. The complete function would be $Extract($StripHTML([Body])|^\r) to get the first line of text of an email body.

When assigned to a Yes/No field, it will give a Yes value when the word is one of the following (case-insensitive): 1,true,yes,yeah,approve,good,ok,okay,sure,alright,fine,affirmative,positive,approved
and No when the word is one of the following (case-insensitive):
0,false,no,nope,no way,reject,rejected
You can assign the extracted value to any field, but if the field is not a Yes/No field, the literal value is used without any conversion.

• StripHTML - As the name implies this function removes the HTML tags that may be a part of the Body of a message. You would need this to remove HTML tags from Email send as HTML, Rich Text and Enhanced Rich text.
• List Title –[List Title], returns the title of the current list
• Site Title – [Site Title], returns the title of the current site.
• List URL – [List URL], returns the URL of the current list.
• Site URL – [Site URL], returns the URL of the current site
• Left - returns the specified number of character from the beginning of a literal or column value. You can include column names for both arguments or even use functions within functions. Example: $Left([Title]|10) or $Left([Title]|[Length]).
• Right - returns the specified number of character from the end of a literal or column value. You can include column names for both arguments or even use functions within functions. Example: $Right([Title]|10) or $Right([Title]|[Length]).
• Substring - returns the specified number of character starting from a specific position within a literal or column value. You can include column names for all three arguments or even use functions within functions. Example: $Substring([Title]|0|10) or $Substring([Title]|[Start]|[Length]).
• Contains - returns whether or not (yes/no) the second argument value is found anywhere within the first argument value. You can include column names for both arguments or even use functions within functions. Example: $Contains([Title]|Marketing) or $Contains([Title]|[Department])
• Find - returns the position (number) of the second value within the first value. If the value is not found, -1 is returned. You can include column names for all three arguments or even use functions within functions. Example: $Find([Title]|ID) or $Find([Title]|[ID]).
• Replace - replaces all occurence of the first string with the second string within the value. You can include column names for all three arguments or even use functions within functions. Example: $Replace([Title]|more|less) or $Replace([Title]|[Category]|[Priority]).
• Split - split a string into parts based on the separator and return desired part. For instance $Split([Days]|, |2) -> $Split('Monday, Tuesday, Wednesday'|, |2) -> Tuesday.
• ToUpperCase - convert the string characters to upper case.
• ToLowerCase - convert the string characters to lower case.
• Length - return the length of a string value.
• GetValue - Returns a field value from an arbitrary list.
  • URL - optional, URL of the site. When omitted, current site is assumed, you must still include the pipe | character. In principal, you can provide URL of any site, assuming the user will have the necessary permissions to access it. Column values and functions are also permitted.
  • List name - name of the list to query. Column values and functions are permitted.
  • Item ID or Condition - ID of the item to retrieve. Column values and functions returning a number are permitted. Another option is to provide a value for any textual or numeric column within the remote item, such as Title=[Product]. The column is not required to hold a unique value, but it's highly recommended. 
  • Field name - name of the column to get the value from. Column values and functions are permitted.
  • Note: In dynamic rules this function only works with simple field types (not person or lookups). In actions it can works with any field type. 

Example: $GetValue(http://server/site1|Contacts|2|Last Name) , $GetValue(|Balance|Title=[Product]|Amount)
Note: only relative paths (such as /site1) are supported in the app version.

• Text - Convert date/time or number value to string according to the specified format string. The format strings are according to Microsoft .NET specifications, e.g. given a date column value. Example: $Text([Date]|MM-dd-yyyy) -> 01-01-2013.Date and time format strings , Numeric format strings
  NOTE: this function is not supported in dynamic rules and other form features
• AddDate - add/subtract value to a date/time value, e.g. $AddDate([Created]|10|day) will return the date 10 days after the Created date of the item. Supported date intervals: year, month, day, hour, minute and second.
• If - returns either expression A or expression B result depending on the value of the Boolean argument. All 3 parameters can be literal, column values or functions. For the Boolean parameter you can even use comparison, such as [Amount]>10 or [Approval]!=Approved. Example: $If([Amount]>1000|Large|Small).
• ToDate - convert a text value into a date value. The text value must be in a supported date format.
• ToNumber - convert a text value to number. The text value must be in a support number format.
• Days - total days difference between two date values.
  For Example: $Days([Start Date]|[End Date])
• Hours - get a total hours difference between two dates
• Minutes - total minutes difference between two date values
• Seconds - seconds difference between two date values
• Manager - (On-Prem only) given user value return it's manager's login name
• ManagerEmail - (On-Prem only) given user value return manager's email address (from Active Directory)