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

    Multi Table Search



      Multi Table Search


      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.


        • 1. Re: Multi Table Search

          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

            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

              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

                P.O number which is included in my invoice table

                • 5. Re: Multi Table Search

                  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

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