Multi-Row Formula Tool

The Multi-Row Formula tool takes the concept of the Formula tool a step further, allowing the user to utilize row data as part of the formula creation. This tool is useful for parsing complex data, and creating running totals, averages, percentages and other mathematical calculations.

The Multi-Row Formula tool can only update one field per tool instance. If you would like to update multiple fields, a Multi-Row Formula tool must be added to your workflow for each field to be updated.

Configuration Properties

  1. Choose the field to update or create a New Field by selecting the appropriate radio button. If a New Field is selected, specify the name, type and size of the field.

  2. Specify the Number of Rows that will display as Variables in the expression editor.

  3. When the Active row is the first row, you will need to specify the Values for the rows that don't exist. Default is "0 or Empty." Choices include:

  4. Specify the Group by fields. Either check the box next to the group field, or select use the All or Clear buttons to the right. This step is optional. A typical reason one may select a group by field would be to create a running total.

  5. Build the expression that will update the chosen field. See Expression Editor.

Example: You have an input with two fields, Name and Value. You want to scan through the data of the Name field for "Transaction" and assign a Transaction Group value to the rest of the records by the instance of that name.

Your Configuration Properties will look like:

And the resulting data will look like:

Name

Value

New Field: Transaction Group

Transaction

2770

1

TMC

2

1

JAG

3

1

DLS

6

1

LMD

8

1

DM

9

1

ZB

7

1

Transaction

2771

2

JAG

3

2

DLS

4

2

ZB

9

2

Transaction

2773

3

TMC

5

3

ZB

7

3

DM

9

3

Because this tool includes an expression editor, an additional input anchor displays when the tool is used in an app or macro workflow. Use the Interface tools to connect to a Question anchor. See Interface Tools.