Formula and Function Reference

JOIN_TEXT: Combines text strings with a separator between them, while skipping empty parameters. It accepts three or more parameters:

JOIN_TEXT("separator", string1, string2, ..., stringN)

• separator: The text that appears between strings. Must be encased in quotation marks. This function guarantees that, if the separator also happen to be within a parameter, the separator appears once—and only once— between the parameters, preventing duplication.
• string1: The first text string, which appears before the separator in the displayed value ( This can be a hardcoded string or a merge variable.) If this is an empty value, then the parameter is skipped.
• string2 - stringN: Any additional string parameters; these appear after the first separator, with additional separators between each parameter. ( These can be hardcoded strings or a merge variable.) If this is an empty value, then the parameter is skipped.

Examples:

• Combining hardcoded strings and merge variables:

  JOIN_TEXT("+","A hardcoded string",{{AFieldFromAModel}})

  Result: A hardcoded string + That field's text value

• The separator is not duplicated:

  JOIN_TEXT("/", "http://example.nintex.app/", "/do-not-duplicate-the-slashes-please/")

  Result: https://example.nintex.app/do-not-duplicate-the-slashes-please/

• Empty parameters are skipped:

  JOIN_TEXT("/", "", "start", "", "middle/", "", "/end/", "")

  Result: start/middle/end/

Note:  As its name implies, this function only works with text strings. If a hard-coded parameter is not encased in quotes, or if a parameter is a merge variable for a number-based field, Nintex Apps returns errors.

JOIN_TEXT vs COMBINE_STRINGS

JOIN_TEXT allows for multiple string parameters and more intuitive separator behavior; it replaces the now deprecated COMBINE_STRINGS function.

If you have used COMBINE_STRINGS in the past, note that:

1. JOIN_TEXT handles separators differently.

2. The order of parameters in JOIN_TEXT is different.

   Compare the following formulas:

   JOIN TEXT	COMBINE_STRINGS
   JOIN_TEXT(null, "/test", "/") returns /test/	COMBINE_STRINGS(null, "/test", "/") returns test
   JOIN_TEXT("/", null, "/test") returns /test	COMBINE_STRINGS("/", null, "/test") returns /

CONTAINS: Accepts two parameters and returns true if the second parameter is contained anywhere within the first. If not, the formula returns nothing.

• CONTAINS("A string of text", "text") = true
• CONTAINS("A string of text", "123") returns nothing.

Note:  This function is case sensitive.

CONTAINS_ANY: Determines if a string contains any of a list of provided, case sensitive, parameters. Each parameter can be a merge variable or a hardcoded string, but any strings must be encased in quotes.

CONTAINS_ANY( stringToExamine, stringToLookFor1, stringToLookFor2, ... )

Examples:

• CONTAINS_ANY({{InputField}}, "Foo", "bar", "BAZ")
  • If InputField is "Yummy Food", the above function would be true
  • If InputField is "barbeque" the above function would be true
  • If InputField is "ice cream baz" the above function would be false

• CONTAINS_ANY({{InputField}}, {{InputField}}) =true

CONTAINS_ALL: Determines if a string contains all of the provided, case sensitive, parameters. Each parameter can be a merge variable or a hardcoded string, but any strings must be encased in quotes.

CONTAINS_ALL( stringToExamine, stringToLookFor1, stringToLookFor2, ...)

Examples:

• CONTAINS_ALL({{InputField}}, "Foo", "bar", "fruity drinks")
  • If InputField is "Food, barbeque, and fruity drinks", the above function would be true
  • If inputField is "Food and barbeque", the above function would be false
  • If InputField is "Foo bar fruity drinks", the above function would be true

CASE: Returns different results based on whether the parameter meets the specified criteria instead of using complicated chains of IF functions. Each parameter can be a merge variable or a hardcoded string, but any strings must be encased in quotes. An optional default value can be added as a final parameter for use if none of the provided case-result pairs match.

CASE(testValue, case1, result1, case2, result2, ..., default)

Note:  Case functions can be entered as a single line, but the examples below are multi-line for ease of reading.

Examples:

• To return a numeric indicator corresponding to text values for an Opportunity "Stage" picklist, the CASE function looks like this:

  Copy

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  CASE(
  {{StageName}},
  "Prospecting", 10,
  "Qualification", 20,
  "Needs Analysis", 30,
  "Proposal", 50,
  "Proof of Concept", 75,
  "In Review", 80,
  "Closed Won", 100,
  "Closed Lost", 0)
  

• A CASE function with a default value contains several pairs of cases and results, along with an extra parameter. That function looks like this:

  Copy

  1
  2
  CASE( {{Input}}, "Foo", "Bar", "Hello", "World", "Unknown")
  

• A CASE function without a default value looks like this:

  Copy

  1
  2
  CASE( {{StageName}}, "Prospecting", 10, "Closed Lost", 0)
  

ENDS_WITH: Accepts two parameters and returns true if the second parameter is at the end of the first. If not, the formula returns nothing.

FIND: Returns the index of the search term from within a string. The function searches left to right. It starts at the leftmost character unless an index value specifies a different start location. Case sensitivity is enforced by default. The index value for a no result return is -1.

FIND(stringToSearch, searchTerm, searchStartIndex, isCaseSensitive)

Example:

• FIND("Giant Squid", "Giant") = 0
• FIND("Giant Squid", "Squid") = 6
• FIND("Giant Squid", "Squid", 2) = 6
• FIND("Giant Squid", "Squid", 7) = -1
• FIND("Giant Squid", "SQUID", 0) = -1
• FIND("Giant Squid", "SQUID", 0, false) = 6

LEFT: Returns a specified number of characters starting from the left of the first parameter. Requires a string and the number of characters to return.

Example:

• LEFT("A string of text","4") = A st

LEN: Returns the length—or number of characters—of a string.

LOWER: Returns the value of the parameter with all letters as lowercase.

RIGHT: Returns a specified number of characters starting from the right of the first parameter. Requires a string and the number of characters to return.

Example:

• RIGHT("A string of text","4") = text

STARTS_WITH: Accepts two parameters and returns true if the second parameter is at the beginning of the first. If not, the formula returns nothing.

UPPER: Returns the value of the parameter with all letters as uppercase.

VALUE: Accepts a string and turns it into a number. This function can process + and - signs, numerals, decimal points, and exponents, but ignores other characters—and any numbers that come after them.

Examples:

• VALUE("42") = 42
• VALUE("0.42E+2") = 42
• VALUE("2A03") = 2

If the parameter for the formula starts with a letter, the formula returns nothing.

Example:

• VALUE("Text123") does not return a value.

MODEL_LOOKUP: A function that returns the value of a field within another model, similar to a VLookup Excel function. Commonly used for including one model's data or functionality—such as an aggregation on an aggregate model—within a component already assigned to a different model.

Note:  For the examples below, Model 1 refers to the model being looked up, while Model 2 refers to the model within this function.

To use MODEL_LOOKUP, there must be at least two parameters. However, the typical use case generally employs four parameters:

MODEL_LOOKUP("Model1Id","FieldIDfromModel1","KeyFieldfromModel1",{{KeyFieldfromModel2}})

• The first two parameters are required, and indicate which data to display from Model 1:

  • The ID of the model being looked up. (In the example: Model1Id.)
  • The field containing the value to display. (In the example: FieldIDfromModel1.)

• The third and fourth parameters are optional but excluding them only returns the first row of the model being looked up. These parameters provide context and tell Nintex Apps how to match that data to the current row:

  • A field ID or alias in Model 1 from parameter 1 that matches the key field from Model 2. (In the example: KeyFieldfromModel1.)
  • The field from Model 2 which correlates to the key field specified in the previous parameter—inserted via merge variable. (In the example: KeyFieldfromFieldfromModel2.)

  Example:

  • With all of that in mind, a practical example of this function is referencing the total amount of all opportunities for each account:

    • MODEL_LOOKUP(Opportunity,sumAmount,accountId,{{Id}})

Important:  Models load in the order they are listed within the Page Designer's Models tab. If Model 1 has a UI-Only field that references Model 2, and Model 1 loads before Model 2, this will produce an error because Model 1 won't be able to reference Model 2-- it's not loaded yet. Make sure the models on the page load in an appropriate order.

AMPM: Adds "am" or "pm" to a datetime field.

DATE_ADD: Takes a source date value, adds a specified amount of time to it—for example, 15 minutes, 2 weeks, or 5 months—and returns a new date. ( Use a negative integer to subtract time. )

Example:

• DATE_ADD(Date value, number, unit string, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR) .

Example:

• DATE_ADD(MyDate, 1, WEEK)

DATE_DIFF: Calculates the difference between two dates to determine how much time exists between them, then returns an amount of time in the designated time span (minutes, days, weeks, etc.)

The syntax is DATE_DIFF(first Date value], second Date value, unit string, such as SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR) . If a "first Date value" isn't designated, the default for that value is NOW.

Example:

• DATE_DIFF(MyDate, MySecondDate, DAY)

DAY_NAME_SHORT: Returns an abbreviated day name from a Date or datetime Field as an internationalized label.

DAY_NAME: Returns the full day name from a Date or datetime field as an internationalized label.

DAY_OF_WEEK_UTC: Returns a number corresponding to the UTC (Universal Coordinated Time) code for the day of the week from a Date or datetime field.

DAY_OF_WEEK: Returns a number corresponding with the day of the week (in local time) from a Date or datetime field.

DAY: Returns a number corresponding with the day in a month from a Date or datetime field.

FORMAT_DATE: Returns a string with the date formatted according to the format string specified. The format string follows jQuery's $.datepicker.formatDate(), whose format characters are based on Java's SimpleDateFormat. For more technical information, see the skuid.time topic.

FORMAT_TIME: Returns a string with the time portion of a datetime field formatted according to the format string specified. The format string follows jQuery's $.datepicker.formatDate(), whose format characters are based on Java's SimpleDateFormat. For more technical information, see the skuid.time topic.

HOUR_UTC: Returns the hour portion of a datetime field with no time zone adjustment.

HOUR: Returns the hour portion of a datetime field, adjusted for the time zone of the system.

MIDNIGHT: Returns the value of midnight for the current day based on the organization's time zone.

MILLISECOND: Returns the millisecond portion of a datetime field (if there is one.)

Note:  Salesforce Dates do not contain milliseconds. This function is only relevant for connections other than Salesforce.

MINUTE_UTC: Returns the minute portion of a datetime field with no time zone adjustment.

MINUTE: Returns the minute portion of a datetime field, adjusted for the time zone of the system.

MONTH_NAME_SHORT: Returns an abbreviated name of the month from a Date or datetime field as an internationalized label.

MONTH_NAME: Returns the full name of the month from a Date or datetime field as an internationalized label.

MONTH_UTC: Returns the number corresponding to the UTC (Universal Coordinated Time) code for the month from a Date or datetime field with no time zone adjustment.

MONTH: Returns the number for the month from a Date or datetime field, adjusted for the time zone of the system.

NOW: Returns a datetime using current time from the device.

SECOND_UTC: Returns the second portion of a Datetime field corresponding to the UTC (Universal Coordinated Time), with no time zone adjustment.

SECOND: Returns the second portion of a Datetime field, adjusted for the time zone of the system.

YEAR_UTC: Returns the number corresponding to the UTC (Universal Coordinated Time) for the year from a Date or datetime field with no time zone adjustment.

YEAR: Returns the number for the year from a Date or datetime field, adjusted for the time zone of the system.