Need a Phone/Email calc or custom function

[IGotIt]

Hi Guys,

 

Is their a calculation or custom function that will take a phone number like 8005554444 and make it 800.555.4444 and recognized an email address and leave it as entered? So I guess it would have to know that anything entered with an @ sign is an email and leave it alone right?

 

Thanks so much,

 

Milo

[Maarten Witberg]

Hi,

 

the following will leave anything with an @ in it as is, format a string that has exactly ten digits as required and mark the rest in red (a kind of soft validation: if you force the phone mask on a number that is not exactly ten digits, it will return a wrong number especially if there are more than ten)

I can't think of any kind of validation for an email adress, there are too many options.

 

make it an auto-enter replaces existing:

 

Let
(
 [
 string=Filter( PhoneField;"0123456789");
 isemail=PatternCount(PhoneField;"@")
 ];
Case
(
Isemail;TextColor ( PhoneField ; RGB ( 0 ; 0 ; 0 ) );
 Case
 (
  Length(string) <> 10;
  TextColor ( PhoneField ; RGB ( 255 ; 0 ; 0 ) );    
  TextColor ( Left(string;3)&"."& Middle(string;4;3)&"."& Middle(string;7;4) ; RGB ( 0 ; 0 ; 0 ) )    
 )
)
)

 

kjoe

[David Head]

Hi kjoe

 

So why do you format text with RGB ( 0 ; 0 ; 0 )?

Wouldn't you just leave it as is?

And you don't need the nested Case statement.

Wouldn't this do the job?

 

Let(

[

string=Filter( PhoneField;"0123456789");

isemail=PatternCount(PhoneField;"@")

];

Case(

Isemail ; PhoneField ;

Length(string) 10; TextColor ( PhoneField ; RGB ( 255 ; 0 ; 0 ) );

Left(string;3)&"."& Middle(string;4;3)&"."& Middle(string;7;4)

)

)

[Maarten Witberg]

Hi David

 

Oops yes you're right about the nesting... I just stacked the email test on top of something I used myself.

I added the RGB(0;0;0) formatting because I suggested using it in an auto-enter calc. If the user corrects a field that has text marked red, it will stay red if you don't force it back to black....

 

kjoe

[comment]

Note that RGB ( 0 ; 0 ; 0 ) returns 0, so you can shorten:

TextColor ( PhoneField ; RGB ( 0 ; 0 ; 0 ) )

to:

TextColor ( PhoneField ; 0 )

[Maarten Witberg]

Yes and the email has to be forced back to black in case of an edit also, so putting it all together:

(if I'm not mistaken, the TextColor (XXX;0) can be left out in case of a normal calculation but not in an auto-enter replaces existing)

 

Let(

[

string=Filter( PhoneField;"0123456789");

isemail=PatternCount(PhoneField;"@")

];

Case(

isemail ; TextColor ( PhoneField ; 0 );

Length(string) = 10;

TextColor (Left(string;3)&"."& Middle(string;4;3)&"."& Middle(string;7;4) ; 0);

TextColor ( PhoneField ; RGB ( 255 ; 0 ; 0 ) )

)

)

 

thanks people

kjoe

[IGotIt]

Thanks guys this really helped and Kjoe thanks for putting it all together at the end.

 

It's so helpful to see the posts with different ideas. It helps to see how and why things are done and different ways of doing them. I learned something rather than just getting the answer and I appreciate it.

 

Milo

[LaRetta]

Yes and the email has to be forced back to black in case of an edit also...

Nope. Not since vs. 8. In vs. 7 the text colorizing didn't work quite right but they've fixed it now. It *is* true that you must reference the field again as a default result (if you use Replace) or the field will be empty ... but the colorizing back to black isn't needed. Try replacing that line with just the field name again to see it in action.

 

As David indicates, it isn't necessary. smiley-laughing

 

LaRetta

[Maarten Witberg]

Hi LaRetta,

 

Please tell me what I'm missing... I made this in version 8adv. If I don't use RGB(0;0;0) the field stays red after editing.Something wrong with the calculation? The platform maybe (I'm on mac)?

 

kjoe

[LaRetta]

VERY STRANGE! My file changes - yours doesn't!! And you're using vs. 8? I'm on 8.0v2. Try this one!

[Maarten Witberg]

I'm using 8.0v2 also. But please try this:

-type in a string that will be colored red such as 313123123123123123

-then replace it by an email adress.

 

it will stay red.

the calc has not one but two results where I used rgb(0;0;0): one when "isemail" returns 1, and one where string is exactly ten digits. in your calc, that one is still in place!

 

kjoe

[comment]

By "replace", kjoe means "edit". Because if you delete all (red) content, then type something new, it will be black.

[Maarten Witberg]

ah yes I see what you mean, but what I had to do was select all, delete, commit the record, enter new data, and then I had black text. I don't think that's very practical or clear to users.

 

kjoe

[IGotIt]

Hi guys, I hope you can help once more. Here is the calculation i'm using.

 

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

 

Choose(GetAsNumber(Style); ""; "("; "") &

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

Choose(GetAsNumber(Style); "-"; ") "; "/"; "."; " "; "*"; "") &

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

Choose(GetAsNumber(Style); "-"; "-"; "-"; "."; " "; "*"; "") &

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

TextColor ( Phone ; RGB ( 255 ; 0 ; 0 )))

 

When the phone has more or less than 10 numbers the numbers turn red and thats great and how it should be. The problem is when I enter an email address the address turns red. How do I change the script so its red for the wrong amount of numbers and black for the email address?

 

Thanks for your help you guys are great.

 

Milo

[Maarten Witberg]

Your calculation will almost always return a red email adress since there probably aren't any email adresses consisting of exactly ten digits plus an @, and in that case the phone format will be forced on it. You need to test for the presence of @ to designate the field content as an email address.

 

So, going back to my original suggestion, and adding the style options, you get for a normal calc field:

 

FormattedPhoneNumber=

 Let(
[
string=Filter( DataEntryPhone;"0123456789");
isemail=PatternCount(DataEntryPhone;"@")
];
Case(
Isemail ; DataEntryPhone ;
Length(string) = 10; 
Choose(GetAsNumber(style); ""; "("; "") & 
Left(string;3) & 
Choose(GetAsNumber(style); "-"; ") "; "/"; "."; " "; "*"; "") & 
Middle(string;4;3) &
Choose(GetAsNumber(style); "-"; "-"; "-"; "."; " "; "*"; "") & 
Middle(string;7;4);
TextColor ( DataEntryPhone ; RGB ( 255 ; 0 ; 0 ) )
)

 

and for an auto- enter calc in a text field:

 

FormattedFoneAutoEnter =

 

Let(
[
string=Filter( FormattedFoneAutoEnter;"0123456789");
isemail=PatternCount(FormattedFoneAutoEnter;"@")
];
Case(
Isemail ; TextColor(FormattedFoneAutoEnter ;0);
Length(string) = 10; 
TextColor( 
  Choose(GetAsNumber(style); ""; "("; "") & 
  Left(string;3) & 
  Choose(GetAsNumber(style); "-"; ") "; "/"; "."; " "; "*"; "") & 
  Middle(string;4;3) &
  Choose(GetAsNumber(style); "-"; "-"; "-"; "."; " "; "*"; "") & 
  Middle(string;7;4);
0);
TextColor ( FormattedFoneAutoEnter ; RGB ( 255 ; 0 ; 0 ) )
)
)

 

Making use of the Let () function and using Filter() instead of ABS() and Substitute() reduces the parentheses maze. BTW it's exotic I know but hard-coding the substitute "." will break when the solution is used in a country that uses metric (ABS will leave "," in place instead). Abs is an algebraic function, not a text manipulation (though I think its the kind of lateral use of functions that sometimes is very nifty indeed).

 

kjoe

 

PS if Style is a number field you can also leave out GetAsNumber( )

[IGotIt]

Hi Kjoe and thank you for your help. I used the "FormattedFoneAutoEnter" and it works great. The Style field is a text global field.

 

Thanks again for your help,

 

Milo