Formula builder

Use the Formula builder to create formulas with calculated and connected values for variables A 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..

A formula consists of different parts and in this topic, the functions, inline formulas, and variables are distinguished in the following formats:

  • function
  • inline formula
  • variable

Let's look at a simple example on how to calculate a variable value using a formula. You have a leave request form, which has three fields: Name, Start date, and End date. To calculate the total days of leave, you must calculate the number of days between the start date and end date entered in the form. You can create a variable named LengthofLeave and apply the following formula using the dateDiff function:

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

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

  • You are applying for 10 days of leave.

For step-by-step instructions, see Step-by-step example: Create variables using formulas via Designer tab.

The formula builder can be used:

  • When configuring form controls in the form designer, you can insert inline formulas directly on a field that has the Insert option available.

  • When creating If-Then-Else statements for form rules, you can create and insert variables with applied formulas.

  • When creating form variables in the Variables tab.

Jump to:

Access the Formula builder

Access the Formula builder from the Form designer, Rules, and Variables tabs.

Open the Formula builder from the Designer tab

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. See Insert dialog fields, buttons, and settings for field descriptions.

  1. In the form designer, click the Designer tab.
  2. Click the form control where you want to insert a variable with an applied formula. The form control configuration panel is displayed.
  3. Hover over a field that has + displayed, until +Insert is displayed.
  4. Click Insert.

    The Insert dialog that includes the Formula builder is displayed.

  5. In the Formula field, type the formula.

    6. Note: 
    • As you type the formula, suggestions of possible functions and variables are displayed to select from if required.
    • From the Lookup pane on the right, you can search for Functions and variables to insert to the formula.
    • You can use logical operators such as && (And), || (Or), == (Equal), and != (Not Equal).
    • On the Functions tab, click the name of a function to display its description, format, and an example.

  6. After you create the formula, 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.

Open the Formula builder from the Rules tab

When creating If-Then-Else statements, you can create and insert variables with applied formulas.

To access the Formula builder via Rules tab:

  1. In the form designer, click the Rules tab.
  2. Depending on your workflow, click Add form rule or Add submit rule.
  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 over a field. The Insert dialog appears where you can view the Formula builder and a list of form controls and variables with applied formulas. See Insert dialog fields, buttons, and settings for detailed descriptions of the Insert dialog.
  6. To type a formula do one of the following:
    • Select the Formula tab on the left and type your formula in the Formula field and then click Insert. Your new variable is shown as an inline formula.
    • Select the Form variable tab on the left and then complete the following steps:
      1. Click Add form 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, you can create a rule to check that the age entered matches with the age calculated based on birthday. If true, the form can be submitted. Else, a warning message appears.

Note: 
  • As you type the formula, suggestions of possible functions and variables are displayed to select from if required.
  • From the Lookup pane on the right, you can search for Functions and variables to insert to the formula.
  • You can use logical operators such as && (And), || (Or), == (Equal), and != (Not Equal).
  • On the Functions tab, click the name of a function to display its description, format, and an example.

Open the Formula builder from the Variables tab

  1. In the form designer, click the Variables tab.

  2. Click Add form variable. The Add form variable section is displayed with the Formula builder.

For more information, see Variables

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 Label 1, and then type Leave Request form in the Text field.
    2. Click 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 Add form 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 the Variables 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:

  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 will look like this:

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.
SharePoint columns

Variables equivalent to the SharePoint columns in your list.

Context Context variables that represent data related to the current workflow instance, such as current item or site URL and workflow name.
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

Add form 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.

  Recalculate the variable in the following modes

Recalculates the variable when the form is in the selected mode. All modes are selected by default.

  • New: Recalculate the value when the form is displayed in new mode at run time.
  • Edit: Recalculate the value when the form is displayed in edit mode at run time
  • Display: Recalculate the value when the form is displayed in display mode at run time.

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.

For more information, see Functions.

 

 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.