6 Replies Latest reply on Nov 17, 2016 11:12 AM by philmodjunk

    Best way to handle sub-projects


      I am an intermediate user and nonprofessional developer.  I’m looking for some resources on methods to handle sub-projects.


      Imagine a FileMaker application that has two tables: Projects and Tasks.  Each Project can have many Tasks (One to Many).   Certain Projects are complicated enough that I would like to be able to break them down into sub-projects.  (Projects are connected to Contacts and Document tables as well, but let’s leave them out for the sake of simplicity.)


      The user interface would allow the user to choose a Project, and the display would show a list of sub-projects on the left and a list of Tasks on the right.  Yes, that means using some form of Master-Detail but let's leave that complication out of the discussion for the moment.


      There is nothing in the FileMaker manual about this, and the Missing Manual doesn’t mention sub-projects either.  There is very little on the web about it but I found one discussion in FMForums that advised to put sub-projects in the Projects table and do a self-join. I see the advantage is that this makes displaying Tasks easier (one TO of Projects, one TO of Tasks).  On the other hand, the Projects table will be be cluttered with sub-projects although I don’t know if that matters much. 


      What I’m looking for are any articles or resources that anyone might suggest that would show best practices, methods, and the pros/cons.  




        • 1. Re: Best way to handle sub-projects
          Johan Hedman

          Then I create a extra Field in my Project table that I call FatherProjectID. I use a Value List for all Project and that Value List uses ProjectID and ProjectName. With this new Field I then create a relationship on the same table from FatherProjectID to ProjectID. This will make it possible to related project to each other and you can even have sub-sub-project if you like

          • 2. Re: Best way to handle sub-projects

            Using the same table for projects and sub-projects makes sense if you will make projects that don't have any subprojects.


            When researching this, don't limit yourself to "projects". There are a lot of examples that deal with entities and subentities that aren't labeled as projects but which are valid examples of this problem.



            Manufactured items that have bills of materials (BOMs) that are in turn manufactured items with their own BOMs.


            Invoices with line items that are "kits" that are made up of line items that can be ordered individually.

            1 of 1 people found this helpful
            • 3. Re: Best way to handle sub-projects



              Good point.  What words would I use to search for this concept?

              • 4. Re: Best way to handle sub-projects

                Maybe others can chime in with some good examples?


                In any case, you can use some of the terms from my last post such as bill of materials or Invoice Kits. But I see where this won't be the easiest search in the world. I was more thinking of what to keep an eye open for when browsing some of the available training materials.

                • 5. Re: Best way to handle sub-projects

                  Assemblies and sub-assemblies is another set of terms to research (such as for Bill-of-Materials). There are examples out there of building a hamburger out of components. Have it your way and there can be several different assemblies!


                  • 6. Re: Best way to handle sub-projects

                    But to discuss projects and sub projects here. The advantage to using the same table is that you can link other records to either a project or a sub project. You can essentially look at a sub project and treat it as a project when needed. Tasks, contacts and documents can be linked to a sub project instead of the "parent project" at your option if that is something useful. It's a bit complicated, but you can even produce master lists of these related items if you need to see all of them from the perspective of a parent project. So that's just a discussion of the pros and cons of this approach.


                    Your relationship can look like this:




                    Projects::__pkProjectID = Projects|Sub::_fkParentProjectID


                    Depending on what you need, occurrences of tasks, documents and contacts can be linked to either or both occurrences of projects.


                    And if you need a list of just "parent" projects, you can do a find,  executeSQL query, or filter a portal for only those Projects records where _fkParentProjectID is empty. That might address your "clutter" concerns.


                    Come to think of it, there are parallels between this and a current project that I am working on. I'm setting up a table of records where each record is a "form letter" or "standard email" where data from my tables are inserted into the text stored in these records. (This produces "form letter" type messages that can be edited and managed by users rather than a FileMaker developer.)


                    I'm now working out a "version control" methodology where each record can have a "status" of either "draft", "current" or "archived". For a given message, there will only be one draft and one current record, but possibly several "archived" records. Just like your projects and sub projects, I'm going to need to be able to link the draft and archived records to their corresponding "current" record--the one that the system currently uses to produce the message. And my relationships are going to be similar to what I have just outlined here as the "draft" and "archived" records will have an FK link back to the PK of the "current" record.

                    1 of 1 people found this helpful