I'm having a requirement where I need to send an email at a particular time or at the end of the day for all the Accounts whose status is changed during the day. Here status is a picklist value. How can I query to get all the accounts whose status is changed
Its not possible to compare new and old value of field inside a query.
An option would be to create a Boolean field in the object and using Workflows sets its value based on picklist field's.
In Scheduler, query all the records where the field's value is set and after sending email, reset the field value.
One way of achieving this requirement is to create a object to record all the instances of pick list values changed in a trigger and then send email to them at end of day by querying for records in that object for that particular day.
- create an object which has fields like accountId,previousPicklistvalue, new piclistValue, booleanFlag to tell if email is already sent or not.
- After trigger to record whenever interested field is updated and then insert a new row in that object with the accountId and other values.
- A schedule job which gives you a list of all accounts,which in turn gets you email id's and then send emails.