List of functions and formulas

Applies to JungleDocs for SharePoint 2010-2019.
For a similar tutorial for JungleDocs for Office 365, click here.

In JungleDocs you can use formulas to achieve many different things. JungleDocs formulas are very similar to formulas used in spreadsheet programs such as Excel. You can use different values, functions, and operators in a formula.

Formulas can be used in:

• Base rules for calculating a new file name
• From Existing rules for setting field values
• Content controls

In this tutorial, you will find a full list of all the formulas and functions you can use in JungleDocs.

Common values

• Numeric values.
  Example: 1.25
• Text values surrounded by quotation marks.
  Example: "some text"

Core variables

• True, False
  Yes/No values. Can be used for setting SharePoint Yes/No Column value.
• Now
  Retrieves current date and time. A text representation of date and time depends on the current SharePoint Site Regional Settings.
  Example: Now Result: 6/6/2016 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: 6/6/2016 12:00:00 AM Calculating tomorrow's date: Today + 1

Core operators

Common

• + – addition.
• - – subtraction.
• * – multiplication.
• / – division.

Comparison

• = - is equal
• < - is less than
• <= - is less than or equal
• > - is greater than
• >= - is greater than or equal to
• <> - is not equal

Text

• & – concatenation (joining strings together).
  Example: "The" & " table" Result: "The table"

Record set

• : - colon. Used for SharePoint lookup columns.
  Example: Customer:Address

SharePoint-specific values

• SharePoint Column values. Column display name or internal name can be used in formulas.
  Example: Title
• SharePoint Lookup Column values. Use semicolon to specify lookup related column name.
  Example: Customer:Address

Other SharePoint-specific values (variables):

• Me 
  Retrieves current SharePoint user's full name.
• SiteCollectionUrl – full URL (as text) of the current site collection
• SiteCollectionTitle – title of the current site collection
• SiteCollectionLink – hyperlink to the current site collection
• SiteId – ID (GUID) of the current site
• SiteUrl – full URL (as text) of the current site
• SiteTitle – current site's title
• SiteLink – hyperlink to the current site
• ListId – list ID (GUID)
• ListUrl – list's full URL (text)
• ListTitle – list title
• ListLink – hyperlink to the list
• ItemUrl – list item's full URL
• ItemDisplayName – item's display name (Title or (No title) message for list items; document name without an extension for files)
• ItemLink – hyperlink to the list item
• ItemID – list item's ID (GUID)
• ViewItemPropertiesUrl – full URL (text) to item properties view form
• ViewItemPropertiesLink – hyperlink to item properties view form
• EditItemPropertiesUrl – full URL (as text) to item properties edit form
• EditItemPropertiesLink – hyperlink to item properties edit form

Core functions

Logic

• And(condition), And(condition1; condition2; ...) 
  Returns TRUE if all of the arguments evaluate to TRUE.
• If(condition; trueValue)*, If(condition; trueValue; falseValue)If(condition1; trueValue1; condition2; trueValue2; ...)*, If(condition1; trueValue1; condition2; trueValue2; ...; falseValue) 
  Evaluates a certain condition and returns the trueValue if the condition is TRUE, and falseValue if the condition is FALSE. Can process multiple conditions and return a corresponding trueValue. If all conditions are FALSE, returns falseValue.
  *Note: Returns FALSE if a condition is FALSE.
• Not(condition) 
  Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa.
• Or(condition), Or(condition1; condition2) 
  Returns TRUE if any argument evaluates to TRUE.

Text

• SubString(text; startPosition), SubString(text; startPosition; length) 
  Retrieves a substring from a 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
  Note: crashes if startPosition or length is out of text bounds.
• 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
• Replace(text; searchText; replaceText) 
  Replaces all occurrences of searchText in text with replaceText. Case-insensitive.
  Example: Replace("ABXD"; "xd"; "cd") Result: ABcd
• PadLeft(text; length), PadLeft(text; length; symbol) 
  Returns a new string of a specified length in which the beginning of the current string is padded with spaces or with a specified symbol.
  Example: PadLeft("123"; 5; "0") Result: 00123
• PadRight(text; length), PadRight(text; length; symbol) 
  Returns a new string of a specified length in which the end of the current string is padded with spaces or with a specified symbol.
  Example: PadRight("123"; 5; "0") Result: 12300
• LimitText(text; length) 
  Crops text to approximate character length without a word cut-off.
• PlainText(text) 
  Removes HTML version of the text and leaves only Plain text version.
• FirstWord(text) 
  Returns the first word of the specified text.
• LastWord(text) 
  Returns the last word of the specified text.
• HtmlEncode(text) 
  Returns the HTML-encoded string.
• HtmlEncodeMultiline(text) 
  Returns the HTML-encoded multiline string.
• IfContains(text; search; trueValue; falseValue) 
  Returns trueValue if text containt search criteria.
• Hyperlink(url; title) 
  Returns hyperlink made from title and Url.
• CurrencyToLiteral(number), CurrencyToLiteral(number; cultureName) 
  Converts number to a currency text representation. Currency format is taken from SharePoint Currency Column settings.
  Note: cultureName is a language tag (text) from the list: https://msdn.microsoft.com/en-us/library/cc233982.aspx
  Example: CurrencyToLiteral(TotalAmount) Result: two thousand five hundred dollars and zero cents (TotalAmount Column value is $2500).

Read more about text formatting formulas.

Date and time

• FormatDate(date; string)
  Converts date to text representation using specified format ("d", "t", "yy", "yyyy", "MMMM", "MM", "dd",). It also can be used to extrtact part of a date. Read more.
• Now()
  Retrieves current date and time. The same as Now value.
• Today()
  Retrieves current date. The same as Today value.
• ToISO8601(date)
  Converts a date to ISO-8601 format.

Numbers

• Round(number; digits) 
  Rounds the number to the specified number of digits. Read more.

Record set

• Sum(recordSet; fieldName) 
  Returns the sum of its arguments. The arguments can be numbers, cells references or formula-driven numeric values. Read more.
• Count(recordSet) 
  Returns the number of values in the list of arguments (recordSet). Read more.
• Average(recordSet; fieldName) 
  Calculates the average (mean) value from all items specified in recordSet by fieldName field. Read more.
• Median(recordSet; fieldName) 
  Calculates the median from all items specified in recordSet by fieldName field. Read more.
• Max(recordSet; fieldName) 
  Returns the largest number from all items specified in recordSet by fieldName field. Read more.
• Min(recordSet; fieldName) 
  Returns the smallest number from all items specified in recordSet by fieldName field. Read more.
• GroupBy(recordSet; fieldName) 
  Groups items specified in recordSet by fieldName field.
• OrderBy(recordSet; fieldName), OrderBy(recordSet; fieldName; ascending), OrderBy(recordSet; fieldName1; ascending1; fieldName2; ascending2), OrderBy(recordSet; fieldName1; ascending1; fieldName2; ascending2; fieldName3; ascending) 
  Orders recordSet items by fieldName values in ascending or descending direction. Read more.
• JoinValues(recordSet; fieldName), JoinValues(recordSet; fieldName; splitter) 
  Joins all values from recordSet FieldName field into one text line using specified splitter or semicolon if a splitter is not provided.
• JoinUniqueValues(recordSet; fieldName), JoinUniqueValues(recordSet; fieldName; splitter) 
  Joins unique values from recordSet FieldName field into one text line using specified splitter or semicolon if a splitter is not provided.

Collect field values

• CollectUniqueValues(recordSet; fieldName), CollectUniqueValues(recordSet1; fieldName1; recordSet2; fieldName2; ...) 
  Collects unique field values from one or multiple recordSets. Optionally orders alphabetically if sorting is TRUE. The result will contain a new list of items with a single field named Value representing the unique field value.
• CollectUniqueValues(recordSet; fieldName; sort), CollectUniqueValues(recordSet1; fieldName1; recordSet2; fieldName2; ...; sort) 
  Uses value text representation for comparing field values (case-sensitive, whitespace-sensitive, includes empty values). It does not split SharePoint Lookup columns with multiple values.

Examples:

CollectUniqueValues(GetView("Contracts"; "AllItems"); "Company")

CollectUniqueValues(GetView("Contracts"; "AllItems"); "Company"; GetView("Proposals"; "AllItems"); "Company"; true)

Filters

FilterItems(recordSet; fieldName1; fieldValue1), FilterItems(recordSet; fieldName1; fieldValue1; fieldName2; fieldValue2; ...) – Filter items by comparing field values. Uses value text representation for comparing field values.
Example: FilterItems(ReportItems; "Company"; "Contoso")

SharePoint-specific functions

• GetNextNumberedValue(columnName, prefix, numberFormat, suffix)
  Searches the list and gets the next sequence number. Used for automatic document numbering in JungleDocs. Read more.
  Example: GetNextNumberedValue("BaseName"; "INV-"; "PadLeft($NextNumber; 4; \"0\")"; "") Result: INV-0001
• GetLastNumber(fieldName; prefix; suffix) 
  Finds last numbered value of fieldName.
• Image(Url) 
  Finds an image by URL. Full or site-relative URL can be specified. The image must be located in the SharePoint environment. Used to fill Word document templates in JungleDocs.
  Example: Image("http://site/images/contoso.png")
• IfEmpty(testValue; trueValue) 
  Returns trueValue if testValue is empty. If it isn't, testValue is displayed. Read more.
• GetUniqueItems(recordSet; fieldName) 
  Returns only items that have unique values in a specified column. Read more.
• GetItemAttachments()
  Retrieves item attachments from a SharePoint list. Used in conjunction with additional functions to retrieve the attachment's file name, display the attached image, etc.. Read more.
• GetItemByUrl(fileUrl)
  Retrieves items or documents by their URL address from a SharePoint list or library. Read more.
• GetView(listTitleOrUrl; viewTitleOrUrl; [additionalParameter; additionalParameterValue])
  Retrieves record set from particular list and view, additional conditions to filter items from view are supported. Read more.
• FindItems(listTitleOrUrl; columnName; value; [additionalParameter; additionalParameterValue])
  Finds Items in a particular list. Read more.
• ParentContext:– allows you to access the current document values inside a repeater, such as GetView() or FindItems().
  Example: ParentContext:Title Result: Retrieves the Title of the current document, not the Title of the item inside the repeater.
• EmbedFile()
  Embeds a document from a SharePoint library. Read more.
• InsertFiles(recordSet; [updatable]; [clearFormatting]; [keepFormatting])
  Resolves specified documents and imports their content into a JungleDocs template. Read more.
• TakeColumnsFromView(recordSet; viewTitleOrUrl) 
  Takes columns from specified SharePoint view to be used for automatic report table generation. Read more.
• GetCalendarItems(listTitleOrUrl; viewTitleOrUrl), GetCalendarItems(listTitleOrUrl; viewTitleOrUrl; fromDate), GetCalendarItems(listTitleOrUrl; viewTitleOrUrl; fromDate; tillDate) 
  Returns items in a calendar list.
• Lookup(listTitleOrUrl; itemID) 
  Finds item by ID.
• ReverseLookup() 
  Performs a reverse lookup, used when you have an existing list (contacts for example) being used as a source of data for a target list. Read more.
• Matrix(twoLevelGroupedRecordSet), Matrix(twoLevelGroupedRecordSet; cellExpression), Matrix(listTitleOrUrl; viewTitleOrUrl), Matrix(listTitleOrUrl; viewTitleOrUrl; cellExpression) 
  Does statistical data transformations using data from grouped lists. Read more.
  Note: default cellExpression is Count($Items). List view should be a two-level grouped view.
• InsertBookmark(bookmarkName)
  Inserts a bookmark into the content control and around the inner content. Read more.
  
• CrossReference(bookmarkName; [updateOnOpen]; [contentOption]; [preserveFormatting]; [defaultContent])
  Creates a cross-reference to a specified bookmark. Read more.
• GetListColumnDisplayName(fieldName; [listTitleOrUrl])
  Inserts selected list's column display name. Read more.
• ManagedMetadataColumn:TermXXXX
  Returns Managed Metadata column values from a selected term set of managed terms. Read more.

Commands

• DoNothing() 
  Does nothing. Could be used in conditions. Read more.
• DeleteControl()
  Deletes content control with its content. Read more.
• DeleteControlIf(condition) 
  Deletes content control with its content if a condition is true. Returns the DoNothing command if a condition is false. Read more.
• Eval(expression) – Evaluates the expression and returns it.
  Example: Eval("1+2")Result: 3. Eval("Price" & Currency) Result: Returns column value of PriceEUR if EUR is the metadata of the current document Currency column.