Salesforce-Apex-根据活动历史记录查询帐户 [英] Salesforce - Apex - query accounts based on Activity History

问题描述

嘿，Salesforce专家，

Hey Salesforce experts,

我对有效查询帐户信息有疑问.我想根据activityHistory对象中的更新查询帐户.我遇到的问题是，无论是否存在完整的" activeHistory，都将检索所有帐户.那么，有没有办法我可以编写此查询来仅检索具有activeHistory且具有status ="complete"和Type_for_reporting ='QRC'的帐户?

I have a question on query account information efficiently. I would like to query accounts based on the updates in an activityHistory object. The problem I'm getting is that all the accounts are being retrieved no matter if there's "complete" activeHistory or not. So, Is there a way I can write this query to retrieve only accounts with activeHistory that has status="complete" and Type_for_reporting='QRC'?

List<Account> AccountsWithActivityHistories = [    
    SELECT
         Id
        ,Name
        ,( SELECT
                ActivityDate
               ,ActivityType
               ,Type_for_Reporting__c
               ,Description
               ,CreatedBy.Name
               ,Status
               ,WhatId
            FROM ActivityHistories
            WHERE Status ='complete'  and Type_for_Reporting__c = 'QRC'
        )
    FROM Account
];

推荐答案

您在历史记录中有一个WHERE子句，但在帐户级别上仍然缺少一个.

You have a WHERE clause on the histories but you still miss one on the Account level.

例如，这将仅返回具有联系人的帐户:

For example this would return only Accounts that have Contacts:

SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact) // try with NOT IN too

---

使用Activity会比较棘手，因为它们不喜欢以这种方式在WHERE中使用.

---

With Activities it's trickier because they don't like to be used in WHERE in that way.

http://www.salesforce.com/us/开发人员/docs/officetoolkit/Content/sforce_api_calls_soql_select.htm

子查询当前不支持以下对象:

The following objects are not currently supported in subqueries:

• ActivityHistory
• 附件
• 事件
• EventAttendee
• 注意
• OpenActivity
• 标签(AccountTag，ContactTag和所有其他标签对象)
• 任务

• ActivityHistory
• Attachments
• Event
• EventAttendee
• Note
• OpenActivity
• Tags (AccountTag, ContactTag, and all other tag objects)
• Task

另外， ActivityHistory >定义也令人沮丧.

Additionally the fine print at the bottom of ActivityHistory definition is also a bit discouraging.

以下对没有查看所有数据"权限的用户的限制有助于防止性能问题:

The following restrictions on users who don’t have "View All Data" permission help prevent performance issues:

• 在关系查询的主子句中，您只能引用 一条记录.例如，您不能过滤所有记录， 帐户名以"A"开头；相反，您必须引用一个 帐户记录.
• 您不能使用WHERE子句.
• 您必须将列表中返回的行数限制为499个或更少.
• 您必须按升序对ActivityDate进行排序，并按降序对LastModifiedDate进行排序；您可以最后显示空值.为了 例如:ORDER BY ActivityDate ASC NULLS LAST，LastModifiedDate DESC.

• In the main clause of the relationship query, you can reference only one record. For example, you can’t filter on all records where the account name starts with ‘A’; instead, you must reference a single account record.
• You can’t use WHERE clauses.
• You must specify a limit of 499 or fewer on the number of rows returned in the list.
• You must sort on ActivityDate in ascending order and LastModifiedDate in descending order; you can display nulls last. For example: ORDER BY ActivityDate ASC NULLS LAST, LastModifiedDate DESC.

---

看起来您将需要多个查询.选择任务(或事件，取决于自定义字段的可见性)，编写一组AccountId，然后查询Accounts?

---

Looks like you will need multiple queries. Go for Task (or Event, depending for which the custom field is visible), compose a set of AccountIds and then query the Accounts?

或者您可以从原始查询中手动筛选列表，将帐户复制到帮助者列表:

Or you can manually filter through list from your original query, copying accounts to helper list:

List<Account> finalResults = new List<Account>();
for(Account a : [SELECT...]){
    if(!a.ActivityHistories.isEmpty()){
        finalResults.add(a);
    }
}

这篇关于Salesforce-Apex-根据活动历史记录查询帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！