Calculate the Age of a SugarCRM Record with this Calculated Field
In this blog post we explain how to build a calculated field that displays the age of a record. This could be used in any module where the age of a record is important, such as Cases, Leads, or Quotes. In this walkthrough guide, we’ll explain how to build a calculated field that on a Cases Record, that displays the age of a case for an organization who wants to resolve all cases in less than 14 days.
Here is what the field looks like:
There are three main steps to implement a custom field.
- Build the dependent field
- Add the field to the layout
- Test the field
Build the Calculated Field
The first step to build this calculated field is to create the field in Sugar Studio. To access Sugar Studio, log into Sugar using an account with administrative privileges. Next, access the admin panel by clicking the top right drop down arrow in the navigation bar and clicking “Administration”.
Scroll down to the part of the admin page labeled “Developer Tools” and click on “Studio”.
When Studio loads it will display a list of modules you can edit. Click on Cases.
Next, select the module component you wish to modify. We want to create a new field, so select “Fields”.
You are now looking at all the existing fields that have been previously built in the Cases module. Custom fields will be marked with an asterisk (*). Click “Add Field” to create a new field.
You are now looking at the field builder. First, select the field type, labeled here as “Data Type”, to be “Integer”.
Next, name the field by entering the desired name into “Field Name”. The Field Name cannot contain any spaces or uppercase letters. I named my field “age of record”. Once you enter your Field Name and click outside of the field, the fields “Display Label” and “System Label” will automatically populate. You may wish to make some adjustments to the “Display Label” field since this will be displayed to users in your system.
Next, we need to indicate that this will be a calculated field by checking off the “Calculated Value” box. After that, we need to create the formula which will calculate the age of the record.To do this, click the “Edit Formula” button next to the “Formula” field. This will open up the formula builder.
This is the final formula we need to build: “negate(daysUntil($date_entered))”. You can simply copy and paste this into the formula builder, or follow the steps below. First, we will use the function "negate". Without this function, the number we calculate would show as negative since the date we are referencing (date created) is in the past.To correct this, find the negate formula in the list of functions and click on it to add it to the formula you are building.
Next, we will use“daysUntil”. This function calculates the number of days between now and the specified date. The specified date can be another field or a static date. For this calculated field, choose the specified date to be date the record was created by choosing “$date_entered” from the list of available fields.
Click “Save” within the formula builder and then at the top of the page to save the field.
Add the Field to the Layout
Click “Cases” at the top of the page to navigate back to the editing options.
Click “Layouts” so we can add the field to the layout. Now click “Record View” so we can add the dependent alert to the record view for Cases.
To add the field to the record view, click on “New Row” in the toolbox on the left-hand size of the layout editor, and drag a new row to the layout.
Scroll down within the toolbox and find the field we just created. Drag and drop this field into the filler space we just added.
Click “Save and Deploy” at the top of the editor.
Test the Field
Now we can test the field by navigating to the Cases module. On the record below you can see the new field we built is visible from the record view. The value in the field should be equal to the difference between the date the record was created and today’s date.
We demoed this field in a Sugar Webcast, along with several other custom fields. Watch the full recording below.