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.
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.
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
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.
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..
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.
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.
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!
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.
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...
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...
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:
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 ]
Go to Layout [TestCases]
Show All Records
Go To Record/Request/Page [First]
Set Variable [$TestcaseID ; value: Testcases::TestCaseID ]
Go To Layout [TestResults]
Set Field [TestResults::ProjectID ; $ProjectID ]
Set Field [TestResults::TestCaseID ; $TestCaseID ]
Go To Layout [TestCases]
Go To Record/Request/Page [next ; exit after last]
Go To Layout [original layout]