Calculated Field - Formatting Phone Number Fields

Overview

This article will present an example formula for a calculated field that will format the value entered in the Office Phone field into a specific format such as +1 (XXX) XXX-XXXX (standard US format). For more information on standard phone number formats, please refer to this article available on wikipedia.org.

In Sugar 11.0 and higher, if an admin has turned on the Enable Click-to-Call setting in Admin > System Settings, phone number fields are displayed as links that can be opened to dial them using the default computer telephony integration (CTI) software on the user's computer. For Sugar Serve users logged in to Amazon Connect, calls are dialed in SugarLive. In some older, unsupported versions of Sugar, if an admin has turned on the Enable SkypeOut Integration setting in Admin > System Settings, phone number fields are displayed as links if they use the standard format (e.g. +1 (480) 123-4567). Having this calculated field in place will help ensure that the phone number meets the format required by your computer telephony integration (CTI) software, whether it is an application on your computer or SugarLive.

For an overview of Sugar Logic and Calculated Fields, please refer to the Introduction to Calculated Fields article.

Use Case

In this example, we will create a calculated field (e.g. Office Phone Formatted) in the Accounts module which will automatically format the value entered in the stock Office Phone field into the standard US format (e.g.+1 (XXX) XXX-XXXX). 

Note: Only unformatted values (e.g. 6197846741) will be properly parsed and returned in the expected US format.

Prerequisites

You will need to create a new field (e.g. Office Phone Formatted) of type "Phone" in Studio and mark it as calculated before entering the following formula. The new field will also need to be added to the appropriate module layout(s) (e.g. record view, detail view, etc.) where you wish to view the information. For more information on adding fields and modifying layouts in Sugar, please refer to the Studio documentation.

Formula

The formula below will take the value entered in the Office Phone field and format it. The formatted version will appear in the Office Phone Formatted field in the Accounts module once you specify this formula via Admin > Studio > Accounts > Fields > Office Phone Formatted:

concat("+1(",subStr($phone_office,0,3),") ",subStr($phone_office,3,3),"-",subStr($phone_office,6,4))

Formula Breakdown

Each part of the phone number (e.g. 4081234567) is broken into substrings using the subStr() function. The first three characters that are entered in the Office Phone field represents the area code value, and the subStr($phone_office,0,3) call will grab the first three characters (408) from the string of characters. The "0" tells the function to start counting at the first character, and the "3" tells the function how many characters to the right to copy. Next, the subStr($phone_office,3,3) call will grab three characters starting with the fourth character as the region code (123). Finally, the subStr($phone_office,6,4) call will grab the last four characters (4567) of the region code as a substring starting with the seventh character).  

The concat() function will then concatenate or combine the string of values beginning with "+1", the substrings (408, 123, 4567), spaces, and the dash character (-). For our example phone number of 4081234567, the formula will return +1 (408) 123-4567.

Please note that this formula can be modified accordingly based on the standard phone number format for your region. One thing to keep in mind is that the formula does not validate the value entered in the Office Phone field. So if a user enters the phone number that is already formatted (e.g. (408)123-4567), the calculation will return incorrect results (e.g. +1((40) 8)1-23-4).

Application

Once your custom field is created with the calculated value formula and added to the module's record view, navigate to the Accounts module to edit or create a new account. Enter a phone number (e.g. 4081234567) in the Office Phone field and the Office Phone Formatted field will display the phone number in the standard US format.