3 Replies Latest reply on Apr 8, 2010 3:19 PM by JanCornell

    How to add multiple related records of data into one table?



      How to add multiple related records of data into one table?


      I am very new to working with Filemaker and databases in general, so please forgive me is this a very dumb question; I am busy building a relational database for a laywer-kinda firm and I've hit sort of a 'brick wall' in the design proces of this database. I am using Filemaker Pro 10 on a Windows XP Prof OS.


      I've created a table with laywercase information and what I would like to have on this table is the possibility to add multiple victims or accused people unto it (sometimes there is 1, sometimes there are 20 - it can greatly vary as you can imagine). what is the best way of doing that?


      I allready thought that maybe I need to create two extra tables like 'victims'  and 'accused' or something and make sure that all the people and their related data (name, current adres, status, etc) are located in those two databases, but then how do I add as many as I want unto this table for the laywercase information? and how can I freely add more then 1?


      I hope my question is clear - looking forward to some feedback - thank you very much for your time in advance!


      Regards, Jan Cornell

        • 1. Re: How to add multiple related records of data into one table?

          Howdy Jan,


          Let me give you a "shove" in the right direction including the terms you'll want to look up in the help index to get more familiarity...


          It looks like you would want three TABLES within the single database (Case, Victim, Accused) {think about also Lawyer, Judge, etc.}


          These three tables could be linked via CaseID#.

          The vicitims could be shown in your layout via a PORTAL and you could have as many as you would like (as much as one likes having victims, of course ;) )  When you fill in a portal row, you would actually be creating and adding data to a record in the related table.  As long as the relationship is set up to CREATE NEW RECORDS VIA THIS RELATIONSHIP, you can freely create and edit info on multiple related victims...you will not be limited to 20.


          Is this what you're after?

          • 2. Re: How to add multiple related records of data into one table?

            Hi Jan,


            I would suggest using one additional table called People.  Then include a Category field with value list attached and value list would be:  Victim, Accused, Witness, Judge, Attorney, Police Officer ... all people involved.


            I would also suggest a related address table because, sadly in some cases, the first three I mentioned can all be from same family.  You would have a Case table each person could relate to the CaseID as well as the AddressID in addresses.  I would use a join table which just holds IDs because one person can related to multiple cases and one case can have multiple people.


            In this way, if your new secretary takes a call and forgets to get the number, you can search your ONE People table for Nick Anderson and find him even if he is a person's lawyer or a witness on Case #77.  You can have filtered portals (without additional table occurrences since vs. 11) displaying only victims, witnesses, etc. as you wish.  But printing a report showing all people involved in Case#77, sub-summarized by Category is as easy as searching for Case 77 and adding a leading part with Category.  You can even have multiple judges if one judge steps down, or multiple police officers. That report with all people involved in Case##7 (including their address & phone) can be slipped into the Case file or handed to Assistant DA when required.


            There are times to split data into separate tables, whenever you have a one-to-many situation, and there are times to keep 'like' things in the same table, such as people.  So People can relate to one case or many cases and they can have one address or many addresses.  And if you get an anonymous note saying that the gun which fired the shot is at 127 SW 3rd street, you can search your addresses and find out that the butler lives there and he's a witness involved in Case#77 and an accused in Case#82 . :smileyhappy:

            • 3. Re: How to add multiple related records of data into one table?

              Hello Laretta and Ninja,


              Thank you for very much for your quick responses - they both have been very helpful - the idea of creating a portal sounds very nice, I guess I will have to read up on the basics of creating a portal and how to get the view for the right records and stuff but your ideas on those have been very helpful indeed!


              Thanks again,


              Jan Cornell