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.

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 truecalled [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 TableYear and Quarter Closed Come from a Jobs Report TableSo 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.

I need a report to summarize a totals columns

13 Replies

NathanielHammer
Qrew Cadet
7 years ago
Alright I will get this underway. Thank you for all your help!
NathanielHammer
Qrew Cadet
7 years ago
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?
QuickBaseCoachD
Qrew Captain
7 years ago
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.

Forum Discussion

I need a report to summarize a totals columns

13 Replies

Related Content

Default column widths on embedded reports obscuring data

Report Column labels not saving

Report help

Rearrange Fields/Columns in table

Dashboard Format Question (summarizing)