1

My company's opportunities have custom fields on opportunities with up to 5 different invoice dates/amounts. I would like to create a field on the accounts object that will sum any of the amounts within opportunities that are not lost and the date is within current year and current quarter. The total opportunity amount is what shows as projected right now and we would like to see it broken down by invoice date.

For example, maybe two opportunities are created as follows on one account:

Opportunity 1
     1st Invoice Date 11/1/14     Amount  $10
     2nd Invoice Date 12/1/14    Amount  $10
     3rd Invoice Date 1/1/15       Amount  $10
     4th Invoice Date 2/1/15       Amount  $10
     5th Invoice Date 3/1/15       Amount  $10
Opportunity 2
     1st Invoice Date 11/1/14     Amount  $100
     2nd Invoice Date N/A           Amount  N/A
     3rd Invoice Date N/A            Amount  N/A
     4th Invoice Date N/A            Amount  N/A
     5th Invoice Date N/A            Amount  N/A

So the rollup field on the account page would be 2014 Q4 projections = $120.

Is there an easier way to do this than create rollup summary fields within a date range for each invoice date, then adding them together? Is this something that would require code?

1

You won't be able to use RSF fields that rely on criteria that looks at date or time. See the Help doc

So, alternatives

  1. Scheduled Apex batch job that sweeps the database and rewrites the Account records with the current yr/qtr totals. Could run daily or even more often

  2. Install (free) package Declarative Lookup Rollup Summaries by esteemed SFSE contributor Andrew Fawcett. This has a scheduled mode. A clicks, not code solution

  3. (I've not used this) AppExchange package Rollup Helper

  4. (This one is clever, see Steve Molis) - create two custom formula fields on Opportunity, both of type checkbox: isClosingInCurrentQtr__c and isClosingInCurrentYear__c. Create a third custom date field called itIsNow__c. Use workflows (you'll need time-based ones) to execute daily on every Opportunity, setting the value of itIsNow__c to TODAY() via a Field Update. The custom checkbox formula fields evaluate to true if the closeDate is in the same quarter (year) as itIsNow__c. Now, you can use your RSF fields on Account by getting around the restriction I mentioned at the top of the answer - the RSF criteria no longer (directly) depends on TODAY().

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.