1 Reply Latest reply on May 7, 2009 8:52 AM by philmodjunk

    Need help with Relational fields

    Rbevens

      Title

      Need help with Relational fields

      Post

      Hi,

       

      I'm pretty new to filemaker and I'm having difficulty wrapping my head around this one.  I am building an assessment database adding onto the template contact management.  Here's what I want to do--I want to be able to select a "group" dropdown field for each contact which then populates my assessment "category" dropdown field and after choosing the category the "Questions" drop down field will be populated.  My problem is understanding the relationships because to me each contact is part of many groups, each group contains many contacts, each group contains many assessment categories, these categories fall under many groups, each category has many questions, and these questions fall under different categories.  I know I'm not suppose to have any many-to-many relationships.  I'm confused... Any help is greatly appreciated!

        • 1. Re: Need help with Relational fields
          philmodjunk
            

          Rbevens wrote:

          I know I'm not suppose to have any many-to-many relationships.  I'm confused... Any help is greatly appreciated!


          I'm responding to just this one little part. Other posters can respond to the details of this template's design.

           

          Of course you can have many to many relationships! You just have to know how to set them up. The standard text book approach that works with Filemaker and most other relational databases is to set up a join table that servers as a bridge from one "many" table to the other.

           

          Let's take your contacts and groups relationship for an example. I don't use the contacts template, so my field and table names won't match it, but you should be able to apply the concept.

           

          Your groups table should have a GroupID field, probably an auto-entered serial number. Your contacts table should have a similar but separate ContactID field. To set up a many to many relationship, you create a "join" table with the following fields and relationships:

          GroupTable::GroupID--=--JoinTable::GroupID

                                                   JoinTable::ContactID--=--ContactTable::ContactID

           

          This sets up the relationships you need for portals, reports and calculations that need this "many to many" relationship.