Custom field showing a field from the first of multiple custom object entries

Question

I have a custom object that we use for test scores for our students(contacts). A student can take the same test multiple times. My goal is to create a custom field in the contacts object that shows the score of the first test a student takes based on a date-entry field in the same custom object. Below is an example:

Test A - Date: 1/5/2010 - Score: 60

Test B - Date: 2/15/2010 - Score: 50

Test C - Date: 3/25/2010 - Score: 70

Jane Doe - First Score: 60

I have tried creating a rollup summary that takes the MIN of the Date field, then create a Number field to access that field, but there doesn't seem to be a formula that loops through all entries for that student. Also tried creating another rollup summary and using the field criteria with the MIN Date rollup summary but with no luck.

Thanks for any help.

Answer 1

This can't be done with a regular Rollup Summary. You'd need to write a trigger on your custom object. I haven't tried it, but I know there is an app that gives you some additional rollup options. Not sure if this is one of them. Check out Rollup Helper.

Answer 2

How about having a cross object workflow field update? Havent tested, but worth a try.

http://login.salesforce.com/help/doc/en/workflow_cross_object_field_updates.htm

Have a workflow rule defined on the child object whose criteria would be "formul that eveluates to true". Your formula would be: Test.Date = contact.date rollup sumary field.

Then define a field update and update the score field in the contact record with the value of score field in test record.

For existing records you might have to do a blank update once. For new records, this will work. On deletion of first test record, you might again have to perform a blank update to all the test records.