Workflow to send reminder email to owner and manager when invoice is overdue?

Question

I have a custom object, Sales Invoice, that has a custom formula field to calculate the number of days overdue. I want to send an automated email at XX days overdue to both the account owner and their manager.

My first problem is I don't think the workflow is going. I have it set to send an email alert when Days_Overdue__c = XX (formula is TODAY()-Due_Date__c) criteria is everytime created and edited to meet criteria. I also have two time based workflows that updates a picklist with either 1 or 2, switching off each day (if value =1, update picklist value to 2 in 24 hours and the opposite if value=2). I was thinking this would take care of needing to edit the record for the email workflow to send. If I manually edit a record, the email workflow works properly.

My second problem is the manager's email. I created an email field on the object which has another workflow to update to the contents to Account__r.Owner.Manager.Email when Sales Invoice: Manager's Email NOT EQUAL TO Account__r.Owner.Manager.Email. The manager email does populate when the record is created, but not if the account owner changes or on records that existed before this was created(which I thought would also be updated with the picklist 1/2 workflows).

Am I making this too complicated?

Answer 1

You could try creating a timed based workflow rule which would run 2 days after the Due_Date. Have it update the email field and then send the email. According to this doc here you cannot predict the order the actions will occur but field updates will be executed first:

The order in which individual actions and types of actions are executed is not guaranteed. However, field update actions are executed first, followed by other actions.

Answer 2

You can achieve this by creating time based workflow action on due date. here is the link to understand https://help.salesforce.com/HTViewHelpDoc?id=workflow_time_action_considerations.htm&language=en_US