4 Replies Latest reply: May 15, 2014 10:07 AM by Lianne _ RSS

    Automatically assigning users to a job list

    Lianne _



      I have a monarch model which is run for a report that shows me a large list of 'jobs' that need to be worked by a large team.


      I have created various summaries from the data which gives me the exact management information I need but the team manager would like to know if there is a way to auto assign an owners name to each job to save her completing this manually.


      Currently she takes the total number of jobs in each Key Field (status) splits number equally by the number in that team and manually adds the teams names into excel against that number of jobs, which takes her some time! The problem is there are a number of status's and each team who manage these has a different number of people.




      Status A may be managed by 2 members and have 30 lines today

      Status B may be managed by5 members and have 249 lines today

      Status C may be managed by 1 person and have 38 lines today


      I can complete the Status C with a simple lookup but does anyone know of anyway I could work out an equal split of key field A & B and add a assigned name to this? I've worked through a number of formula and am just coming across a stumbling block each time.


      This may be too complex for Monarch Pro but it will be the first thing we haven't been able to find a way to do somehow if it is..!


      Thanks in advance if anyone has a solution up their sleeves!



        • Automatically assigning users to a job list
          Grant Perkins

          Hi Lianne,


          Interesting challenge.


          Do I understand correctly that the team leader wants to auto allocate an individual name form here team pool to each job? Any member of the teram can be allocated to any job?


          As you say Status C looks easy and I assume that it is also easy to allocate the team name to all jobs.


          Lets consider status aA.


          You have member 1 and member 2 (names could be associated off line against team members and then looked up - less maintenance of the core allocation method that way.)


          So one might consider simply allocating even numbered lines to M1 and Odd numbered lines to M2. Would a concept as simple as that work for you?


          I guess, given a contiguous set of 'line numbers' from somewhere, you could to the same with any number of people in the team.


          Does the number of people in the team vary from day to day (in theory) and do you need to allow for this day by day ?


          Do the jobs have to be shared out with some reference to their chronologic age? (Perhaps to provide some sort of guidance to the team members about the order in which they need to work in them.)




          ETA: It occurs to me that you could probably make this a dynamic activity so that, for example, if the Manager could maintain a simple external lookup 'file' somewhere for number of staff available for the day (by Status group) and a list of the names of those staff (if the output is to be personalised rather than Member 1, Member2, etc.) then those values could be part of the process of allocation. The number of available staff is, perhaps, more important than knowing the names since adding a block of names to a grouped list of jobs in Excel is no big deal. It may be less work than maintaining the names for a lookup file of who is available!

            • Automatically assigning users to a job list
              Olly Bond

              Hello Lianne,


              If you sort the jobs by Status, so you have:


              Job#     Status

              1     A

              2     A


              30     A

              31     B

              32     B


              270     B

              271     C

              272     C


              310     C



              then I think you could do this as follows:


              Define calculated fields ModA, ModB (ModC is trivial as only one person is chosen). Mod A could be Mod(Rowno();2) giving a value of either 0 or 1. ModB would be Mod(Rowno();5) giving values from 0 to 4. Now define internal lookup fields based on these called NameA, NameB. Name A might map 0 to Bill and 1 to Sue, for example. Then tidy these up with one field called Name with the formula: if (Status="A"; NameA; if(Status="B"; NameB; "The name of the person who looks after C jobs"))


              This should give you:


              Job#     Status     Name

              1     A     Sue

              2     A     Bill


              30     A     Bill

              31     B     Jerry

              32     B     Mary


              270     B     Henry

              271     C     Charlie

              272     C     Charlie


              310     C     Charlie



              Is that what you need?


              Please note that Rowno() will fall over if you have filters on the model, so best make a clean filtered table export first and work from that.


              Best wishes,



            • Automatically assigning users to a job list
              Data Kruncher

              OK Lianne, let's give this a go. There's likely some additional logic that's applied when the work is assigned manually, but perhaps certain compromises are acceptable in the name of automation.


              Assuming that the records can be assign sequentially (first records go to the first team, etc.), then this might work.


              First up, we need to factor in the total number of records in play. This will require an extra step, and summary with count as the only measure. Name this field RecordCount. It doesn't matter what the down Key field is, but rename the down key to "Key". On the General tab of the summary definition, include the Total Line, and specify the label exactly as TotalStaff. I know that this seems odd, but it'll let you use a single key field for external lookups, just for consistency and convenience.


              Setup a project export to export the summary to RecordCount.xls. Set the export to overwrite existing files. Run the export and save and close the model and project files.


              Now re-open the model and add a new Character calculated field named Key with the formula "TotalStaff".


              Create an external lookup to the RecordCount.xls file using the Key field, importing RecordCount.


              Now create a new Excel file manually named StaffCounts.xls, as follows:

              Key          Status A Count Status B Count    Status C Count    StaffCount

              TotalStaff                2              5                 1             8



              Now create another external lookup, using the Key field to the StaffCounts.xls file. Import each of the Status Counts and the Total Staff fields.


              Now we can calculate/set the Status Groups for each record.


              Create another Character calculated field using:

              if(recno()<recordcount/staffcount*,"Status A",

              if(recno()>=recordcount/staffcount .and. recno()<recordcount/staffcount(+),"Status B",

              "Status C"))[/CODE]


              Just revise this formula as necessary should you add more teams.


              Is this a possible solution for you?