summarizing data in a portal timesheet database

[John Kostenbader]

Consider a standard timesheet application with a parent "timesheet" table and a related "timesheet details" table connected with a one-to-many relationship. On a layout based on the timesheet table I have a portal that outlines the details for a two week pay period. One of the elements on the timesheet details is what type of entry is it (i.e. regular hours, support hours, etc.). I would like to be able to summarize the hours of each of those entries on the timesheet layout. I have been able to create a calculated field that does sum the total hours well and I thought if I made the calculation something like this:

If ( Timesheet_Details::Type = "Regular" ; Sum ( Timesheet_Details::Hours_With_Lunch ))

It would only summarize only those timesheet details that had "Regular" in the "Type" field. Alas it does not work. Not sure how to approach this. I tried a summary calculation but it does not allow for summarizing specific data (seems all or nothing). 

Would appreciate some direction on this. Ideally I'd have three calculations on the timesheet layout for "Regular" hours, "Support" hours (which are calculated differently and "WFH" hours. 

 

[comment]

6 minutes ago, John Kostenbader said:

I would like to be able to summarize the hours of each of those entries on the timesheet layout.

Is this for display only, or do you intend to use this further in any way?

[John Kostenbader]

Really good question. I suspect I will want to further manipulate it. For instance I will probably want to take the summary of "support" time and add a calculation to indicate this is at time and a half

John

[Fitch]

Are you familiar with SQL at all? You could use a calculation something like:

ExecuteSQL(

"SELECT SUM(Hours_With_Lunch)
FROM Timesheet_Details
WHERE Type = ?
AND PeriodStart >= ?
AND PeriodStart <= ?"

; "" ; "" ; "Regular" ; selectedStartDate ; selectedEndDate )

 

[comment]

59 minutes ago, John Kostenbader said:

I will probably want to take the summary of "support" time and add a calculation to indicate this is at time and a half

I think such calculation would be better performed in the "details" table. As an aside, you should consider if the evaluation of "Support" as 1.5 will ever change in the future.

For display, I would suggest you define a summary field in the "details' table as total of hours, and place it on the layout of the parent table within a one-row portal, filtered to show only "regular" records from the child table. Duplicate twice, and change the portals filtering to the other types.

[John Kostenbader]

Wow...both excellent suggestions. I'm not really great with the ExecuteSQL function yet (I'm practicing). I find the portal approach very fascinating too and I'll probably try that first (very creative). Thank you both for your suggestions

John

The portal suggestion works excellent. I'm curious though....what would be the procedure to attached to the "Type" field to refresh the summary fields in the portals. I calculates well if I leave the layout and return but I'd like to add a script trigger to the type field to update the calculations if it changes

[comment]

8 hours ago, John Kostenbader said:

what would be the procedure to attached to the "Type" field to refresh the summary fields in the portals.

The summary fields should update when you commit the record.