SugarCRM SupportKnowledge BaseStudio and Module BuilderSugar LogicCalculated Field - Formatting Phone Number Fields

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.

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

Note: Sugar's SkypeOut integration allows users to utilize a click-to-dial feature where you can click the phone number on the record view, list view, etc. to call out using Skype. In order to use this feature, the phone number must 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 required format when utilizing this integration. For more information on enabling the SkypeOut integration, please refer to the System documentation.

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

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

 

Last modified: 2018-10-17 19:16:27