This article will present a Sugar Logic formula that isolates the month portion of a date field into a custom calculated field. We then extend this field’s use to create another custom calculated field that displays both the month and year of the date field.
For an overview on Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields.
In this example formula, we will extract the month portion of the Opportunities module’s “Expected Close Date” to a custom field. The example close date is 10/01/2012.
Note: Beginning with version 9.1.0, each user’s license type (e.g. Sugar Serve) determines what functionality is available as described in the User Management documentation. The Opportunities module used as an example in this article is not available for Sugar Serve users, but creating the custom calculated field will work similarly for other modules.
You must be an administrator or have developer-level role access in Sugar to create custom fields and modify layouts. For more information, please refer to the Developer Tools documentation.
Note: This article is written for a Sugar instance that is configured to use Opportunities without Revenue Line Items. If your instance utilizes the Revenue Line Items module, simply perform the same steps below for the Revenue Line Items module instead of the Opportunities module.
Create a new field of type TextField in the Opportunities module and mark it as “Calculated”. For our example, we named the field
$month_closed_c. with the display label “Month Closed”.
To display the month based on the expected close date, copy the following formula into the “Edit Formula” window for
If you would like to isolate both the month and the year of the date field, then you can create a second TextField field in the Opportunities module with the following formula that references the previously calculated field,
$month_closed_c. For this example, we named the new field
$month_year_c with the display label “Month and Year”, which you can see in the layout pictured in the Application section.
monthofyear() function returns the month portion of the given date field. For our example, the
monthofyear() function returns 10 as the Expected Close Date field’s value is 10/01/2012.
The second formula goes through some additional steps to include the year in its result. In Sugar, dates may have the year coming first or last depending on your date format. Also dependent on the date format is whether the date’s day, month, and year are separated by a slash, period, or hyphen. To account for these differences, the second function uses an
ifElse() function that will check if the third character of the date is a period, dash, or slash. If so, it assumes that the year comes at the end of the date because the month or day is only two characters long while the year is four characters. The formula then uses the
subStr() function to grab four characters starting with the seventh character as the year. If the
ifElse() evaluates false, then we know that the year comes at the beginning of the date and thus the formula uses the second
subStr() to take the first four characters as the year. Both
subStr() functions also use the
toString() function to change the date-type field
$date_closed into a string because
substring() only works with string fields.
subStr() to isolate just the third character,
createList() generates a temporary list of the potential separator characters so that the
isInList() function can determine if the third character of the date is a separator character. Whether this
isInList() match returns true or false determines whether the first or second
subStr() is returned from the
concat() function joins the month, the character “/”, and the year returned by the
ifElse() into a single string (e.g. “mo/year”). For our example date of 10/01/2012, this function returns the string “10/2012”.
Once you have created the custom text fields with their calculated value formulas, add the field(s) to the module’s record view to display the month and/or month and year that the opportunity is expected to close.