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.
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.
- Numeric values.
- Text values surrounded by quotation marks
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.
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.
6/6/2012 12:00:00 AM
Note: Value types are automatically converted.
"10" + 2 Result:
&– Concatenation – Used for combining strings together.
"The" & " table"Result:
SharePoint Specific Operators
- Colon (
:) — Used for SharePoint Lookup Columns.
SharePoint Specific Values
- SharePoint Column values. A column display name or internal name can be used in formulas.
- SharePoint Lookup Column values. Use a colon to specify a lookup related column name.
Me– Retrieves the current SharePoint User's full name.
- Other SharePoint specific values like
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.
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.
two thousand five hundred dollars and zero centsNote: TotalAmount Column value is $2500.
Now()– Retrieves current date and time. The same as
Today()– Retrieves current date. The same as
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
SubString("abcde"; 2; 1)Result:
Lower(text)– Converts text to lowercase.
Upper(text)– Converts text to uppercase.
PadLeft(text; totalLength; symbol)– Right-aligns the characters in text, padding with spaces or symbol on the left for a specified total length.
PadLeft("123"; 5; "0")Result:
PadRight(text; totalLength; symbol)– Left-aligns the characters in text, padding with spaces or symbol on the right for a specified total length.
PadRight("123"; 5; "0")Result:
SharePoint Specific Functions
GetNextNumberedValue(columnName; prefix; numberFormat; suffix)– Searches the list and gets the next sequence number. Used for automatic document numbering in JungleDocs.
GetNextNumberedValue("BaseName"; "INV-"; "PadLeft($NextNumber; 4; \"0\")"; "")Result:
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.