Site workflow to send reminder email to Created By based on status of the item

Question

I want to create site workflow to send a reminder email to created by(emails) when Item created is more than 30 days and choice field called "Status" is "open". When someone has more than one items with status open in list then that person get one reminder mail only

Thanks in advance

Answer 1

Aside from the issues of maintaining multiple long-running workflows on a site, each item with an open status would need its own workflow so that when it was updated to any other status it could self-terminate.

To resolve prevent duplicate emails, there would need to be an additional list to track which user received a notification email along with the date/time. This list could be queried by a standard rest API call with $select=Id,Modified&$top=1&$orderby=Modified+desc&$filter=<userIdField>%20eq%20<ID> which would provide the most recent record and based on the date determine if an additional email was warranted. If there is no record of an email sent, then a record can be inserted to indicate that "this" workflow instance will send out an email.

It may be important to run this query a second time before the email is delivered to ensure that a race condition did not occur, but if found, the duplicate record with the newer time-stamp will delete its duplicate and self-terminate.