5

I am working with ActivityHistories & OpenActivties and trying to see if there is a work around to my issue. This query works but returms accounts that do not have the specific activities

Select id,
(SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
(SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account

But if I try to use this query I receive an error:

Select id,
(SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
(SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account where id IN (SELECT Account.id FROM ActivityHistories where subject ='Email: Test')

I just want to return the Accounts with the correct associated activities. Is this possible in SOQL?

Cheers, M

0

I just want to return the Accounts with the correct associated activities. Is this possible in SOQL?

Yes it is possible but neither the OpenActivity nor ActivityHistory sObject are supported for semi join inner selects. What this means is that your second query would be invalid.

Select id,
    (SELECT Account.id,What.name, ActivityDate FROM ActivityHistories where subject ='Email: Test'),
    (SELECT Account.id FROM OpenActivities where status = 'Submitted')
FROM Account where id IN (SELECT Account.id FROM ActivityHistories where subject ='Email: Test')

What you will need to do is make separate queries for OpenActivity and ActivityHisory sObjects, hold the Account Ids in a Set and then use the Set to query the Accounts.

Something like this should work for you.

Set<Id> accountIds = new Set<Id>();
for(OpenActivity oa : [SELECT AccountId FROM OpenActivity WHERE Subject = 'Email: Test']){
    accountIds.add(oa.AccountId);
}
for(ActivityHistory ah : [SELECT AccountId FROM ActivityHistory WHERE Subject = 'Email: Test']){
    accountIds.add(ah.AccountId);
}

List<Account> accounts = [SELECT id,
                            (SELECT AccountId, What.Name, ActivityDate FROM ActivityHistories WHERE Subject ='Email: Test'),
                            (SELECT AccountId FROM OpenActivities WHERE Status = 'Submitted')
                        FROM Account WHERE Id IN :accountIds]
| improve this answer | |
  • You missed to use the accountIds set in the where condition of query :) – Vigneshwaran G Apr 13 '16 at 15:41
2

What.name doesn't exist. The WhatId is a reference to another object and can't be used in that manner. That's why your 2nd query is failing. Remove the field and it should work.

EDIT

It sounds like you want to add criteria in your WHERE clause to something like `WHERE WhatID LIKE '00U%`` which is the ID prefix for an Event. If there are other types of WhatIDs (a polymorphic object) you want to include such as TASK, then you can grab those using Workbench and include them in your query with an OR in the Where clause.

| improve this answer | |
  • Thank you of suggestion. The query runs fine but the search results returns all of my Accounts. Do I not have to have a where clause after the last part of the query? from Accounts where ....... – Michael Apr 13 '16 at 14:57

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.