Is it possible to run a report that will total up certain fields based on a corresponding date field?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

*REVISED* 

I am looking for some help on creating a Summary Report. 

We have Engineers that detail drawings and release the drawings to our shop for fabrication. We track this by the square footage of material on each drawing. In QuickBase our Engineers record how many square feet they release to the shop by entering the square footage amount into a numeric field and the release date into a corresponding date field. They can have up to 16 different releases for each job and the jobs often span over several months. In addition, each Engineer has multiple jobs going on at once. 

We want to run a Summary Report that will tell us how many square feet each Engineer has released during each month for the past 12-months. 


I have attached a screenshot of my fields.

Thanks!

Photo of Jasmine

Jasmine

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
You say that you want the calculations to be based on dates within a specific range; does that mean that you are expecting the User to somehow select a date range, and then a formula to determine which of the entered values fall inside that date range and then adding up the SF?  Will multiple people be accessing the records simultaneously?  Are you expecting the output to be part of a Summary Report or just a field value in the record?
Photo of Jasmine

Jasmine

  • 0 Points
I would like the output in a Summary Report. Preferably the user would be able to specify a date range when they pull up the report , but it would also be okay if the report showed a summary of total SF Released each month.
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
I do not believe you can do this with your existing tables.

What I would do instead would be to create a "history" table; where each time they change the SF, you create a duplicate record capturing the value of the SF for that record at that point in time.

This essentially means "duplicating" some of your data into a separate table; from which you can then generate summary reports looking for calculations on SF based on a date range, which in this case would be the [Date Created] or ToDate([Date Created]) value.

This would require your users when entering SF amounts to press a customized button which will save the value and create the historical record in the background returning them to the same page they are on.  It would take some training to ensure that they use the right save button; but it would provide you with essentially a history of the changes and allow you to determine for a date range how many SF were changed/entered.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
A suggestion is the scrap your current concept with those 12 fields on each job and transition going forward to have a child record on the job for each release to the shop.  Then  it will be much easier to do your reporting.
.
Photo of Jasmine

Jasmine

  • 0 Points
Thank you, I just finished making a child table for the releases. I was hoping to get a report for the existing data, but at least this way I will have the reporting for the data moving forward.

Thanks again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,780 Points 50k badge 2x thumb
No problem to get a report.  You can have it in 10 minutes by recreating the historical data.  2 minutes to understand this and 8 minutes to do it.

Just make a report where [date 1] is not blank with [Record ID#] the [Date 1] and the [SF 1] as your columns.

Use More .. to Copy these records to another table and map the [Record ID#] into [Related Job] and the other two fields.
That will take 30 seconds.

Edit the report to filter on [date 2] <> blank and change the date and SF columns and do it again,

repeat until all 16 buckets are done.

Then your report will have complete data and you can hide those old fields on the Job record
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
Very smart method, Mark!!  Like it a lot.
Photo of Jasmine

Jasmine

  • 0 Points
Thank you, Mark!  That is exactly what I needed.