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 d...
Forum Discussion
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.
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.