0

In the below query, I am trying to extract all Events that have a Opportunity of record type Id = 012240000002Cu8. I also want to further restrict this query based on the opportunity's stage

My query:

Select Id,X1st_booked_meeting__c, whatId FROM Event where what.recordtypeId = '012240000002Cu8' 

What I am trying to achieve:

Select Id,X1st_booked_meeting__c, whatId FROM Event where what.recordtypeId = '012240000002Cu8' AND what.StageName!='Order Processed' AND what.StageName!= 'Closed Lost'.

what.StageName is throwing an error saying :

No such column 'StageName' on entity 'Name'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

Here StageName is standard picklist field on opportunity. what.StageName or whatId.Stagename does not seem to work.

I would like to know if this is possible?

3

You can do it in one soql-query without not needed loops:

[
    select Id,X1st_booked_meeting__c, WhatId
    from Event
    where WhatId in (
        select Id
        from Opportunity
        where RecordTypeId = '012240000002Cu8'
            and StageName != 'Order Processed' 
            and StageName != 'Closed Lost'
        )
]

PS. Hardcoding ids in code is a bad practice.

  • Thanks for the quick solution, It worked great. And I am using this query in data loader to fetch the records and not using it inside my code.. – Cheryl Aug 17 '17 at 9:34
4

Since you can not directly query this on Event as your what Id can be any object. What you can do is first query the opportunity

 List<Opportunity> opp = [select Id from Opportunity where RecordTypeId = '012240000002Cu8' AND StageName != 'Order Processed' AND StageName != 'Closed Lost'];
List<Id> oppId = new List<Id>();
for(Opportunity op : opp){
   oppId.add(op.Id);
}

Then query your Event based on this

Select Id,X1st_booked_meeting__c, whatId FROM Event where whatId IN : OppId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.