Topics covered include:

Calculated fields in Pipeliner can be used for numerical calculations — e.g. the calculation of a Commission Amount based on a percentage of the Opportunity value — and also for “Scoring” fields where the calculation is based on values selected by your users in custom dropdown fields — e.g. the Opportunity Score field shown which is calculated from the values selected in the “Strength of Influence”, “Personality”, “Knowledge” and “Interest Level” fields. This article will show you how to create both types of calculation ⤵

Enter the Admin Module — from the Pipeliner CRM Application, click on the "App Switcher" icon in the top left corner ⤵

NOTE: Only users based on an Administrator role will see the Administration menu.

Select the "Fields & Forms" Menu Item. Next from the top of the page select the entity you would like to work with. In this article we will work on Opportunities but you use the exact same principles working with any other entity ⤵

Click on your field (or create it if it doesn’t already exist).

​See this article for instructions if you haven’t created custom fields before ⤵

Tick the “Make this field calculated (using formula)” box.

NOTE: If you don’t see this checkbox option, your field cannot be enabled as a calculated field (see Field Types later in this article).

Once you’ve enabled this option, the Calculated Formula box will display and below it will be a list of the fields that you can base your calculation formula on ⤵

You can enable the following field types as Calculated Fields - Integer Number, Float Number, Base Currency and Multiple Currencies fields.

NOTE: All currency calculations are based on the system base currency only — it is not possible to select the currency in a calculated field. For this reason, it’s best to always use Base Currency type fields when you intend to enable a calculation.

NOTE: When enabled as a Calculated Field, the calculation is applied across all forms that the field is used on. This is particularly relevant for Opportunities where you have different forms per pipeline. Specifically, this means that if you enable the Opportunity Value field as a Calculated Field, the same calculation will be used in each of your pipeline Opportunity Forms — there is no way to set different calculations per form.

NOTE: Following on from the note above, all fields used in your calculation have to be on the form you're using. If you're using a calculated field on the Lead, Account or Contact form, this is not going to be a problem BUT, if you have multiple pipelines, each with its own form, this means that for a calculation to work, all fields referenced in the calculation must be placed on each Opportunity form where the calculation is used. So, as an example, if Opportunity Value is going to be calculated as (Field A + Field B) x Field C then Fields A, B and C must all be placed on every Opportunity Form (as Opportunity Value appears on every Opportunity Form). If not, you'll get a 0 in your Opportunity Value.

Once you've enabled a field as "Calculated", you will then be able to access certain fields to add into your formula within that field. The following field types can be used within your calculation: Integer Number, Float Number, custom Date and custom Date/Time (not system/default fields), Base Currency, Multiple Currencies and Custom Dropdown fields.

NOTE: When calculating the difference between 2 custom Date or Date/Time fields, the result is returned as a number of seconds. You will, therefore, need to divide the value by, for example, 3600 to get the result in hours or 86400 to get the result in days.

NOTE: When creating the list of values for a custom dropdown field, you can add a numerical value to each text value and this numerical value can be accessed in a calculation formula. See the examples below for more detail.

We want to create a formula that calculates the "Commission $" for a deal as a percentage of the Opportunity Value. 

There are options as to how to set this up and the method you choose will depend on how you want to work:

This will work for you if you want your users to fill in the Opportunity Value and also type a number into the Percentage Commission field. The Commission Percentage value could be a full number (if you’ve created an Integer Number field) or include decimals (if you’ve created a Float Number field).

FORMULA: If this is your setup, the formula will be Opportunity Value x Commission Percentage/100

This is how it would look in the "Field Properties" ⤵

Search for the "Opportunity Value" field by name in the search box below “Fields available for calculation” and click on the "+" beside the "Field ID" number to add it to your formula ⤵

NOTE: You can search by partial name.

Click into your formula field and type in the asterisk (multiplication symbol) ⤵

NOTE: You’ll get a warning that “Formula is incorrect due to bad syntax”. This will disappear when your formula is correct and valid.

Make sure the cursor in the "Calculated Formula" box is flashing AFTER the asterisk you just typed in and then search for your Commission Percentage field in the search box and click on the "+" to add it to your formula ⤵

NOTE: The incorrect formula warning has now disappeared as this formula is correct and valid (though incomplete in our scenario).

Click into the Calculated Formula box and add the "/100" at the end of the formula to complete it ⤵

Click on "Save". 

Next, make sure you have placed your Percentage Commission and your Commission Amount fields on the Opportunity Form and then "Publish" your changes.

Here’s how it looks on the Opportunity in the Pipeliner CRM App ⤵

The user has typed "12" in the Commission Percentage field and the Commission Amount has been calculated as "£240".

BUT...

If the user types "0.12" instead of the "12" that we are expecting, our formula will be wrong as it will calculate the Commission as "£2.4".

So here’s an alternative solution that can eliminate this type of data entry problem.

When you create a custom dropdown field in Pipeliner, as you add values into your list, the second column contains a number. By default, this auto-completes, incrementing by one for each additional text value that you add. This number can be changed and can be accessed in calculations...

So, in this example, I’ve created a dropdown field for "Commission %" and changed the numbers associated with each entry in my list to represent the numerical value that I want to use in my formula for Commission Amount ⤵

FORMULA: the formula will now be Opportunity Value x Commission % ⤵

This approach takes a bit more work on the Admin side but eliminates problems with users typing the wrong number into your field!

And here’s what it looks like in the App ⤵

You can use this ability to assign a numerical value to a text selection in a custom Dropdown field to set up “Scoring” fields.

NOTE: The most common scenario is Opportunity or Lead Scoring based on the values entered in a number of “qualification” fields.

This is an example of how this setup would look on the Opportunity Form ⤵

As a user selects different values in the dropdown fields the score updates.

This is the setup for one of those fields, “Strength of Influence” ⤵

So when a user selects “Weak”, One (1) is added to the score but when the user selects “Strong”, Ten (10) is added.

If you’ve set up a formula that includes one field divided by another, if your users don’t fill in the second field, they’ll get a “DIVIDE BY ZERO” error message when making any changes to the record.

In this example, I’ve created a “Number of Months” Integer Number field (I only want whole months) and a Monthly Amount Base Currency Field. I want to calculate the Monthly Amount as Opportunity Value/Number of Months. If my users don’t complete the Number of Months field, this is the error that will come up ⤵

You could set the Number of Months field as “required” but that’s often not what you want to do as it would mean that no opportunity could be saved without that field being filled in.

Instead, we can adapt our formula to accommodate an empty field by using an IF statement. As you’ll see from the table above, when using an IF statement, the format of your formula must be IF(logical_expression, value_if_true, value_if_false). For our example, we want to only calculate the "Monthly Amount" when "Number of Months" is filled in and if it isn’t, set "Monthly Amount" to zero. We can achieve this using a formula as shown below:

FORMULA: IF (“Number of Months”>0, Opportunity Value/Number of Months,0).

And this is how it looks when enabled for the "Monthly Amount" field ⤵

And here’s another way of achieving the same result by using the "ISEMPTY" function rather than ">0".

FORMULA: IF(ISEMPTY(“Number of Months”, 0, Opportunity Value/Number of Months).

NOTE: This time the 0 value assigned to Monthly Amount is inserted if it’s TRUE that the Number of Months is empty and the calculation Opportunity Value/Number of Months applied when FALSE i.e. filled in.