4 Replies Latest reply on Dec 17, 2012 10:25 AM by philmodjunk

    Need advice how to setup trainer database



      Need advice how to setup trainer database


           I work with about 55 different trainers that each train different topics (some train more than 1 topic).

           I need to set up a trainer's roster database that will allow me to print a roster list that will be divided by topic and will list each trainer who trains THAT topic.

           I've tried a test database that had each trainer as a single entry, with a repeating field so I could select the multiple topics they train.  That didn't seem to work when I went to do a roster report.  It sorted and grouped pretty well, except when a trainer did multiple topics, they only showed up under one topic and did not repeat on the report.

           Is there an easier way to do this?  I need to have detailed information about each trainer, in addition to which topics they train.  There are up to 7 topics.  Some trainers train up to 5 different topics.

           How can I effeciently set up a database that will allow me to create a roster that is subdivided up by the topics.  In other words, I want a report with subheaders that are the topics and each trainer that does that topic under it, and then on to the next topic title and so on.

           Thank you all for you help!!!

        • 1. Re: Need advice how to setup trainer database

               First, don't use repeating fields for this.

               Am I correct that more than one trainer can train the same topic?

               If so, you have a many to many relationship. A trainer can teach many topics and a topic can be taught by many trainers.

               Many to Many relatiionships are typically implemented with a third table serving as the "join" table between the other two:


               Trainers::__pkTrainerID = Trainer_Topic::_fkTrainerID
               Topics::__pkTopicID = Trainer_Topic::_fkTopicID

               see the first post of this thread if my notation is unfamiliar: Common Forum Relationship and Field Notations Explained

               WIth this set up, you can place a portal to Trainer_Topic on a Trainers layout and it will list all topics that they teach. Fields from Topics can be included in this portal. In similar fashion, a portal to Trainer_Topic on a Topics layout will list all trainers that teach that topic.

               And your roster can be printed from a layout based on Trainer_Topic with fields from the other two tables included on the layout to supply such info as the trainer's name or a topic's title.

          • 2. Re: Need advice how to setup trainer database

                 Oh thank you thank!!  I'm fairly new to portals but I understand the concept.  I'm gonna get hopping on this and I'll let you know how it comes out!!



            • 3. Re: Need advice how to setup trainer database

                   Ok so now I have more questions LOL.  I hate to sound so much like a novice on this, but I am.

                   So, am I going to want to make a table where I have a record entry for each trainer and each single module they train?  ie:

                   RECORD 1; John Smith, Module 1

                   RECORD 2: John Smith, Module 3

                   and so on?

              • 4. Re: Need advice how to setup trainer database

                     First, make sure that you carefully read the first post of the link that I shared in my last post. It not only explains the notation that I used here, but also recommends how to set up primary keys as serial numbers (the fields starting with __pk) in each of your tables.

                     You put "John Smith" in a a record of your trainers table. You'd put "module 1" in a record of the topics table. Then, in a portal to Trainer_Topic, you'd either select a topic (if done from the trainers layout) or select a trainer (if done from the topics layout) to create a new record in the portal that links "John Smith" to "Module 1" to show that this is one of the topics that John Smith teaches.

                     You may find this demo file on many to many relationships helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                     It has portals set up like I have described here as well as buttons for adding a new record on the other side of the join table and some more sophisticated approaches you can try out once you get the "basic setup" to work for you.

                     If you are using FileMaker 12, open this file from the File menu and FileMaker 12 will produce a copy converted to the FileMaker 12 format.