I'm sure this has been addressed thousands of times over the years, but since I just had to write the script, I thought I would post it here for posterity.
By default, Dynamics GP stores phone numbers as a long string of zeroes. This format is based on the GP Format Definition assigned to phone number fields in GP.
I recently had to write a SQL query that would be used to export vendor information to a CSV file. Because the system receiving this data has a free form phone number field, we wanted to make sure that the phone number data coming from GP was formatted consistently.
I'm sure there are several other ways to do this, but this is the script that I created to put the phone numbers in US phone format. It's pretty basic, but gets the job done.
SELECT
CASE RTRIM(PHNUMBR1)
WHEN '00000000000000' THEN ''
WHEN '' THEN ''
ELSE '(' + SUBSTRING(PHNUMBR1, 1, 3) + ') ' + SUBSTRING(PHNUMBR1, 4, 3) + '-' + SUBSTRING(PHNUMBR1, 7, 4)
+ CASE WHEN RIGHT(RTRIM(PHNUMBR1), 4) <> '0000' THEN ' x' + RIGHT(RTRIM(PHNUMBR1), 4) ELSE '' END
END AS PHONE
FROM PM00200
If you are having to store international phone numbers in GP, then it probably makes sense to edit the "Format" resource in Modifier to use a Fill of Space, and no format string.
Or if anyone has any other tips or tricks on querying and formatting GP phone numbers, post a comment!
We found that sometimes accounting will add an "x" for extension in Dynamics, causing a double "xx". This was fixed by adding a REPLACE:
ReplyDeleteCASE RTRIM(PHNUMBR1)
WHEN '00000000000000' THEN ''
WHEN '' THEN ''
ELSE '(' + SUBSTRING(PHNUMBR1, 1, 3) + ') ' + SUBSTRING(PHNUMBR1, 4, 3) + '-' + SUBSTRING(PHNUMBR1, 7, 4) +
CASE
WHEN RIGHT(RTRIM(PHNUMBR1), 4) <> '0000' THEN REPLACE(' x' + RIGHT(RTRIM(PHNUMBR1), 4), 'xx', 'x')
ELSE ''
END
END as [Phone]