Each record in these tables has a related "meta" record that matches on its key.
Does that mean that you have one table that is linked to all "40+" tables. What kind of key? What kind of relationship?
I want to be able to search for a string and be given all records across all main tables that have that string in any field.
"given all records" (with that string) in what format? A single found set? A layout based on the "meta" table where you have portals to each of the 40+ tables showing the records that contain that string?
And a question that always has to be asked when someone wants to search multiple tables for the same data: "Are you really sure your data should be split up into 40+ different tables like this? (Sometimes people split data into multiple tables that should actually be kept in a single table. Please note that I am asking here, not suggesting that this is the case for you.)
The possibility that the specified string could be in any field is highly unusual. The fields that make up a given record usually are much more specific than that and thus normally only single field might possibly hold a specified criterion. In a few cases, several fields might need to be searched, but any field makes for a very unusual situation and suggests a very unstructured table of information.
Yes, the "meta" table is linked to all 40+ tables. The relationship is a match on the key (So table1.key = meta.key and table2.key = meta.key, etc). Basically, for every record in every table, there is a "meta" record that has the same key as that record.
What I was planning on doing for the search was going to the layout for each table, doing a quick find for the search string, then somehow marking those records as "found" so I can display their key from their related meta record on the final result layout. I cannot search a layout based on the "meta" table, because the fields that I want to search are not located there.
In this case, I do have to split my data up into 40+ tables. Each table has completely different fields. The only one they all have is a "key" field.
That is true, I probably wouldn't need to search EVERY field, just several. If push comes to shove, just searching a "name" or "description" field would be enough. Note that some of these tables do not have a "name" or "description" field. In that case, I would just look at the "key" field.
I probably wouldn't need to search EVERY field, just several. If push comes to shove, just searching a "name" or "description" field would be enough.
And that's an important point that I am trying to make. Quick find is a "broad" search. It searches every field on the current layout for which quick find has been allowed to pull up a found set.
But you can also enter find mode and specify find criteria in one or more fields and set up one or more "requests" to get a number of different resulting found sets. And you can setup a system where you enter your criteria in a global text field and then use a script to perform one or more finds based on that criteria.
Each table has completely different fields. The only one they all have is a "key" field.
And that's the other side of the coin here. If the fields are completely different, that then justifies multiple tables, but then if the data is completely different in these tables, what kind of meaningful search requires you to search all fields (or even some fields) of every table in the same query of your database?
Example: If I am searching for "main" in a table of addresses, it would seem that I want to see a group of addresses for "main street". But if I search a table of Person's names for "main" it would seem that I want a table of people named "Main". Pulling up a set of addresses AND a set of People if the person's name OR the street name contains "Main" makes very little sense.
But let's assume that this is an unusual situation and you really do need to perform this search as described thus far:
It would appear that you need a found set of Meta records where at least one related record contains data matching the search in at least one related table. I can think of two general approaches that may serve. One that uses Quick Find and one that uses the standard Find. You can experiment with both to see which produces the best results:
1) Design a layout based on the Meta table with portals to each of the 40+ related tables. Put each field that might possibly hold data matching your search in a row of the appropriate portal on this layout. Make sure quick search is enabled for all of these fields. Use a script to: 1) freeze the window, b) switch to this layout and perform the quick search and c) go to the layout also based on the Meta table where you have set up it up to list your Meta keys.
2) Set up a layout with a global field and perform a scripted find that generates one find request for each of the 40+ related tables. It can perform this find on any layout based on the Meta table so it can go directly to the list layout for listing the results. With each new request, there is a set field step that uses the data in the global field to specify search criteria in a special calculation field that you add to each of your related tables. This field uses the concatenation operator to combine all fields that might possibly hold the data being specified as search critiera. You only need this field if you have to search multiple fields from the same table.
Thank you for your suggestions. I am starting with something like suggestion 1 with just a "name" and "key" field.
I just remembered, though, that not every record has a "Meta" record. Only records that need additional information have a meta record. In that case, I think searching everything is hopeless, since the records are not all related. Is this truly the case?
I wouldn't say hopeless, but it does complicate displaying the results.