JungleDocs for Office 365 | Using formulas

To let JungleDocs 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:

• How to configure document templates
• What content controls are and how to use them
• How to create and use JungleDocs rules

What are formulas and when do you use them?

Formulas are simple commands. In JungleDocs you use formulas as a way to tell the app what to do. In 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 let JungleDocs automatically create file names.

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

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

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