Hi - I have timesheet history table which stores hours, by staff position (eg: Coordinator, Manager, etc.) againts Projects. Each record also stores the Project ID along with other information. There are multiple records for each project.

My problem is that I want to have a portal in my Project table which shows the position and total hours from the history file (I think through a combined Project ID_Position ID relationship).

I can get the subtotals for hours to work but how do I limit the # of times a Position appears in a portal. The Coordinator may have 10 records with a total of 50 hours recorded. I want only one line to appear in the portal for each staff position with the total recorded against that position.

Right now using the combined relationship of Project ID and Position ID, I show one line in the portal for every Coordinator record (although the total hours for the coordinator shows correctly).

The system works fine using a bunch of calculation fields but it's cumbersome and doesn't lend itself well to adding in new positions in the company.

Again - any help is appreciated.

thanks

Debra

Hi Debra,

On the history table, you can create a summary field TotalWorkingHours that totals the number of hours, and then another calculation field HoursPerPerson that calculates the number of working hours per person; use the fuction GetSummary, with TotalWorkingHours as the summary field, and StaffID as the break field. Remember that you have to sort by StaffID for it to work. If you put it in a portal, you have to sort the portal by StaffID as well.

I hope that is what you are asking for.

Best Regards,

Alejandro

Edited March 6, 200619 yr by Guest

Hi Alejandro - thanks for responding to the question. In the system I built for my current agency that was exactly the solution I used. And it worked great, but when our history file got to over 40,000 records it was SOOO slow. So I was trying to come up with another alternative. But I think you may have the right answer on this particular application - it's for a very small agency so total history records would probably stay fairly small. I'll think I'll go that route.

Thanks

Debra