doing one find through many fields

[new to this]

Is it possible to do a find for one word (for instance a name) over a number of different fields in a database? I have a dozen fields on each record for names of individuals...on each record the names may be in different fields - in a different order (depending on their placement on a list)...so in essence, if I want to find Tony Jones, on record one he may be in the third name field ("name3"), while on record two he may be in the sixth name field ("name6")...is there a script I could write that will allow the user to input a name in one of the name fields and have it search all of the name fields during a find?: This was one of my first databases, and the possiblity of this problem didn't occur to me at the time.....

[stanley]

New:

Sure it's possible. One way to do it would be the following:

1. Create a new field "t_Found_Set", make it a text field.

2. Make a special layout for this type of find, with a field for the user to enter the name into.

3. Allow the user to enter the find criteria (the name) in Browse mode, then trigger the script with a button.

4. The script would do the following:

Freeze Window

Show All Records

Copy [wherever_the_name_was_entered]

Set Error Capture [on]

Enter Find Mode

Paste [First_Name_Field]

Perform Find

If [status(CurrentFoundCount)>0]

Go to Record/Request [First]

Loop

Set Field [t_Found_Set, "Yes"]

Go to Record/Request [next, exit after last]

End Loop

Show All Records

# Let's do the same again, for the next name field

Set Error Capture [on]

Enter Find Mode

Paste [second_Name_Field]

Perform Find

If [status(CurrentFoundCount)>0]

Go to Record/Request [First]

Loop

Set Field [t_Found_Set, "Yes"]

Go to Record/Request [next, exit after last]

End Loop

# next is the third name field...

and so-on, through all of your six name-field instances. Then, show all, find for t_Found_Set="Yes", and you've got your list. Before refreshing the window, though, do another loop, and set all of the t_Found_Set fields to "No", so you clear out that pointer.

-Stanley

[MoonShadow]

I hate to break the news to you ...

and the possiblity of this problem didn't occur to me at the time.....

With the structure you are proposing, ie, several fields within each record that contain different names, you will be using your above phrase repeatedly.

These TWELVE names should be in a related file. Proper structure would eliminate the kind of problem you are now facing. Any time you have 'like' data (in your case, names) they indicate a 1:n (or one-to-many) situation and usually warrent another file/table.

Having said that, yes, you can search several fields by concatenating them into one text calculation and then searching on the calculation, viz:

Name1 & " " & Name2 & " " & Name3 (on up to twelve!)

But this setup will cause you nothing but headaches because each time you try to do anything with those names, you will run into this (or a similar) problem. And you will be required to run yourself in circles and create scripts such as Stanley's (or create multiple calculations) just to get what you need. And many times, you will NOT succeed and be very frustrated. For instance, generating a summary of records by name would be (nearly) impossible.

[Lee Smith]

new to this,

First, the answer to your question.

Each field will have to have it's own request (an OR find). To do this, go Into Find mode, enter your requested name in one of the field, and then do a new record request (Cmd N in Find Mode will do this), then enter the requested name into the second field, another Cmd N and enter the requested name into the third field. and so on until you have the 12 request. once all of the fields have the information needed, do your find.

Second, I have a question for you.

Why do you have so many fields for names on your layout that would have that many possibilities for different names for each record? There might be a better way to structure you files than what you have.

HTH

Lee

[new to this]

thanks for all your help....I was VERY new to Filemaker when I created this monstrosity - for some reason it made sense at the time to have so many different fields...this will be giving me headaches forever, I am sure...each date is a different record, and at the time I thought seperate fields for each individual was a good idea...CLEARLY I was wrong!