I know this is a FAQ but I don't find the answer.

Two tables with a simple relationship.

Project

-------

ProjectKey

ProjectName

Task

----

TaskKey

ProjectKey

Description

The user is entering a new task. They know the ProjectName but not the ProjectKey. I want the user to be able to type into a field, the ProjectName and ProjectKey would be assigned.

I currently do this with a pull down, but because of the number of Projects, the list has become unwieldy. I could do something with another window/area to do a find, but it seems over kill when the user knows the full project name.

Any techniques for doing this?

Search for "clairvoyance" on this forum.

Thank you for your comment, but I am not looking for that type of functionality. I have explored many of the references to Clairvoyence and even employ several of them.

I can use value lists, find functions and relationship "trickery" to present the user with a list of choices to choose from. But that is not what I am looking for.

Project name is important to the user. This is what they think of as the "Key" into the relationships. They know this value. But, as suggested here and other places and as good database design, I use unique numbers (the method suggested by Kurt Knippel) to create keys used for relationships.

What I need is for the user to be able to type "4009-04" or whatever and this will "trigger" filemaker to do some magic (sorry, just watched Harry Potter with my kids) to understand that "4009-04" is really "000112-014199-102104".

I am currently using a great method by Matt Petrowsky of ISO FileMaker Magazine called NoNumbers popup. http://www.filemakermagazine.com/modules...der=&thold=

But this method requires typing in the search string, tab/return/enter and then selecting the one record found. It does it nicely without any scripting.

If you could point me to a specific article or post that will help, great and thanks.

I've gone back through and looked at every reference to clairvoyance for the past year. There are some that are close to usable. Here is what I am thinking.

Rather than displaying a screen first and let the user whittle away at a list of choices, I will put a global on the layout. This captures the users input. But here is where I would like to deviate some. From the data entered, and using techniques listed from several sources, I will know if the entry produces one row or several. Now this is where I am stuck.

Everything up to this point has been scriptless. Nothing for the user to do but type in the data. But if the results produce more than one row, I want to popup a window and present a list of results. I think I can do this with a custom function, but any suggestions would be welcome.

[

to understand that "4009-04" is really "000112-014199-102104". ]

Isn't that obvious?

(Sorry, I couldn't resist.)

Hi Swens. What about using a value list based on the results of a clairvoyance-style match? That is, instead of outputting matches in the typical clairvoyance portal, output them in a clairvoyance value list, values from field (whateverFieldHasThoseLongNumberS).

It sounds as if maybe you have already tried this; if so, what was the result? I'm curious.

j

Here's what I would do:

My data entry layout is based on task table. I have a global field (temp_projectName) and a value list that is all of the project names in the project table. I also setup a relation from temp_projectName to another occurance of the project table.

The data entry layout contains

- project id (for when the user knows it),

- global temp_projectName field, formatted as popup list set to the project name value list

- other task fields

When the user clicks "ok", a script looks up the related project id using the new relation (from the temp_projectName field), and fills it in if the field is blank. If it is not blank, the field would have to validate itself based on the regular related project table.

It sounds as Jerry get it straight. You don't even need a new window to pop-up, unless you really don't want the user to act on the ID itself.

But then, bringing a new window to front is called from a script, would it be plugged or directly triggered by a button, when "lauching" the Clairvoyance process.

Maybe I don't understand completely, but wouldn't a lookup fit the bill? I know you can use a lookup field to fill in other fields (as in type in customer name and it fills in the address, etc.). You did say the user knows the ProjectName.

Just a thought...

Kevin