I need a report to summarize a totals columns

  • 1
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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 report and defining a calculated column, but the calculations have not turned out. I can drill down by employee by quarter to see my total budget and actual hours and manually calculate it but, I would like to have a report to show this.
Photo of nathaniel hammersmith

Posted 3 months ago

  • 1
  • 1
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 
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.
Wow, That will Work! Thank you. I have been trying to get this solution right for some time now. My company will be happy. Cheers!
Ok, Everything you are saying makes good sense. I created a summary report in the details table called "Emp#-yyyy-q" as a text formula. I summarized total Employee IDs and grouped equal Emp#-yyyy-q. This gave me my records with like employees, year, quarter. That worked awesome. I then created a new table called Quartly summaries and created a text field call "Emp#-yyyy-q" set it as key field. Copied records from summary report in details table just fine.
    When I go to create the relationship between the summary and details table(1:M respectively), I set the reference field as the formula field(emp#-yyyy-q) in the details table, no problem. From there I can see all my details in my summary report but, when I go to summarize data, I get the Error:

"We are sorry -- at this time, you cannot create a summary field using a relationship where the reference field refers to a lookup field."

If I set my reference field to something else, I get no values into my summaries fields for actual and budget hours. Again, everything you are saying makes sense but, the correct way of doing this is eluding me.
Hmmm so there is a limitation of Quick base that the reference field of a relationship (the field on the right aside of the relationship) may not be derived by a lookup field.  It's a technical limitation which is well known to the product engineers, but not easily solved without hurting Performance.

Which of those fields (employee #, YYYY or Q ) is a lookup field? .... And then we will need to see how to get around that by either finding a field to use to represent the EE which is not a lookup field, or else there is a way to copy the lookup field into a so called "Scalar" field (data entry) field using a saved table to tabla import and an Automation to run each day.

Employee# essentially comes from an Employees Table
Year and Quarter Closed Come from a Jobs Report Table
So they are all Lookup fields. I can see how this can severely inhibit performance.

I have no Lookup fields that can be used to relate the tables. The only fields given to lookup are those created by Quick Base when creating a new table.
Isn't combining the ID, Year, and Qtr a so called Scalar field? Can I copy our "emp#-yyyy-q" into the details table after a record is entered using an automation and then use it that way? Essentially trying to take the lookup fields out of this field making it unique to the details table? Does that make sense?

If you want to go straight to plan B, yes, we can use a a Form Rule to save the field 
[emp#-yyyy-q by formula] field to the field

[emp#-yyyy-q] which is a text field.

Then that can be backed up with an Automation to run when the record is saved and somehow the Form rule fails or else the edits were done in Grid Edit, and that can be backed up with a daily Automation to run a saved table to table import to detect any records where the formula field is not equal to the text version and copy the record into themselves by firing the saved table to tabla import.

The issue with relying n the Automation, can be a performance hit to the app.  If you have a large # of record, I have found that the the Automation to have a saved record trigger an update of itself is not so efficient as basically the system needs to scan all records looking for the correct [Record ID#] to edit.  For many apps performance is not a concern, but I did get burned on this once in an app design and while there was not a better way to do this, I did realize that the user experience with an app that was already performance challenged was made worse by that kind of Automation.
Plan B makes sense. If I don't want to take the performance hit; even though on my apps scale it would be minimal, how would I accomplish plan A? Everything from Emp name, date complete, job, work order, is a lookup field aside from actual hours and the Quickbase fields. Using record ID somehow might work.
Plan B makes sense. If I don't want to take the performance hit; even though on my apps scale it would be minimal, how would I accomplish plan A? Everything from Emp name, date complete, job, work order, is a lookup field aside from actual hours and the Quickbase fields. Using record ID somehow might work.
No, if they are all lookup fields, then you need to use plan B.  Plan B is to make a scalar field and mirror it.  I suggest included in your Plan B is that nightly Automation to update any records where the two fields do not match.  not that a job can have its source lookup fields change  (e.g. a Close Date field on a Job may be updated)  and that would only be caught by the overnight Automation.

Alright I will get this underway. Thank you for all your help!
One last Question, is a form rule more efficient than an automation in terms of performance. It seems that I can copy the formula field to text field via automation when record is created or modified or via a dynamic form rule. Which is better developing applications?
A form rule take zero resources as it runs in the browser and it has the advantage of only being smart enough to act on the record on your screen in human edit mode.

The Automation has the "disadvantage" of being more powerful and having to run after the record is saved, so then it has to scan all the records in your table to locate the one to be edited.

But you can use both, as what will happen is that you will only trigger the Automation when the record is added or modified and those two fields don't match.  99% of the time they will match (because of the form rule) and it will not fire.  (but in Grid Edit mode, Forms rules do not apply, so then the Automation will kick in).

Be sure on the form rule to un-check that box at the bottom to make sure it always fires when the fields do not match.