Sub-Totals For Idiots

[Donald_Newlands]

Hi All,

I’m trying to generate a block of text with totals of hours summed by “task” from a set of linked timesheet records.

As a really simplified example, suppose I have the following timesheet table linked to my invoice record:

Hours Task Link
2 Work InvoiceXX
5 Play InvoiceXX
1 Play InvoiceXX
5 Work InvoiceXX
3 Sleep InvoiceXX

In my InvoiceXX record, I want to generate a list like this with sub-totals by task for the Page Designer block:

Play: 6
Work: 7
Sleep: 3

I’m guessing I need to rollup the unique task name values and use those to conditionally sum my hours, but I don’t see how… (I guess I need more sleep.) Is there an example base here that tackles this?

Thanks!
-Donald

[W_Vann_Hall]

The simplest way is also the least flexible, so it may or may not fit your needs. However, if the only possible activities to be tracked are Play, Work, and Sleep, you’re in luck.

In your timesheet entry record, instead of a single {Hours} field, define [Play Hours}, {Work Hours}, and {Sleep Hours}. That allows you to roll up each category individually without having to do any fancy footwork.

A potentially more elegant approach would be to retain {Hours} and [presumably] the single-select you use to specify how to allocate the hours logged. Define three new fields, named as above, but this time make them formula fields. The formula for {Play Hours}, for instance, would be

IF(
    {Hour Select}='Play',
    {Hours}
   )

with the other two configured similarly. Everything else (the rollup, etc.) remains the same, and in a production environment, feel free to hide the hourly breakout fields.

[Donald_Newlands]

W,
Unfortunately, we’ve got hundreds of tasks in a table that the timesheets link to and are not predefined. We need to get that list of unique tasks in our record set (work, play and sleep) and use it to conditionally sum up the hours.
-Donald