Tech Tuesday – GP and Phone Numbers

Hey all, recently I’ve had a lot of questions revolving around what to do with phone numbers when importing data into GP as well as taking data out. GP stores phone numbers without any formatting characters and then adds them later in the interface, however your source data may have formatting already in it.

To handle that, we can make use of the built-in functions in SmartConnect to remove any unnecessary characters for us so we don’t have to write a longer script to handle it. We can create a new calculated field in SmartConnect and then expand the Functions list on the side of the scripting window find the function that we need. In this case, we will use fn.STRIPPHONENUMBER so you can just drag that into the scripting portion of the window and provide the field that has the phone number in it. When you are done, it will look something like this for the calculated field:

Return fn.STRIPPHONENUMBER(_SourcePhone)

Where _SourcePhone is your source field name.

On the opposite side of the issue, sometimes you want to add formatting onto a phone number – whether you are sending the data over to CRM, SQL or a flat file. This can be a bit more complicated since you may have extensions on the number or that GP field might not be populated at all. The script below handles most of the occurrences that you will run across:

‘Declare the variable to hold the phone number

Dim myPhone as String = _SourcePhone.ToString.Trim

‘If there isn’t any data, return an empty string

If String.IsNullOrEmpty(myPhone) Then

     Return “”

‘If it is stored as 0’s in GP, return an empty string

Else If myPhone.Substring(0,3) = “000” Then

  Return “”

‘If the extension is all 0’s, return the number without the extension

Else If myPhone.Substring(10,4) = “0000” Then

  Return “(” & myPhone.Substring(0,3) & “)” & myPhone.Substring(3,3) & “-” & myPhone.Substring(6,4)

‘Otherwise, return the full phone number

Else

     Return “(” & myPhone.Substring(0,3) & “)” & myPhone.Substring(3,3) & “-” & myPhone.Substring(6,4) & ” Ext. ” & myPhone.Substring(10, 4)

End If

There is a bit of line wrapping going on, but if you copy it out to your calculated field it should appear correctly. Again, the field _SourcePhone will need to be replaced with your field that has the phone number, but the rest of the script shouldn’t need to be modified unless you want to customize it further. This script is built specifically for phone numbers in GP, as there is a check in it that determines whether there is an extension or not by checking if those values have all 0’s for the extension – which is how GP stores it if there is no extension entered.

This isn’t the only way to handle phone numbers, it is just the latest methods I’ve used for a couple clients. If anyone else has methods they have used to format phone numbers or other fields let us know in the comments.