Using formulas

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 365. However, using formulas properly requires you to have some basic knowledge of the way JungleDocs 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 you use formulas as a way to tell the app what to do. This way, adding a formula inside of a content control in a document template will tell JungleDocs 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

If I would only want the year mentioned, 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, in JungleDocs you will often use:  ReportItems or GetView. Aside from that, they may include a location (a SharePoint list), potentially a 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 current your SharePoint Site Regional Settings.
    Example:  Today Result: 6/6/2012 12:00:00 AM

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

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")
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