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?

1 Answer 1

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.

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, 2018 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, 2018 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, 2018 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, 2018 at 9:52
  • Also, this 'TARGET' object will have the time period where the data should be taken from.
    – Laureant
    Nov 29, 2018 at 10:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .