Jump to content

summarizing data in a portal timesheet database


This topic is 2326 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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. 

 

2016-02-17_17-21-15.jpg

Link to comment
Share on other sites

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 )

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2326 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.