Forum Discussion

CassandraFrien1's avatar
CassandraFrien1
Qrew Member
2 years ago

Calculate the time a field held a certain value

I am creating a project management platform, and am hoping the community might have some suggestions on how to record how long a certain field held a value. For instance, each project will have particular stages that it will go through, like: Initialization, Planning, Engineering, etc. I would like to be able to record how long the Stage field was in each of these drop down selections. So, how long it was in Initialization before it was changed, then how long it was in Planning before it changed, then how long it was in Engineering before it changed.

Does anyone have a step-by-step on how to do this? I'm still fairly new and appreciate any help you can provide!



------------------------------
Cassandra Friend
------------------------------

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago

    This is an interesting question.  It's a bit advanced as to how to do this.

    My suggestion is to create a child table to your Projects table called perhaps "Project Status History".  One Project has many Project Status History records.  

    Create fields for Current Status and Next Status and a date/time field for the Status End Date / Time.

    Now the trick will be how to automatically populate this table using a Pipeline.

    The first step of the pipeline would be easy. It would trigger "On New Event" when a Project is added or modified and the status changes. The first step of the pipeline would be to add a child status history record and populating the Current Status field.

    Then it would search the status history records filtered for Related Project is equal to the Project [Record ID#] in the Trigger record and also filter where the Record ID is not the Record ID of the Trigger record and and the the Status End Date / Time is empty and in a For Each loop it would populate the current time into the Status End Date / Time and it would populate the Next Status field with the current Status of the project.

    If this is just a casual request and you are new to QuickBase then it's probably too many jumps to go through. But if it is a real need that your company has and you have a small budget to work with feel free to contact me directly by the email line in my signature and we could get on a zoom call and get this working.  



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Commander
    2 years ago

    Cassandra,

    I second Mark's advice.  Here is a picture because the visual always helps me.

    You need three tables and Pipeline.

    When someone adds a Project Status Change to a Project, the Pipeline will fill in the Stop date and time of the previous Project Status Change field as he described.

    Elapsed Time is a Formula Duration field:    Stop -Start

    A nice benefit to this is you will also see who put in the Project Status Change.  I like to make people put a note in there as well that describes why it went from Engineering to Production or what ever your various Project Status values are.



    ------------------------------
    Don Larson
    ------------------------------