Using formulas

JungleMail for Office 365 formulas are very similar to formulas used in spreadsheet programs such as Excel. You can use formulas to get the most out of JungleMail. In this tutorial, you will learn how to do this.

You can use different values, functions, and operators to build a formula. To execute formulas in your email body you should surround them with {}.

Example:  {FormatDate(Today; "yyyy")}

While using formulas in the Dynamic Block/Section condition fields, surrounding the formula with  {} is not required.
Example:  FormatDate(Today; "yyyy")

Formulas are typically used email templates.

Common values

• Now – retrieves current date and time. Text representation of date and time depends on the current SharePoint Site Regional Settings.
  Example:  Now Result: 15/1/2015 12:55:56 PM
• Today – retrieves current date (time is 00:00). Text representation of date depends on current SharePoint Site Regional Settings.
  Example:  Today Result: 15/1/2015 12:00:00 AM

Note: value types are automatically converted.
Example:  "10" + 2 Result: 12

SharePoint-specific values

• SharePoint Column values. Column display name or internal name can be used in formulas (e.g., Title)
• SharePoint Lookup Column values. Use a semi-colon to specify a lookup-related column name. (e.g., Customer:Address)
• Other SharePoint-specific values (e.g.,Me - retrieves current SharePoint user full name. Other examples: ListTitle, ListUrl, ItemUrl).

Common functions

• FormatDate(date; format) – converts date to text representation using specified format ("dd", "dddd", "yy", "yyyy", "MM", "MMMM"). It also can be used to extrtact part of a date.
  Example:  FormatDate(Today; "yyyy") Result: 2015 Example: FormatDate(Today; "MMMM" "yyyy") Result: January 2015 Example: FormatDate(Today; "dddd","dd" "MM" "yyyy") Result: Thursday, 15 01 2015

  Read more about Standard Date and Time Format Strings and Custom Date and Time Format Strings.

• CurrencyToLiteral(amount) – converts number to a currency text representation. Currency format is taken from SharePoint Currency Column settings.
  Example:  CurrencyToLiteral(TotalAmount) Result: two thousand five hundred dollars and zero cents Note: TotalAmount Column value is $2500.
• Now() – retrieves current date and time. The same as Now value
• Today() – retrieves current date. The same as Today value.
• SubString(text; startPosition), SubString(text; startPosition; length) – retrieves a substring from text. The substring starts at a specified character position and has a specified length. First character is at 0 position.
  Example:  SubString("abcde"; 1) Result: bcde 
  Example:  SubString("abcde"; 2; 1) Result: c
• Lower(text) – converts text to lowercase.
  Example:  Lower("The Table") Result: the table
• Upper(text) – converts text to uppercase.
  Example:  Upper("The Table") Result: THE TABLE
• PadLeft(text; totalLength), PadLeft(text; totalLength; symbol) – right-aligns the characters in text, padding with spaces or symbol on the left for a specified total length.
  Example:  PadLeft("123"; 5; "0") Result: 00123
• PadRight(text; length), PadRight(text; totalLength; symbol) – left-aligns the characters in text, padding with spaces or symbol on the right for a specified total length.
  Example:  PadRight("123"; 5; "0") Result: 12300
• FirstWord(text) – retrieves a first word from text.
  Example:  FirstWord("One Two Three") Result: One

Common operators

• + – Addition.
• - – Subtraction.
• * – Multiplication.
• / – Division.
• & – Concatenation – used for combining strings together.
  Example:  "The" & " table" Result: "The table"

SharePoint-specific operators

• Colon (:) — Used for SharePoint Lookup Columns.
  Example:  Customer:Address

Placeholders for hidden metadata

• FileRef – retrieves server-relative URL to document or item or folder
• FileLeafRef – retrieves document or item or folder name
• FileDirRef – retrieves server-relative URL to a folder of a document, an item or a folder

Example values:

FileRef: /MFS/Reports/EventReport-0001.xlsx

FileLeafRef: EventReport-0001.xlsx

FileDirRef: /MFS/Reports

Proper function

The Proper function sets the first character in each word to uppercase and the rest to lowercase.

• Example: PROPER(text) Result: This Is An Example

Examples

• Calculating tomorrow's date: Today + 1.
• Current time: FormatDate(Now; "t").