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.