I have created a custom object "Assigned tasks" that works like tasks such that it has due date, subject, priority e.t.c. This custom object "Assigned tasks" is a child object to case object. Case object has a date/time field called "Event start date". I want the system to keep checking the due date of the assigned task and the value in Event start date on the case level. when there are only 45 days left in the event start date and status of task is pending, it should send an email alert/reminder to the assignee of the task that they have to complete the task.
I created a formula field(Checkbox) on the assigned task object:
IF(AND(OR(TEXT(Status__c) ='Pending', TEXT(Status__c) = 'Waiting on Someone'), DATEVALUE(Case__r.Event_Start_Date__c -45) = TODAY()),true, false)
Then in process builder , I am trying to check if the formula above/checkbox is true, it sends an email alert.
The issue is that the checkbox does get selected 45 days prior to event start date but it does not trigger the process builder to send an email alert.Need help.