You need to sign in to do that
Don't have an account?
alvatse
Calculating average survey score for a Contact
Hi all, I'm trying to have a field on Contact to calculate the average score of all the surveys this contact filled out. e.g. Contact: John Smith filled out 2 surveys, and he gave a score = 5 on one survey, and gave a 10 on the other survey. I then want an 'Average Score' field to calculate: [(Score 1 + Score 2 + Score n)/count of surveys].
The scores reside in a custom object called 'Survey Responses', and the a Contact can have multiple survey responses. thanks.
Is there a way to do that? thanks in advance.
Jakester
I assume Survey is a child of Contact in a master-detail relationship with it. If that's the case, I think you could create two Roll-up Summary fields. One that would count the number of surveys, and one that would sum the scores of the surveys, and then I think you can divide one by the other. The big uncertainty here is whether you make a formula that uses Roll-up Summary fields. If you can't, you'll probably have to do this with a report.
alvatse
Hi, thanks for the response. Unfortunately Survey wasn't built as a child of Contact (I inherited the system unfortunately). In this case, is there a solution? Thanks.
Jakester
Probably not without using Triggers. How are they linked?
alvatse
Survey is a custom object, and one of the fields is 'Contact' which is a lookup in the Contact object. That's the only association between Survey object and Contact object. Would trigger be the way to go?
Jakester
Yes, but this is easily an 8 hour developer project, possibly much more. You'll need different triggers to fire when there's a new survey linked to a contact, when a survey is deleted, when a survey points to a different contact, when a survey is undeleted, and so on. All of this code has to have unit testing with it, too. Triggers are very powerful, but non-trivial.
alvatse
Got it. Thanks very much again.