Conditional phone# format?

[RDecker]

I'm working on a DB that includes a contact module. I had been using multiple fields in the same table as the individual, and using a calculated result to format the phone number (linked to a phone# style pref setting the a separate pref file). See that calc below.

I've just decided that it would be better to place these phone numbers into a related table (in same file) and make it a more general "contact" field that can accept phone numbers, email addresses, InstantMessage screen names, etc.

My puzzle is how to still auto-format the phone numbers w/o generating an error msg. for, say, an email address. How can I make this calc conditional? Or are there better ways to do this?

The calc I've been using (adapted from something similar that someone posted here, I think) is:

If(Length(Abs(Substitute(phoneOrContactNmbr; "."; ""))) = 10;

Choose(GetAsNumber(Settings::phoneNmbrStyle); ""; "("; "") &

Left(Abs( Substitute(phoneOrContactNmbr; "."; ""));3) &

Choose(GetAsNumber(Settings::phoneNmbrStyle); "-"; ") "; "/"; "."; " "; "*"; "") &

Middle(Abs( Substitute(phoneOrContactNmbr; "."; ""));4;3) &

Choose(GetAsNumber(Settings::phoneNmbrStyle); "-"; "-"; "-"; "."; " "; "*"; "") &

Middle(Abs( Substitute(phoneOrContactNmbr; "."; ""));7;4);

"Error: click for info: Invalid Phone Number, Did you include the area code?")

Thanks,

decker

[CobaltSky]

Hello decker,

If you want the formatting to be conditional, you'll first have to devise a test that will tell you whether the content of the field qualifies as a phone number. There are various ways you might go about that, depending on what other uses the field will be put to, but one way would be to see if it has seven or more digits (assuming that email asddresses and screen names etc rarely have large numbers of digits in them.

Then you will be able to set up the contact field as an auto enter with a formula which applies your number fomatting conditionally, eg. along the lines of:

Case(

Length(GetAsNumber(ContactField)) > 6;

--Your phone number formatting formula here--;

ContactField

)

The auto-enter will then update entries to the field which meet the criteria for a phone number, but all other content will remain unchanged.

[RDecker]

Thanks for the idea. I'm not sure that length would work--my email address has 19 characters, but yes, screen names are typically shorter.

What if I reversed the fields in the portal so that data entry would enter the type first (phone, emai, etc.), and then using that as the condition for doing the calc formula on the 2d field in the portal (the data field)? If type = phone, format..., else no format. (I haven't tried to figure out how to write that yet...)

decker

[CobaltSky]

In the test I was suggesting, what matters is not the length of the email address or screen name, but how many digits (as opposed to alphabetic characters) it contains. GetAsNumber( ) returns only the numerals within the supplied string.

However if you have a type field and would prefer to use that, that should certainly work - irrespective of where in the data entry process it sits (but if it comes after the contact field, the phone no formatting may not be applied until the type field has been filled in).

[BruceJ]

This is what I've used:

Case(Length(GetAsNumber(

phone

)) =10 ; "(" & Left(GetAsNumber(

phone); 3) & ") " & Middle(GetAsNumber(

phone

); 4; 3) & "-" & Right(GetAsNumber(

phone

); 4) ; Length(GetAsNumber(

phone

)) = 7; "(___) " & Left(GetAsNumber(

phone

); 3) & "-" & Right(GetAsNumber(

phone

); 4) ;

phone

)