2 Replies Latest reply on May 22, 2009 1:10 PM by philmodjunk

    Relationship Rules of Thumb

    carlz_1

      Title

      Relationship Rules of Thumb

      Post

      Casn someone give me a listing, or rules of thumb to better understand the relationship and layouts?

       

      Say I have a main, parent table called Applicants.  I also have several child tables called Assets, Vehicles, and Income.  EAch of  these has a child key that is linked to the Parent APPLICANT table.

       

      When I create a layout that contains Assets and Vehicles, should the Layout be based on the APPLICANT table or one of the child tables?  

       

      I had a layout based upon the APPLIOCANT table and on it was a calculation field from a child table that would not calculate.  The reason it would not calculate I am not sure but when I looked at the number of records in that child table, it was 0.

       

      I know this is probably basic stuff, but I still have difficulty understanding it. 

       

      Can anyone give me an example or rule of thumb that might explain this a bitt better?

       

      As always, thank you.

        • 1. Re: Relationship Rules of Thumb
          mrvodka
            

          You have tables, table occurrences, and then layouts.

           

           

          Table

          Table Occurrence ( instance representation of the table )

          Layout( based off the TO )

           

           

          Therefore, if you create a layout based off a particular Table Occurrence such as People, and then you put a field from let's say a related address table,directly on the layout, it will only display the first related address to each person's record. Since there can be more than one address for a person, to display ALL the related addresses, FileMaker gives you a portal to display them.

           

          Does this help?

          • 2. Re: Relationship Rules of Thumb
            philmodjunk
              

            "When I create a layout that contains Assets and Vehicles, should the Layout be based on the APPLICANT table or one of the child tables?"

            You can do it either way. The difference lies in how you define your relationships and the purpose for your layouts.

             

            Relationships fall in one of the following categories:

             

            One to One: One record in the Parent table matches one and only one record in the child table. This relationship is needed only for special purposes as it's nearly the same as simply putting all the fields in a single record.

             

            One to Many: One record in the Parent table matches any number of records in the child table. A classic example of this is an invoicing database where the one record in the parent table represents a single invoice and the matching records in the Child table represent the different items purchased on that invoice.

             

            Many to Many: Many Records in the one table match Many records in the related table. One table might list different animal species and the related table might list the geographical areas where that animal lives. Since one animal can live in multiple locations and one location can be the living location for multiple species it's described as "many to "many". Almost always, a many to many relationship is implemented by using a Join table that breaks it up into two One to many relationships. In this example, a record in the join table would match one animal to one location. There would be many Join records for a single animal, each matching to a different location and many records in the join table for a given location each matching to a different animal, thus giving you two "one to many" relationships.

             

            Since "One to Many" is the most common relationship, let's discuss it in terms of deciding which table to reference in the set up for a given layout. Generally, data entry layouts refer to the parent table, with a portal for collecting data for each related child table. This allows you to view and work with all the related records for a given parent record.

             

            In some cases, this works also for printing out a report, but since portals aren't very flexible for printing purposes, you may choose to base a layout on the child table and place fields from the parent table in Sub-summary, Header, footer or Total type report parts. This gives you a flexible report format where every related record is visible and data from the parent record is displayed in headings, subheadings and totals.

             

            Hope that helps. Feel free to respond with more specific questions.