{power({subtract({divide({add({multiply(10; 2)}; 12)}; 8)}; 1)}; 2)}
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.
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!
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.
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!
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.
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.
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.
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 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.
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 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.
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 |
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 |
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 |
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 |
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 |
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 are used to manipulate numbers in various ways. Several mathematical operators are implemented as functions in Calculate Fields.
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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.
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 |
There are several counters implemented in Calculate Fields which can be used in various scenarios.
The counters sorted into two groups:
Global counters: Counters which are incremented every time an affected formula is evaluated
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.
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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.
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.
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 |
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:
Save the Workflow and create a new Opportunity:
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:
AOW Calculated Fields was contributed by diligent technology & business consulting GmbH
You can remove Action Lines by clicking the x on the top right hand side of the Action.
You can remove Field and Relationship Lines by clicking the - on the left hand side of the Action.
Content is available under GNU Free Documentation License 1.3 or later unless otherwise noted.