Workflow Calculated Fields

In Workflows, there is a particularly powerful and complex Action called Calculated Fields which allows you to do operations on the values in the records you’re handling.

216Calculate fields

Adding parameters

It is possible to add parameters to the formulas by using the dropdown in the Parameters section of the Calculate Fields’s user interface. The dropdown contains all of the (basic and custom) fields which belongs to the module selected in the basic fields section.

To add a parameter, select the field from the dropdown and click Add parameter. After this action, a new line appears in the parameter table with the name of the field and the given identifier.

For some fields (dropdowns and multi-selects) an additional dropdown shown up where the user can select if the raw or the formatted value should be used in Calculated Fields. The raw format means the value which is stored in the database and the formatted value means the label for that database value.

To remove a parameter from the table, simply click the - in the row of the parameter. Be aware, that if you remove a parameter, all of the identifiers are recalculated, so the identifiers could change for fields!

217Add parameter

The identifier is used to reference this field when the user creates the formula. For example all appearances of the {P0} identifier will be replaced with the Account’s name in the formula. All parameters are like {Px} where x is the sequential order of the parameter. The amount of the parameters is not limited.

Adding relation parameters

Relation parameters are very similar to the regular parameters, the only difference is that the user first selects an entity which is in a one-to-one or one-to-many relationship with the actual entity.

To add a relation parameter, select the relation first, and then select the field from the connected entity and push the Add relation parameter button. After this action, a new line appears in the relation parameter table with the name of the relationship, the name of the field and the given identifier.

As for parameters for some relation parameter fields (dropdowns and multi-selects) an additional dropdown shown up where the user can select if the raw or the formatted value should be used in Calculate Fields.

To remove a relation parameter from the table, simply click on the - button in the row of the relation parameter. Be aware, that if you remove a relation parameter, all of the identifiers are recalculated, so the identifiers could change for fields!

128Adding relation parameter

The identifier is used to reference this field when the user creates the formula. For example all appearances of the {R0} identifier will be replaced with the creator user‘s username in the formula. All relation parameters are like {Rx} where x is the sequential order of the relation parameter. The amount of the relation parameters is not limited.

Creating formula for a field

In the Formulas part of the user interface the user can add formulas for fields of the actual entity.

To add a formula, select a field from the dropdown first and then push the Add formula button. After this action, a new line appears in the formula table with the name of the field and with the place for the formula.

To remove a formula from the table, simply click on the - in the row of the formula.

129Add formula

The formula is a textbox where the user can write the formulas. The module evaluates the formula on the given time (on save, on scheduler run or both) and fills the selected field with the evaluated value.

The formula can contain any text (with full UTF-8 support), but only the function parts (functions with parameters between ‘{‘ and ‘}’) are evaluated. For example and with the parameters added in the previous sections, if we fill the formula like: Account {P0} created by user name {R0}, then the description field will have the following value after save: Account My Account created by user name MyUser (implying the account’s name is My Account and the creator user’s username is MyUser).

The Calculate Fields has many built-in functions which allows the user to build complex formulas to achieve various goals. These functions are described in the next section.

Available Functions

As it is mentioned above, all of the functions are wrapped between { and } signs, and they look like {functionName(parameter1; parameter2; …)}. The count of the parameters are different for the different functions. The module evaluates the functions and changes them with their result in the formula.

The functions can be embedded into each other (using a result of a function as a parameter for another function) like in this example:

{power({subtract({divide({add({multiply(10; 2)}; 12)}; 8)}; 1)}; 2)}

This function is the formalised look of the following mathematical expression:

((((10 * 2) + 12) / 8) – 1)2

The functions are divided to six groups. These groups are described in the next section of the document.

Logical Functions

Logical functions are returning true or false in the form of 1 and 0 so checkboxes typed fields can be filled with these functions. They can be also used as the logical condition for the ifThenElse function.

equal

Signature

{equal(parameter1;parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 equals with parameter2

Returns

1 if the two parameters are equal or 0 if not

Example call

{equal(1; 2)} returns 0

notEqual

Signature

{notEqual(parameter1; parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 not equals with parameter2

Returns

0 if the two parameters are equal or 1 if not

Example call

{notEqual(1; 2)} returns 1

greaterThan

Signature

{greaterThan(parameter1; parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 greater than parameter2

Returns

1 if parameter1 greater than parameter2, 0 if not

Example call

{greaterThan(3; 3)} returns 0

greaterThanOrEqual

Signature

{greaterThanOrEqual(parameter1; parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 greater than or equal parameter2

Returns

1 if parameter1 greater than or equal parameter2, 0 if not

Example call

{greaterThanOrEqual(3; 3)} returns 1

lessThan

Signature

{lessThan(parameter1; parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 less than parameter2

Returns

1 if parameter1 less than parameter2, 0 if not

Example call

{lessThan(3; 3)} returns 0

lessThanOrEqual

Signature

{lessThanOrEqual(parameter1; parameter2)}

Parameters

parameter1: can be any value of any type

parameter2: can be any value of any type

Description

Determines if parameter1 less than or equal parameter2

Returns

1 if parameter1 less than or equal parameter2, 0 if not

Example call

{lessThanOrEqual(3; 3)} returns 1

empty

Signature

{empty(parameter)}

Parameters

parameter: text value

Description

Determines if parameter is empty

Returns

1 if parameter is empty, 0 if not

Example call

{empty(any text)} returns 0

notEmpty

Signature

{notEmpty(parameter)}

Parameters

parameter: text value

Description

Determines if parameter is not empty

Returns

1 if parameter is not empty, 0 if empty

Example call

{notEmpty(any text)} returns 1

not

Signature

{not(parameter)}

Parameters

parameter: logical value

Description

Negates the logical value of the parameter

Returns

1 if parameter is 0, 0 if parameter is 1

Example call

{not(0)} returns 1

and

Signature

{and(parameter1; parameter2)}

Parameters

parameter1: logical value

parameter2: logical value

Description

Applies the AND logical operator to two logical values

Returns

1 if parameter1 and parameter2 is 1, 0 if any parameters are 0

Example call

{and(1; 0)} returns 0

or

Signature

{or(parameter1; parameter2)}

Parameters

parameter1: logical value

parameter2: logical value

Description

Applies the OR logical operator to two logical values

Returns

1 if parameter1 or parameter2 is 1, 0 if both parameters are 0

Example call

{or(1; 0)} returns 1

Text Functions

Text functions are used to manipulate text in various ways. All the functions listed here are fully supports UTF-8 texts, so special characters should not raise any problems.

substring

Signature

{substring(text; start; length)}

Parameters

text: text value

start: decimal value

length [optional parameter]: decimal value

Description

Cuts the substring of a text field from start. If the length optional parameter is not set, then it cuts all characters until the end of the string, otherwise cuts the provided length. Indexing of a text’s characters starting from 0.

Returns

Substring of the given text

Example call

{substring(This is my text; 5)} returns is my text

{substring(This is my text; 5; 5)} returns is my

length

Signature

{length(parameter)}

Parameters

parameter: text value

Description

Count the characters in a text.

Returns

The count of the characters in a text.

Example call

{length(sample text)} returns 11

replace

Signature

{replace(search; replace; subject)}

Parameters

search: text value

replace: text value

subject: text value

Description

Replace all occurrences of search to replace in the text subject.

Returns

subject with replaced values.

Example call

{replace(apple; orange; This is an apple tree)} returns This is an orange tree

position

Signature

{position(subject; search)}

Parameters

subject: text value

search: text value

Description

Find position of first occurrence of search in a subject

Returns

Numeric position of search in subject or -1 if search not present in subject

Example call

{position(Where is my text?; text)} returns 12

lowercase

Signature

{lowercase(parameter)}

Parameters

parameter: text value

Description

Make text lowercase

Returns

The lowercased text.

Example call

{lowercase(ThIs iS a sAmPlE tExT)} returns this is a sample text

uppercase

Signature

{uppercase(parameter)}

Parameters

parameter: text value

Description

Make text uppercase

Returns

The uppercased text.

Example call

{uppercase(ThIs iS a sAmPlE tExT)} returns THIS IS A SAMPLE TEXT

Mathematical functions

Mathematical functions are used to manipulate numbers in various ways. Several mathematical operators are implemented as functions in Calculate Fields.

add

Signature

{add(parameter1; parameter2)}

Parameters

parameter1: number value

parameter2: number value

Description

Adds parameter1 and parameter2

Returns

The sum of parameter1 and parameter2

Example call

{add(3.12; 4.83)} returns 7.95

subtract

Signature

{subtract(parameter1; parameter2)}

Parameters

parameter1: number value

parameter2: number value

Description

Subtracts parameter2 from parameter1

Returns

The distinction of parameter2 and parameter1

Example call

{subtract(8; 3)} returns 5

multiply

Signature

{multiply(parameter1; parameter2)}

Parameters

parameter1: number value

parameter2: number value

Description

Multiplies parameter1 and parameter2

Returns

The product of parameter1 and parameter2

Example call

{multiply(2; 4)} returns 8

divide

Signature

{divide(parameter1; parameter2)}

Parameters

parameter1: number value

parameter2: number value

Description

Divides parameter2 with parameter1

Returns

The division of parameter2 and parameter1

Example call

{divide(8; 2)} returns 4

power

Signature

{power(parameter1; parameter2)}

Parameters

parameter1: number value

parameter2: number value

Description

Raises parameter1 to the power of parameter2

Returns

parameter1 raised to the power of parameter2

Example call

{power(2; 7)} returns 128

squareRoot

Signature

{squareRoot(parameter)}

Parameters

parameter: number value

Description

Calculates the square root of parameter

Returns

The square root of parameter

Example call

{squareRoot(4)} returns 2

absolute

Signature

{absolute(parameter)}

Parameters

parameter: number value

Description

Calculates the absolute value of parameter

Returns

The absolute value of parameter

Example call

{absolute(-4)} returns 4

Date functions

There are several date functions implemented in Calculate Fields, so the user can manipulate dates in many ways. Most of the functions uses a format parameter, which is used to set the result of the functions formatted as the user wants to. The options for these formats are equivalent with the PHP format parameters:

Format character Description Example returned values

For day

d

Day of the month, 2 digits with leading zeros

01 to 31

D

A textual representation of a day, three letters

Mon through Sun

j

Day of the month without leading zeros

1 to 31

l

A full textual representation of the day of the week

Sunday through Saturday

N

ISO-8601 numeric representation of the day of the week

1 (for Monday) through 7 (for Sunday)

S

English ordinal suffix for the day of the month, 2 characters

st, nd, rd or th. Works well with j

w

Numeric representation of the day of the week

0 (for Sunday) through 6 (for Saturday)

z

The day of the year (starting from 0)

0 through 365

For week

W

ISO-8601 week number of year, weeks starting on Monday

42 (the 42nd week in the year)

For month

F

A full textual representation of a month, such as January or March

January through December

m

Numeric representation of a month, with leading zeros

01 through 12

M

A short textual representation of a month, three letters

Jan through Dec

n

Numeric representation of a month, without leading zeros

1 through 12

t

Number of days in the given month

28 through 31

For year

L

Whether it’s a leap year

1 if it is a leap year, 0 otherwise

o

ISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead

1999 or 2003

Y

A full numeric representation of a year, 4 digits

1999 or 2003

y

A two digit representation of a year

99 or 03

For time

a

Lowercase Ante meridiem and Post meridiem

am or pm

A

Uppercase Ante meridiem and Post meridiem

AM or PM

B

Swatch Internet time

000 through 999

g

12-hour format of an hour without leading zeros

1 through 12

G

24-hour format of an hour without leading zeros

0 through 23

h

12-hour format of an hour with leading zeros

01 through 12

H

24-hour format of an hour with leading zeros

00 through 23

i

Minutes with leading zeros

00 to 59

s

Seconds, with leading zeros

00 through 59

For timezone

e

Timezone identifier

UTC, GMT, Atlantic/Azores

l

Whether or not the date is in daylight saving time

1 if Daylight Saving Time, 0 otherwise

O

Difference to Greenwich time (GMT) in hours

+0200

P

Difference to Greenwich time (GMT) with colon between hours and minutes

+02:00

T

Timezone abbreviation

EST, MDT

Z

Timezone offset in seconds. The offset for timezones west of UTC is always negative, and for those east of UTC is always positive.

-43200 through 50400

For full date/time

c

ISO 8601 date

2004-02-12T15:19:21+00:00

r

RFC 2822 formatted date

Thu, 21 Dec 2000 16:01:07 +0200

U

Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

For all functions without timestamp parameter, we assume that the current date/time is 2016.04.29. 15:08:03

date

Signature

{date(format; timestamp)}

Parameters

format: format text

timestamp: date/time value

Description

Creates a date in the given format

Returns

timestamp in the given format

Example call

{date(ymd; 2016-02-11)} returns 160211

now

Signature

{now(format)}

Parameters

format: format text

Description

Creates the actual date/time in the given format

Returns

Current date/time in the given format

Example call

{now(Y-m-d H:i:s)} returns 2016-04-29 15:08:03

yesterday

Signature

{yesterday(format)}

Parameters

format: format text

Description

Creates yesterday’s date/time in the given format

Returns

Yesterday’s date/time in the given format

Example call

{yesterday(Y-m-d H:i:s)} returns 2016-04-28 15:08:03

tomorrow

Signature

{tomorrow(format)}

Parameters

format: format text

Description

Creates tomorrow’s date/time in the given format

Returns

Tomorrow’s date/time in the given format

Example call

{tomorrow(Y-m-d H:i:s)} returns 2016-04-30 15:08:03

datediff

Signature

{datediff(timestamp1; timestamp2; unit)}

Parameters

timestamp1: date/time value

timestamp2: date/time value

unit: years/months/days/hours/minutes/seconds; default: days

Description

Subtracts timestamp2 from timestamp1

Returns

The difference between the two dates returned in unit

Example call

{datediff(2016-02-01; 2016-04-22; days)} returns 81

addYears

Signature

{addYears(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount years to timestamp

Returns

Incremented date in format

Example call

{addYears(Ymd; 2016-04-22; 1)} returns 20170422

addMonths

Signature

{addMonths(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount months to timestamp

Returns

Incremented date in format

Example call

{addMonths(Ymd; 2016-04-22; 1)} returns 20160522

addDays

Signature

{addDays(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount days to timestamp

Returns

Incremented date in format

Example call

{addDays(Ymd; 2016-04-22; 1)} returns 20160423

addHours

Signature

{addHours(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount hours to timestamp

Returns

Incremented date in format

Example call

{addHours(Ymd H:i:s; 2016-04-22 23:30; 5)} returns 20160423 04:30:00

addMinutes

Signature

{addMinutes(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount minutes to timestamp

Returns

Incremented date in format

Example call

{addMinutes(Ymd H:i:s; 2016-04-22 22:58; 5)} returns 20160422 23:03:00

addSeconds

Signature

{addSeconds(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Adds amount seconds to timestamp

Returns

Incremented date in format

Example call

{addSeconds(Ymd H:i:s; 2016-04-22 22:58; 5)} returns 20160422 22:58:05

subtractYears

Signature

{subtractYears(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts amount years from timestamp

Returns

Decremented date in format

Example call

{subtractYears(Ymd; 2016-04-22; 5)} returns 20110422

subtractMonths

Signature

{subtractMonths(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts amount months from timestamp

Returns

Decremented date in format

Example call

{subtractMonths(Ymd; 2016-04-22; 5)} returns 20151122

subtractDays

Signature

{subtractDays(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts amount days from timestamp

Returns

Decremented date in format

Example call

{subtractDays(Ymd; 2016-04-22; 5)} returns 20160417

subtractHours

Signature

{subtractHours(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts mount hours from timestamp

Returns

Decremented date in format

Example call

{subtractHours(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 07:37:00

subtractMinutes

Signature

{subtractMinutes(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts amount minutes from timestamp

Returns

Decremented date in format

Example call

{subtractMinutes(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 12:32:00

subtractSeconds

Signature

{subtractSeconds(format; timestamp; amount)}

Parameters

format: format text

timestamp: date/time value

amount: decimal number

Description

Subtracts amount minutes from timestamp

Returns

Decremented date in format

Example call

{subtractSeconds(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 12:36:55

Control Functions

There is only one control function implemented in Calculate Fields so far, but this function ensures that the user can write very complex formulas with conditions. Since the functions can be embedded in each other, the user can write junctions with many branches.

ifThenElse

Signature

{ifThenElse(condition; trueBranch; falseBranch)}

Parameters

condition: logical value

trueBranch: any expression

falseBranch: any expression

Description

Selects one of the two branches depending on condition

Returns

trueBranch if condition is true, falseBranch otherwise

Example call

{ifThenElse(\{equal(1; 1)}; 1 equals 1; 1 not equals 1)} returns 1 equals 1

Counters

There are several counters implemented in Calculate Fields which can be used in various scenarios.

The counters sorted into two groups:

  1. Global counters: Counters which are incremented every time an affected formula is evaluated

  2. Daily counters: Counters which resets every day. (Starting from 1)

In this chapter we assume that the counters current value is 4, so the incremented value will be 5 with the given format.

GlobalCounter

Signature

{GlobalCounter(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name with length numberLength

Returns

Counter with length numberLength

Example call

{GlobalCounter(myName; 4)} returns 0005

GlobalCounterPerUser

Signature

{GlobalCounterPerUser(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the user who creates the entity with length numberLength

Returns

Counter with length numberLength

Example call

{GlobalCounterPerUser(myName; 3)} returns 005

GlobalCounterPerModule

Signature

{GlobalCounterPerModule(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the module of the entity with length numberLength

Returns

Counter with length numberLength

Example call

{GlobalCounterPerModule(myName; 2)} returns 05

GlobalCounterPerUserPerModule

Signature

{GlobalCounterPerUserPerModule(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the user who creates the entity and for the module of the entity with length numberLength

Returns

Counter with length numberLength

Example call

{GlobalCounterPerUserPerModule(myName; 1)} returns 5

DailyCounter

Signature

{DailyCounter(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name with length numberLength

Returns

Counter with length numberLength, or if the counter is not incremented this day then 1 with length numberLength

Example call

{DailyCounter(myName; 1)} returns 5

DailyCounterPerUser

Signature

{DailyCounterPerUser(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the user who creates the entity with length numberLength

Returns

Counter with length numberLength, or if the counter is not incremented this day for this user then 1 with length numberLength

Example call

DailyCounterPerModule

Signature

{DailyCounterPerModule(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the module of the entity with length numberLength

Returns

Counter with length numberLength, or if the counter is not incremented this day for this module then 1 with length numberLength

Example call

{DailyCounterPerModule(myName; 1)} returns 5

DailyCounterPerUserPerModule

Signature

{DailyCounterPerUserPerModule(name; numberLength)}

Parameters

name: any text

numberLength: decimal number

Description

Increments and returns the counter for name for the user who creates the entity and for the module of the entity with length numberLength

Returns

Counter with length numberLength, or if the counter is not incremented this day for the user who creates the entity and for this module then 1 with length numberLength

Example call

{DailyCounterPerUserPerModule(myName; 1)} returns 5

Example - Calculate monthly fee for an opportunity

Use Case

The user would like to calculate a monthly fee of an opportunity to a custom field by dividing the amount of the opportunity by the duration.

Setup

Our opportunities module has a dropdown field called Duration with values: (database value in brackets) 6 months [6], 1 year [12], 2 years [24]. There is also a currency field called Monthly.

Workflow

Go to Workflow module and create a new Workflow. Set the base options like the following:

Name: as you wish

Workflow Module: Opportunities

Status: Active

Run: Only on save

Run on: All records

Repeated runs: checked

130Example workflow

We do not create any conditions, since we would like the Workflow to run on all opportunities.

Now, add an action and select Calculate Fields from the dropdown.

Then, add two fields from Opportunities as parameters. First, select Opportunity amount (amount) and add it as a parameter (it will be {P0}) then select Duration and the raw value option from the data type dropdown and add it as parameter two (it will be {P1}). There is no need to add any relational parameters for this formula.

Now, add a formula for the monthly field and fill the textbox with the following formula:

{divide({P0}; {P1})}

So the whole action should look like this:

ExampleCF updated1

Save the Workflow and create a new Opportunity:

ExampleCF orig2

As you can see, we did not even add the monthly field to the EditView, because we don’t want to force the user to make calculations. Save the Opportunity and check the results on the DetailView:

ExampleCF orig3

AOW Calculated Fields was contributed by diligent technology & business consulting GmbH

Removing Actions

You can remove Action Lines by clicking the x on the top right hand side of the Action.

131Removing actions

Removing Field and Relationship Lines

You can remove Field and Relationship Lines by clicking the - on the left hand side of the Action.

Removing Fields/Relationship Lines

Content is available under GNU Free Documentation License 1.3 or later unless otherwise noted.