13 Replies Latest reply on May 5, 2015 12:22 PM by user14967

    Assigning employees to a job

    user14967

      I could use your help thinking through how to assign employees to a job.

       

      There are two required roles for each job: an Account Director (AD) and a Project Manager (PM). The rest of the team is variable, and could include a designer, an editor, a copywriter, etc.


      The way we've dealt with this so far is to just have two required fields in the Jobs database: AD and PM. The rest of the team is assigned in a portal to a related table called "team." Each row of the portal has two fields: role and name. Pretty standard stuff.


      But it bugs me to have it split out this way. It makes searching a little difficult, and I can see where it could be a problem down the road if we wanted to create a dashboard for our employees.

       

      So, to those of you with more experience, I ask: what am I trying to do here? Can you give me some direction?

       

      Thanks.




        • 1. Re: Assigning employees to a job
          mikebeargie

          You're trying to transition to a more normal form, where your two related records of AD and PM are required.

           

          What I would do is put the two roles into the same table as the portal, and use a trigger (like OnRecordCommit) to check for the two existing values. Then block the user from continuing until the two related records that are required are added.

           

          You could even sort the portal based on a value list where AD and PM are at the top, so your portal always shows those two roles at the top of the list.

           

          Lastly, write a quick loop script that pivots the AD and PM from being in the parent record, into new child records.

          • 2. Re: Assigning employees to a job
            erolst

            Mike Beargie wrote:

            use a trigger (like OnRecordCommit) to check for the two existing values. Then block the user from continuing until the two related records that are required are added.

             

            Another approach is to script the creation of new jobs, so you can create the two team records for AD and PM automatically.

             

            Same for deletion of team records to ensure that these two records cannot be deleted.

            Mike Beargie wrote:

            Lastly, write a quick loop script that pivots the AD and PM from being in the parent record, into new child records.

            Or simply find all jobs with non-empty AD, import into Team (with job_pk as job_fk); repeat for PM.

            • 3. Re: Assigning employees to a job

              Consider an option of using three tables for this:

              Job...Job ID

              Assignments   Assignment ID Job ID  Employee ID Employee Title  AD, PM, WorkerBee

              Employees    Employee ID

               

              The Assignments table can have the records created automatically, your managers records and xx WorkerBees.

               

              The Emplyees are assigned by inseting the ID numbers so you get a relationship like:

               

              Job<---Assignments<---Employees

               

              The Title field in Assignments solves the problem. You can have a standard script create the assignments for each job and then when the time comes insert the employee IDs. Job Done.

              • 4. Re: Assigning employees to a job
                erolst

                jackrodgers wrote:

                Job<---Assignments<---Employees

                 

                Actually, it would be

                 

                Job --< Assignment >-- Employee

                • 5. Re: Assigning employees to a job

                  Actually it would be more like:

                  Jobs<-->Assignment<-->Employee since FileMaker doesn't do single links.

                   

                  So, I created a simple demo that shows how to like the Jobs, Assignments and Employees.

                   

                  This contains the bare essence of what is needed to create a new job, click a button and automatically create a job assignments list that you can then select the employees for the job slots.

                   

                  I've deliberately made it quite simple for demo purposes. Enjoy.

                  • 6. Re: Assigning employees to a job

                    Here's a demo file that illustrates how to use an inbetween file to linke Jobs : Assignments : Employees and how to create a script to automatically create a new job, create a number of assignments and let you choose the employees for the assignment.

                     

                    Note how it is sized for the iPhone but how it expands to fill what ever screen you use: iPhone, iPad, PC, Mac

                    • 7. Re: Assigning employees to a job
                      erolst

                      Actually this way to illustrate relationships is intended to show ERD cardinality, regardless of what FM does (enforces would be the correct term).

                      • 8. Re: Assigning employees to a job

                        I try to remain within FileMaker's lingo in the help file (not always sucessful) and I've seen a growth of non-FileMaker help file terms creeping into lists.

                         

                        ERD Cardinality would probably not be a common term among the millions of FileMaker users.... and the percentage of FileMaker developers who use it is at a guess, relatively small since the relationship graph is used instead of ERD.

                         

                        So, as a helpful illustration for the questions. here is a graphic which is from the file I supplied which is probably more helpful than this side discussion:

                         

                        Assignments.png

                        • 9. Re: Assigning employees to a job
                          DanielShanahan

                          I suggest also having a table for roles, which serves as a master file for all the roles such as Account Director, Project Manager, etc.  Fields might include:

                           

                          Role

                          Abbrev

                          Duties

                          • 10. Re: Assigning employees to a job
                            erolst

                            DanielShanahan wrote:

                             

                            I suggest also having a table for roles … Fields might include:    Role  Abbrev  Duties

                            Abbrev??

                             

                            But I concur that coding/defining ones business rules (and having specific roles is a business rule) as data, rather than burying them in a value list, is a good idea.

                             

                            Another field might be “required”, which could be read and acted upon by a New Job script to create any required roles.

                            • 11. Re: Assigning employees to a job
                              DanielShanahan

                              erolst wrote:

                              Abbrev??

                              Yeah, I thought it would be fun to abbreviate the word; kind of like an onomatopoeia.

                               

                              Glad you found the humor in it!

                              • 12. Re: Assigning employees to a job
                                erolst

                                jackrodgers wrote:

                                I try to remain within FileMaker's lingo … probably not be a common term … the relationship graph is used instead of ERD

                                FM may have its own lingo, but it wasn't incepted / doesn't operate in a vacuum, and neither should we. So why not try to make your intended audience a bit more familiar with the wider world, and the concepts used therein, and demo some good practices while you're at it (educate them on the sly, so to speak).

                                 

                                And please accept that in doing so, one can still be succinct and provide an appropriate solution to the issue at hand; so kindly refrain from rehashing once more that old story of the forum (or “list“, in your lingo) thread where everyone misses the point except for we-all-know-who …

                                • 13. Re: Assigning employees to a job
                                  user14967

                                  Thanks for the recommendations! I'll play around with it and let you know if it works out.

                                   

                                  ALSO: disabled_jackrodgers, I appreciate your honeybee analogies. I'm a beekeeper on the side, so I got a real kick out of this.