Logging data from a calculate field of a report

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
What I want to do in my head is fairly simple - but theory to practice has been a challenge. I have a report that has a calculated field. It sums a duration to field to come up with X number of days total work outstanding.

I'd like to be able to trend this data over time, by day or week. I couldn't find a native way to do this in Quickbase off hand. I thought if I created a new "trend" table and could create a new record each day/week that automatically pulled in the calculated field I could trend that. I haven't found a way to do this.

Any thoughts on how to accomplish this? Either by my above method or something entirely different. 

Thanks!
Photo of Andrew

Andrew

  • 90 Points 75 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
The best way to get true trend reporting is to do what you mentioned with one extra step that requires some code.

I'm going to use a made up example to help explain.

Imagine you had a project that had some type of duration of x days that you wanted to see a trend report of all the x days from all the y projects combine into one report.

Suppose it had a duration of 5 days.

But the Data is on a project, and the duration might have a start and end date, so you could tie the start date to the "Days/Trends" table.  But then you would get a 5 on the one day, and not a count of 1 on the 5 days, thus your trend is not accurate.

What you need is a joined table of duration days.  
So if the duration on the project it would create 5 child records in this joined table.

Those 5 records are then connected to the days table through a formula relationship.  Thus you get automatic and accurate trend reporting.

The tricky part here is getting the child records to be made, and update appropriately if the 'start' changes.

There are several details that I skimmed over, so if you want more help let me know.