Sub-summary, Sort on checkbox fields

[NeutronFlux]

I have a layout that shows a WorkOrders table with fields WorkOrderNo, StartDate, Technician and HoursWorked. I've set it so that there is a sub-summary for sorting by WorkOrderNo and by Technician (Drop-down list of the names of the technicians). There is no problem with sorting by WorkOrderNo. But, when I sort by Technician, I start getting some funny results. For instance, if I sort by "Bob", I might get the following sub-summaries:

Bob

- WO-100001 | Dec 18, 2012 | Bob | 5

- WO-100003 | Dec 21, 2012 | Bob | 2

Bob

- WO-100006 | Dec 31, 2012 | Bob | 4

John

- WO-100002 | Dec 19, 2012 | John | 5

 

The desired result is that only a sub-summary for "Bob" should appear. I haveve discovered the reason this is happening is because Technician is a checkbox field where some records contain more than one technician. For instance, the first set of WorkOrders is tasked only to "Bob", the second set is actually "Bob" and "Mark" and the third set is "John" and "Bob".

 

So my question is if there is any way to get things to show as:

Bob

- WO-100001 | Dec 18, 2012 | Bob | 5

- WO-100003 | Dec 21, 2012 | Bob | 2

- WO-100006 | Dec 31, 2012 | Bob | 4

- WO-100002 | Dec 19, 2012 | Bob | 5

 

If that's too difficult to do, then maybe as:

Bob

- WO-100001 | Dec 18, 2012 | Bob | 5

- WO-100003 | Dec 21, 2012 | Bob | 2

Bob, Mark

- WO-100006 | Dec 31, 2012 | Bob, Mark | 4

John, Bob

- WO-100002 | Dec 19, 2012 | John, Bob | 5

 

Thank you.

[eos]

Your data isn't granular enough. Simple example: if you had three WOs with a total of five different technicians involved, then a summary report should show five sub-summaries, one per technician – but there are only three records: the WOs. If by chance the same technician's name was always the first in the field, you'd get an accurate summary for this person, but the other ones would have no records "left" to summarize. A summary can "subtract" records, but it cannot add any.

 

What you need to get at the desired result is to create an additional table related to WO (preferably by a meaningless internal serial ID, not the WO number!), where you add the individual technicians (even if there is only one); this is also the perfect place to enter individual values for hoursWorked. A simple portal on the WO layout should do nicely.

 

Create your summary layout in that table. Use a script to first find the WOs to report on, then use Go to Related Records in your TechniciansInWO table with the summary layout.