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 GeneralYou 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 StringWhen building a Concatenate string (i.e. adding your own words within the new field) there are a few things to be aware of:
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 FieldA 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 FieldAdditionally 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. MultiCurrency Calculation FieldsWhen adding a multicurrency 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 multicurrency field. Date Calculation FieldYou can use Date Calculation fields within OpenCRM to do specific calculations with dates, the two options currently available are:
Using the DateAdd fieldTo add predefined incremental amounts, you will need to go to Settings>StudioCustom Field Settings and select the module you are going to create the field in.
Let's break down that formula: DATEADD($date_consent_given,d,10)
This example will add 10 days to the date which is in Date Consent Given field.
Using the DateDiff fieldTo add predefined incremental amounts, you will need to go to Settings>StudioCustom Field Settings and select the module you are going to create the field in.
To go into the DateDiff formula in more detail: DATEDIFF($date_consent_given,$cf_435,d)
If you would like to do a calculation based on the current date you would use this  new Date() All modifiers for both DateAdd and DateDiff
Other Tips
You may also be interested in:
