I have two object one is "Account"(Parent) and second one is "Information"(Child).

in that "Information" object have two field "balance" and "duedate"

now at account level i want the total of all the balance which value is not 0(Zero) and duedate equal to this month.

So now here what can i do to get the total of the balance at account level?

Please suggest

  • I presume from your question that Information is not a Detail record and RSF fields are not available. There are some other options - vendor appexchange.salesforce.com/… and community - andyinthecloud.com/2014/02/09/… that are worth considering to see if they meet your requirements – cropredy Sep 24 '14 at 19:31
  • @Keith C, I briefly saw your answer, but couldn't your solution work in combination with a time-based trigger that set the flag for this month? – Jenny B Sep 24 '14 at 19:50
  • @JennyB OK I've undeleted it so if you want to add an answer that builds on it please go ahead. – Keith C Sep 24 '14 at 19:52

Note this answer is wrong - see @crop1645's comment; I've left it in case it is of use referenced from someone else's answer.

Roll-up summaries do support some basic filtering as shown in the screen shot below. (The relationship between Account and Information has to be master-detail.)

But formula fields are not available in the list of fields offered, so instead of being able to just add a checkbox formula field called e.g. IsThisMonth of:

MONTH(DueDate__c) == MONTH(TODAY()) && YEAR(DueDate__c) == YEAR(TODAY())

for use as the filtering field, that calculation will have to be done in a trigger so that it updates a non-formula field that can then be used as the filtering field.

That is a fairly simple trigger (compared to all the cases you have to handle to do the roll-up via triggers) so the combination of that trigger and the roll-up field is the approach I suggest.

roll-up filter screen shot

  • yes -- but don't you have to re-sweep all the Information__c records every month and recalculate the isThisMonth__c field? – cropredy Sep 24 '14 at 19:42
  • @crop1645 I added this comment above but I believe Keith's solution would work if you used a time-based workflow rule to set/reset the this month checkbox. – Jenny B Sep 24 '14 at 20:01
  • ah -- good idea. You might want to elaborate on how to setup such workflows. Of course whole approach relies on master-detail which isn't always possible. – cropredy Sep 24 '14 at 20:30
  • We use a daily batch to process all records and set the 'Current Year" flag (checkbox) for use in rollup summaries. Much more manageable than time based workflows as there is a limit on processing TBW as well (1000/hr) which could impact many processes as well as the timeliness of updating these records – Eric Sep 25 '14 at 1:58

Well, this seems like something you will need custom code for since a roll-up summary requires that you roll up ALL child records. You have 2 options for this:

1) Write a trigger than tallies up the children anytime a new one is added. This could potentially cause some incorrect data if the duedate is in a future month and nothing happens to the children in that current month.

2) Write a batch to tally up the data each month. The only downside to this, is that the data won't be in real time and only gets updated when the batch fires.

  • i think it is a combination of these -- trigger to handle CRUD on Information_c (real-time) plus monthly scheduled batch job to recalculate all the Accounts. – cropredy Sep 24 '14 at 19:49
  • 1
    Perhaps you could update the first sentence as roll-up summaries can be filtered based on non-formula fields of the object being aggregated, but with the "this month" factor here that doesn't help (thanks @crop1645 for that). – Keith C Sep 24 '14 at 19:50

One alternative to the custom field approach if you don't need reporting but only need to show, on-demand, what the total balance is for items with duedate in the current month is to add a Visualforce page to your Account page layout.

Such a page would invoke a controller that queried all Information__c children of the current account where dueDate__c = THIS_MONTH and then either sum all the amounts or present back a list of Information records. The results would display inside the VF page that is included within the overall page layout


If triggers are not an option, you could use the rollup helper on the app exchange here. There is also an idea on the idea exchange here that explains why they don't allow using formulas with dates, if you want a work a workaround using clicks you could do the following based on Keith's answer above:
Add the following fields to the "Information" object:
Checkbox field to flag current month
Formula field month due (results in the 1st of the month for the due date)
Formula field month due end (results in the last day of the month for the due date)
Workflow rule that fires when month due is not empty that has the following two time based workflow field updates:

  1. Check current month flag 0 days after month due
  2. Uncheck current month flag 0 days after month due end date

You could then use that checkbox as criteria in your rollup field (assuming master detail)

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.