1 2 Previous Next 18 Replies Latest reply on Apr 3, 2009 10:39 AM by philmodjunk

    I suspect this is an extremely basic DB Relationship question, but...

    AMalyuchik

      Title

      I suspect this is an extremely basic DB Relationship question, but...

      Post

      I suspect that if there are no stupid questions, then I guess I am a stupid person asking questions.

       

      I am a pretty much the next step up from the person who have never seen a relational DB before.

       

      After a few days of putting together this DB, I ended up with a database which technicaly two parts that work fine among themselves, but refuse to cross one stinking point and I can't seem to figure out how to connect them. I feel I most-likely am missing a table, but for the life of me I can't figure out how to formulate it so it gets the relationship going.

       

      Short background:

       

      1. I create books

      2. Each book has a list of labs

         a. Each lab fits into one category

         b. Each lab can be a part of a specific Type of labs

      3. Each Lab has a list of Materials.

         a. Each Material can fit into it's own category 

         b. Each Material can go into any or all of the four kits (Classroom, Presenter, Participant, Retail)

         c. Some of the Materials have to be shipped with refills.

       

      Final objective is to select a book from the list and get 4 lists out to print so the warehouse can fulfill them.

       

      What I get:

       

      I can create a book line item where I get Lab names in each book.

       

      And I can get the Materials Line Item where I can get the list of materials in each lab.

       

      I can get a list showing me labs in each book. And I can get a list showing me materials in each lab with the quantities and types for each kit.

       

      What I cannot get:

       

      List of materials with quantities and types for each kit for each book. Basically I can't take the two working steps above and connect them together. It lies in one spot in the relationship diagram. I have outlined it in red. The working parts are outlined in green and orange.

       

      Thank you for any info you guys can provide.

       

      BTW. When I create layouts, it tells me that the tables are related, but it just doesn't show the information in the field in the list.

       

       

       

       Problem Child

        • 1. Re: I suspect this is an extremely basic DB Relationship question, but...
          philmodjunk
            

          This is most definately NOT a basic DB question.

           

          Your screen shot of your Relationships gave me the clue I needed.

           

          I believe you need an additional relationship.

           

          You have two Join Tables: BookLabsLine and LabMaterialLine

           

          With the relationships graph open, drag from BookLabsLine::Lab_ID_fk to LabMaterialLine::Lab_ID_fk. You'll get a "Table Instance" dialog popping up. This is not a new table, but it will look like one on your diagram. Give this new instance a name Like BooksMaterialsLine.

          Use the same method to link an Instance of Materials to BooksMaterialsLine.

           

          Now refer to your fields using these two new Table Instances in your layout and I think you'll get what you need.

          • 2. Re: I suspect this is an extremely basic DB Relationship question, but...
            AMalyuchik
              

            Thanks a Lot for your reply.

             

            Unfortunately that didn't really change anything.

             

            I'm starting to think I need to rethink my tables because my hair is too short to pull.

             

            Here is what I did:

             

            Here it goes:

             

            Here goes

            • 3. Re: I suspect this is an extremely basic DB Relationship question, but...
              philmodjunk
                

              AMalyuchik,

               

              I'm going to sleep on this one and check back in the am. My first post was to get you started with relationships that can make this happen if you set up your layout the right way. I'm going to double check my advice to make sure that I gave you good advice and then take a crack at the next step.

               

              The main issue here is that you have multiple many to many relationships--this is the design you need--but the devil is in the details.

              • 4. Re: I suspect this is an extremely basic DB Relationship question, but...
                philmodjunk
                  

                Here's a quick check for understanding. This isn't a solution, but a check to confirm my initial analysis is correct:

                 

                You need the following report:

                 

                Book 123     Principles of Chemistry 3rd Edition

                   Lab 145 Basic Observation

                         Material 234 Candle

                   Lab 236 Electrolysis

                         Material 101 Battery

                         Material 202 Wires w/alligator clips.

                   Etc.

                 

                and this is the report you can't get.

                 

                Table relationship summary:

                 

                For any given book record you have multiple Lab Records.

                For any given lab record, you may have multiple book records.

                BookLabsLine functions as a "Join table" linking many book records to many Lab records.

                 

                For any given Lab you may have many materials.

                For any given material, it may be used in many different labs.

                LabmaterialLine functions as the Join table linking many labs to many materials.

                 

                The challenge lies in linking the two sets of many to many relationships.

                That's what I've been mulling over for you off and on since my initial post. The light bulb hasn't come on yet, but I'll keep thinking.

                In the meantime, maybe the above post will prompt another Forum participant to post a solution suggestion.

                • 5. Re: I suspect this is an extremely basic DB Relationship question, but...
                  AMalyuchik
                    

                  That is the correct report, except I don't really need the Lab names in it.

                   

                  All I need is:

                   Book 123 Principles of Chemistry 3rd Edition

                      Science Items

                         Material 234 Candle           (Put 10 into a classroom kit)  (put 1 into a workshop participant's kit) (it is reusable)

                         Material 345 Test tube       (Put 10 into a classroom kit)  (put 1 into a workshop participant's kit) (it is not reusable)

                      Household Items

                         Material 456 matches                                                   (put 1 into a workshop participant's kit) (it is reusable)

                      Liquid Chemicals

                         Material 135 Lead Nitrate   (Box of 12 1 oz. Botles into a classroom Kit) (Not reusable)

                   

                   

                   

                  Quantities and whether the thing is reusable or not is taken care of. I just need to get all of the materials separated by category as above onto a one piece of preintable paper so the warehouse can fullfill the order of a, say, classroom kit.

                   

                  As far as your relationship summary, that is exactly how it works. I can feel that it really isn't too complicated, I just don't have enough experience with databases and even less with Filemaker to make it work.

                   

                  Is there a way of creating a custom query in Filemaker? I am used to programming MySQL and if I can make a "SELECT `Mat_ID_fk` FROM `LabMaterialLine` WHERE `Lab_ID_fk` =  "##" OR `Lab_ID_fk` =  "##" OR `Lab_ID_fk` =  "##" etc..."

                   

                  My issue would probably be solved.

                  • 6. Re: I suspect this is an extremely basic DB Relationship question, but...
                    philmodjunk
                      

                    Actually, leaving out the Lab names eliminates referencing an entire table, so that simplifies things.

                     

                    Which table and which field or fields holds the Science Items, Household Items, Liquid Chemicals information?

                     

                    Is there a way of creating a custom query in Filemaker?

                     

                    We call those find requests.

                     

                    I am used to programming MySQL and if I can make a "SELECT `Mat_ID_fk` FROM `LabMaterialLine` WHERE `Lab_ID_fk` =  "##" OR `Lab_ID_fk` =  "##" OR `Lab_ID_fk` =  "##" etc..."

                     

                    Whether you are using a database tool that uses SQL (I've worked with MS Access SQL a lot) or FMP, it still comes down to the relationships. In FMP you are using the Relationships Graph to connect records and tables. In SQL your expressions will use "Left join, Inner Join" type statements to link tables. The inherent logic is the same.

                     

                    • 7. Re: I suspect this is an extremely basic DB Relationship question, but...
                      AMalyuchik
                         material_category_fk in Materials table. At first, I was going to use 0's and 1's in the Materials table and separate them that way, but then I ended up making a separate table Materials_category
                      • 8. Re: I suspect this is an extremely basic DB Relationship question, but...
                        philmodjunk
                          

                        THis is just to let you know that I haven't forgotten you. I've been playing with a model of your DB tables and think I have a method worked out. It uses a text field that functions as a kind of "Intermediate Query" (to lapse into SQL speak) to get us what you want.

                         

                        Unfortunately, it's quitting time here and I don't have the time to finish testing and then post the rather detailed answer you'll need to make this work. I'll try posting the solution to you tomorrow.

                        • 9. Re: I suspect this is an extremely basic DB Relationship question, but...
                          philmodjunk
                            

                          OK, thanks to the wonders of a good night's sleep and some free associating, I've got the answer and it's actually pretty simple. I had  another approach last night but it was a clumsy "Kludge" while this morning's idea is simple and elegant.

                           

                          Return your relationship graph to it's original state. No need for new tables or additional relationships.

                           

                          Open up Manage | Database | Fields and add a global text field gTitle to your Book table. We'll use this field in the report.

                           

                          Dismiss Manage | Database and enter layout mode.

                          Create a new layout, called "Report" that specifies LabMaterialLine as its table.

                          Make it a List/report type layout and give it the following details.

                           

                          Place gTitle in your header

                          Create a SubSummary part when sorted by Material_category_fk that "prints above" the body. 

                          Place material_category_fk in the Sub-Summary part. From your earlier post, I am assuming that this is a text field that contains "Household", "Science"...

                          Place your detail fields in the body. If you need fields from Labs, Materials, or Materials_category, you should be able to add them here and get righ data to appear.

                          Save and return to browse.

                           

                          Create the following script:

                          Set Field [gTitle, Book Name]

                           

                          If [Count(BooksLabsLine::Book_ID_fk) = 0]

                              Show custom dialog ["no BookLabsLine records exist"]

                              exit script

                          End If

                          Go To Related Record ["Show Only Related Records", From Table: "BookLabsLine", Using Layout: "BookLabsLine" (BookLabsLine)]

                           

                          Go To Related Record ["Show Only Related Records", match found set, From Table: "Labs", Using Layout: "Labs" (Labs)]

                          Go To Related Record ["Show Only Related Records", match found set, From Table: "LabMaterialLine", Using Layout: "Report" (LabMaterialLine)]

                          Sort Records [Restore, no dialog] --- In this sort specify that records be sorted at least by material_category_fk so the sub summary part will be visible

                           

                          I've referred to the default layout names that are automatically created when you first create a table in filemaker. In each Go To Related Record step, save the last, you can refer to any layout that refers to the same table shown in parenthesis. If you specify a layout in a Go To Related record step, script takes you to that layout and creates a found set of records satisfying the relationship criteria you specify. Unfortunately, GTRR will not produce the expected results if there are no matching records. Thus, the Count() function confirms that there are matching BookLabsLine records before permitting the script to continue. You would be advised to design in additional safe guards to insure that at least one matching Labs and labmaterialline record has been created.

                           

                          Now to use the script.

                          Go to a layout that lists your Book records.

                          Select a record. You can perform a find. If it's a form view layout, you can click through the records using the book control in the upper left corner of the task bar. If it's a list or table view layout, you can scroll to the desired record and click on it to make it the current record.

                          Now run the script and you should see the desired report.

                          • 10. Re: I suspect this is an extremely basic DB Relationship question, but...
                            AMalyuchik
                              

                            Thanks Phil, i feel I am closer to it than ever before, however, here is my script. I wasn't able to get some things into it as per your post.

                             

                            For example, I couldn't enter: Set Field [gTitle, Book Name] and Sort Records [Restore, no dialog]

                             

                            Here is a picture of what I was able to get and it doesn't go to the report layout after I run the script.

                             

                            • 11. Re: I suspect this is an extremely basic DB Relationship question, but...
                              philmodjunk
                                

                              Let's fix the minor details first. Both of these shouldn't affect the main problem, but first things first.

                               

                              1. Open up your script in the script editor and click the Set Field step.
                              2. Click the upper Specify button.
                              3. In the pop up, select the Book table from the drop down menu and click gTitle to highlight it.
                              4. Click the lower specify button to bring up the Specify Calculation dialog.
                              5. Select the Book Table from the drop down menu at the top and then double click gTitle to select Book::gTitle as your expression. (You did define this as a global field in Book right?)
                              6. Click OK to return to the script editor
                              7. You should now see Set Field [Book::gTitle, Book::Book Name] in the script editor.

                               

                              Now click on the Sort script step.

                              In the bottom left corner of the script editor a "perform without dialog" check box appears, click it.

                               

                              Your script should now have all the correct steps.

                               

                              Now for the main problem:

                               

                              Go To related record will not switch you to a specified layout if there are no matching records for the specified record/found set and relationship. If you are using FMP advanced, use the script debugger to step through the script one step at a time and see what breaks.

                               

                              If you don't have that tool try this:

                              What layout do you end up on after you run the script?

                              If you see BookLabsLine, then there were no matching records linking any of the records in the found set in BookLabsLine to Labs.

                              If you see Labs, then you there were no matching records in LabMaterialLine.

                               

                              If all else fails, insert pause/resume after each GTRR step and run the script.

                              You can then check the results of each and press Enter to make the script continue to the next pause.

                              • 12. Re: I suspect this is an extremely basic DB Relationship question, but...
                                AMalyuchik
                                  

                                Well, I fixed the script and thatnk you very much for helping me with that.

                                 

                                However, I do end up in BookLabsLine table view. Oddly,I obviously have Lab_ID-fk in the labs field in that table because it shows me the names of the labs associated with the name of the book.

                                 

                                Here is a screen shot:

                                 

                                 

                                Also here is a screen shot of the Layout view:

                                 

                                and this is what I see after I go to the Book Table and run the script:

                                 

                                • 13. Re: I suspect this is an extremely basic DB Relationship question, but...
                                  AMalyuchik
                                    

                                  Ok, here is one more interesting thing.

                                   

                                  You see how when in the BookLabsLine when I have Lab_ID_fk field showing in the layout it shows Lab Name? When I try to tell it to show the actual Lab ID number by saying show me "Labs::lab_id" it just shows me an empty box. 

                                   

                                  Maybe that will help.

                                  • 14. Re: I suspect this is an extremely basic DB Relationship question, but...
                                    philmodjunk
                                      

                                    Both of your previous posts suggest the same thing:

                                     

                                    That you have something wrong with the relationship linking BookLabLine to Labs. Since GTRR can't find records in Labs that match the Lab_ID_fk values in BokLabsLine, it leaves you at the previous layout, BookLabsLine. The empty field you see tells me the same thing.

                                     

                                    Try using find to search the Labs table for Baking Soda Cannons or any of the other Lab_ID_fk values you show me. Verify that they are EXACTLY, letter for letter the same.

                                     

                                    If so, check the field definition for Labs id and Lab_ID_fk. Both fields should be indexed text fields.

                                    1 2 Previous Next