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!
If you sort the jobs by Status, so you have:
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.
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:
Just revise this formula as necessary should you add more teams.
Is this a possible solution for you?