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.
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.