x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 1 reply
  • Subscribers 74 subscribers
  • Views 98 views
  • Users 0 members are here
Browse Forums
Announcements
106
topics
32
replies
General Discussion
6k
topics
18k
replies
Tebasaki

Creating an Email when Anything is Updated on Opportunity

Posted By

CRM 6.2i (soon updating to 7.2b: yay!)

We have multiple people working on an opportunity at once, and it's a great time sink for them to go through hundreds if not thousands of opportunities. So why not create something that Emails them when something in the opportunity has been changed.

I could create a date field, and then check it against something like Oppo_UpdatedDate. When the date/time are different, it emails someone that there's been a change (they may not know what but they can go to the assigned person for clarification) and then the Oppo_UpdatedDate is copied to the date field and stays dormant until someone updates it again.

So here's the problem. What about when there's a new communication (it's a different table) or what about when someone adds a new note? The SQL has to become a JOIN (maybe multiple) and how do I tell it to recognize if there's a new one. (The Oppo_UpdatedDate field is just one field that gets updated, where as the Notes table has a new entry for each note on the opportunity!)

Thanks!

John

  • 0

    You could achieve this pretty easily, but I would suggest against sending an email as it would have the potential to send a load of emails. What I would suggest, for what it is worth, is a new gadget on the dashboard which shows all the oppos that are assigned to a user, the entity which has the latest update/creation, and when it was updated/created. You can then put a drill down to the summary so they can go straight to it to look at what is going on. It also allows them to order by the day etc. to see which was the most recently updated.

    You could use the same logic to create a gadget to show oppos that have not received an update/created record for some time.

    However, once you have your view, you can easily incorporate it into an escalation rule with the triggers you need.

    What you have said about joining all the relevant tables to the oppo is correct. Take a look at the MAX function in SQL:

    www.w3schools.com/.../sql_func_max.asp

    You can then select the MAX updated date/created date compare it to the current day/if it is in the current week then use a case statement to trigger a flag to say it has been updated, and which opportunity:

    msdn.microsoft.com/.../ms181765.aspx

    Using the MAX function allows you to make the relationship 1 to 1 (in the scenario of notes/comms etc.) and the case statement will allow you to identify if it has been updated in any way.

    You could also go a lot deeper with it, reporting which record within the notes table, for example, was updated and by who.

    Hope that helps.

*Community Hub is the new name for Sage City