search multiple fields

[TMsonflieth]

I have done a lot of reading and searching this forum, but have not found my answer. I simply need to perform a find across 5 fields in my table. The fields are,

Contact1

Contact2

Contact3

Contact4

Contact5

I need to be able to find "John Doe" in any of the fields. Is this possible to design without scripting? The only answers I can find seem to require scripting and I am a newbie and have no experience with this. If I can get some help with the script I need, that would be great. I would like to place a button on my layout that says "find Contacts", and then let the script search the 5 fields.

Thanks

Edited January 9, 2009 by Guest

[aldipalo]

To the best of my knowledge you will need to script it. There are several techniques that ISO Filemaker magazine has on their web site. Matt goes over the technique, via video, step by step. Here's one of them:

http://www.filemakermagazine.com/videos/faster-or-search-script.html

However, you must take a minimum 3 month subscription. It's around $25.00.

hth

[comment]

Without scripting, you'd have to manually create 5 find requests and enter the criteria into one field only in each request.

However, your real problem is inadequate structure: instead of the 5 fields, you should have 5 (or any number of) related contact RECORDS.

[TMsonflieth]

Thanks for the reply HTH. I am too cheap to plop down $25 though.

[LaRetta]

If you are taking over an existing structure where you cannot change it right now, you could concatenate the fields and search the calculation (result is text) instead as:

Contact1 & " " & Contact2 & " " & Contact3

Be please listen to Comment here and split your Contacts into records instead of fields if at ALL POSSIBLE. In addition, you should be splitting into FirstName and LastName (at minimum). If this is a new design, switch it now to proper structure or you will surely regret it.

BTW, scripting is not difficult at all. Give it a try. You simply can't design without it.

LaRetta

Edited January 9, 2009 by Guest
Added BTW

[TMsonflieth]

Thanks LaRetta. It is not too late to change my design, so I think I will follow the suggestion. If I follow correctly, right now, I have one table only. I don't have the need to keep detailed info on my contacts, so I was trying to minimize things. The suggestion is to create a second table of my Contact info, such as LastName, FirstName, Phone, etc... . Then I relate the two tables??? Here is my project. I work for a TV outdoors show. Each show has three stories. Each story is a record in my DB. Each story may have multiple contacts, along with a bunch of other data like story length and video tape numbers. The main field in my record is the Story Name. I need to be able to search my DB to find the contact info for "John Doe", and to see what story he appeared in. So, do I set up a relationship between the StoryName in my main table, to maybe the LastName field in the second table?

Thanks for the time!

[Fitch]

Welcome to the forums, TMsonflieth.

Stop! Do NOT use a field that has meaningful data in it to relate tables.

Instead, create an ID field and make it auto enter a serial number. You can use the Replace command to update existing records.

In your Contacts table, make a StoryID field (non-auto entry) and relate that to the ID in Stories.

[TMsonflieth]

Makes sense, as long as I figure out how to add the serial numbers to my existing records. I think I should be able to figure that out.

I see Fitch that you too are in Portland. This project is for the "Oregon Field Guide" show on Oregon Public Broadcasting.

[Fitch]

OPB, meet Fiddlehead Software :

As I mentioned above, you can use the Replace command to update existing records. Note the checkbox in the screenshot attached.

[TMsonflieth]

OK, so I am slightly confused on the structure thing. In my Contacts table, do I create a set of fields for each contact? For example..

Contact1name,Contact1phone,Contact1email

Contact2name,Contact2phone,Contact2email

Contact3name,Contact3phone,Contact3email

Enough of these sets of fields to cover what I think the maximum number of contacts I would ever need? Sorry to sound like such a novice!

[aldipalo]

I know you said you were too cheap to spend $25.00 will you invest your time to do some necessary reading? If so, go here:

http://www.foundationdbs.com/downloads.html

and read:

White Paper for FMP Novices

and

Database Design for FMP.

Also take a look at these 2 test files to see what has been suggested as a basic contact management structure.

These are very simple examples and are no means intended to give you the basis for your product!

Client_Contacts_DB.zip

Company_Contacts.zip

[LaRetta]

Sorry to sound like such a novice!

There is nothing shameful about being new at something. And you ARE investing your time and effort to willingly determine the best solution here, and I commend you for it.

You will want one record per Contact. For sure. I suggest you step back from the designing aspect and focus on some reading (as has been suggested) so you get your base structure correct. Once you understand how to create a relationship, it will make more sense. Feel free to post your design here for review; it seems humbling but you will be getting free consultation from the best there is.

I realize you are probably pushed to begin code; both from your boss/owner and internally but hold off just a bit more. And listen to the guys here. You will know when you grok it - you will feel the lightbulb go off and you will get very excited about relationships!

Hang in there and have fun with it ... there simply isn't a better group than this one!

LaRetta

[TMsonflieth]

Thanks for everyone's time in replying to me. I truly appreciate it. I think the relationship thing is indeed something I really need to grasp. Hmm... That is true in my personal life too. I have glanced through one of the white papers noted, and will read through both soon. Heck, I will probably spring for the $25 subscription too!