Skip to content
+1-888-319-3663

EONE BLOG

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.

2 Comments

  1. Isabell on January 16, 2017 at 3:16 pm

    Hi Chris,

    Thanks for providing this code. I found it very useful. Although I receive an error message while using it and was wondering if anyone could help me try figuring out why?

    Error in ‘MsGpDestination’.
    Line ‘taUpdateCreateCustomerRcd___2DB14AAB-C4B0-4037-9268-A60B00CDD4AB’.
    Parameter ‘Phone Number 1’.
    Parameter set value event. Calculation column : EXCLUDE_EXTENSION_PHONENBR : Index and length must refer to a location within the string.
    Parameter name: length

    Any help will be highly appreciated, thank you!

    /Isabell

    • Isabell on January 17, 2017 at 10:26 am

      Solved my own problem, thank you.

Leave a Comment





RECENT POSTS


Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features
New Webinar: Getting Started with Matrix Reporting in Popdock
Tech Tuesday: Popdock User Security in D365 Business Central
Popdock Data Lake Upload Tool for Dynamics NAV Data - FREE TRAINING

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.