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?

Attribution to: Mike Chale

Possible Suggestion/Solution #1

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');

Attribution to: ebt

Possible Suggestion/Solution #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.

Attribution to: Jeremy Nottingham
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/1135