JungleDocs | Formulas and Functions list

In JungleDocs you can use formulas to achieve many different things. In other words, formulas help you to get the most out of JungleDocs. JungleDocs formulas are very similar to formulas used in spreadsheet programs such as Excel. You can use different values, functions and operators to build a formula.

  Formulas can be used in:

  • JungleDocs Base Rules for calculating a new file name.
  • JungleDocs From Existing Rules for setting field values.
  • Document 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. Text representation of date and time depends on 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.

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

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 – Used for combining 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 current site collection
  • SiteCollectionTitle – Title of current site collection
  • SiteCollectionLink – Hyperlink to current site collection
  • SiteId – ID (GUID) of current site
  • SiteUrl – Full URL (as text) of current site
  • SiteTitle – Title of current site
  • SiteLink –  Hyperlink to current site
  • ListId – ID (GUID) of the list
  • ListUrl – Full URL (as text) of the list
  • ListTitle – Title of the list
  • ListLink – Hyperlink to the list
  • ItemUrl – Full URL of the list item
  • ItemDisplayName – Display name of the item (Title or (No title) message for list items; Document name without extension for files)
  • ItemLink –  Hyperlink to the list item
  • ItemID –  ID (GUID) of the list item
  • ViewItemPropertiesUrl – Full URL (as 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) - Evaluates a certain condition and returns the value you specify if the condition is TRUE, and another value if the condition is FALSE.
    *Note: Returns FALSE if 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 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
    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
  • 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) - Crop text to approximate character length without a word cut-off.
  • PlainText(text) - Removes HTML version of text and leaves only Plain text version.
  • FirstWord(text) - Returns the first word of specified text.
  • LastWord(text) - Returns the last word of 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.

Read more about text functions here.

Date and Time

  • FormatDate(date; format) – 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.
    Example: FormatDate(Today, "yyyy") Result: 2016. Current time: FormatDate(Now; "t"). Learn more about date formatting here.
  • 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), 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) - will calculate the average (mean) value from all items specified in recordSet by fieldName field. Read more.
  • Median(recordSet; fieldName) - will calculate the median from all items specified in recordSet by fieldName field. Read more.
  • Max(recordSet; fieldName) - will find the largest number from all items specified in recordSet by fieldName field. Read more.
  • Min(recordSet; fieldName) - will find the smallest number from all items specified in recordSet by fieldName field. Read more.
  • GroupBy(recordSet; fieldName) -  will group items specified in recordSet by fieldName field.
  • JoinValues(recordSet; fieldName), JoinValues(recordSet; fieldName; splitter) - Joins all values from recordSet FieldName field into one text line using specified splitter or semicolon if 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 splitter is not provided.

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 Functions

  • GetNextNumberedValue(fieldName; prefix; numberFormat; suffix) – Searches the list and gets 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. Image must be located in SharePoint environment. Used to fill Word document templates in JungleDocs.
    Example: Image("http://site/images/contoso.png")
  • IfEmpty(testValue; trueValue) - returns testValue if it is not empty.
    IfEmpty(testValue; trueValue; falseValue) - returns falseValue if testValue is not empty. Read more.
  • GetUniqueItems(recordSet; fieldName) - returns only items that have unique values in a specified column. Read more.
  • GetView(listTitleOrUrl; viewTitleOrUrl), GetView(listTitleOrUrl; viewTitleOrUrl; fieldName1; fieldValue1), GetView(listTitleOrUrl; viewTitleOrUrl; fieldName1; fieldValue1; fieldName2; fieldValue2; ...) - return record set from particular list and view, additional conditions to filler items from view are supported. Read more.
  • FindItems(listTitleOrUrl; fieldName; fieldValue), FindItems(listTitleOrUrl; fieldName1; fieldValue1; fieldName2; fieldValue2; ...) - Find Items in particular list. Read more.
  • GetCalendarItems(listTitleOrUrl; viewTitleOrUrl), GetCalendarItems(listTitleOrUrl; viewTitleOrUrl; fromDate), GetCalendarItems(listTitleOrUrl; viewTitleOrUrl; fromDate; tillDate) - Get Items in calendar list.
  • 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 Note: TotalAmount Column value is $2500.
  • Lookup(listTitleOrUrl; itemID) - Finds item by ID.
  • ReverseLookup(listTitleOrUrl), ReverseLookup(listTitleOrUrl; fieldName) - 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)
    Note: list view should be a two level grouped view
    .

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 condition is true. Read more.
    Note: returns DoNothing command if condition is false

Collect field values

  • CollectUniqueValues(recordSet; fieldName), CollectUniqueValues(recordSet1; fieldName1; recordSet2; fieldName2; ...)- Used to collect unique field values from one or multiple recordSets. It optionally orders alphabetically if sort is true. 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)

Still need help? Contact Us Contact Us