One find for several tables

[jrRaid]

I have several tables (4).

What I need now is a central search screen (I have), where I can input a search item, and search for this item in the 4 different tables.

Then display the result of the found set from the different tables in portals on one layout.

An empty portal = no records found, otherwise a list of items.

Right now I have only or nothing, or all the records in the different portals.

How do I have to go for this ?

A separate table ? But how do I have to make the relationships ?

Or am I looking for something that is impossible ?

TIA

[John Mark Osborne]

Before offering a solution, can you tell us why you have information in 4 different tables that needs to be searched as a single table? It sounds like the information in the 4 tables is similar? Can you tell us what entities are stored in each of the tables and what the differences as far as attributes (fields). The reason I ask is maybe you need to restructure your design rather than create a complicated script to search all of your tables and create 4 keys that display the results in portals.

[jrRaid]

Fair enough as question.

Look at it as 4 different dictionaries.

Phrasal Verbs, Idioms, Regular and Irregular verbs (1 table)

In each table we have a search screen.

It would be great to also have a general search option. Search for one word and return from each table the found set in one screen, f.i. 3 portals...

The user could navigate to whatever item....

I already have the searchpossibility with several script, but now I have to navigate to each table to see the found set...

The problem is to bring the found sets together in one screen...

Maybe with a loop through each found set to set a key = key in the searchResult screen...

This way each portal could be populate with the found set...

Or is this too simple as idea ...

[T-Square]

It looks like you could merge the different tables into one, and add a field to the one table to establish the type of entry. You could set up your results display to include this type and sort on it, to bring together entries of a similar type.

David

[jrRaid]

I don't think so David.

These are 3 different identities.

A PV is a phrase consisting of a verb plus an adverb or preposition for the atributes (with a lot of other fields), where in the verb table I only have a verb(field) with a lot of other fields for the 3th person singular, gerund, past simple etc.

Also the idioms table is complete different as structure.

The final goal is to have the possibility to enter a word (be it a verb or not, be it a sentence or not) and have the search result from PV, idioms and verbs in one result screen.

I already have the search/find routine in 'one move'. The problem is the display of the found sets in one screen/table.

[IdealData]

Sounds to me like you need a relationship based on a global field on your search screen. Use the global field to make the relationship to the other tables.

As you will realise the find functionality in FMP lets you search across multiple fields, and with partial text content to yield the matches, however the relationship method would have to be compromised a little.

Create a "SearchIndex" field in each of the tables as a calculation based on the fields that you would normally perform the find request upon. The calculation should be held as a RETURN SEPARATED list, thereby each line is valid for the relationship. You cannot include any globals or related fields in the calculation as they would render the field un-indexable and will fail on the relationship.

[comment]

The problem is the display of the found sets in one screen/table.

Exactly, and we know that's not going to happen. So you could do one of two things:

1. Create a master table that will be common for all three types. The master table needs to contain only a serialID field and the common field (i.e. the field that will be searched). All the other fields remain in their respective "subtables", which are linked to the master table in a one-to-one relationship.

2. Create a SearchResults table to compile the search results. This could be done by importing, or by getting the serialID's from each of the three found sets. With the latter, you could even present the search results in three separate portals.

[John Mark Osborne]

After reading your description of the entities, I would create a single table for all 4 entity types. They are enough alike that I would put them in the same table. Based on the description of your find, it's going to make it a lot easier. It will also make reporting easier if you ever need to create a single report with the information from more than one of the tables.