I'm trying to format a field that the user inputs a telephone number and it formats it automatically to a certain format. Everytime I set the calculation in that field (using the replace command) I get a circular reference error. I know it's a simle mistake, but I can't figure it out. My field is a calc field to produce text with a replace function of the contents of that field. How else would I be able to do that?

Thanks,

frustatration

What you are saying here doesn't add up. You can't use the menu command 'replace' on a calculated field. Do you mean that you have set up the field as autoenter calculation with the Replace() text function? If so, you're right; it will give a circular ref error if the calculation references it's own contents.

Workaround:

Set up a second field as a calculation field that takes the text from the input field and formats it into the display text. Then, on your layout, place the calculated 'display' field in front of your input field. Make it opaque, and deselect the "allow entry into field" in the field format.

quote:

---

Originally posted by BobWeaver:

What you are saying here doesn't add up. You can't use the menu command 'replace' on a calculated field. Do you mean that you have set up the field as autoenter calculation with the Replace() text function? If so, you're right; it will give a circular ref error if the calculation references it's own contents.

Workaround:

Set up a second field as a calculation field that takes the text from the input field and formats it into the display text. Then, on your layout, place the calculated 'display' field in front of your input field. Make it opaque, and deselect the "allow entry into field" in the field format.

---

OK, you're right, but isn't there a way to input the Tel into the input field and have it modified upon exiting the field? I would like to avoid 2 fields showing the each Phone Number. I know it can be done with a button-set script, but I'm thinking that a seperate calc field should be able to modify the input field after the Phone number is entered???

Here is the trick: there are two fields, one is a text field where people enter the data. The other is a calculation field that modifies the entry to make it look right. The entry itself is never modified.

On your layout, disallow entry to the calc field (field format options) and place the calc field on top of the text field -- give it a solid fill so it hides the text field below it.

Now when people click on the "field" (it's actually both fields remember) they enter the text field and type their stuff. When they click out of the field the calc field formats the phone number and hides the text below it giving the *illusion* that the number has been reformatted, but it actually hasn't.

This is a common technique and it's popular because actually modifying the entry field is complicated requiring scripts etc (as you found out) and is not completly robust and reliable. This method doesn't modify the data, which is a Good Thing

If you insist on using only one field, you can trigger a script upon exiting a field using a plug-in called Script Scheduler (find it at www.filemaker.com). Personally, I'd put a calculation field in front of the entry field. -bd

Hey everybody:

I just figured out the coolest trick to do this. It uses a self-join, a lookup, and a calculated field to make any field reformat itself after the user exits from it or any other specific field. Here is what you need:

code:

---

FieldName Type Options

------------------------------------------------

ModTime Time Auto-Enter Modification Time

SerNo Number Serial Number

SerLkp Calc (Number) =(ModTime*0)+SerNo

PhoneNumber Text Lookup SelfBySerLkp::PhoneNoFormatted

PhoneNoFormatted Calc (Text) =case(PatternCount(PhoneNumber,"("),PhoneNumber,

"("&Left(PhoneNumber,3)&")"&

Middle(PhoneNumber,4,3)&"-"&

Right(PhoneNumber,3))

---

The self-join relationship SelfBySerLkp is on the SerLkp field.

The only field that you need to put on the layout is the PhoneNumber field.

Oops. Me and my big mouth. I just did some more testing and found a hitch in the above technique. It only works on an existing record. If you create a new record and immediately enter a phone number it doesn't work. If you click outside a field and then back in and enter a phone number it's okay. Sorry. I'm going to keep looking at this though. I think it has possibilities.

On the other hand, if you use a script to create a new record, you can just put in an Exit Record step and then a Go To Field step right after you create the new record and that solves the problem.

[This message has been edited by BobWeaver (edited February 22, 2001).]