Searching Related Records along with Primary

[OffTheDeepEnd.com]

It's been a while since I have had a FileMaker project and I just can't figure out how this would work...

I have two tables, a main contact table and also a related contacts table. Both of them are linked based off of a primary key in the main contact table. I would like to have an all inclusive search that would find a name in the main and or related contacts but return the main regardless.

Example:

Main Table:

Name: John Doe

Related Contacts:

John Apple

Jane Smith

Jane Doe

The user would either type the search criteria in a dialog or global search field in find mode. Let say they would search for "John Apple", it would return the John Doe main record but if they searched for John Doe, it would also return the main record.

My thoughts on the solution would be to create a calculation field on the related records containing the record data from the main and related contact and make that my basis for search but that just seems kind of clunky and wouldn't work if the main contact did not have any related contacts. Any help would be appreciated.

Edited November 8, 2009 by Guest

[LaRetta]

If I understand correctly, I would use a find with two requests (untested script)

Enter Find Mode [ uncheck pause ]

Set Field [ Main::Name ; global ]

New Record/Request

Set Field [ Related::Name ; global ]

Set Error Capture [ On ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found." ]

Show All Records *

End If

... do whatever with found set

Since you are searching the related table from main, your results will ALWAYS be the main record regardless because you never leave the main layout.

* If you wish to preserve your prior found set (in case no records are found during this search), then open a new window first to perform your find. Then if no records are found, simply close the window to return to the prior found set.

Edited November 8, 2009 by Guest
Added *

[OffTheDeepEnd.com]

Awesome!

Now to add a little more complexity to the situation...

There is actually seven tables that all related to the main record but could be three or four relations into the the parent record, i.e.

Main Record -> Related Contact -> Purchases -> Projects

All four of these tables have a uniqueID that ties back to the main record but are based off more specific data in the relationship.

How could I search data that deep into the relationship?

Also, would it be worthwhile to build a record index table that would have an individual record for every record in the DB with a related calc field containing all data from that record.

That is the only way I can think of aggregating all that data to make it searchable.

[LaRetta]

If you are searching from Main table and you have relationships to child and even grandchild then you can search the grandchild just as easily as the parent and the resultant parent will have someone in their 'line' matching the global. However, if you are searching several 'grandchild' tables for names, your structure probably should be changed.

Also, would it be worthwhile to build a record index table that would have an individual record for every record in the DB with a related calc field containing all data from that record.

Such a record index table itself suggests that your solution isn't relationally optimum. Calculations would be unstored and slow, depending upon concatenating a lot of related data when, if properly structured, should be easily displayed. We need more information and preferably a file to see what you have. :wink2:

[OffTheDeepEnd.com]

Technically, the grandchild would be different criteria. The client would like a universal search field that will find a string across multiple tables and fields but return the main record. The search would need to include name fields, tasks, notes, emails, projects... Each of these have their own table and I guess I would need one field with all the data stored. That is why I was thinking of the record index.

Sorry that I can't post the solution. It is almost complete development wise and contains client information.

Edited November 8, 2009 by Guest

[LaRetta]

There are several posts addressing this situation. It still should be handled via multiple find requests instead of creating an index (which would be difficult if not impossible to keep in synch particularly since you are on vs. 8).

Here's a thread which should get you going: Google search :smile2: