This extension was designed to allow field calculations to be made as you type (on the spot). It displays the formula field results instantly without having to save the record in order to see the calculation result (standard VTiger workflow functionality needs the record to be saved).

It supports standard & custom fields with ability to multiply, divide, add and subtract currency/integer/decimal values as well as concatenate text, perform functions/calculations on date fields and other custom functions to manipulate Field X value based on Field Y.

The extension Real Time Field Formula, also known as Field Calculation/Concatenation is configured via standard VTiger Workflows. Here’s how you’d configure different kind of Workflows:

First few steps for all the Workflows should be similar with some exceptions.

You’d fill in the Workflow “Name”, “Description”, “Target Module”, and its “Status” as usual.

The settings here should be:

Trigger Workflow on: “Record Updated (Includes Creation)”

Recurrence: “Every time conditions are met”

This is where you will select the fields that are to be calculated or taken into account for the real time calculations and concatenations. Depending upon the formula or task to be done, select the appropriate option from either “All Conditions” or “Any Conditions”. Add as many field as you want to be calculated or concatenated.

You need to:

* Select a Field

* Choose the rule as required. It depends upon what type of calculation or concatenation are you looking to do

* Enter the value as per requirement if applicable

This is the most important section of the configuration of Field Formula Calculation/Concatenation. Its where you’d configure the formulas to SUM or concatenate the total into. Also, this is the block where we can differentiate the category of the calculation i.e. Mathematical, gap between dates, total days, concatenation of text etc.

* Click “Add Action”

* Select “Field Formula” as the action

Action Title: Simply add a title for your own reference

Execute on Demand: If you enable this option, it will show you the result in the total/sum/final/ concatenate field right away as you add values to the condition fields. That means it will be executing the task soon as you add values in fields INSTEAD OF upon actually saving the records.

Add Field: This is where you will select the final/total/sum/concatenate field (whichever is applicable). Choose the field from the list. All standard and custom fields will be available in this list. The fields are updated on the basis of these Standard Rules of VTiger open source Workflow actions. However, the standard rules cannot execute the calculations real time in the Edit view.

Note: Related Fields are calculated but not supported with real time calculations.

Formula Field: The field next to the “Add Field” can be considered as the formula field, as it is the one where you configure the formulas for the calculation and concatenation. Configure it as follow:

1. Expression: The picklist that will be saying default as “Raw text” – you need to set it to “Expression”.
2. Selecting the “Expression” will show further 2 picklists.
3. Use Field: This is where you select a field to be calculated/concatenated. The fields added at the step of “Add Conditions” should be available and selected here.
4. Use Function: Either you want to calculate amounts, gap between days, concatenate addresses or names etc.; this is where all the formulas are.

We’ll see some common examples of how to configure different types of fields to be concatenated. Further details on each of the Formula in the list and how effective they are, can be found here.

The Field Formula calculation/concatenation extension for VTiger is capable of performing nearly dozen types of calculations in real time. Let’s see how the most common ones of them are configured and worked.

To calculate amount or numbers in different fields into the total field, there are several options available. You can perform all of the basic Maths functions such as Add, Subtract, Multiply, Divide.

Configuration of Mathematical Calculations:

First you’d need to setup the workflow as explained in the “Workflow Configuration” section at the beginning of the guide. At the step of adding conditions, add the fields that you want calculated.

In our example, we have configured 4 fields as “Payments”, an “Additional Expenses” field, “Deduction”, “Rate” and “Fees”.

Now at the step of adding task > Field Formulas, we are calculating the above mentioned fields into a field named as “Total”. Now, we selected all the payments fields, Additional Expenses, Fees, and simply added them. Then we subtracted the Deduction field from the total. Also, we wanted to apply a Rate as a factor after the calculations which should be multiplied on the sum of all the previous fields. Here’s how the formula looks like now:

(cf_851 + cf_853 + cf_855 + cf_857 + cf_859 - cf_861 + cf_865) * cf_863

Cf_863 is for the field in which the Rate being applied.

Let’s take a quick look at configuration vs Real time functioning of the same.

Another good example of how powerful Field Formula extension for VTiger is the calculation between two dates, or any date and today. The extension can implement formula to calculate the total number of days between two dates including or excluding those dates as per your requirements.

Configuration of Calculation Between Dates/Date and Today:

==> Between Two Days:

To configure a Workflow for total number of days between any two days, you need to add “All Conditions” as “not empty” instead of Any Conditions due to the fact that total days will be calculated on the basis of dates entered in these two fields.

Then configuring the formula, it should look something like this:

time_diffdays(cf_881 ,cf_883)

The custom fields being calendar as type.

==> Between any Date and Today:

To configure a Workflow to calculate total number of days between a specific date and today, the only condition you need to add is an “All Conditions” as “Date field” > “is not empty”. That will help triggering the calculation soon as a date is entered, as the other date will automatically be set to Today with the help of the formula.

Then configuring the formula, it should look something like this:

time_diffdays(cf_883)

The custom field here being calendar type.

This is the type of field formula that helps to configure Picklist values, depending upon another picklist’s values.

To configure this type of field formula, you’d need to add “All Condition” as “Picklist 1” > “is” > “Value of the Picklist 1 you want”.

Then you need to add a field formula action as “Picklist 2” (the one that you want to change the value for), > “Picklist 2 Value” (this will be the value you set after the trigger is activated by Picklist 1).

Note that if you need to set a different outcome for Picklist 1’s different value, you have to create a separate workflow. That means if you have 10 values in the Picklist 1 and you want to trigger different actions on the basis of each of those, you will have to set 10 different Workflows.

You can configure as many such Workflows as required in order to acquire the required results.

The same configuration and rules as #3 can be used to check or uncheck a checkbox field, depending upon a Picklist Value. For example, Picklist value 1 > checkbox = checked. Picklist value 2 > checkbox = unchecked.

The difference will be at the step of adding field formula expression. In the field formula, you will select the checkbox field, and set the formula to:

[dt_highlight color=”blue”]true:boolean[/dt_highlight] – if you want the checkbox be enabled.

[dt_highlight color=”blue”]false:boolean[/dt_highlight] – if you want the checkbox be disabled.

Again, you will have to setup 2 different workflows for either of the statuses of the checkbox field.

This type of field formula allows to concatenate multiple text fields into one to sum up the required results. That can be used to get Full name, complete address, instructions for next steps etc.

To configure such type of Workflows, the starting steps will be same as described earlier in the section “Workflow Configuration”.

Add “Any Conditions” with all the fields you want to be merged into the final field.

Then, adding field formula task, you need to use the formula “concat” and put all the fields into a single bracket, separated by commas. After the commas, use single quotes (‘ ’) to add space, comma, full stop or even line break (\n). That will help to organize the concatenated text.

A formula could be similar to:

concat(mailingstreet,'\n',mailingcity,', ',mailingstate,' ',mailingzip,'\n',mailingcountry)

And this is the illustration of the settings: