Account and Opportunity Relationship Trigger

Question

I have some code that is intended to work like the following: When the zipcode field on Accounts is inserted or updated, the triggers looks up whether the zip code is in a field called name on a custom object called DMA_Value__c. If the value is there the DMA_Value__c field on the DMA_Value__c object is returned to a field called DMA__c on the Opportunity object. My code is throwing a Invalide foreign key relationship for the line where I have Account.Opportunity. I'm guessing this means I need a map but I'm not sure where it should go. If your answer provides code could you please add an explanation so that I understand this better in the future? Please see the code below.

trigger UpdateDMAAccount on Account (before insert, before update) {
   Set<String> BillingPostalCodes = new Set<String>();
for(Account a : trigger.new){
    if(a.BillingPostalCode != null){
        BillingPostalCodes.add(a.BillingPostalCode);
    }
}
if(BillingPostalCodes.size() > 0){
    //create Map of custom object records with Name (BillingPostalCode) as key
    Map<String,DMA_Value__c> validBillingPostalCode = new Map<String,DMA_Value__c>();
    for(DMA_Value__c obj : [SELECT Id, Name, DMA_Value__c FROM DMA_Value__c WHERE Name IN :BillingPostalCodes]){
        validBillingPostalCode.put(obj.Name,obj);
    }
    for(Account a : trigger.new){
        if((Trigger.isInsert || trigger.oldMap.get(a.Id).BillingPostalCode != a.BillingPostalCode) && validBillingPostalCode.containsKey(a.BillingPostalCode)){

            a.opportunity.DMA__c = validBillingPostalCode.get(a.BillingPostalCode).DMA_Value__c;
        }
        else if(trigger.oldMap.get(a.Id).BillingPostalCode != a.BillingPostalCode && !validBillingPostalCode.containsKey(a.BillingPostalCode)){
            //blank value if new BillingPostalCode does not match a record in the Map
            a.Opportunity.DMA__c = null;
        }
    }
}
}

Answer 1

Typically Opportunity lives under Account, but you have it the other way around. If Account.Opportunity__c is a field, your solution is fairly simple:

List<Opportunity> opportunities = new List<Opportunity>();
for (Account newAccount : accountsToActOn)
{
    opportunities.add(new Opportunity(Id=newAccount.Opportunity__c, DMA__c=dmaValue);
}

This seems unlikely. What is more likely the case is that you have to group child Opportunity records by their parent Account.

Map<Id, List<Opportunity>> accountIdToOpportunities = new Map<Id, List<Opportunity>>();
for (Opportunity childOpportunity : [
    SELECT Id, AccountId FROM Opportunity
    WHERE AccountId IN :trigger.new])
{
    if (!accountIdToOpportunities.containsKey(childOpportunity.AccountId)
        accountIdToOpportunities.put(childOpportunity.AccountId, new List<Opportunity>();
    accountIdToOpportunities.get(childOpportunity.AccountId).add(childOopportunity);
}

Now you can loop through the children for each account meeting your criteria. You didn't specify the type of field for DMA__c so I will assume String will work.

List<Opportunity> opportunitiesToUpdate = new List<Opportunity>();
for (Id accountId : accountIdToOpportunities)
{
    Account a = trigger.newMap.get(accountId);
    if (validBillingPostalCode.containsKey(a.BillingPostalCode))
    { // fill out your condition as needed
        String dmaValue = validBillingPostalCode.get(a.BillingPostalCode).DMA_Value__c;
        for (Opportunity child : accountIdToOpportunities.get(accountId))
        {
            child.DMA__c = dmaValue;
            opportunitiesToUpdate.add(child);
        }
    }
}
update opportunitiesToUpdate;