9 Replies Latest reply on Jun 15, 2017 8:18 PM by philmodjunk

    How many join tables needed?



      How many join tables needed?


           I am setting up a project management database and as part of this, have a People table and a Projects table. In the People table, each person can be any combination of "Client", "Musician", or "Staff" (ocassionally someone fits into more than one category). On the Project layout, I need to be able to attach people from these various categories (I would have a 3-tabbed panel - one for each category - and each tab would have a portal showing either clients, musicians, or staff). I thought the best way to do this would be with 3 different join tables like this:

           Projects ---< TeamClients >--- People

           Projects ---< TeamMusicians >--- People

           Projects ---< TeamStaff >--- People

           That way, a person could be, for example, both staff and musician. That's all fine. But now on the People layout, if I want to show all projects a person was attached to no matter what category they're from and no matter what role they played, having 3 join tables complicates things. It would be better to have just one:

           Projects ---< Team >--- People

           Is there a straightforward way to rectify these conflicting needs?



        • 1. Re: How many join tables needed?

               I would use one join table not three. I'd add a field in the join table for identifying the person's role in the project. If a person has to where two or more "hats" in that project, you can create more than one join table record linking that person to the project, but with a different role specified in each.

               This can give you a single portal on your People and Projects layouts and there are a number of nice summary reports that you can create from layouts based on the join table.

          1 of 1 people found this helpful
          • 2. Re: How many join tables needed?

                 Thanks, Phil. Makes sense.


            • 3. Re: How many join tables needed?



              I have a similar situation exactly regarding people's various roles to a project, and found this thread to answer to my question- should I have multiple join tables or a single with a "Role" field. 


              My question now is: If one of the Roles is "Project Manager" and from the project table/records I want to show who is/are the manager(s) of each project my instinct is to still create an additional join table, but with an additional Global Key field "Project Manager" to match "Role" in the join table. Is this the most elegant solution? 


              The only alternative I can think of is a filtered portal, but I'd rather not having a scrolling portal in multiple matches, and instead be in favor of being able to list and manipulate multiple entries, or access the fields of the Project Manager's record from further away in the database.  Perhaps using a calc field in the rpojects table? I've always thought with multiple related records a calc will simply deal with the first record so I don't know how it could find the records with the correct role.


              I'm fine to create another table occurrence, but between these single use TOs and those used just for creating certain value lists, I feel my relationships diagram and TO list gets bloated and difficult to navigate and I'm not sure if that's normal.  For what seems like a modest solution I am already up to 40 TOs, but I realize there are just so many ways the data relates to itself, and each TO does seem to serve a unique and necessary purpose.


              Many thanks for any and all advice,


              • 4. Re: How many join tables needed?

                There are several easy to retrieve multiple (related) records as a list, even more so in FM 16. The thing we still miss is a way (well, an easy way) to aggregate values conditionally.




                Project --< Team >-- People


                create a calculation field (or an expression in the Data Viewer if you've FM Advanced) with


                Substitute ( List ( People::name ) ; Char(13) ; ", " )


                You could use


                ExecuteSQL ( "

                  SELECT p.name

                  FROM Team t

                  JOIN People p ON t.id_person = p.id

                  WHERE role = ?

                  " ; "" ; "" ; "Project Manager"



                If you want to avoid another TO and don't feel like SQL (or other, more involved methods, not outlined here), try this:


                In Team, create a calc field, type text, as

                If ( role = "Project Manager" ; People::name )


                and in Project, use

                Substitute ( List ( Team::cNameIfManager ) ; Char(13) ; ", " )

                • 5. Re: How many join tables needed?

                  Why not use a filtered portal? It's simple to set up and I don't follow your reasons against using a filtered portal.


                  It it is also possible to set up multiple relationships, each to a different occurrence of the same join table, but that is still just one join table.

                  • 6. Re: How many join tables needed?

                    Primarily for a list view of projects as an overview to list project managers, to me it's awkward to have filtered portals in list views.  In the detailed view I indeed have used filtered portals for certain roles and then a catch all for all others.

                    • 7. Re: How many join tables needed?

                      Make it a list view based on the join table. You can sort to group your records both by project and then by by type. A sub summary part can show data from the project and fields from People can be included in the body.

                      • 8. Re: How many join tables needed?

                        True and simple, but creates a fair amount of white space adding lines for each manager no?  Essentially each project would then take two lines, the summary line of project fields and the people line with possible additional people lines, correct? My "Projects" list is 1509 records so any method that keeps each entry as a single entry is ideal for me.  For me, while possibly taxing computationally, the use of calc fields as suggested by erolst results in the clean density I'm seeking.

                        • 9. Re: How many join tables needed?

                          Yes, but do you list all 1509 records at once? You certainly don't need to.