+ Start a Discussion
edward scott 10edward scott 10 

find all opportunities attached to a contact through a custom lookup field

Hi All,

I am trying to write a SOQL statement on the account object that will select all opportunities that it is associtated through a custom look up field named "Referring Facility" on the opportunity to the account. I would like to be able to sum the amount of all of those opportunities. Right now I have this working through a junction object but I am trying to get rid of those. 

I was able to figure out how to write a trigger to do the same thing on the contact using a trigger that looks down at the contact roles takes the contact id of the referring therapist. But I am struggling to write the SOQL statement upwards from the opportunity to the account through the custom field. The closet I got it to work was to get one opportunity to write its into to the account but it wouldn't sum. 

Also, I am open to advice on the best way to do this. Ideally it would be nice if this happened when the opportunity closed and would update the account without having to press edit on the account to see the new value.

Thanks in advance for your help,
nitin sharma 366nitin sharma 366
It might be possible to use roll up summay field on the account object to sum all the associated opportunities to the account...