Record changes in fields - alternatives to "Log entries"

  • 39
  • 1
  • Question
  • Updated 2 weeks ago
  • In Progress
Looking for solution
I have a table, titled Projects, in it one of the fields is titled [Stage]. [Stage] is a Text - Multiple Choice filed with ten different items on the list, users may not add options. 
The "Stage" is manually changed from one list item to the next as the project develops (external to Quickbase) by a user through a form. 

Information I am trying to collect:
  1. Duration from initial stage to today (total project time)
  2. Duration in current stage
  3. Duration of time in each stage, e.g. if in stage 5, how long spent in each prior stage 1, 2, 3, etc.
Attempts
  • Tried "log entries" - the other fields of the project are modified many times during each so the [Stage] becomes very "messy"; was only able to collect total project time, calculation was manual.  Turned off due to "pollution" in field
  • Currently using QB Action to add a new entry to a separate table (Stage Report) every time [Stage] is changed in Project table. Using formula field to calculate duration since entry (Date Created). Now have running record of every [Stage] change, minimal calculations and most projects now have multiple entries. Report organized by stage entry.

Thank you for your time!
Photo of Earl PowerMurphy

Earl PowerMurphy

  • 70 Points
  • curious

Posted 2 weeks ago

  • 39
  • 1
Photo of Jason

Jason

  • 1,022 Points 1k badge 2x thumb
It is very similar to time card entry. I looked at ABC Tracking In/Out Time.You would need 2 tables.

Table 1
Project Stage - Each record is a record for every project stage.
Project A Stage 1
Project A Stage 2 and so on

Table 2
Time Entry -  this is where you are now keeping the log of changes.

Each Project has many Project Stages
Each Project Stage has many Time Entries

Even though you are adding another table and more data it is simplifying the data.


Sorry I don't have the time to develop an example to use but the app Kirk Trachy has called ABC Tracking In/Out Time will put you on the right path I believe.