5 Replies Latest reply on Sep 10, 2013 2:35 PM by philmodjunk

    Searching across multiple tables

    HP

      Title

      Searching across multiple tables

      Post

           I have a database with 40+ main tables. Each record in these tables has a related "meta" record that matches on its key. 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.

           I understand that I would not be able to view all the fields of the matched records in a single layout, as each table has different fields. I would settle for being able to just see the keys of these matched records. Since all records have a "meta" record, I could just show the key from that meta record on a layout. However, there is another hitch: when searching through all the tables, how do I save the records that match the string? I could copy them all into some generic related table, or have a "found" field in the meta record, but wouldn't that cause problems if multiple users are searching at once?

           Is there any way to accomplish this without introducing race conditions and without restructuring my whole database?

        • 1. Re: Searching across multiple tables
          philmodjunk
               

                    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.

          • 2. Re: Searching across multiple tables
            HP

                 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.

            • 3. Re: Searching across multiple tables
              philmodjunk
                   

                        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.

              • 4. Re: Searching across multiple tables
                HP

                     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?

                • 5. Re: Searching across multiple tables
                  philmodjunk

                       I wouldn't say hopeless, but it does complicate displaying the results.