5 Replies Latest reply on Feb 20, 2012 4:11 PM by philmodjunk

    Assign team with varied responsiblities to project - what's the best way to set up?

    SeattleSquirrel

      Title

      Assign team with varied responsiblities to project - what's the best way to set up?

      Post

      Hi everyone,

      I have a project tracking database set up with Filemaker Pro 11. All projects are a bit different, and I want to figure out the most effective way to assign team members. Currently, the way it's set up, it's very limited - I can only assign 3 people to each project, with one task/responsiblity on each. What I can't wrap my head around is how to do this - I know there has to be a 'best practice' way of setting this up. Does anyone have some insight to share with me?

      Here's a rudimentary example of what I want to achieve:

      My resources are named Susan, Stan, Simon, Stacy, Stella, Sven and Skylar. (no, not for real! Laughing)

      Project 1
      Susan = Role(s): Lead and Design and Production

      Project 2
      Susan = Role(s): Lead
      Stan = Role(s): Copywriting
      Simon = Role(s): Design and Production


      Project 3
      Stan = Role(s): Lead and Copywriting
      Simon = Role(s): Design
      Stella = UX Design and Web Development
      Sven = Graphics Production and Web Development
      Skylar = Graphics Production

      As you can see each person only appears on each project once, but can have many roles. Eech role can appear on multiple people in the same project.

      IMPORTANT: At the end of the year I want to pull up Stella's projects and sort them by her roles on the projects.

      I don't know why I'm making this so complicated. I am distracted by fields vs value lists, check boxes vs pulldown lists. And most importantly, I'm scared to mess up the database current function by messing around with it too much. All insight is very appreciated!!

      Asa Smile

        • 1. Re: Assign team with varied responsiblities to project - what's the best way to set up?
          philmodjunk

          What you have is a "many to many" relationship. A given person can be assigned to multiple projects and a project can have multiple people assigned to it.

          Set up three tables like this:

          Personnel-----<Roles>----Project

          Personnel::PersonnelID = Roles::PersonnelID
          Project::ProjectID = Roles::ProjectID

          Add a "role" field in Roles to identify the role that a person is assigned for that project. If a person has multiple roles for the same project, make one such record for each role in the Roles table, but specify a different role. Alternatively, a check box group formatted field in the Roles table could be used, but this can be a less flexible approach for certain reports. A portal to Roles on the personnel table can list all their assigned roles for all their assigned projects. (A portal filter could limit it to one specified project at a time.) A portal to Roles on the project layout could list all Roles for all personnel assigned to the current project record.

          Here's a demo file on many to many relationships you may want to examine. If you use the one record for each role setup that I am suggesting, DO NOT try to use the methods in this demo file for eliminating duplicate join records as you will need such records to list the multiple roles asigned to one person.

          http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: Assign team with varied responsiblities to project - what's the best way to set up?
            SeattleSquirrel

            Thank you!! I have started to set up the database like you suggested. Someone on another forum suggested this set-up:

            TABLES:

            Project
            __kp_ID

            Role
            __kp_ID
            _kf_ProjectID

            Resource
            __kp_ID

            Join table: Role_Resource
            __kp_ID
            _kf_RoleID
            _kf_ResourceID

            Now one Project can have any number of Roles, and any Resource can fill any number of Roles.

            What's your thoughts on this variation? I'm hoping to do this once, and not having to redo it so I want to determine the 'best practice' - very greatful for your insight!!


            • 3. Re: Assign team with varied responsiblities to project - what's the best way to set up?
              philmodjunk

              The only variation that I see is in the names used. The structure looks identical to me except for the addition of a primary key for the Role table--which is not a bad idea, just not something actually used yet in what's set up here. (I often leave out primary key fields for utility tables where it isn't obvious that I will need a primary key. I can get away with that because adding in a primary key after the fact is a very simple thing to do.)

              • 4. Re: Assign team with varied responsiblities to project - what's the best way to set up?
                SeattleSquirrel

                Thank you for your continued support. You can tell I'm new to this, huh! :D I do have more questions; I read it as if there were 4 tables in this last setup, the 4th table being the 'Roles_Resources' table, and in your scenario I saw 3 tables.  I sketched out my interpretation of each layout like this:

                3 tables

                4 tables

                Where am I loosing the thread? Still not sure how to fully utilize this in the layout. Up till now I've used one giant flat database, but I know it's time to 'grow up'! :)

                • 5. Re: Assign team with varied responsiblities to project - what's the best way to set up?
                  philmodjunk

                  Duh! I'm just not seeing the obvious! can't count today!

                  Can't say off hand that my approach is better or worse than this other suggested approach. The difference is that you have a single join record linking a particular resource to a particular project with a related table used to list all the project roles for that resource.

                  Mine handles that issue by setting up multiple "role" records for the same person when they have more than one role for that project. The layout design will be a bit different for this second approach as you can't put a portal to roles inside a portal to the join table, but that doesn't make it a worse option here, it'd just need to be handled in a different fashion.