Forum Discussion

LeoMatute1's avatar
LeoMatute1
Qrew Trainee
18 days ago

Calculate total days with a condition...

I'll try my best to explain...

I have a dropdown field [Status] with 2 options. 
 - For now we'll label them "Option 1", and "Option 2".

I have a date field [O1_DATE] that puts in the "current date" when Option 1 is selected via dynamic rule.

When Option 1 is selected, a numeric formula field [FORMULA1] calculates [today] - [O1_DATE] (using ToDays and ToDate), and this gives me a total number of days Option 1 has been selected.

so far no problem...

Now lets say I change [Status] to Option 2 and 10 days later I switch back [Status] to "Option 1" for 20 more days. How do I get it to only calculate the days that "Option 1" was selected?

In a nutshell, I just need to know the total time a record had the status of "Option 1".

Thank you and I look forward to your suggestions.

 

LJM

 

architecture
formulas

3 Replies

Sort By
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    18 days ago

    I think I would use a child table and a pipeline. The child table would have two fields: [Date Option 1 Was Selected] and [Date Option 1 Was De-selected].

    Pipeline: When the record is changed and Option 1 is Selected, create a new child (related to the parent) and set [Date Option 1 Was Selected] to Today().

    When [Status] is changed and prev.Status = Option 1, then search for the child that where Date Option 1 was selected is NOT blank, and in that record set [Date Option 1 Was De-selected] to Today().

    Now you have a child table of a list of records of every date Option 1 was selected, then deselected. On each child record, you calculate the duration (Date Option 1 was DeSelected - Date Option 1 was selected).  If the deselected date is blank, then you use Today() for your duration calculation.

    Then use a summary field of the duration to get your total time Option 1 has been selected.

    • LeoMatute1's avatar
      LeoMatute1
      Qrew Trainee
      18 days ago

      That's a good idea. Creating a child table opens a lot more possibilities also.

      I was hoping for a formula to keep it all in the existing table, but your suggestion is definitely an option.

      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        18 days ago

        The trick is somehow you need to keep track of each date that it is changed. If you can guarantee it is only switched a max number of times (say 2 or 3 times) you could keep it in the table by simply making extra fields. [First Time Option 1 Selected Date], [Second Time Option 1 Selected Date], [First Time Option 1 Deselected Date], etc).

        Then you can fill these dates in with dynamic form rules, webhooks, or pipelines. Then get your durations that way. 

        The child table allows for unlimited switching. But somehow, you need to keep logging dates. 

        It might be possible to log the date switches in logged multiple choice field. So say you had a logged multiple choice field with the choices 'Option 1 Selected' or 'Option 1 Deselected'. Use dynamic form rules to keep selecting an option which will log the date. Then you would need some creative formula to extract all the dates from the logged field and do all the math. It should be possible, but I like the child table better.