5 Replies Latest reply on Aug 5, 2010 2:44 AM by craig_gee

    Join Table or Not?



      Join Table or Not?


      Hi there, I'm developing a solution that tracks Kung Fu classes and the members that are enrolled in them. I'm also using it as a replacement to the current staff rota.

      I'm linking instructors (from an instructors table) to instances of classes (in a table currently called occurrences) to show who is teaching them. I want to be able to show classes for a particular week in a portal, sorted by date, and have 3 drop down menus for the 3 instructors (Leader/Assistant 1/Assistant 2) that are teaching each class. The complication is that every now and again a class would have more staff, up to 12, but not very often.

      I was hoping to use a join table between instructors and occurrences so I can have a flexible amount of instructors linked to any one class and avoid loads of un-necessary instructor fields in the occurrences table. But would it be possible to have 3 drop down menus for each class in the portal and use a "details" button to go to a layout that would allow you to add more instructors?

      It's important that one person is identified as the "Leader" and all the other instructors as "Assistants". If I put a field in the join table containing "Leader" or "Assistant" would I be able to always show the leader in the first drop down menu in the portal?

      If I did both (had three fields in the occurrences table for the usual 3 staff and had a join table for any extra staff) would this make reporting the amount of hours the instructors have worked each month overly complicated?

      Thanks in advance for any help,


        • 1. Re: Join Table or Not?

          "If I did both (had three fields in the occurrences table for the usual 3 staff and had a join table for any extra staff) would this make reporting the amount of hours the instructors have worked each month overly complicated?"

          Yes, it would complicate any efforts to track this. Consider a join table that includes a field in the join table that you use to label each Join record as either a leader or an assisstant entry. You can either sort a portal of these so that Instructors are listed at the top followed by the assistants or you may set up two different portals to the same join table that have filters defined on the portal (filemaker 11 ) or filtering fields in different relationships ( older versions ) so that instructors are listed in one portal and assistants in the other.

          You could assign teaching personnel to a class with a portal to the join table with one drop down for selecting the instructor and a second for identifying them as "Leader" or "Assisstant".

          • 2. Re: Join Table or Not?

            Yes, a join table. There would ONLY one "Instructor" field (should be an ID) in the join table. I would use a simple number field, with a Boolean Value List (only value is 1), as a checkbox, for "Leader_flag".

            If, for a particular instance of a class, there was a leader and 2 assistants, there would be 3 records. The leader would have Leader_flag checked.

            I would NOT use text words "Leader" or "Assistant" (unless there could be a 3rd term later; doubt it). It is just easier to deal with Boolean values; someone either is or is not the leader.

            I think your main problem is that you want to show "class instances for a week" in a portal on an Instructor record (which is fine), but then you want to assign up to 3 instructors in THAT portal. Which is a little odd, as you're on a particular Instructor record. Normally that would make that instructor the only one to add from there.

            Here's how I'd do it I think:

            1. Portal to all class occurrences for the week (or 7 days, whichever date range)

            A button to click in that portal, to assign THIS Instructor to that class occurrence.

            That would populate another portal of only "this instructor's class occurrences for this date range."

            That portal's relationship would be to the join table, based on this Instructor's ID (and date range).

            An "Edit" button in that portal would pop up another (small) window. It would show that class occurrence record, on its table; likely using Go To Related Record [ portal relationship; show only related, new window]. It would have a portal for the "instructors" for that class occurrence. The relationship would have [x] Allow creation of related records. There would be a drop-down to choose "assistants" (filtering out Leader_flag, unless you can have multiple leaders for a class occurrence).

            The popup window allows you to "step out" of the Instructor context (the layout could even be attached to a table occurrence from a different table occurrence group). It would be a "dedicated" layout, not normally visible, for this purpose only.

            The assistants would then show in the "my class instances" portal on THEIR Instructor record.

            Alternatively you could be on a Class table record (I assume there IS a Class table, the parent of the "class occurrence", which are for each instance, at a particular date/time). Then show a portal there of the instances for the appropriate date range. And do much the same. It would be simpler.

            • 3. Re: Join Table or Not?

              Thanks for your replies. I will use the join table with the boolean value for the Leader Flag.

              The reason I'd want all three staff in the portal is because I want to show all classes for that week in a general rota layout, not just classes linked to a particular instructor. The layout will therefore be linking instructors and classes none of which are taken from the table that the layout is based on. It's so the person in charge of the rota can see all classes at once and fill in the gaps from there without having to go to instructors first.

              Is the easiest way to do this by using a new table occurrence of any table and relating it to the class occurrence table using the "x" relational operator, and then basing the portal on the class occurrence table? This should give me a list of all class ovccurrences that I can then sort and filter as a portal. If I use filtered relationships I would then just not use the "x' and use global fields for the relationship instead.

              I guess the other option is to use a list view in a layout directly based on class occurrences but this would make more work for me as my interface will have already been designed in form view, as well as not being able to put other things (like buttons) on the layout to the left and right of the class list.

              • 4. Re: Join Table or Not?

                You can show the "class occurrences for a week", as a filtered portal, by a global week (this can be done from anywhere, as week is week).

                The problem is always: you cannot have a functional* portal within another portal. So there's no way to add multiple instructors for that particular class occurrence within that portal. I reject the idea of 3 fields. Whenever you "hack" a structure to solve an interface problem you pay the price, sooner or later.

                You can however easily go to the records of that class occurrence, and/or set its ID into a global. You could have another portal on the layout, filtered by that global ID. But in this case I'd prefer going to the class occurrence record itself, in a new window. Then it's trivial to have a portal for multiple instructors, which is a child table of class occurrences (it can also be thought of as a join table between class ooccurrences and and a table occurrence of the Instructors table).

                I created an example file of this method. I used rather generic names for the entities. So, in this case, Parent would be "class occurrence" and Child would be "class occurrences' instructors".


                * I say "functional", because it is possible to put a portal within a portal. But that only really works if the "portal within a portal" relationship is to a "constant". Its use is to make something (like a button) visible or not.

                • 5. Re: Join Table or Not?

                  Thanks so much for your advice! I've been on holiday but now back and building the solution.