Formula builder

Use the Formula builder to create formulas with calculated and connected values for variablesA placeholder that stores changes each time the workflow runs, such as the information entered in a form, or the name of a file. You can also create variables to store information you use in the workflow, such as an email address.. You can insert variables with applied formulas in forms and rules throughout your workflow.

Note: All variables that you create in Start event forms are available as an object under start event variables in the workflow; while variables created in Form based tasks are available as task variables. See Manage variables in a workflow.

In this topic, the functions, inline formulas, and variables are distinguished in the following formats:

  • function
  • inline formula
  • variable

Let's look at an example on how to create a formula for a variable value. Say you want to create a leave request form, which has three fields: Name, Start date, and End date.

To calculate the difference of number in days between the start date and end date entered in the form, you would create a variable named, for example, LengthofLeave and apply the following formula:

  • dateDiff(Datetime.TotalDays,Form.Start date,Form.End date)

On your form, you insert your LengthofLeave variable in a Label control so that the following message displays:

  • You are applying for 10 days of leave.

See the Step-by-step example: Create variables using formulas via Designer tab

Access the Formula builder

You can access the Formula builder from the Designer, Rules, and Variables tabs.

While designing forms, you can insert inline formulas directly on a field that has the Insert option available.

Note: Click the Insert option to insert form controls and variables with applied formulas into your message. See Insert window fields, buttons and settings for detailed descriptions on the Insert window.

To access the Formula Builder via Designer tab:

  1. In the form designer, click the Designer tab.
  2. Click the form control where you want to insert a variable with applied formula. The config panel appears on the right.
  3. In the field that has the Insert option, click in or hover your mouse to display the Insert option.
  4. Click Insert. The Formula builder appears.
  5. In the text box, type your formulas.

    6. Note: 
    • As you type the formula, suggestions of possible functions and variables appear on screen that you can select.
    • From the Lookup pane, search for functions and variables to insert in your formula.
    • You can use logical operators such as && (And), || (Or), == (Equal), and != (Not Equal).

    Tip: From the Functions tab, you can click the name of a function to display its description, format, and an example on how to use it.

  6. After you make changes, click Insert. Your new variable is shown as an inline formula and the calculated value becomes the default value.

    7. For example, avg(84,96,150) will insert 110 as the default value.

When creating If-Then-Else statements, you can:

  • Create and insert variables with applied formulas.
  • Insert form controls.

To access the Formula builder via Rules tab:

  1. In the form designer, click the Rules tab.
  2. Depending on your workflow, click Add New from:
    • Form rules
    • Submit rules
  3. Type a Name for your rule.
  4. Enter parameters for the following statements:
    • If
    • Then
    • Else
  5. Click the Insert option that appears on each field when you hover a field. The Insert window appears where you can view the Formula builder and a list of form controls and variables with applied formulas. See Insert window fields, buttons and settings for detailed descriptions of the Insert window.
  6. To type a formula do one of the following:
    • Select the Formula tab on the left and type your formula in the text box and then click Insert. Your new variable is shown as an inline formula.
    • Select the Form variable tab on the left and then do the following:
      1. Click Create variable to open the Formula builder.
      2. Type a Name for your variable.
      3. Select a Type.
      4. In the Value field, type your formulas.
      5. Click Create & insert.

      Your new variable with applied formula is now included in the If-Then statement.

      For example, the following rule checks that the age entered matches with the age calculated based on birthday. If true, the form can be submitted. Else, a warning message appears.

      Formula Builder sample rule to calculate age

Note: 
  • As you type the formula, suggestions of possible functions and variables appear on screen that you can select.
  • From the Lookup pane, search for functions and variables to insert in your formula.
  • You can use logical operators such as && (And), || (Or), == (Equal), and != (Not Equal).

Tip: From the Functions tab, you can click the name of a function to display its description, format, and an example on how to use it.

See Variables on how to access and use the Formula builder in the Variables tab.

This sample scenario will create a variable with a formula that calculates the number of days between two date variables (DateTime). We will create the variable in the Designer tab.

  1. In the form designer, click Designer tab.
  2. Design the form:
    1. Drag controls onto your form designer canvas in the following order:
    • Label 1
    • Text - short 1
    • Date/ time 1
    • Date/ time 2
    • Label 2
    1. Click on Label 1, and then type "Leave Request form" in the Text field.
    2. Click on Text - short 1, and then type "Name" in the Title field.
    3. Click on Date/time 1, and then type "Start date" in the Title field.
    4. Click on Date/time 2, and then type "End date" in the Title field.
    5. Click on Label 2, type "You are applying for days of leave" in the Text field, and then place cursor before the word "days".
  3. To create the variable with a formula:
    1. Click the Insert option that appears on the Text field. The Insert window appears.

    2. Click Create Variable. The Formula builder appears.
  4. Type "LengthofLeave" in Name.
  5. Select "Integer" in Type.
  6. From the Functions tab in the Lookup pane, hover your mouse over dateDiff then click Insert.

    7. Tip: You can click datediff to view its description, format, and an example on how to use it.

  7. In Value field, enter the parameters for the dateDiff function:

    8. dateDiff(Text date unit type, DateTime start, DateTime end)

    1. For Text date unit type, type "Total". A dropdown option appears where you can select the unit of time for your variable. Select TotalDays.
    2. For DateTime start, click Form tab, hover your mouse over Start date, and then click Insert.
    3. For DateTime end, hover your mouse over End date, and then click Insert.

    Your formula should look like below:

    Formula Builder example to use datediff function

  8. After you make changes, click Create & insert. The Formula builder closes and your new variable with applied formula is now created.

    9. Your form should look like the following:

User interface

Description
Formula

Formula builder to create formulas and variables.

Form variables

List of variables with applied formulas which you can use throughout your form and workflow.

This tab appears depending from where you click Insert. For example, this tab appears when you click Insert from the Label control in the Designer page.

Click Create variable to open the Formula builder where you can create variables with applied formulas.

Form controls

List of variables that hold values entered in form controls. Each form control that you add in the form designer is listed here.
Context

Variables that represent data related to the user of the current workflow instance. This includes:

  • First name

  • Last name

  • Email address

You can use context variables to personalize the text that appears on the published form. For example, the label can display a greeting with the signed-in user's first name and last name, such as "Welcome John Doe."

Important: 
  • Context variables are supported only in authenticated start forms.

  • Context variables are available only if you insert variables from the Text field of the Label control.

  • To view the output, you need to publish the workflow. Context variables are not yet available for preview.

External data

Variables that hold data from third-party services or Connectors.

Note: External data is available only when you insert a data source variable from the Text field of Label controls or from the Rules tab.

For more information, see Data source variables.
Name Name of the variable with applied formulas.

Type

Type of variable:

  • Text
  • Integer
  • Decimal
  • Boolean
  • DateTime

Value

The values and/ or formulas stored in the variable.
(Insert variable)

If in Designer tab, click to insert the variable into the Label control.

If In Rules tab, click to insert variable into a field of a statement.

(Edit variable)

Click to edit an existing variable.

(Delete variable)

Click to delete a variable.

Section

Field

Description

Create variable

Name

Type a name for your new variable.

 

Type

Select a type for your new variable.

 

Value

A text box where you type your formulas.

 

Create

Saves your new variable.

 

Update

Saves your updates.

 

Cancel

Discards your changes and hides the Formula builder.

Lookup

Functions

A list of functions that you can use to build formulas. Each function is a code that performs a specific operation.

Click the name of a function to display its description, format, and an example on how to use it.

 

 Variables

Includes the following:

  • Form variables: A list of variables created in the Formula builder.
  • Form controls: Variables that hold values in form controls.

 

Insert

Appears when you hover over a row in the Functions, Form, or Workflow tab.

Click to insert a function or variable into the Value box.

Functions

The following functions are available to use in the Formula builder:

Forms