+ Start a Discussion

query problems with WHERE clause

I'm trying to total Referrals given for each Contact in my organization, and I'm coming up with a problem. Theres a field in my Opportunity that is a lookup to the Contacts to specify who gave the referral, and that's the only place where who the "Referrer" was is stored. I decided to create a field on the Contacts called "Referrals Given" and create a trigger on the Contact to query all the Opportunities to see which have the given Contact Name as the Referrer and then get the size of that list and fill the Referrals Given with that number. However, it's not returning the right value, it always says 0 even though I know there are several Opportunities that have the Contact name I'm testing as the Referrer. My code is this: 


trigger getNumReferrals on Contact (before update) {
    for (Contact c : Trigger.new) 
        String nameR = c.Name;
        List<Opportunity> oppList = [SELECT j.Name FROM Opportunity j WHERE j.Referrer__c =: c.Name];
        Integer count = oppList.size();
        c.Referrals_Given__c = count;


 So I need to know what's wrong with the trigger, or is there a better way to do what I'm trying to get done? 




If the field is a lookup, then its value is the Id of the contact, not their name, so it should be where Referrer__c =: c.id


Simon is correct. Use ID instead name.


BUt this trigger will hit governor limit if you are updating more than 100 records at a time.

You need to bulkify the trigger.