Forum Discussion

FranciscoOjeda's avatar
FranciscoOjeda
Qrew Trainee
7 months ago

Time Calculation

Hi, 

I am developing an app for project management.

It has a table called Projects and each projects goes throughout different stages in wich some tasks are asigned to different people. 

Our PMs needs to see the days spend in each stage in order to see which stage is the more time consuming. So, I need to count how many days it has been in that stage and when it change the stage to keep that information.

We see the process through a KanBan report, I will like this "Days in stage" field to be seen in the kanban card.

How can I do this? 

Thanks!



------------------------------
Francisco Ojeda
------------------------------

10 Replies

Sort By
  • PrashantMaheshw's avatar
    PrashantMaheshw
    Qrew Captain
    7 months ago
    1. I would have a Status field with values Not Started,In Progress, Completed . Be default all tasks will have status of Not Started , when user changes the above to In Progress , I can capture timestamp via pipeline/actions in a field DateTime called StartDateTime
    2. When the user "completes" the task, I can again capture the timestamp when the task was completed in a DateTimeField FinishedDateTime
    3. A Duration field can then calculation the duration between StartdateTime and FinishedDateTime



    ------------------------------
    Prashant Maheshwari
    ------------------------------
    • FranciscoOjeda's avatar
      FranciscoOjeda
      Qrew Trainee
      7 months ago

      Thank you!

      So with a pipeline that is triggered when the State is changed, I capture the timestamp in another field, how can I do this? It is with Jinja? 

      The tasks are not really important, I want to keep the information of the days that the project has been in each stage. 

      I cant really imagine how can I do this with a Pipeline.

      I thought of doing it with this formula but the problem that I have is that it returns to Zero when the Job record is modified, I need to return to zero only if the STAGE field is modified.

      This is the formula that I tried:

      ToNumber(Day(Today()))-ToNumber(Day(ToDate([Date Modified]))) 

      thanks!



      ------------------------------
      Francisco Ojeda
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        7 months ago

        Ok, so you have a project and it might have 10 possible stages it goes through and you drag Kanban to change the stage.

        I suggest a child table of Stage History records.  One Project has Many Stage Histories.  

        You will have a field for the Stage, Stage Start (This will be a for July mirror of the date created field, and the stage end which will be a date time field.  Then a duration formula field which will use the end time of now if the stage end  is null or Elle's the stage end time.

        Make a Pipeline which will triggers on new event and the stage has changed.  When that happens the first step will be to search all Stage histories children of the Project where the stage end is not .set and then in a For Each Loop, make them be the current time.  You can use a native quickBase field with a formula of Now() to get the current date time, or else use the Jinja expression. {{time.now}}

        Then the pipeline will create a new stage history record, by writing out the value of the Stage.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------