SharePoint: Info Management Policy and running of workflows for reminder notifications

I have a requirement to send out reminders via email a certain # of days prior to two independent dates (call them R1 and R2).  R1 and R2 set in a workflow which is trigger upon list modification.  R1 and R2 can change during the day  - so I can't just do a reminder workflow with a pause until.  R1 and R2 might move up or down in dates.
So set a field called h-earliest-reminder (set via a workflow)  which is the earlier of the two (or blank if they are in the past or blank themselves).
I put in a info management policy to run a workflow called Reminder based on the h-earliest-reminder date  - I also set recurrence of 1 day in the info management policy rule.
(If you don't set recurrence, and it has previously run the workflow - it won't run again I've learned)
Everything works in testing during the day.
The problem is that the Reminder workflow is triggered sometimes when the h-earliest-reminder date is blank by the info M policy!   In another case, the h-earliest-reminder set to 2/9/2018 yet the reminder WF was run on 2/8/2018 (policy should have waited until 2/9/2018 to run it).  In another case, the reminder workflow wasn't triggered at all by the policy where others with the same reminder date were triggered... Why does it not trigger all records with the same date?
Right now, I'm changing the reminder workflow so that it checks to see if it was properly triggered and only send out notifications if it should be running.  I was hoping info management policy would trigger it properly but that doesn't seem to be the case.

Anyone ever set up a reminder WF- which must be based on a reminder date field which can change?
There are about 2000 records in this list - I don't want to run a workflow on every record every night to check on reminders
Another interesting feature: the reminder WF modifies the record sometimes - one feature is that this does NOT trigger another workflow which is set to trigger upon modification.

Environment: SP 2013 on-premises  with SP 2010 workflows ONLY  - no Ninex or other extensions - Full control only (no SCA, no access to central admin, etc)  
I've double checked with the farm admins and the timer jobs seem to be running properly every night - no other developers complaining about problems.
I make sure to reset the policy workflow whenever it is changed (you must refresh the policy or it will run the old workflow version)
The Info policy timer jobs run overnight about 2am, every day.  This cannot be changed.
Keyboard CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter CurtisSharePoint AEDCommented:
Great job of describing your design and approach. I did get lost a little, but that is because of a lack of coffee, not in the way you describe your design.

I have worked with IMP as you have and I discovered limitations. I changed my approach to simply have IMP trigger a workflow daily and all of the logic was in workflow(s). As you describe, the IMP system was not very robust, although it does dependably trigger the daily workflow. But on every item, whether it needs it or not. This has not created a problem because I add a condition at the beginning of the workflow that stops the workflow it is not needed.

I don't have IMP/workflows running on a list with 2000 items however, so that may be a problem depending upon your system. SharePoint will handle that however if the system horse power is there.

Hope that helps some...
Keyboard CowboyAuthor Commented:
Very helpful as always Walter - I thought I was losing my mind.  I'm adjusting the workflow to handle IMP not working properly.  Still stumped on why IMP doesn't run the WF on some records.  I'll get back to you.
Keyboard CowboyAuthor Commented:
Walter - I wanted to make sure I know how to have the workflow run against every item - every night.  Just set it to run on created date with recurrance of 1 day?   Some other technique?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Walter CurtisSharePoint AEDCommented:
Here is how I set it up,

I added a "Date" column named "Refresh Date" to the list and seeded with current date.

I created a stage for the IMP that activates when the time period is [Refresh Date" + 0] (see screenshot below.)

When the stage is triggered the action is set to start a workflow. Recurrence is set to "1 days", (also in screenshot below.)

Also there are two timer jobs that I modified that control when IMP runs. They are: "Information management policy" and "Expiration policy". I set those time jobs to run daily. Be careful with those settings. They are a web app scoped timer job and may break things. (It took a lot of adjusting to get the timing perfect and considering you have 2000 items it will take a lot of patience to get it right, but I am sure you will!)

Here are screenshots of those settings:

Hope that helps...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter CurtisSharePoint AEDCommented:
Forgot this important part: the workflow refreshes the "Refreshed Date" with the current date so the IMP will process it daily.

I haven't tried this, but if the workflow didn't update the column, the IMP may skip starting the action next time around therefore save a workflow. Sounds like it will work.

Good luck...
Keyboard CowboyAuthor Commented:
Thanks for the details and snaps, Walter - big help.  
I decided to take a different approach which makes for simpler and fewer workflows running.  I don't want to trigger a workflow on every element since this list will be over 10,000 items long eventually - with the reminders on/y set on about 50 records at a time.  So I created a separate list - which holds the reminder date and ID from the main list.  Then a WF on the reminder list just pauses until the reminder date  - checks some things and then sends out an email.  The main list workflow deletes/adds reminder list items as needed.  I have two new lists - one for each workflow.  And I can immediately see what reminders are pending.    That allows the reminder notification date to change on the fly as the main WF just deletes and re-adds reminder list items  - which then pause and then send out emails. Let me know if you get the picture.  I have most of this working already - Wish I had thought of this rather than spending a week on trying to get IMP workflow triggering to work properly.
Keyboard CowboyAuthor Commented:
Thanks again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.