+ Start a Discussion
isalewisalew 

Dot Notation Query Returning Null

I'm building a trigger handler that references multiple objects to update the OpportunityLineItem object. I want to use dot notation as much as possible to eliminate SOQL queries.

The following loop shows an example of one cross-object reference that returns null, but I do not understand why:
for(OpportunityLineItem oli :olis){
    System.debug('Revenue Type: ' + oli.PriceBookEntry.Revenue_Type__c);
}

The following cookbook recipe (http://developer.force.com/cookbook/recipe/using-relationship-queries) suggests I can access these fields with dot notation:
SELECT Id, Name, Account.Name,
       (SELECT Quantity, UnitPrice, TotalPrice,
               PricebookEntry.Name,
               PricebookEntry.Product2.Family
        FROM OpportunityLineItems)
FROM Opportunity

Why am I unable to access this field with dot notation? How would I write things differently?
Thank you all for your help!
Best Answer chosen by isalew
James LoghryJames Loghry
The following statement:

for(OpportunityLineItem oli :olis){
    System.debug('Revenue Type: ' + oli.PriceBookEntry.Revenue_Type__c);
}

Is likely throwing an NPE because the data simply doesn't exist in the trigger context.  The trigger context will contain non-formula fields and also Ids of parent relationships (like PriceBookEntryId in your case), but you will not able to access cross object fields.

What you need to do is requery for the Opportunity Line Items or PriceBookEntries to get the fields you need.

The following would work in an after insert or before update / after update trigger (Trigger.newMap is not available with before insert triggers)

Map<Id,OpportunityLineItem> oliWithPriceBookEntryMap =
    new Map<Id,OpportunityLineItem>(
       [Select 
            PriceBookEntry.Revenue_Type__c 
        From 
            OpportunityLineItem 
        Where 
            Id in :Trigger.newMap.keySet()]
    );

for(OpportunityLineItem oli : Trigger.new){
    System.debug(oliWithPriceBookEntryMap.get(oli.Id).PriceBookEntry.Revenue_Type__c);
}

Otherwise, if this is for a before insert trigger, you'll have to query the PriceBookEntry object and compare it using the OpportunityLineItem's PriceBookEntryId relationship(s).

Hope that helps.

All Answers

KevinPKevinP
In general you can use dot notation to access fields *above* the object you're querying *from* in parent child relationship terms. In other words, if OpportunityLineItem was a child of pricebookEntry you could access PricebookEntry.name. However, PricebookEntry is not a parent of OpportunityLineItem (see this ERD: https://www.salesforce.com/us/developer/docs/api/Content/sforce_api_erd_products.htm).

In order to do this with dot notation you'll need to query from product2 and even then I'm not sure it'll work.
James LoghryJames Loghry
The following statement:

for(OpportunityLineItem oli :olis){
    System.debug('Revenue Type: ' + oli.PriceBookEntry.Revenue_Type__c);
}

Is likely throwing an NPE because the data simply doesn't exist in the trigger context.  The trigger context will contain non-formula fields and also Ids of parent relationships (like PriceBookEntryId in your case), but you will not able to access cross object fields.

What you need to do is requery for the Opportunity Line Items or PriceBookEntries to get the fields you need.

The following would work in an after insert or before update / after update trigger (Trigger.newMap is not available with before insert triggers)

Map<Id,OpportunityLineItem> oliWithPriceBookEntryMap =
    new Map<Id,OpportunityLineItem>(
       [Select 
            PriceBookEntry.Revenue_Type__c 
        From 
            OpportunityLineItem 
        Where 
            Id in :Trigger.newMap.keySet()]
    );

for(OpportunityLineItem oli : Trigger.new){
    System.debug(oliWithPriceBookEntryMap.get(oli.Id).PriceBookEntry.Revenue_Type__c);
}

Otherwise, if this is for a before insert trigger, you'll have to query the PriceBookEntry object and compare it using the OpportunityLineItem's PriceBookEntryId relationship(s).

Hope that helps.

This was selected as the best answer
isalewisalew
Thank you, James. This sent me the right direction.

I went with a before trigger scenario, so I queried the PriceBookEntry object and used a Map to compare the records.
//Get PriceBookEntryId for OpportunityLineItems
Set<Id> pbeIds = new Set<Id>();
for(OpportunityLineItem oli : olis){
    pbeIds.add(oli.PricebookEntryId);
}

//Query PriceBookEntry for PriceBookEntry and Product2 Information
Map<Id,PriceBookEntry> pbeMap = new Map<Id,PriceBookEntry>([SELECT Id, PriceBookEntry.Revenue_Type__c FROM PriceBookEntry WHERE Id IN :pbeIds]);

for(OpportunityLineItem oli : olis){
    PriceBookEntry pbe = pbeMap.get(oli.PricebookEntryId);
    System.debug('Revenue Type: ' + pbe.Revenue_Type__c);
}