This week SugarCRM Gold Partner EnableIT SugarUK‘s Mike Gee shows how you can use calculated fields to countdown the number of days until a specified date.
Version 6.2 of Licensed editions of SugarCRM (Professional, Corporate, Enterprise and Ultimate) saw the option of creating custom Calculated Fields.
This post shows how to create a calculated field which will give a Total Commission Due based on the value of an Opportunity and a Commission percentage rate.
In order to complete this task you will need to have administrator privileges to your SugarCRM instance.
Go to the Admin section of your SugarCRM and scroll down to the Developer Tools section. Click on Studio
For this example, we will create a Total Commission field on the Opportunities Module, showing total commission due on the Opportunity. You will already need to have created a decimal field to store your Commission Rate.
Click on the Opportunities Icon in the Studio window.
To allow the Total Commission value to be stored, we first need to create a field to store the value in.
Click on the Fields icon.
Click on Add Field at the top left of the Edit Fields window.
The Data Type of the field needs to be Currency to allow the value to display with the currency symbol as a result. Select Currency from the Data Type drop-down list.
In the Field Name field, enter total_commission
Enter Total Commission in the Display Label field.
Put a tick in the Calculated Value field. This will then make the Formula field visible.
Click on Edit Formula. This will bring up the Formula Builder popup window.
The top section is the formula creation panel where the formula will be entered, the lower left panel allows available functions to be selected and the lower right panel shows the available fields in the module.
In the Functions list, scroll down until you find multiply and click on it. It will put the multiply( function into the formula creation panel.
In the Functions list, scroll down until you find divide and click on it. It will put the divide( function into the formula creation panel.
In the Field list, click on $commission_c (or whatever the name of the field is which contains the commission percentage). It will appear after the open bracket in the formula creation panel.
Click into the formula creation panel at the right hand end of the text and enter ,100),$amount_usdollar). This calculates the field based on commission rate divided by 100, multiplied by Opportunity amount (without Tax).
Your formula should now read something like multiply(divide($commission_c,100),$amount_usdollar)
Click on Save at the bottom right of the formula builder window.
Your formula will now be stored in the Formula field. Do not worry if you cannot read all of the formula.
Click on Save at the top left of the Edit Field window.
You have now created the Total Commission field which can now be added to the DetailView of the Opportunities module. Once added to the DetailView layout and deployed to your users, the field will start update once a record has been created or each time the record is updated.
This tip was originally posted on the EnableIT SugarUK Blogs where you can find a whole host of other useful SugarCRM tips.