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
 |
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...
Select + Add Column from the dropdown, and enter a new column name.
Type: Select from the Supported Data Types.
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 |
/* block comment */ | Conditional |
// single-line comment | Conditional |
BinToInt | Conversion |
CharFromInt | Conversion |
CharToInt | Conversion |
ConvertFromCodePage | Conversion |
ConvertToCodePage | Conversion |
HexToNumber | Conversion |
IntToBin | Conversion |
IntToHex | Conversion |
ToDegrees | Conversion |
ToNumber | Conversion |
ToRadians | Conversion |
ToString | Conversion |
UnicodeNormalize | Conversion |
DateTimeAdd | DateTime |
DateTimeYear | DateTime |
DateTimeMonth | DateTime |
DateTimeDay | DateTime |
DateTimeHour | DateTime |
DateTimeMinutes | DateTime |
DateTimeSeconds | DateTime |
DateTimeDiff | DateTime |
DateTimeFirstOfMonth | DateTime |
DateTimeLastOfMonth | DateTime |
ToDate | DateTime |
ToDateTime | DateTime |
DateTImeParse | DateTime |
DateTimeQuarter | 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 |
FinanceCAGR | Finance |
FinanceEffectiveRAte | Finance |
FinanceFV | Finance |
FinanceFVSchedule | Finance |
FinanceIRR | Finance |
FinanceMIRR | Finance |
FinanceMXIRR | Finance |
FinanceNominalRate | Finance |
FinanceNPER | Finance |
FinanceNPV | Finance |
FinancePMT | Finance |
FInancePV | Finance |
FinanceRate | Finance |
FinanceXIRR | Finance |
FinanceXNPV | Finance |
ABS | Math |
ACOS | Math |
ASIN | Math |
ATAN | Math |
ATAN2 | Math |
Average | Math |
AverageNonNull | Math |
CEIL | Math |
COS | Math |
COSH | Math |
EXP | Math |
Factorial | Math |
FLOOR | Math |
LOG | Math |
LOG10 | Math |
Median | Math |
Mod | Math |
PI | Math |
POW | Math |
RAND | Math |
RandInt | Math |
Round | Math |
SIN | Math |
SINH | Math |
SmartRound | Math |
SQRT | Math |
TAN | Math |
TANH | Math |
BinaryAnd | Math: Integer |
BinaryNot | Math: Integer |
BinaryOr | Math: Integer |
BinaryXOr | Math: Integer |
ShiftLeft | Math: Integer |
ShiftRight | Math: Integer |
Between | Min/Max |
Bound | Min/Max |
Max | Min/Max |
MaxIDX | Min/Max |
Min | Min/Max |
MinIDX | 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 |
Coalesce | Specialized |
EscapeXMLMetacharacters | Specialized |
GetVal | Specialized |
Message | Specialized |
Null | Specialized |
RangeMedian | Specialized |
Soundex | Specialized |
Soundex_Digits | Specialized |
TOPNIDX | Specialized |
UrlEncode | Specialized |
Contains | String |
CountWords | String |
DecomposeUnicodeForMatch | String |
EndsWith | String |
FindNth | String |
FindString | String |
GetLeft | String |
GetPart | String |
GetRight | String |
GetWord | String |
Left | String |
Length | String |
LowerCase | String |
MD5_ASCII | String |
MD5_UNICODE | String |
MD5_UTF8 | 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 |
STRCSPN | String |
StripQuotes | String |
STRSPN | String |
Substring | String |
TitleCase | String |
Trim | String |
TrimLeft | String |
TrimRight | String |
Uppercase | String |
UuidCreate | String |
CompareDictionary | Test |
CompareDigits | Test |
CompareEpsilon | Test |
EqualStrings | Test |
IsEmpty | Test |
IsInteger | Test |
IsLowerCase | Test |
IsNull | Test |
IsNumber | Test |
IsString | Test |
IsUpperCase | 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.
 |
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.