0

We have a custom object which would be used for reporting.

This custom object has a Sales Rep lookup field attached, so each object will have a link to a Sales Rep.

We would like to collect the SUM of all the won opportunities by the Sales Rep.

I tried adding a Formula field to the custom object, however, I'm not able to access Opportunity data from here.

Any chance this is possible?

Improve this question
1

This object model is not going to work especially well for you. You cannot SUM() record values unless they are related via a Master-Detail relationship (using a Roll-up Summary Field), and you do not have that here. You can't create that relationship here either, because Opportunity is a standard object and cannot be made the detail side of a custom Master-Detail relationship.

I'm assuming that your reporting needs are more complex than a native Salesforce report can handle, but before you embark on development, I'd encourage trying to fit this summarizing into a Summary Report - it'll save you a lot of work if that native functionality can get it done.

The way to actually do rollups with no custom development is to go install Declarative Lookup Rollup Summaries. You can use that free, open source tool to build a rollup across a lookup relationship, such as between your Opportunity and its associated Sales Rep (whether that's a User or a custom object). Note that DLRS doesn't roll up into separate records: it stores the rollup results on one of the objects that's part of the relationship, helping to make it possible to report directly on those objects rather than create new records just for reporting processes.

Hence, you can total Opportunity values to fields on the Sales Rep and report directly on the Sales Rep, with no need for additional reporting-only objects.

If you need totaling at a monthly level, you can use date literals in your DLRS rollup filters (like CloseDate = THIS_MONTH) and schedule the rollup to run on the 1st of each month to refresh all of your data. The rollup can also run in real time, so that throughout the month you have live data.

Improve this answer
8
  • I see! Thank you for your comment David. So basically, I would be able to use DLRS to collect all the necessary data into a field on my custom object? And it's done via a schedule? – Laureant Nov 26 '18 at 9:38
  • @Laureant DLRS can roll up across any defined relationship, but not to an arbitrary unrelated object. So you could roll up to a field on your User record, and refresh it in real time and on a schedule (like "current month"), but you could not roll up to a new, unrelated custom object. For that, you would need to do custom development. – David Reed♦ Nov 26 '18 at 14:00
  • I see. Thank you David Reed, this looks like a great option to try! I will try to install it in a Sandbox, to test the feature. I'm really shocked that we were not able to create a report without third party things. – Laureant Nov 27 '18 at 14:16
  • Hi David! After playing around with DLRS, I came to to conclusion that it's a great tool, HOWEVER, I have a small little problem: as you mentioned above, there's no need for a separate third custom object to store the rollup information, however, what if that's needed in my case? I want to use a custom "TARGET" object, where the monthly target is specified for a user, but the 'Actual' and 'Funnel' amounts are rolled up from the 'Order' and 'Opportunity' objects. Is it possible? – Laureant Nov 29 '18 at 9:52
  • Also, this 'TARGET' object will have the time period where the data should be taken from. – Laureant Nov 29 '18 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.