You may have a need to add custom fields which perform a calculation on your behalf. This is easily done by navigating to Settings->Studio->Custom Field Settings and choosing the relevant module.

Calculation General

You will then be able to add a new custom field. Once there, you will need to select "Calculation General" from the field type menu.

To demonstrate this in action we have set up an example of a field "Total Employees" which is the sum of the two fields "No of Employees North East" and "No of Employees North West".

You then need to take the following steps. 

1. Select then field type of "Calculation General" or "Calculation Currency".

2. Give the field the name you wish to display on screen

3. Select the name of the first field using the "Select Field" drop down, and copying the field value that is displayed on the right hand side, in the example above "$cf_429".

4. Select the type of calculation you wish to use, and copy the relevant value displayed on the right hand side in the box below, beside the value you have already pasted, so we now have "$cf_429+"

5. Select the next field you wish to use in the calculation, completing the formula above "$cf_429+$cf_431".

6. Continue adding fields and mathematical functions until you have built your desired calculation and hit Save.

You can then use the field you have created.

The screenshot below shows the new calculated fields adds together the values in the fields you have specified:

Building a Concatenated String

When building a Concatenate string (i.e. adding your own words within the new field) there are a few things to be aware of:

1. To add a string, you will need to place it between two quotes: ($field1,"a string of text",$field2)
2. If you need to add a comma or new line, this also needs to be between two quotes: ($field1,", ",$field2)
3. Adding a quote into the string, is more complicated as you might imagine. You will need to add a slash (\) ahead of it so the system doesn't think you're adding another string: ($field1," \" to be or not to be \" ",$field2)
4. Symbols can be used in a concatenation but mathematical operators should not be in the same string as a $:
   1. so do this: ($field1,"+","$",$field2)
   2. not this: ($field1,"+ $",$field2)

if you're unsure about a calculation, please get in touch with our support team. 

Note: An incorrect calculation can and will break the edit screens for the module the field is on. It can also cause issues on save from imports, etc. So please use carefully.

Setting a Minimum or Maximum value in a Calculation Field

A further function you can use when creating a standard or currency calculated field is to override the calculation with a Minimum or Maximum Value. For example, if you have a calculated field to work out a commission, but you want this to have a minimum of £150 regardless of the sum calculated, then you can use the MAX function to tell the system to display either £150, unless the commission is greater than that figure, in which case

The formula is built as follows:

1. Use the MAX function to display the highest available value in a field:

MAX(300, $fieldname, $fieldname2) 

If fieldname has a value of "100" and fieldname2 has a value of "200" then your custom field will display 300 as its the largest value out of the 3 . If either fieldname or fieldname2 were to exceed 300 then the custom field would display that maximum value.

2. Use the MIN function to display the smallest available value in a field:

MIN(50, $fieldname, $fieldname2)

If fieldname has a value of 100 and fieldname2 has a value of 80 then your custom field would display 50 as this is the lowest value.

You can use the field calculation wizard as outlined above to generate the desired formula.

You can also embed calculations within the field. Using our example above - we want to pay a commission of a minimum of £150, or the sum of 50% of the Order subtotal, if this is higher than £150. The formula would be: MAX(150, ($hdnSubTotal/2)) - telling the system to display either 150, or half of the SubTotal, whichever one is higher.

Concatenating in a Calculation Field

Additionally you can use concatenation within our calculation fields in much the same way you would in Excel.

The formula for this is: CONCATENATE($fieldname,"symbol or space",$fieldname).

If you wanted to show a percentage in a particular field, for example the percentage of employees in the North East using the above example, you would write: CONCATENATE(($cf_445 / $cf_446)*100,"%"). This takes the "No of Employees North East" field and divides it by the "Total Employees" field, multiplies the result by 100 and then sticks the percentage symbol at the end of the number.

Multi-Currency Calculation Fields

When adding a multi-currency calculation field, there may be some rounding errors on the fields.  This is because the amount for that field is calculated in the default system currency, and then converted to the currency on the record using the system exchange rates.  This method allows single currency fields to be used in calculations for the multi-currency field.

Date Calculation Field

You can use Date Calculation fields within OpenCRM to do specific calculations with dates, the two options currently available are:

1. Adding certain date increments (day, week, month, year) - called DateAdd, or
2. Finding a numeric difference between two dates - known as DateDiff

Using the DateAdd field

To add pre-defined incremental amounts, you will need to go to Settings->Studio-Custom Field Settings and select the module you are going to create the field in.

1. Click to create a New Custom Field
2. Scroll to the bottom of the list and choose "Calculation Date"
   
3. You will now need to use the "Select Field" option to get the field name or number for the field you are adding the incremental value to and select the DateAdd formula to get the correct format.
   IT IS VITAL that the format follows exactly the one below or your calculation will not work.
   In this case, we are using a custom field and want to add a stock value of 10 days to it.
   
4. Click Save

Let's break down that formula:

DATEADD($date_consent_given,d,10)

• DATEADD - This is what function you want to do, you want to "add" to a date
• $date_consent_given - This is the field you wish to add to. 
• d - This is the modifier, what you want to add to, in this example we are using "d" which is days (Please see below for list of modifiers)
• 10 - This is the increment we want to add to the date - Alternatively you can also use a field here so if you wanted to add the amount from another field. use the field name format along the lines of "$date_consent_given" or for custom fields the relevant field ID such as "$cf_429" instead of 10

Using the DateDiff field

To add pre-defined incremental amounts, you will need to go to Settings->Studio-Custom Field Settings and select the module you are going to create the field in.

1. Click to create a New Custom Field
2. Scroll to the bottom of the list and choose "Calculation General"
   Important: This is not the same as the above DateAdd function.
   
3. Select the field you would like to use and choose the "DateDiff" function, following the below format exactly.
   The below example is requesting the number of dates between two already existing date fields.
   
4. Click Save

To go into the DateDiff formula in more detail:

DATEDIFF($date_consent_given,$cf_435,d)

• DATEDIFF - This is what function you want to do, you want to find the "difference" between 2 dates
• $date_consent_given - This is the first field you wish to use to find the difference
• $cf_435 - This is the second field you wish to use to find the difference, you will be finding the difference between field 1 and field 2
• d - This is the modifier, what you want the difference to be returned in, in this example we are using "d" which is days (Please see below for list of modifiers)

If you would like to do a calculation based on the current date you would use this - new Date()
E.g. To show the current age using the 'Date of Birth' field the formula would be: DATEDIFF($birthday,new Date(),y)

All modifiers for both DateAdd and DateDiff

Other Tips

• You can get a CRMID using $record_id