Formatting formula

[dakone]

I'm building a report in Crystal and it has fields which are being brought in from a SQL Server through an ODBC connection. One of the fields is phone1(for phone #). The numbers in the table are entered however the user sees fit, so they can look like '(000) 000-0000' or '0000000000' or '00000000000000'(the extra zeros are an extension# on the last example). My question is: How can I ensure that the numbers will be displayed as
'(000) 000-0000 ext.0000'? And do I use the Selection Expert or another way of formatting the field in Crystal? Any help will be graciously excepted as I have no prior experience in this area.

 

[resdevlabs]

My suggestion is to create a formula field to format all phone numbers. I would first remove any non numeric character, then format it the way you want. I.E.:

StringVar fmtPhone;
fmtPhone = Replace( {db.phonecolumn}, "(", "" );
fmtPhone = Replace( fmtPhone, &quot", "" );
fmtPhone = Replace( fmtPhone, "-", "" );
fmtPhone = Replace( fmtPhone, " ", "" );

"(" + Mid(fmtPhone,1,3) + &quot " + Mid(fmtPhone,4,3)...

 

[synapsevampire]

Enhancing that, alter the last line to something like:

if len(fmtPhone) >= 10 then
picture(fmtphone,"(xxx) xxx-xxxx xxxx&quot
else
picture(fmtphone,"*(xxx) xxx-xxxx xxxx&quot

The asterisk would then designate that the number is too small.

You can elaborate on the error checking I added, and the Replace functions used by Res.

I would address this on the database level with a script to clean the phone numbers, then to be safe, clip your front end programmers finger nails with a rusty, dulled axe, and hire someone else that knows about front end validation...

-k

 

[dakone]

Took your advice res and currently I have this:

StringVar fmtPhone;
fmtPhone = Replace( {SOP10100.PHNUMBR1}, "(", "" );
fmtPhone = Replace( fmtPhone, ")", "" );
fmtPhone = Replace( fmtPhone, "-", "" );
fmtPhone = Replace( fmtPhone, " ", "" );


If Len(fmtPhone) = 10 Then
    fmtPhone = "(" + Left(fmtPhone, 3) + ")" + Mid(fmtPhone, 4, 3) + "-" +
    Right(fmtPhone, 4)
Else
    If Left(fmtPhone, 1) <> &quot;(&quot; Then
        fmtPhone = &quot;(&quot; + Left(fmtPhone, 3) + &quot;)&quot; + Mid(fmtPhone, 4, 3) + &quot;-&quot; +
Mid(fmtPhone, 7) + &quot; Ext.&quot; + Right(fmtPhone, 4)

Crystal snyntax. The problem is it returns 'True' or 'False' in my field instead of the phone number. When I try converting using 'ToText' I loose the formatting? Any Suggestions?

P.S.

Vamp: That front end programmer is gonna lose more than his fingernails!

 

[dakone]

Scrap that. The code I'm using is as follows:

If Length ({SOP10100.PHNUMBR1}) < 10 Then
   {SOP10100.PHNUMBR1} = &quot;(&quot; & Left ({SOP10100.PHNUMBR1}, 3) & &quot;) &quot; & Mid ({SOP10100.PHNUMBR1}, 4, 3) & &quot;-&quot; &
   Right ({SOP10100.PHNUMBR1}, 4)
   
Else 
   If Left ({SOP10100.PHNUMBR1}, 1) <> &quot;(&quot; Then
      {SOP10100.PHNUMBR1} = &quot;(&quot; & Left ({SOP10100.PHNUMBR1}, 3) & &quot;) &quot; & Mid ({SOP10100.PHNUMBR1}, 4, 3) & &quot;-&quot; &
      Mid ({SOP10100.PHNUMBR1}, 7) & &quot; Ext.&quot; & Right ({SOP10100.PHNUMBR1}, 4);
     
If NumericText({SOP10100.PHNUMBR1}) Then
   ToText({SOP10100.PHNUMBR1})

Same problem with converting to text though.

 

[lbass]

Try this:

stringvar fmtPhone;

replace(replace(replace(replace({SOP10100.PHNUMBR1},"(", "&quot, &quot", "&quot, "-", "&quot, " ", ""
"(" & left(fmtPhone, 3) & &quot " & mid(fmtPhone, 4, 3) & "-" & mid(fmtPhone, 7, 4) & if len(fmtPhone) > 10 then " ext " & mid(fmtPhone), 11));

-LB