Using formulas

JungleMail 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.

Formula syntax

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 in newsletter templates.

Common values

• Now – retrieves current date and time. The 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.

• WeekOfYear(datetime,[calculateInIsoFormat]) - returns week of year number from a specified date, additional parameter [calculateInIsoFormat] is optional and false by default. It is used to calculate weeks of year using ISO8601 format. The default option is to calculate weeks of year using current culture calendar configuration (.NET)
  Example: WeekOfYear(Now;1) Result: 42
  Example: WeekOfYear("2012-12-31";1) Result: 1
  Example: WeekOfYear("2012-12-31") Result: 53

• 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. The 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 the first word from text.
  Example:  FirstWord("One Two Three") Result: One
• GetListColumnDisplayName(fieldName; [listTitleOrUrl]) - inserts selected list's column display name.
  Example:  GetListColumnDisplayName("Author"; "Tasks/HR") Result: Created By

Common operators

• + – addition.
• - – subtraction.
• * – multiplication.
• / – division.
• & – concatenation – used for combining strings together.

Examples:

  "The" & " table" Result: "The table"

Calculating tomorrow's date:  Today + 1

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

Note: server-relative URL is without domain. For example, if the full URL is https://www.enovapoint.com/blog/sample-post, a server-relative URL is /blog/sample-post.

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