[SalesForce] SOQL Query on Event to get Opportunity (custom fields)

I need help to query on Task Event to get Opportunity custom fields like Campaign name and Product name.
I would like to filter my tasks related to Oppty and Lead based on product name and campaign name.

String LeadObjName= 'Lead';
        String OpportunityObjName= 'Opportunity';
        String Completedsatatus= 'Completed';

String queryStr='select Id, Subject, Status,ActivityDate,Priority, OwnerId, Owner.Name, Owner.IsActive,Description,WhoId,Who.Name,WhatId, What.Name,AccountId,Account.Name ';
            queryStr+=' from Task where OwnerId = :userId and (Who.Type =:LeadObjName OR What.Type = :OpportunityObjName ) ';
            queryStr+=' and Status !=:Completedsatatus';
            queryStr+='  order by ActivityDate ASC, Priority DESC';

Ques: We want to access opportunity and lead custom fields in my query but not able to add custom fields here in this query
Thanks in Advance!

Best Answer

You cannot do this in a single query because WhatId is polymorphic (...unless your organization has the SOQL Polymorphism feature enabled, which is in Developer Preview).

What you'd need to do is query on Task and Event to find those records that are interesting to you, filter based upon their WhatId values (for example, you can look for a WhatId that starts with 006 to find Opportunities), and accumulate the WhatId values in separate sets by object type. So you'd have, say,

Set<Id> opportunityWhatIds;
Set<Id> leadWhatIds;

and you'd populate them by looping over your Task and Event records.

You'd then have to run one additional query per sObject, using a WHERE Id IN :opportunityWhatIds clause, to obtain the information from that parent object.

Related Solutions

[SalesForce] Help with SOQL Query on Event to get Opportunity Fields

In the query example you provided above, "What" is already the Opportunity, you don't need to "drill-in" to a reference off of that field. So, you should be able to access Opportunity fields using What.[Opportunity-Field-Name] as long as the fields you are querying are Standard fields.

To query custom fields from a related object on Tasks or Events, you could use TYPEOF in a SOQL statement, but you'd need to contact Salesforce to get it enabled first.

I'm not entirely sure if this would work because I don't have TYPEOF enabled, but you could try this:

// where getOpportunityId is a method that returns the Opportunity Id
My_Opportunity_ID = getOpportunityId(); 

Assnmnt = [
    SELECT 
        Id, OwnerID, Owner.Name, Subject, ActivityDateTime, ActivityDate, 
        DurationInMinutes, StartDateTime, EndDateTime, Description,  WhatID, WhoID, 
        Who.Name, 
        TYPEOF What 
            WHEN Opportunity THEN Account_Address__c, Account.Name, 
                 Account_Phone__c, Customer_Contact__c 
        END
    FROM Event 
    WHERE Id = :Asn2Create 
    AND WhatId = :My_Opportunity_ID
    AND What.Type = 'Opportunity'
];

[SalesForce] SOQL – How to obtain Parent Name of CombinedAttachments

This is an example of a Polymorphic relationship. I don't have this feature enabled, so cannot confirm, but reading through the docs it's likely still that your requirement to obtain this information within the same SOQL will not be resolved by this feature either. Though it does allow you to determine in Apex the type, such that you could perform additional query/s after you group the ParentId's by type.

TYPEOF is currently available as a Developer Preview as part of the SOQL Polymorphism feature. For more information on enabling TYPEOF for your organization, contact salesforce.com.

I suspect the reason being, is that despite Name being quite a common field, it's not quite common enough (there are a few standard objects that don't have it) to be permitted when de-referencing a polymorphic relationship. Only really Salesforce could explain why they don't support this i guess.

Best Answer

Related Solutions

[SalesForce] Help with SOQL Query on Event to get Opportunity Fields

[SalesForce] SOQL – How to obtain Parent Name of CombinedAttachments

Related Topic