Formula and Function Reference

Skuid uses formulas and functions in:

  • UI-Only fields, to define the metadata of a field with a Display type set to Formula. (Select code-block Edit Formula to open the Formula Editor.)
  • Metadata overrides when a field’s Display type is changed to Formula. (Select code-block Edit Formula to open the Formula Editor.)
  • In situations where an action occurs as the result of a formula, for example in Update rows and in logic actions. (Select code-block Branch Formula to open the Formula Editor.)

When the Formula Editor is open, Skuid offers dropdown menus of Operators, Functions, and (when applicable) Fields. (Only those fields subject to the formula are offered.)

Formula Functions with UI-Only Fields

When a UI-Only field’s display type is set to Formula, the field’s value are determined by the statement listed within the Formula tab of the Properties pane. To create this formula statement, you may use merge variables, strings, numbers, and a variety of UI-Only functions.

Formula Return Type

A UI-Only field’s formula return type determines how Skuid stores the result of the formula. Options include:

  • Checkbox
  • Currency
  • Date
  • Datetime

Note

Skuid stores all date and datetime fields in ISO 8601 format.

When directly manipulating the value of a date or date time field in a Skuid model, the value must be formatted as follows:

  • Dates: yyyy-MM-dd
  • DateTimes: yyyy-MM-dd'T'HH:mm:ss[.SSS]'Z'
  • Percent
  • Number
  • Text

After evaluating a field’s formula, Skuid attempts to convert the result into the selected return type. For example, if the result of the formula is the text value "003" and the return type is Number, Skuid converts that value into an actual number, 3, before storing it in the model.

Warning

It’s important that the formula return type be appropriate for the output of the function. For example, for a formula that formats a date value into a non-ISO 8601 format, select an output type of Text. If you instead select the Datetime return type, Skuid attempts to convert that date string into an actual date value—and errors may occur.

Operators

For all formula functions, Skuid uses standard transactional operators within formulas.

Equality operators in Skuid

Equality operators are always interpreted strictly, meaning they compare both type and value. For example, the formula 1 = "1" returns false, because "1" is a string type, even though the value could be interpreted as “one.”

Note

Even if these equality operators are formatted non-strictly (= and !==), Skuid nonetheless interprets them strictly. Both = and == are interpreted as follows:

1 = "1" returns false

1 == "1" also returns false

Warning

In previous Skuid releases, equality operators could be used non-strictly (== for equal and !== for does not equal). This functionality is no longer supported.

Conditional operation

  • IF: Returns one of two values based on whether an equation (or equations) are true or false.

    IF(equations, value1, value2)

    • equations: The equations to evaluate. Equations can be evaluated using the transactional operators and can be chained with the && and || operators.
    • value1: The value returned if the equation is true.
    • value2: The value returned if the equation is false.
  • ISBLANK: Returns true or false based on whether its parameter is blank. This function returns true if its parameter is an empty string, null, or undefined.

    True examples:

    • ISBLANK('') = true
    • ISBLANK(null) = true
    • ISBLANK(undefined) = true

    False examples:

    • ISBLANK('test') = false

    • ISBLANK(true) = false

    • ISBLANK(9) = false

      Note

      If the string contains a value that’s typically false-y, that is not considered blank.

    • ISBLANK("0") = false

    • ISBLANK("false") = false

  • NOT: Returns the opposite boolean value of its parameter, which must be a boolean value or an expression that evaluates to a boolean value. For expressions returning true, this formula returns false, and vice versa.

    • NOT(true) = false
    • NOT(false) = true
    • NOT(2+2==4) = false
    • NOT(2+2==7) = true
    • NOT(ISBLANK('')) = false
    • NOT(ISBLANK('Not an empty string')) = true

    Note

    While expressions within this function evaluate before this function executes, if those expressions return non-boolean values (like strings or null values) then this function returns true.

Specific function examples

Note

Strings must be surrounded by single ('string') or double quotes ("string”). Either work; just be consistent. While JavaScript uses both, both the Salesforce and JSON formats favor double quotes, so that is used in the following examples.

Number manipulation [[]]

  • ABS: Returns the absolute value of a number (42 and -42 both return 42).
  • CEIL: If a number is not whole, it is rounded up to the next integer.
  • FLOOR: If a number is not whole, it is rounded down to the next integer.
  • ROUND: If a number is not whole, it is rounded up or down to the next integer depending on which is nearer.

Text manipulation and analysis [[]]

  • 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.skuidsite.com/", "/do-not-duplicate-the-slashes-please/")

      Result: https://example.skuidsite.com/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, Skuid 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:

       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      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:

      1
      2
      3
      4
      5
      CASE(
        {{Input}},
        "Foo", "Bar",
        "Hello", "World",
        "Unknown")
      
    • A CASE function without a default value looks like this:

      1
      2
      3
      4
      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.

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

Referential data access [[]]

  • 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 Skuid 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}})

    Warning

    Models load in the order they are listed within the App Composer’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.

Date / Time format and manipulation [[]]

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

  • 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 data sources 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.

    Note

    For Skuid on Salesforce users, the user’s current time zone is based on the user’s Salesforce locale.

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

  • TODAY: Returns the current date from the user’s time zone.

    Note

    TODAY uses the device’s time zone to get a value representing midnight. When that value is converted into a date or datetime, it is translated to the user’s local time zone based on the user’s Salesforce locale. These time zones may not match.

    If the user’s time zone is behind the device’s time zone, the datetime value is rolled back by the number of hours that reflect that discrepancy. For example, if the device’s time zone is at 1 am, but the user’s time zone is 4 hours earlier, the value returned will be 9 pm (1 am minus 4 hours). This results in a date/datetime value that represents yesterday, rather than today.

    While TODAY and NOW (below) both return datetime values, those values will be different (except at midnight). Skuid recommends that you use NOW unless it’s necessary to use the device’s time zone for date/datetime values. Using NOW will result in datetime values that are more consistent with the user’s time zone.

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

Custom Formula Functions

Note

If you are a developer and would like to learn more about creating custom formula functions using JavaScript, see the skuid.formula API documentation.

To use a custom formula function within the App Composer:

  1. Ensure the formula function is available to a page within either an inline resource or a static resource.

  2. Input the namespace—followed by two underscores—and the name of the formula function in the formula field:

    nameSpace__formulaName(arg1, ... argN)