6 Replies Latest reply on Mar 15, 2011 1:20 PM by thong127

    Multi Table Search

    thong127

      Title

      Multi Table Search

      Post

      Hi All,

      Ive been searching the net & the forum but I cant find an answer...kindly share a multi table search sample data base. thank you.

      thong

        • 1. Re: Multi Table Search
          philmodjunk

          That's a broad subject and the need for a Multi-table search often indicates design issues better resolved by restructuring the tables and/or their relationships.

          Can you post an example of what kind of "multi-table search" you have in mind?

          • 2. Re: Multi Table Search
            thong127

            I have the following tables, product table and suppliers tables, contact table, invoice table. I like to make a single search srcipt saying that result for product table...total of...search result for suppliers table...search result for contact table..and search result for invoice table...

            Im sorry Phil, I accidentally click the choose the best answer beacuse my computer is having trouble. when I open my net browser it takes time.

            • 3. Re: Multi Table Search
              philmodjunk

              Yet these are related tables. Won't those relationships affect the results your users will need to see?

              A seach of the invoice table isn't likely to use criteria anything like the criteria I might use for searching a contact or supplier table nor is it likely to contain data for the same type of search as a product table....

              Can you give an example of criteria you might enter for a search of these tables?

              • 4. Re: Multi Table Search
                thong127

                P.O number which is included in my invoice table

                • 5. Re: Multi Table Search
                  philmodjunk

                  P.O as in Purchase order?

                  Which wouldn't likely be in the suppliers, contact or products tables, so why would you search all of these other tables for a purchase order number?

                  I'm trying to make the point that how you design your system and link your tables have a great deal to do with how you'd perform such a search and then how you'd present the results.

                  A generalized outline of the script would start with the criteria entered into a global field or group of such fields.

                  Go To layout [Invoices]
                  Enter Find Mode[]
                  Set Field [Invoices::PO Number ; Globals::gPO Number]
                  Set Error capture [on]
                  Perform Find[]
                  Set Field [Globals::InvoiceSearchCount ; get ( FoundCount )  ]

                  Go To Layout [//next layout to different table]
                  Repeat process with this table (can also loop this with a set field by name step if you have identical field names in each table)

                  When done, a layout can display all the global fields to show how many records were found and buttons next to them can use Go To Layout to go to a layout where you'd see the actual records found in that table. (There are ways to have portals to the found records here, if you want.)

                  • 6. Re: Multi Table Search
                    thong127

                    I will try your suggestion phil, thank you so much.