Finding one search string or text fragment in many tables

[ndelac]

This winter, I completed a resturcturing of our company's big flat-file database, separating the various companies and contacts into proper entity-specific tables. We are a book business, so 3 example entities would be: Publisher, Vendor, Printer.

Say a user knows a company name, but is not sure if they are classified as a Publisher, Vendor, or Printer. Using relationships and portals, I am able to find any/all exact text matches that occur in any of the 3 entity tables... What my team is looking for, however, is something more like a Google search or individual FMP find request, where they can enter a fragment of a word and get all matches across all 3 entity tables.

Can I the search string/fragment a global and then doing some sort of cascading find request in all 3 tables..?.. Or somehow assign a temporary "match key" to found records..?

[comment]

Sounds like Publisher, Vendor, and Printer are really different types of Contact, and belong in the same table. Do you have many fields specific to each type?

[ndelac]

Sorry I was a bit unclear; Publisher, Vendor, and Printer are distinct types of "companies" or "buildings" that have an indefinite number of people/contacts linked to them.

I made them separate entities because we engage in distinct types of transactions/activities with each...

Edited June 6, 2006 by Guest

[comment]

I am trying to say that engaging in distinct types of transactions/activities with each is by itself not a sufficient reason to split them up into separate tables. The very need to search in 3 tables is an indication that the records really belong in a single table.

To answer your original question: you can search each table in turn, but you will need 3 separate windows in order to present the search results. Alternatively, you could import the found records from each table into a union table, so that they could be viewed together.

[ndelac]

Cool -- I could admit to taking the whole entity separation too literally and too far. This DB was a huge, amorphous "rolodex" when I inherited it, so I got a little overzealous...

All this said, thanks for the tips, Comment! I will look into the multi-window and union table options.

[comment]

I only mentioned those options to show how convoluted even the most basic operations can become with this structure...