Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

Is there any way that we can put the date filter on child record (like created in last one year) while creating roll-up summary field? I want to count only those child records which are created within last one year. I tried it by creating formula field on child object but it does not appear while filtering the records (As per the doc: Automatically derived fields such as current date or current user are not allowed in roll-up summary fields). Any workaround if someone face this issue before ?

share|improve this question
    
Pretty messy solution here salesforce.stackexchange.com/questions/1135/…. –  Keith C May 1 at 7:46
    
@Keith Yeah I saw that, will go for Time Based workflow to update the field, if I'll not found any other feasible solution. –  user3077 May 1 at 8:13

2 Answers 2

As far as I know it can't be done with normal rollup. You would expect any TODAY()-related stuff to magically move when the boundary date passes (or in your case - January 1 comes). But the rollups aren't calculated when you view the record like formulas are, they're calculated when child record is updated.

So you need some way to "touch" all records at some point (by touch I mean an update that doesn't change anything, edit & save). And you'll need either time-based workflow for that or a scheduled batch job. Time-based sounds easier but even then you have to submit to the queue all your existing records so they aren't left forgotten when the time comes.

share|improve this answer
    
Thanks for your response. Yup I believe time based workflow field update is the only solution which I have found at the moment. Any idea how many time-based workflow I can submit in the queue, I wonder if I hit the limit in future as no of record could be high. –  user3077 May 1 at 8:43
    
I don't think there's any limit on amount: help.salesforce.com/…. Worst case scenario - it'll take a while to process them all if they're to fire on same day? From what I remember you need to deactivate the workflow if you want to modify it (and this will wipe out the queue) so if you need to change anything - remember to resubmit all you records back! Maybe add it to the workflow rule's description... –  eyescream May 1 at 9:30
    
Yeah got it now. It says"Salesforce limits the number of time triggers an organization can execute per hour. " thanks for your help !! –  user3077 May 1 at 10:00

I have a pretty easy solution for this one, but its not very scalable.

Create a formula field on the child records that checks the date of this entry and if its after the specified date, then it gets the full value otherwise it gets 0. Then just use a regular roll up summary to calculate that total. You can hide that field from users so they don't realize there is an extra calculation.

share|improve this answer
    
Last time I tried it works only if the date you compare with is hardcoded (so the formula result is "deterministic"). Anything with TODAY() or NOW() doesn't show up on the list of fields you can rollup. –  eyescream Jun 12 at 20:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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