Using formulas

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

To let JungleDocs for Office 365 know what metadata from your SharePoint to insert into your document templates you will have to use formulas.

Before you begin

Using formulas you can achieve a great number of things in JungleDocs for Office 365. However, using formulas properly requires you to have some basic knowledge of the way JungleDocs for Office 365 works. Make sure you understand the following before you start implementing formulas in your document templates:

What formulas are and when to use them

Formulas are simple commands. In JungleDocs for Office 365 you use formulas as a way to tell the app what to do. This way, adding a formula inside of content control in a document template will tell JungleDocs for Office 365  what metadata from SharePoint to place there. You could also use formulas to automate file naming.

Example

Your formula could, for example, be   Today() which would result in the document being filled with the date of creation: 01/02/2018 15:45:00 PM

To display the year only, you could extend your formula to be more specific: FormatDate(Today;"yyyy") ,  the result being: 2018 

Things to know about formulas

  • They are very similar to formulas used in spreadsheet programs such as Excel
  • You can use different values, functions, and  operators to build a formula
  • You can use them for automatically creating new file names
  • You can use them inside of content controls for pulling metadata from your SharePoint

What a formula looks like

A formula consists of several different parts. All formulas start with an action, e.g., ReportItems or GetView. Aside from that, they may include a location (a SharePoint list), potentially a list view title and filters. These are separated by semicolumns. 

The example below shows a GetView formula, used to pull SharePoint data from a specific view into a document.

Common values

  • Numeric values.
    Example :  1.25 .
  • Text values surrounded by quotation marks
    Example:  "some text"
  • True / False – Yes/No values. These can be used for setting a SharePoint Yes/No Column value.
  • Now – retrieves current date and time. Text representation of date and time depends on your current SharePoint Site Regional Settings.
    Example:  Now Result: 6/6/2012 12:55:56 PM
  • Today – retrieves current date (time is 00:00). The text representation of date depends on your current SharePoint Site Regional Settings.
    Example:  Today Result: 6/6/2012 12:00:00 AM
  • 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.

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

SharePoint-specific values

  • SharePoint Column values. A column display name or internal name can be used in formulas.
    Example:   Title
  • SharePoint Lookup Column values. Use a colon to specify a lookup related column name.
    Example:   Customer:Address
  • Me – retrieves the current SharePoint User's full name.
  • Other SharePoint specific values  like  ListTitle , ListUrl ,    ItemUrl.

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

Common functions

  • FormatDate(date; format) – converts date to text representation using specified format ("d", "t", "yy", "yyyy", "MMMM", "MM", "dd",). It also can be used to extract part of a date.
    Example:  FormatDate(Today; "yyyy") Result :  2016 FormatDate(DocumentDate; "yyyy-MM-dd") Result: 2021-06-18 . Learn more about date formatting here.
  • CurrencyToLiteral(amount) – converts a number to a currency text representation. The currency format is taken from your 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 starts at  the  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
  • 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.

    • Logic functions

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

    • Record set functions

    A record set is any formula or command that returns a list of items (e.g., GetView, ReportItems)

  • Sum(recordSet; fieldName) - returns the sum of its arguments. The arguments can be numbers, cells references or formula-driven numeric values.
  • Count(recordSet) - returns the number of values in the list of arguments (recordSet).
  • Average(recordSet; fieldName) - will calculate the average (mean) value from all items specified in recordSet by fieldName field.
  • Median(recordSet; fieldName) - will calculate the median from all items specified in recordSet by fieldName field.
  • Max(recordSet; fieldName) - will find the largest number from all items specified in recordSet by fieldName field.
  • Min(recordSet; fieldName) - will find the smallest number from all items specified in recordSet by fieldName field. 
  • GroupBy(recordSet; fieldName) - will group 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. Supports up to 3 fields. Orders in ascending direction if ascending is true or in descending direction if ascending is false. The last ascending argument is optional.
  • GetUniqueItems(recordSet; fieldName) – Returns only items that have unique values in a specified column.

SharePoint-specific functions

  • GetNextNumberedValue(columnName; prefix; numberFormat; suffix) 
    Searches the list and gets the next sequence number. Used for automatic document numbering in JungleDocs.
    Example:  GetNextNumberedValue("BaseName"; "INV-"; "PadLeft($NextNumber; 4; \"0\")"; "") Result: INV-0001
  • Image(imageUrl) 
    Finds an image by URL. A full or site-relative URL can be specified. The image must be located in your 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.
    IfEmpty(testValue; trueValue; falseValue) 
    Returns trueValue if testValue is empty. If it isn't, falseValue is displayed.
  • GetView(listTitleOrUrl; viewTitleOrUrl; [additionalParameter; additionalParameterValue])
    Returns record set from particular list and view, additional conditions to filter items from view are supported. Read more.
  • GetVersions([rowLimit]; [majorOnly]; [ascendingOrder]; [imitateNextVersion]) 
    Returns a document version list. Read more.
  • FindItems(listTitleOrUrl; columnName; value; [additionalParameter; additionalParameterValue])<br>Returns items from a SharePoint list, filtered by specific values. Read more.
  • GetItemAttachments() 
    Retrieves item attachments from a SharePoint list. The GetItemAttachments function works only within a repeated section. Read more.
  • 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.
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us

Related Articles