Forum Discussion

KatieKnoshaug's avatar
KatieKnoshaug
Qrew Member
5 years ago

Duration with stop points

We provide a service that has different stages (text-multiple choice).  In Stage 4, we track the time from the starting action (Doc Received Date) to the end point (Updated Scope Received Date) in days.  However, there are exceptions to the simple End Date - Start Date formula. 

Sometimes, the job goes in holding when we are waiting on something from our client.  In extreme cases, it may go in to holding 5 or more times.
Sometimes, we put it in a later stage to bill if the client will not complete the task in a timely manner.  Most of the time, this only happens once or twice.

What is the best way to trigger a counter that starts when the Stage = 4
 -- Pauses when it goes into holding
 -- Restarts when it is no longer in holding
 -- Pauses/Ends when it goes into a later stage
 -- Restarts when it goes back into Stage 4
Ends when it is again moved out of stage 4?

I have a multi-line field that tracks stage and holding movement, but I'm not sure if I'm able to search for the entry and use the timestamp in a formula.

I've considered doing a manual numeric offset field where I can take the Days(Start-End)-Offset, but that requires that the team actually does the manual math.

Thoughts?

------------------------------
Katie Knoshaug
------------------------------
Formulas and functions
  • KatieKnoshaug's avatar
    KatieKnoshaug
    Qrew Member
    5 years ago
    I actually mean Days(End-Start)-Offset.

    ------------------------------
    Katie Knoshaug
    ------------------------------
    • BrianCameron's avatar
      BrianCameron
      Qrew Cadet
      5 years ago

       Katie,

      Here is one approach

      It seems you want to be able to do something with the duration(days) when the service goes ON HOLD to when they come off ON HOLD.  Must be that you do NOT want to include those days in the ultimate calculation of how long it took to complete Stage 4?  If so, then try this...

       

      Create a new Child Relationship to this table. Call it [Stage 4 Pause Records]

      ADD these fields to your table:

      • Date Field – [ON HOLD DATE]
      • Date Field – [OFF HOLD DATE]
      • Formula Date Field - Value is Today()
      • Formula Duration Field – [Running Count] in [Days]
        • (Today() - [ON HOLD DATE])
      • Formula Duration Field – [ON_OFF HOLD] in [Days]

      ([OFF HOLD DATE] - [ON HOLD DATE])

      • Formula Duration Field – [ACTUAL DAYS] in [Days]
        • If IsNull([ON_OFF HOLD], [Running Count], [ON_OFF HOLD]
        • This is the field that you will summarize back to the Parent Record

       

      Leave your current Parent table Duration Formula in place.  I assume it just goes from Stage 4 create trigger to Stage 4 complete trigger.  All the days.

      • ADD these fields to your table:
        • Formula Date Field - Value is Today()
        • Date Field – [ON HOLD DATE]
        • Date Field – [OFF HOLD DATE]
        • Report Link Field – [Record ID#] to [Record ID#] to the same table. (For use in ACTIONS)
        • [Summary Field - # of Actual Days] from new Relationship
        • Formula Duration Field – [STAGE 4 DURATION DAYS] in [Days]
          • [Your Current Stage Duration field] - [Summary Field - # of Actual Days]
        • This gives you the number of Stage 4 days excluding those days ON HOLD.

       

      You will need Parent Record triggers to automatically update the [ON HOLD DATE] and [OFF HOLD DATE]. The update to these fields will be to copy the value in the [Today()] field into these values based upon the triggers you set or currently have in place. 

      • Stage 4 goes "ON HOLD" copy Today() to [ON HOLD DATE]
      • Stage 4 goes "OFF HOLD" copy Today() to [OFF HOLD DATE]

       

      These will trigger the Actions / Automations below

       

      Create Actions or Automations to do the following  with triggers from Parent Record)

      1. When [ON HOLD DATE] changes ADD a record to the [Stage 4 Pause Records]
        1. Import [Record ID#] to [Related Parent]
        2. Import [ON HOLD DATE]

       

      • When [OFF HOLD DATE] changes EDIT the Related [Stage 4 Pause Records] record.
        • Change the value in [Stage 4 Pause Records] [OFF HOLD DATE] field to the value from your [Parent Table] [OFF HOLD DATE] field

       

      Updating the [OFF HOLD DATE] essentially closes this record.  If the Stage 4 process needs to go ON HOLD again, you change the [ON HOLD Date] and new Child Record is created, and the process starts all over again.  The [Summary Field - # of Actual Days] will keep track of all the starts and stops and keep the Formula Duration Field – [STAGE 4 DURATION DAYS] in [Days] always accurate.

       

       

       

       

       



      ------------------------------
      Brian Cameron
      ------------------------------