8 Replies Latest reply on Apr 8, 2016 12:59 PM by wraynewton

    Adding Firms to a Project


      I am working on the final portion of a solution for my employer. It will be used by 5-7 workers across two regional offices and hosted by one of the Filemaker Server hosting providers. We are in the construction industry and the goal of the solution is to track projects, quotes, orders, and firms. More specific to the problem I am having, we need to be able to track the following:


      • Which Firms are involved in which projects
      • Which Firms received which quotes
      • Which Firms are involved on which orders


      The part I am struggling with is coming up with an elegant efficient way to associate firms with projects (I say projects here. I believe, however, that the solution will probably also apply to quotes and orders).


      I understand/have tried the following:


      • This is a many-to-many relationship. Any number of firms could be involved with any number of projects. It is not even outside the realm of possibility that a firm would be involved on the same project twice, but in different capacities. For instance, once as the architect and once as the construction manager in the case of a design-build firm.
      • I have a join table called 'projectACTIVITYfirms' that has the following notable fields:
        • __ID (primary key); _projectsID (foreign key, tied to primary key on projects table)
        • _firmsID (foreign key, tied to primary key on firms table)
        • Role (this is a list of possible roles, e.g., Design Architect, Architect of Record, GC, CMaR, etc.)
      • On the Projects data-entry form I have a tab for firms and a portal for viewing records in the table 'projectACTIVITYfirms'.
      • Provided I allow for data entry in this portal, I can develop a variety of ways to populate the fields in the portal such as a drop down list that allows me to select the firms name, assign them a role, and have that firm's pertinent contact info show up.


      What I want to have happen (and what has been requested of me by my boss), however, is something similar to the method in which you add Resources to a task in the Filemaker Starter solution for 'Projects'. Essentially, when you click on 'Add Resources' or 'Edit Resources' (depending on the status of the resource list) you get a pop-window that lists all contacts. You can then select one or more contacts from the list and add them to the task. You can also use this window to search for contacts and create one if you can't find who you're looking for.


      It doesn't have to be this slick, however. The solution could be a popup that gets filled in with the appropriate data and then those entries get added to the activity join table. I do have to be able to either select the firm from a searchable list or add a new firm.


      This is my first solution from scratch and I am in over my head. Any help? This one feature is the only thing keeping me from finishing. Let me know if I need to upload files for further clarity.

        • 1. Re: Adding Firms to a Project

          I'm a little unclear on what the question is.

          Is it that the Projects example file doesn't quite do what you want?

          Or the Projects example file does exactly what you want - but you need to figure out exactly how it  is done?

          • 2. Re: Adding Firms to a Project

            Sorry for the confusion, Bruce.


            The Projects file example does what we want, at least so far as this one feature is concerned. So, yes, I suppose one of my quests is figuring out how it is done. I think I am getting bogged down in some of the scripts.


            Again, I don't necessarily have to do it the way it's done in the Projects example, but it seems to be the appealing approach thus far. If I can figure out how that's done, I believe I can apply it to our project.

            • 3. Re: Adding Firms to a Project

              I will have to look at the sample file to see what it does for this, but I would think a portal based in your join table would do this. You might also consider a table with the Role and a foreign key in the join table for Role. This way you can easily have one firm with many roles related to the project.

              • 4. Re: Adding Firms to a Project

                I think the problem isn't the join table; it's the selector mechanism to find and add records.

                However; this isn't clear. It may be all of the above.

                • 5. Re: Adding Firms to a Project

                  Yes, it is the selector mechanism. Sorry again. Thank you for the help with the vocabulary.


                  Join tables, I have.

                  Portals, I have.

                  It is the selector mechanism I need to learn.

                  • 6. Re: Adding Firms to a Project

                    I took a look at the sample file. It is a pretty slick setup and might be confusing to you.


                    You can do this more simply by having PopUps for the foreign key fields in the portal based on the join table with a value list that displays the second field as the name field only and the primary list field as the firm primary key. This will be your easiest start. How is your value list for firms configured?

                    • 7. Re: Adding Firms to a Project

                      Right. I can do that and have. It works fine. I am only looking into other mechanisms because my boss asked for the functionality to be able to search for a firm or add a new one on the fly without having to leave the project form.


                      He saw the projects example and asked if it was possible to replicate that (or something like it). The aspect he liked the least was using the drop down list to select the firm. He would rather type in the name (or a portion of the name) to find the firm.

                      • 8. Re: Adding Firms to a Project

                        Thanks all for chiming in. I still haven't had the time to really study the Projects starter solution and learn how this works (save that for another day), but I have come up with a solution that I will have to write more about once I understand it all myself.