Help with calculating and look up field

[dailo]

Currently I have two tables setup with users and phone numbers. Currently everything works fine the way I set it up is that if I enter a phone number it will automatically pull up all the users information and input them into the fields by using lookup. I changed the phone number field so that it automatically formats phone numbers to 000-000-0000 but when I enter a phone number without the dashes the users info will not show up anymore. I have to enter the phone number with the dashes in order for it to work. Is there anyway I can get it to look up the field after it converts the phone number format?

Also is it possible to set a relationship so that it will either look up info by phone number or user id? Because when I tried to do it would only work if I put both information in, not one or another. Thanks!

[Brian C]

I suppose the easiest way would be to have your relationship use a calculation field instead of the field you are using to input the phone number.

By using a calculation field you can check the format of what was input and either reformat it with the dashes as your result or just leave it alone if it is already entered in the correct format.

[Brian C]

To have it attempt a lookup via the phone or the user id, I suppose you could incorporate a multikey in your destination database, so that each record contains the ID number on line 1, and the phone number on line 2. (Remember multikeys are are return delimited) this could be accomplished by having the link field in your destination database be a calculation field that stores the data as such:

ID & "P" & Phone (the "P" is a hard return)

[dailo]

By setting the relationship to use a calculation field, everything works the way you described but there is a problem...

I'm actually using IT Journal Lite (http://bigabytes.net/ITJournalLite/index.php) and I was trying to edit it so that it would format numbers inputed, but also bring up information if the user has called in before.

The only problem now is that the New Ticket button doesn't work anymore on the user screen. Is it because i'm setting up a relationship between a calculation and a text field? I always get the error "This action cannot be performed because this field is not modifiable. Any ideas why this might be happening?

[-Queue-]

That error message means that you are trying to set a calculation field via Set Field, Insert Calculated Result, and the like, which cannot be done. You can set a text, number, etc. field with a calculation, but a calculation field will always retain the result of its definition and is not modifiable.