Filemaker Formula

[bv2000]

I have a field that looks for a telephone number from another table if the ID# matches. If it is a new client, how do I write a script to #1 - look to see if there is a match for that ID# and then automatically enter the phone number, and #2 if there isn't, then how do i enter a mask so the newly entered phone appears like (xxx)xxx-xxxx. When, FM definitions are set up, there is a choice to either have a look up or a formula. How do I do both?

 

[tcoyle777]

Try this calc. for phone number to be formated (XXX) XXX-XXXX

Let(
Clean Phone = Filter(Home Phone;"0123456789");
Case(
Length(Clean Phone) = 10;
"(" & Left(Clean Phone;3) & ")" & " " &
Middle(Clean Phone;4;3) & "-" &
Right(Clean Phone;4);Home Phone)
)

 

[JeanW]

Use a lookup based on the ID# relationship and put the above formula as auto-enter calc.