On our Accounts, we have a related list to test scores (custom object) that "expire" after five years. I am trying to use a roll-up summary to display the best test score within the past 5 years.

Solutions tried:

  1. Use a criteria of Date Taken equals Last 5 Years and Date Taken equals Last 1827 Days, similar to what is possible on reports, but the criteria fields do not accept relative dates like that.
  2. Create an "Is Within Last 5 Years" formula field on the custom object, but the field was not available for filtering. Some searching led me to this idea, which asks for filtering roll-up summaries based on formulas.

Is there anyway to only show the MAX test score within the past five years?

2 Answers 2


How about a checkbox on the Test Score object called Last 5 years, checked by default. Use a Time-dependent workflow to uncheck that box 5 years after Date Taken. Then your Rollup Summary will be based on Last 5 Years= TRUE.

  • The button click admin side of me really likes that; any idea if there's a limit on how many objects can be in a time-based workflow at once? This would easily be in the thousands of objects.
    – Mike Chale
    Commented Sep 4, 2012 at 18:23
  • I think if you had a very large number of records that were all firing at the same time, you would maybe see some performance issues. But I've never seen any limits on how many outstanding workflow triggers/field updates there can be in the queue. Commented Sep 4, 2012 at 18:57
  • I just tested this out and it will not be possible; a time trigger must happen within 999 days as the UI will not let you enter a longer time span.
    – Mike Chale
    Commented Sep 5, 2012 at 18:11
  • The time trigger won't add more than 999 days to the date in a field. However, you can create a date formula field on Test Score, called "Last 5 Expiration Date" that is equal to Date_Taken__c + (5 * 365) (you could be more precise than this if you like). Then your time trigger will be 0 days after that new date. Commented Sep 5, 2012 at 21:50

how about an aggregate Query?

 rs = [select id,MAX(score__c) score from <Student Tests> where student__c = :studentId GROUP BY ID]
 Decimal score = (Decimal) rs.get('score');
 ID testId = (ID) rs.get('Id');
  • I'm assuming this Apex or VisualForce, of which I have very little experience. Would you please elaborate on how I could incorporate this?
    – Mike Chale
    Commented Sep 4, 2012 at 18:24

You must log in to answer this question.

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