Phone number format

[mjbosko]

CR v8.5

Any thoughts on this one?

We just went through a major database migration into a new system format. In doing so, our phone number data had to come across as free-format entry. that is, we have phone numbers entered as (555) 555-5555, 555-5555, 5555555, 555-555-5555, etc (mistyped phone numbers are also included such as 123 1233-1222, or 555abc2342)

I'm generating a report from this assortment of data and need to print the phone number. To help make some type of sense out of all this, I'd like to try to format the phone numbers as consistently as possible.

First, is there any way to strip everything but numerics from the field? (ie (555) 555-5555 = 5555555555 or 555-555-5555 = 5555555555). This would allow me to at least try to use the picture function on this data.

Next, we are beginning to allow international phone numbers into the mix.... any great ways to deal with these?

thanks!!

Mike

 

[kenhamady]

You can use the Replace function to replace one character with another. With v8.5 you should be able to replace spaces, dashes, etc with an empty string ("&quot to strip them out. You can even nest the replaces to replace several different characters in one formula. Alpha Characters will be a problem, unless you want to check for every one of them.

You could also write a loop to check each character and append to a string if it was between 0 and 9. Depending on how bad the values are, this might be more efficient. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

As Ken suggests, you can handle this in the report using loops or by stripping offending characters, but the best bet is to fix in on the database side, permanently, using the same methodology with SQL.

Now make sure that any code which populates the column going forward uses an edit mask and has some form of integrity check.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

Getting the correct format enforced on your database certainly gets my vote. But, while that's something to consider for future cases - (hopefully before you get your internationals) - now, you need to fix this on the reporting side.

I think you should perhaps consider something like a combination of the looping and picture functions as mentioned by you guys earlier:

//@PhoneFaxFix

WhilePrintingRecords;
StringVar CurrentPhone;
StringVar NewPhone;
BooleanVar BadPhone;

CurrentPhone := {PhoneField};

While Length(CurrentPhone) > 0
Do
(
If IsNumeric(Left(CurrentPhone,1)) Then NewPhone:= NewPhone + Left(CurrentPhone,1);
CurrentPhone:= Mid(CurrentPhone,2);
);

If Length(NewPhone) in [10,7] Then BadPhone := False Else BadPhone := True;
If Length(NewPhone) = 10 then Picture(NewPhone,'(xxx) xxx-xxxx')
Else
If Length(NewPhone) = 7 then Picture(NewPhone,'xxx-xxxx')
Else NewPhone;

The 'Else NewPhone' would probably be best served with being 'Else {PhoneField}'. It depends how you want the numbers which couldn't be converted to be shown up.

I've put in the BadPhone variable to trigger when Crystal comes across values that it can't convert, as you might be interested in bringing these values up for a closer look as to why they couldn't be beaten into shape. Instances like missing or extra digits in the phone string will fall into this category. Might be an idea for you to apply a conditional colour on the field based on something like "If BooleanVar BadPhone = True Then CrRed Else NoColor" if you do want to easily spy the offenders.

All the best with your report,

Naith