10 Replies Latest reply on Oct 13, 2014 11:24 PM by gmfischer

    Searching in related table from a Parent layout

    gmfischer

      Title

      Searching in related table from a Parent layout

      Post

      I have a main menu with Drop Down Lists, The Value list comes directly from one of the related tables field, in this case a Manual Number, In that related table are two fields a Manual Number and a URL link to a Web Page to the manual. The intention is you go to the main menu in the parent table layout select from a list of manuals and have it open that URL in a web page. The problem is when i do this the URL ends up empty or I get error no record found depending how I write the script. I keep trying different way to write the script with no luck. Is it possible to do this? am I missing something simple, or do I not understand relationships between tables

      Please Note - In the parent table I have Products the have Manual Fields that relate to the Manual Table,  (fields are Parts Manuals, Operation Manuals, etc... and they related to the Manuals Numbers in the Manual Table.

       

       

       

        • 1. Re: Searching in related table from a Parent layout
          philmodjunk

          Can one manual be linked to more than one product?

          The precise relationships that you currently have defined are needed to see where things might not be working for you.

          Is your drop down list a list of all manuals in your table of manuals or is it a conditional value list of just the manuals linked to that product?

          • 2. Re: Searching in related table from a Parent layout
            gmfischer

            A manual might be used for several products, each product might have several different manuals (parts, operations, repair). I have attempted to set up couple ways to find a Manual 1) the drop down list on Main Page  2) once you locate a product you select a Manual from a separate Parts layout that has common used parts and manuals for that product.

            I have a Manual Table with 950 Manual Numbers and a separate field with a Weblink to the manual's PDF, I have defined a relationship between the manual numbers used by the Products in the Product Table with the Manuals in the Manual Table

            When I select the manual number in Product Table I want it to run a script that will find that part number in the Manual Table and Open the URL to the manual, I just keep coming up with a empty URL

             

            • 3. Re: Searching in related table from a Parent layout
              philmodjunk

              I don't see why you don't put the URL field into the Manual table. I still do not know the details that I requested, but what you describe is a many to many relationship. Many Products to Many Manuals.

              Start with these relationships:

              Products-----<Product_Manual>-----Manuals

              Products::__pkProductID = Product_Manual::_fkProductID
              Manuals::__pkManualID = Product_Manual::_fkManualID

              You can place a portal to Product_Manual on the Products layout to list and select  Manuals records for each given Products record. Fields from Manuals can be included in the Portal to show additional info about each selected Manuals record and the _fkManualID field can be set up with a value list for selecting Manuals records by their ID field.

              A button inside the portal row of the Product_Manual portal could use Open URL to open the URL associated with each manual--which should be a field in Manuals.

              Note that each manual is a separate record no matter whether it is a Parts Manual, an Operations Manual or some other type.

              • 4. Re: Searching in related table from a Parent layout
                gmfischer

                It maybe that I this is more complicated then I thought.

                 

                 

                Manuals Table Fields =  Manual Number, Manual URL

                Product Table Fields = Parts Manual, Operations manual, Repair manual

                Product: (Parts Manual, Operations manual, Repair manual) >----------------------------< Manual: Manual Number

                This might be extremely elementary but how does the pkProductID relate to fkProductID if I look up a Product:PartsManual in the Product table shouldn't it directly find the matching Manual Number in Manual:ManualNumber

                If lookup a PartsManual 123456 in the Product Table how do I get it to find it in the Manual Table

                I have work on and off with Filemaker since version 1 most of my databases in filemaker over the years have just been hobby stuff, and simple relational and mostly non-relational databases.

                I have watched videos from Lynda.com, hour of them, but they never seem to show you how one id field in one table helps you find data in a related table, just that you have to have them and they seem to be nothing more then a serial number for each record where an unchanging part number is a unique ID, I feel like I am missing one small simple step that i have struggled to locate online and in videos for months, I can get most of the database working fine then it seem simple things hang me up

                any advise on where i can go to get a better understanding on not just related tables but how the Primary Key fields and Foreign Key Field Function . Sorry for my ignorance thanks for your help

                • 5. Re: Searching in related table from a Parent layout
                  gmfischer

                  Here is an example of one thing I am trying to do with the manual table, My drop down menu is on another layout, it almost there its as if I have a box miss checked or a script command out of place????

                  • 6. Re: Searching in related table from a Parent layout
                    philmodjunk

                    My point is that I'd use a portal to list the join table records instead of using multiple separate fields. That would seem a much more efficient design than what you are using. You would not need to look up any manuals as the data would be already listed right in the portal where you need it. The __pk to _fk relationships I've described are standard one to many relationships, which when used with the join table, produce the typical many to many relationship consistent with what you describe.

                    Your current design requires different relationships for each manual as you've chosen to put the manual number for each into a different field.

                    An alternative approach is to use a script to perform a find for this data using a script that set's a variable to the field's value, switches to the manual table, performs the find, opens the URL and then returns to the original layout.

                    Set Variable [$ManualNumber ; value: Get ( ScriptParameter ) ]
                    Go to Layout ["Manuals" (Manuals) ]
                    Enter find mode []
                    Set Field [Manuals::ManualNumber ; $ManualNumber ]
                    Set Error Capture [on]
                    Perform Find []
                    OPen URL [Manuals::URLField ]
                    Go to Layout [original layout]

                    You's specify the different fields in the button's script parameter box as the way to pass the manual number from your individual "button fields".

                    I offer this as a short term solution. Setting up the many to many relationship using a join table offers a much better way to manage this data in the long run.

                    • 7. Re: Searching in related table from a Parent layout
                      gmfischer

                      Thank you very much, that helps, My next version of the database I will use the join table approach, I have a few other Many to many relationship with other components related to the products.  thanks again

                      • 8. Re: Searching in related table from a Parent layout
                        gmfischer

                        Paul, this seemed to work fine and as I tested it started to come up with the same manual , no matter what I picked, do I need to clear the variable? or is there a script set I forgot to add

                         

                        • 9. Re: Searching in related table from a Parent layout
                          philmodjunk

                          Paul's my older brother wink

                          What value are you passing as the script parameter? Each button should specify a different field as the script parameter expression.

                          • 10. Re: Searching in related table from a Parent layout
                            gmfischer

                            I forgot to set the Script Parameter, but it worked a dozen or more time without doing that , was it picking up the number from the field I used as a button? Regardless i made the changes and they are working great thanks again for your help Phil