Skip to main content

Formula Tool

Use Formula to create new columns, update columns, and use 1 or more expressions to perform a variety of calculations and operations.

For a list of Functions, see the Workflow Functions Reference page.

You can use the Formula tool to...

  • Apply conditional statements.

  • Convert numbers and strings.

  • Format dates.

  • Apply mathematical calculations.

  • Find the minimum and maximum values.

  • Cleanse string data.

  • Perform validation tests on data.

Tip

This tool has a One Tool Example. Visit Access Sample Workflows to learn how to access this and many other examples directly in Designer Cloud.

Tool Components

Formula Tool anchors.png

Figure: Formula Tool with anchors.

The Formula tool has 2 anchors.

  • Input anchor: Use the input anchor to select the data you want to apply a formula to.

  • Output anchor: Outputs the filtered data.

Configure the Tool

In Formula, select an Output Column of data from the Select a Columndropdown. You can choose an existing column or add a new column. To add a new column of data...

  1. Select + Add Column from the dropdown, and enter a new column name.

  2. Type: Select from the Supported Data Types.

  3. Select the expression editor to build your expression. Once the workflow runs, the Data Preview box displays the first row of data from the specified column with the expression applied.

Build an Expression

Use any of these methods to build an expression.

  • Enter text directly in the expression editor.

  • Press Ctrl + Space to view a list of all functions.

  • Enter a word or phrase to view a matching list of functions.

  • Enter a [ (left bracket) to view a list of variables that you can use in the expression.

    • Columns: Data from an incoming connection or from a column created in a previous expression.

  • Select the Functions icon (fx) to search and browse through categories of functions.

  • Select the Columns and Constants icon (x) to search and browse through incoming or newly created columns and constants.

Available Functions

These functions are available for use in the expression editor. For more information on these functions, go to Workflow Functions.

Function

Function Type

IIF

Conditional

SWITCH

Conditional

IF condition THEN t ELSE f ENDIF

Conditional

IF c THEN t1 ELSEIF c2 THEN t2 ELSE f ENDIF

Conditional

CharFromInt

Conversion

ToNumber

Conversion

ToString

Conversion

DateTimeAdd

DateTime

DateTimeYear

DateTime

DateTimeMonth

DateTime

DateTimeDay

DateTime

DateTimeHour

DateTime

DateTimeMinutes

DateTime

DateTimeSeconds

DateTime

DateTimeDiff

DateTime

DateTimeNow

DateTime

DateTimeToday

DateTime

DateTimeFirstOfMonth

DateTime

DateTimeLastOfMonth

DateTime

ToDate

DateTime

ToDateTime

DateTime

DateTImeParse

DateTime

DateTimeFormat

DateTime

DateTimeTrim

DateTime

=

Filter Operators

IsNotNull

Filter Operators

!=

Filter Operators

Contains

Filter Operators

>

Filter Operators

IsNotEmpty

Filter Operators

IsNull

Filter Operators

NotContains

Filter Operators

>=

Filter Operators

<

Filter Operators

<=

Filter Operators

IsEmpty

Filter Operators

IsTrue

Filter Operators

DateRange

Filter Operators

IsFalse

Filter Operators

PeriodBefore

Filter Operators

PeriodAfter

Filter Operators

FinanceIRR

Finance

FinanceXIRR

Finance

ABS

Math

ASIN

Math

ATAN

Math

ATAN2

Math

Average

Math

CEIL

Math

EXP

Math

FLOOR

Math

LOG

Math

Median

Math

Mod

Math

PI

Math

POW

Math

RAND

Math

RandInt

Math

Round

Math

SmartRound

Math

SQRT

Math

TAN

Math

Max

Min/Max

Min

Min/Max

Addition +

Operators

Subtraction -

Operators

Division /

Operators

Multiplication *

Operators

Boolean AND &&

Operators

Boolean AND - Keyword

Operators

Boolean NOT !

Operators

Boolean NOT - Keyword

Operators

Boolean OR ||

Operators

Boolean OR - Keyword

Operators

Equal To =

Operators

Not Equal To !=

Operators

Greater Than >

Operators

Greater Than or Equal >=

Operators

Less Than <

Operators

Less Than or Equal <=

Operators

value IN (...)

Operators

value NOT IN (...)

Operators

Null

Specialized

Contains

String

CountWords

String

EndsWith

String

FindString

String

GetWord

String

Left

String

Length

String

LowerCase

String

PadLeft

String

PadRight

String

REGEX_CountMatches

String

REGEX_Match

String

REGEX_Replace

String

Replace

String

ReplaceChar

String

ReplaceFirst

String

ReplaceString

String

Right

String

StartsWith

String

StripQuotes

String

Substring

String

TitleCase

String

Trim

String

TrimLeft

String

TrimRight

String

Uppercase

String

CompareEpsilon

Test

IsEmpty

Test

IsInteger

Test

IsNull

Test

IsNumber

Test

IsString

Test

Add an Additional Expression

Adding multiple expressions to a single Formula tool can be helpful when modifying data in related columns or performing similar operations. When modifying unrelated data or performing unrelated functions, using multiple Formula tools assists with workflow troubleshooting. To add an expression, select Add Formula.

formula-add-formula.jpg

Figure: Add formula.

Reorder an Expression

The sequence in which expressions are applied affects the results when multiple expressions modify the same data. To reorder an expression, select and hold anywhere on the expression title and then drag the expression up or down.