Skip formula evaluation when a particular field is changed?

Question

I need some help with below requirement.

We have an existing formula field(Text) - InactiveTime__c on standard object which calculates the duration the record was inactive based on lastmodifieddate field. When the record is updated this time will be set 0 mins. But when a particular field - Fieldabc__c is changed we shouldn't reset the time(DO NOTHING). I tried this by creating two fields - checkbox(isFieldABCChanged__c), Text(Inactivetimehidden__c) and a process builder. In process builder, when Fieldabc__c is changed I'm setting isFieldABCChanged__c to true and Inactivetimehidden__c with PRIORVALUE(InactiveTime__c). when Fieldabc__c is not changed I'm setting isFieldABCChanged__c to false.

Now, in the formula field I added a conditional statement as

IF(isFieldABCChanged__c, Inactivetimehidden__c, Existing time evaluation logic)

When Fieldabc__c is modified, formula field will be updated with the value from Inactivetimehidden__c but the time doesn't keep counting because formula evaluation is in realtime and as isFieldABCChanged__c will always be true until some other field is updated. Is there any better approach to solve this issue ?

Answer 1

Ok I got you. So you need to approach the issue from another side, because of that rule that when Fieldabc__c this is not counted as object update.

1. Make a new date time field called LastDateModifiedSpecial__c

2. Make a process that is triggered when the object is created or edited. in this process use the formula ISCHANGED(field) in a decision element to see if your special field is updated or not.

3. If it is not updated just copy the standard field LastUpdated date time in your new custom field. If the special field is updated do not change the value in your special field.

4. Then use this specialLastDate field to calculate the InactiveTime__c in a formula field of the object.

5. Then you would need to trigger and update on all records so that you trigger this new procces and so your ne field is populated for old records (you can do that using a scheduled flow or a dataloader import updating a dummy check box field on the object to trigger the update and the initial population of your new LastDateModifiedSpecial__c)

Please tell me if you got my idea and if this solved your issue.

Answer 2

I think it would be better if you go with a auto launched FLOW and implement all logic there ate one place. Depending on how fresh you want your data set the flow to run every day, or make a few flows that run different times (auto lunched flow can run only one per day).

If you want more frequent calculation of this time you would need scheduled APEX with probably batching. Please give me how often would you like to calculate this time field so I can give you more detailed answer.