Can I track changes within a record in a report?

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

I have several fields :

  • [Estimated completion date]
  • [Change requested by] (dropdown with 1. Internal 2. Customer)
  • [Total $ per record]

I would like to capture in a report: 

  1. the number of records which [Estimated completion date] has changed for each day.
  2. grouped by [Change requested by] field 
  3. sum of [Total $ per record]

Is there a way I can capture the daily changes in a report?

Example:Estimated completion date changes for 4/25/2016:Internal   12  $1,234,567External  2    $   101,234
I know I can create notifications of specific field changes and deliver the $ amounts in an email, but not sure if this can be done via a report...
Photo of Kevin

Kevin

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
There is probably a way to do this. But it will require the use of form rules.  Are you OK with preventing users from using Grid Edit to update those fields?
Photo of Kevin

Kevin

  • 0 Points
I see--- yes. OK with preventing users from using Grid Edit. I can get a log with form rules I guess, but nothing  able to be reported on...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
OK, so you can set up a TEXT field called [Estimated Completion Date Log] 

and 

[Change Requested by log]

Both will be text field set to log chnages.

Then a form rule

when the record is saved and any if these fields have changed, then chnage the valeu in the log field to the value in the field the original field.



The you can use these formuals to parseout the values on those log fields.



Most recent value (formula text field type)

Trim(Right([my update field]),"]"))

Date of most recent update (this needs to be a formula date field)

ToDate(Left(Right([my update field],"["),9))

Who did the most recent update (formula text field type)

Trim(NotLeft(Left(Right([my update field],"["),"]"),9))




Then you can run a report of change made by date, but that will only count the last change made each day, not if there were multiple changes on the same day.

If you needed to log every change I would use a different approach to have the change made in a child tale and then float up the most recent change to the parent as the only way to make that change.



That latter method is what i would recommend for a clean log of the history and foolproof / failproof as I never really like relying on form rules if something is really important to track.