I need a report to summarize a totals columns
I use an app that tracks employee hours(actual vs budget) by job by year and quarter closed. I want to be able to calculate an efficiency by quarter for each employee. I have tried creating a summary ...
Forum Discussion
The only way to do this will be to create a new table to summarize the total budget and total actuals for each Employee for each quarter.
The key field will need to be in the format like Employee#-YYYY-Q. That table can be initially populated by making a formula field in the details table to calculate that string, and then making a summary report and copying across the records. Then using that same formula field make a relationship to the Summary table, do your summary fields and do your efficiency calculations there.
As for maintaining that summary table going forward, you can make a formula checkbox field with a formula of
true
called [Employee#-YYYY-Q exists]
and look that up own to the details records.
Then set up an Automation to create that Summary record when a detail record is created and the [Employee#-YYYY-Q exists?] lookup field is false.
The key field will need to be in the format like Employee#-YYYY-Q. That table can be initially populated by making a formula field in the details table to calculate that string, and then making a summary report and copying across the records. Then using that same formula field make a relationship to the Summary table, do your summary fields and do your efficiency calculations there.
As for maintaining that summary table going forward, you can make a formula checkbox field with a formula of
true
called [Employee#-YYYY-Q exists]
and look that up own to the details records.
Then set up an Automation to create that Summary record when a detail record is created and the [Employee#-YYYY-Q exists?] lookup field is false.