Skip to main content
Question

Calculate total days with a condition...

Forum|alt.badge.img+1

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

 

This topic has been closed for replies.

Forum|alt.badge.img+20

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.

    Terms & ConditionsAccessibility statement