2 Replies Latest reply on Jun 22, 2010 8:50 AM by philmodjunk

    One search for multiple tables

    ProETS

      Title

      One search for multiple tables

      Post

      I am pretty new to filemaker so bear with me please.  I do not expect a complete solution but if someone can help me in the right direction maybe I can fill in the blanks.  I have a product catalog I am converting to a filemaker database.  I want an overall search field that would search the entire database so that someone could just type in what they want and get results.  I have used some of the google like search tutorials with sucess but my vision of what the DB needs to be doesn't seem to work.

       

      So details, for simlicity I have 2 product types, Bolts and Clips.

       

      Bolts:

      Part Number - Like B123

      Thread - Text Field (3/8, 5/16, etc)

      Notes - Just a general field for details about what the part goes with

       

      Clips

      Part Number - Like C123

      Thickness - Numeric

      Width - Numeric

      Notes - same as above

       

      My current attempt:

       

      __Parts:

      __ID

      Part Number

       

      Bolts:

      __Part Numberfk

      Thread

      Notes

       

      Clips:

      __Part Numberfk

      Thickness

      Width

      Notes

       

      With this setup I am able to open the Bolts layout and create parts via a relationship so that the parts table contains all possible parts but the problem is that while I can search the Bolts layout, or the clips layout, I want to be able to search the Parts layout and display results from Bolts or Clips.  I tried creating a calculated field in bolts and clips both that was like "Thread:" & Thread but I can't get that field to show up on the parts table.

       

      If I need to provide a sample database I can, any clues or links will be very appreciated as well as any ideas of what I could search for to find help.

       

      Thanks again!

        • 1. Re: One search for multiple tables
          NickTsotsikyan

          I have the similar idea on my DB one field for a search of all fields in one table but you can probably use the same script I have created with adding a bit more to go through multitple tables.  Bascially you should use "Loop" "End Loop" script steps.  Email me your email address and I'll send you my script.  NickT@capatrol.com

          • 2. Re: One search for multiple tables
            philmodjunk

            If you have defined relationships in Manage | Database | Relationships like this:

             

            Parts:: Part Number = Bolts:: Part Numberfk

             

            Parts:: Part Number = Clips:: Part Numberfk

             

            You should be able to just place the datat fields from Bolts and Clips directly on your Parts layout and you can then put search criteria in these fields when performing a find.

             

            Example: Use the Field tool to add a new field to your layout. Select Clips from the drop down and click Thickness to add the Thickness field to select it.