Sugar Tip – Creating a calculated field to count days until a date

bsoremsugar —  November 11, 2011 — 10 Comments

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.

Since version 6.2, Licensed editions of SugarCRM (Professional, Corporate, Enterprise and Ultimate) have given the option to create Calculated Fields.

This post shows how to create a calculated field which will give a countdown of the number of days until a date stored in another field.

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 countdown field on the Opportunities Module, showing how many days remain until the Expected Close Date.

Click on the Opportunities Icon in the Studio window.

To allow the number of days 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 an Integer to give whole days as a result. Select Integer from the Data Type drop-down list.

In the Field Name field, enter days_to_close

Enter Days Remaining 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 pop-up 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 daysUntil and click on it. I will put the daysUntil( function into the formula creation panel.

In the Field list, click on $date_closed (this is the field where the Expected Close Date is stored). 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 a close bracket )

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 countdown 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.

10 responses to Sugar Tip – Creating a calculated field to count days until a date

  1. 

    Can you give the formula to calculate days between 2 dates using calculated field.

  2. 

    I can’t find  calcution in the community edition or is it only available with the pro?

  3. 
    Nikolay Krustev April 9, 2012 at 11:28 am

    Hello,
    i’ve tried this method to calculate days remaining on people’s contracts. (custom module)
    Sometimes i dont have end dates for those contracts and this throws an exception.
    Can you confirm that if there is nothing inserted in the date field this formula will crash fatally.
    This might be a bug i think.
    Thank you.

Trackbacks and Pingbacks:

  1. SugarCRM Developer Blog » Blog Archive » SugarTip – Automatically Calculate the number of days between two dates - March 14, 2012

    [...] of the questions I was asked most as a result of my previous post Sugar Tip – Creating a calculated field to count days until a date was “How can I calculate the number of days between two date [...]

  2. SugarCRM vs Salesforce | SugarCRM vs Salesforce Administration | CRM Blog - September 24, 2012

    [...] a component that helps them to easily make complex, upgrade-safe customizations that can support calculated fields, date fields, and calculated-dependent [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s