+ Start a Discussion
Jeff TalbotJeff Talbot 

Best way to get sales summary data into a Contact report?

1) Opportunity has two custom fields - "Sale Brand" and "Sale Channel". Sale Brand is one of three brand names. Sale Channel is Retail or Web.


2) Opportunity has a custom lookup relationship to Contact.


Data Example:

TestContact has 4 Opportunities -  a Web sale from Store1, a Web sale from Store2, a Retail sale from Store2 and a Retail sale from Store3.


I need a report like this:


ContactCount of  Store1 SalesCount of  Store2 SalesCount of  Store3 SalesCount of  Web SalesCount of  Retail Sales


Currently to accomplish this, I export reports of all Contacts and all Opportunites, join the tables in MS-Access, and use SQL queries to produce the desired totals. Then I upload the results into custom fields on the Contact record. This gives our managers the data they need in one simple Contact report. But of course, the data is only as current as the last time I ran through the process. Not good when I can only get to this every other week!


So I need to fully automate the population of this sales summary data into custom Contact fields. I am considering ways to accomplish this.


It seems that I could get the desired results using Apex Triggers. However, as a novice programmer at best, I'm concerned about my ability to write a trigger with the level of complexity that would be needed (as the Triggers would need to fire different updates for different transactions - stage changes, brand assignment changes, customer changes, etc.)


Alternatively, I am considering setting up an an automated datamart type process to run each night. I would use a database tool that we have (Boomi) to query all Contacts and Opps in Salesforce and write them to files on a local server. Then I'd use MS-Access to run the queries and write results to another file. Boomi would then pickup that final file and push the results back to SF Contact records.


Is there an easier option that I should consider? If you have any other ideas you think I should consider, or any thoughts on my ideas, please PLEASE reply. I don't want to take an ineffecient approach with this reporting initiative. Thank you!