Calculate the Age of a SugarCRM Record with this Calculated Field

by Katie Liesmann on April 24, 2017

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:

Age of Record

There are three main steps to implement a custom field.

  1. Build the dependent field
  2. Add the field to the layout
  3. 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”.

Administration tab in SugarCRM

Scroll down to the part of the admin page labeled “Developer Tools” and click on “Studio”.

Studio tab

When Studio loads it will display a list of modules you can edit. Click on Cases.

Cases

 

Next, select the module component you wish to modify. We want to create a new field, so select “Fields”.

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.  

Add Field option

You are now looking at the field builder. First, select the field type, labeled here as “Data Type”, to be “Integer”.


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.

 

Edit field

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.

Calculated Value box

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.

negate option

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.

daysUntil

Click “Save” within the formula builder and then at the top of the page to save the field.

Save Formula

Add the Field to the Layout

Click “Cases” at the top of the page to navigate back to the editing options.

Cases

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.

Record View

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.

New Row  

Scroll down within the toolbox and find the field we just created. Drag and drop this field into the filler space we just added.

Drag and Drop

Click “Save and Deploy” at the top of the editor.


Save and Deploy

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.

Test the Field

We demoed this field in a Sugar Webcast, along with several other custom fields. Watch the full recording below. 

Find similar articles in these categories:

PRODUCT: SugarCRM

AUDIENCES: Administrators End Users

Katie Liesmann
Marketing Manager at UpCurve Cloud
More From This Author »