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..?

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?

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, 200619 yr by Guest

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.

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.

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