12 Replies Latest reply on Nov 14, 2011 10:27 AM by philmodjunk

    Newbie: creating initial relational database

    FawnHill

      Title

      Newbie: creating initial relational database

      Post

      I am new to relational database setup and I am trying to figure out how to set up a test case database in filemaker.

      See attached image -- I figure that I will need a main table to contain records of each of the test cases -- new test cases will be added as time goes on.  I believe I would then have an additional table for each new project which would contain a subset of applicable test cases, but each test case record in the project table would also contain results information for the test case in that particular project -- the results and other fields will be different for each of the projects, but the test cases themselves would not change, unless the main test case is changed.  How would I initially set this up?

      test_cases.PNG

        • 1. Re: Newbie: creating initial relational database
          mgores

          I would use a join table, a table between the project and test case tables.  This table will hold the link between the project record and related test case records.  You can add the fields that are specific to that particular project-test case to that table as well.

          • 2. Re: Newbie: creating initial relational database
            FawnHill

            Thanks, Mark. I'm very newbie -- could you give me an example of what I would use to join the two tables such that I could have multiple project tables with the same fields that would contain different entries?  For example, how I would have a test case joined to two different project tables, the project tables would have a field called "results".  One project might have "pass" for that field and the other project might have "fail" ?  I have an idea of the concept, just not how to actually do it in filemaker.

            • 3. Re: Newbie: creating initial relational database
              mgores

              This is real simple, but the top line is the tables and below each are the fields, the dotted lines show the keys that link them.

               

              projects  --<projects_testcase >--Test case

              ProjectID  ---  projectID                 Test name

              Name             testcaseID -----------TestcaseID

                                   Pass/fail

               

              So you can have a project_test case record showing Project A with test case Z and a result of "pass"   and Project B with test case Z with a result of "fail", etc

              with any combination without having to change the fields in the two main tables.

              • 4. Re: Newbie: creating initial relational database
                FawnHill

                Are the "keys" created by a relationship tie?  How can I get copies of (all or most of ) the records in the Test Case table copied to each of the projects as they are created?  Maybe I'm just thinking of this wrong..

                • 5. Re: Newbie: creating initial relational database
                  mgores

                  The keys are auto entered serial numbers in the parent tables and entered into the join table when the join record is created.  You don't have to copy the data from the test case table, you only have to create a record in the join table that has the projectID and TestcaseID.  This allows you to display any of the data from the Test Case record(s) on the Project layout.  You add fields like "pass/fail" to that join table for information that only goes with that Project-Test combination.

                  • 7. Re: Newbie: creating initial relational database
                    FawnHill

                    Thank you both for all your help!  I'm going to chew this up tonight and see if I can figure this out tonight. I'll post again tomorrow to let you know if I can do it.

                    Cheers!

                    • 8. Re: Newbie: creating initial relational database
                      FawnHill

                      Thanks.  I think I basically have this figured out.  Just a couple of more questions, if you don't mind.  I have a testcase tabl (with a whole bunch of testcase records), and I have a project table.  In the testcase table, I have a (serial number) field that I'm using as the key (TID), and in the project table I have a (serial number) field that I'm using as it's key (PID)

                      Then, I create a join table for a specific project which contains both a TCID and PID (serial number) field.  In the relationship graph, I joing the TID of the testcase table to the join table, and the PID of the project table to the join table.

                       

                      Now, I have an empty join table and in order to populate it with all of the testcase records, I have to create a new record for each record in the testcase table -- only then do I get testcase records in the join table.  Is there any way to automatically populate the join table with all of the testcase records without having to add them one at a time? I'll eventually have several new projects (each with it's own join table) and it will be hard to keep adding new records to all of the join tables that I create for all of the new projects.

                      Also, if I add new records to the testcase table, or if I make changes to some of the fields of the testcase records, is there any way to automatically add the new records to all of the join tables, and also make the changes in the testcase table take effect in the join tables?  After I create testcase records, I will often go in and change text in some of the text fields, but the join table has the old data still in it.  That won't work -- not if I have to go and make each change manually in all of the join tables.

                       

                      Thanks again for all your help!

                      Fawn

                      • 9. Re: Newbie: creating initial relational database
                        mgores

                        The records are only created in the join table when you assign a test record to a project. It should have the key fields from the other two tables and is just a list of the combinations.

                        • 10. Re: Newbie: creating initial relational database
                          philmodjunk

                          is there any way to automatically add the new records to all of the join tables

                          Populating the join table in this fashion defeats the purpose of setting up the join table.

                          I'll eventually have several new projects (each with it's own join table) and it will be hard to keep adding new records to all of the join tables that I create for all of the new projects.

                          You should not create new tables for each new project but should keep using the same table, adding new records for the new projects and related records as needed. Creating a new table each time would also require creating new layouts for each...

                          • 11. Re: Newbie: creating initial relational database
                            FawnHill

                            To PhilModJunk: The problem is each project will use the same testcase records, with just the addition of a results field (pass/fail) and some other information about the results of the testcase for that particular project.  So for example, I will have, say, 1000 testcases and each testcase needs to be run for each project.  The testcases themselves will be the same for all projects, just with different results between the projects.  What Mark describes above fits more into this scenario; having just one copy of the testcases and storing the results and a few other bits of information in the join table.  I'd rather create a new layout for each project than to have to duplicate all of the records (which will mostly contain the exact same data)  for each new project.  Otherwise I'd end up with a huge database with mostly duplicated data.  If I correctly understood your last post.

                             

                            If there's a better way to do it, I'm open for that too.  There's got to be a better way...

                            • 12. Re: Newbie: creating initial relational database
                              philmodjunk

                              You need to put the correct fields in each table.

                              The problem is each project will use the same testcase records, with just the addition of a results field (pass/fail) and some other information about the results of the testcase for that particular project.

                              You would have three tables such as:

                              Projects---<TestResults>----TestCases

                              The info that describes each test case that is the same for every project, such as the name of the test case, should be part of the TestCases table. The info that is specific to the testCase for this particular project, (Results and info about the results) are fields to define in the TestResults table. In the demo file, you'll note that several contacts can be linked to a single event record, but their "paid" status is different for each contact for each event. This is done by defining this status field in the Event_Contact table just like you would put a field for test results in your join table.

                              If you want to automatically generate a set of linking records in TestResults so that every new project is already linked to a standard set of TestCases records, that can be scripted. Your script can loop through all or a set of records in Test Cases and create one matching record in TestResults. The alternative is to add the linking records at the same time that your record your test results.

                              Here's a script that starts from the current record in Projects and creats a link to every TestCases record in that table:

                              Set variable [$ProjectID ; value: Projects::ProjectID ]
                              Freeze window
                              Go to Layout [TestCases]
                              Show All Records
                              Go To Record/Request/Page [First]
                              Loop
                                Set Variable [$TestcaseID ; value: Testcases::TestCaseID ]
                                Go To Layout [TestResults]
                                New Record/Request
                                Set Field [TestResults::ProjectID ; $ProjectID ]
                                Set Field [TestResults::TestCaseID ; $TestCaseID ]
                                Go To Layout [TestCases]
                                Go To Record/Request/Page [next ; exit after last]
                              End Loop
                              Go To Layout [original layout]