JungleDocs 365: Using formulas

JungleDocs 365 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:

  • for calculating new file names.
  • for document content controls.

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

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

Examples

  • Calculating tomorrow's date: Today + 1.
  • Current time: FormatDate(Now; "t").

Still need help? Contact Us Contact Us