Filtering in Lightning's Activity Timeline

The Lightning Experience’s record pages come with a very nice Activity timeline and publisher.

Lightning Activity Timeline

One of the features of this timeline is highlighting different types of activities with topical icons, and permitting the user to apply filters on the fly to isolate activities of interest - like just Emails, or just Calls, or all Tasks owned by the user.

Filters for Activity Timeline

How, though, does Lightning distinguish between these different activities to populate the various filters? And further, can we control that filtration to place activities we generate under specific filter headings?

The answers turn out to be “a bit of magic” and “sort of.” Lightning recognizes five categories of activity: Emails, Events, List Emails, Logged Calls, and Tasks.

Events are distinguished from Emails, List Emails, Logged Calls, and Tasks by sObject type. Events have the object type Event, while the other four are all of type Task. The Emails, List Emails, Logged Calls, and Tasks filters work differently. All of them filter based upon the picklist value in the field TaskSubtype on the Task object.

Confusingly, this field isn’t connected to the Type field whatsoever - it’s not a dependent field, and the Type field plays no role in Activity filtering.

This field is populated by the system, and cannot be changed. At the database level, it’s createable but not updateable.

[TaskSubtype] Provides standard subtypes to facilitate creating and searching for specific task subtypes. This field isn’t updateable.

The four values of this picklist (which is restricted) are Email, ListEmail, Call, and Task, each of which maps to exactly one filter. Email and ListEmail are entirely separate; they don’t appear in one another’s filtered view.

Manipulating the TaskSubtype

Knowing, then, how Lightning sorts tasks under different filter headings, can we manipulate the filters to sort our custom activities in bespoke ways? Only to a slight extent, as it turns out.

We cannot change the behavior of the filters. The Activity timeline is a non-configurable Lightning component; we can add it to record pages, but it doesn’t have any public attributes for us to set.

Because the TaskSubtype field is createable, but not updateable, we cannot move existing records from one category to another.

The one route we have is to override the category at the time of creation of the Task, and there are some unique behaviors to this approach. When inserting records via the publisher, if the TaskSubtype is ultimately going to be 'Task' (i.e., it is not an Email, List Email, or Call), we can, in a before insert trigger on Task, set the TaskSubtype to one of the other three values. A putative Task can be transmogrified into an Email, Call, or List Email:

Task converted to Call

However, this does not work in the other direction. Records that are being created from the publisher as Emails, List Emails, or Calls can’t have their TaskSubtype overridden to 'Task', or to any of the other available values. Attempting to do so has no effect on the created Task, although it doesn’t cause an error. The TaskSubtype field is null upon publisher creation; it’s set behind the scenes at some point between the before and after trigger invocations, but by the time we reach after insert, the field’s inherent non-updateability takes over.

None of this applies to Tasks inserted via Apex. If the publisher isn’t the source of the Task, any TaskSubtype value can be transformed into any other.

There’s still one more caveat of applying this technique, though: if the Task-to-be-converted is added via the publisher, Chatter records the original type of the task in its feed:

Chatter post

This mismatch does not occur when the Task is inserted via code, which doesn’t produce a Chatter post and hence preserves the illusion of being (say) a Call the entire time.

The question of whether it’s wise to manipulate the Activity timeline in this way is another story. Because this functionality is at least somewhat undocumented, I wouldn’t rely on the publisher continuing to work exactly the same way in future API versions. Vote for this Idea to make TaskSubtype editable!

One last interesting facet: there’s similar data model on Event, with Event.EventSubtype. Like with Task, this field isn’t updateable. However, permitted values are not documented, and the picklist has only a single value, ‘Event’, which is populated on standard events. Perhaps we’ll see more functionality around timeline filtering in future releases.

fix15 - A New Tool to Support Data Loads and ETLs

I’ve released a new Python tool, fix15. This tool aims to simplify Salesforce data-load and ETL workflows by seamlessly converting 15-character Id values to their corresponding 18-character Ids in CSV data.

There are existing online Id converters, but I wanted a solution I could use from the command line as I prepped and manipulated files for data load, without copying and pasting or using complex, very slow array formulas. With fix15, just do

fix15 -c Id -c AccountId -i test.csv -o done.csv

to convert the columns “Id” and “AccountId” in the file test.csv and write the result to done.csv.

The tool is tiny, tested, and MIT-licensed. It has no dependencies outside the Python standard library and doesn’t require access to Salesforce (or, for that matter, a network connection).

Everyday Salesforce Patterns: Filtering Parent Objects By Child Objects

Sometimes, we need to filter an Account query by its Contacts, or some custom object Project__c by its associated Subject_Area__c records. There might not be rollup summary fields in place, or the criteria might go beyond what rollups can do, or we might be dealing with a lookup relationship, forcing us to express this filtration directly in SOQL or in Apex.

Suppose, for example, that we do have a custom object Project__c. A second custom object Subject_Area__c is connected to it by a lookup relationship, Linked_Project__c, with the relationship name Subject_Areas. Suppose further that Subject_Area__c also has a junction object Subject_Area_Expert__c creating a many-to-many relationship to Contact; Subject_Area_Expert__c also records the dates an expert is assigned to that subject area.

We’d like to be able to a perform a variety of queries that express different kinds of filters on the parent based upon characteristics and qualities of the different child objects:

  1. Locating Project__c records with no subject areas at all.
  2. Locating Project__c records with more than five subject areas.
  3. Locating Project__c records with subject areas whose name contains ‘Industry’.
  4. Locating Project__c records without subject areas whose name contains ‘Industry’.
  5. Locating Project__c records with at least one Subject_Area__c record that itself has at least one Subject_Area_Expert__c assignment with current dates to a Contact whose Title is “Solution Architect”.

We should also note that some of these requirements can potentially be met using native Salesforce reports with Cross Filters, or by the use of Declarative Lookup Rollup Summaries.

There are several basic ways to approach constructing these queries:

  • Using a parent-child query and performing filtering in Apex.
  • Using a parent query with an IN or NOT IN child subquery with filter (semi-join/anti-join).
  • Using a child aggregate query and postprocessing in Apex.
  • Linking multiple queries of these kinds by performing a synthetic join in Apex.

Parent-Child Query

for (Project__c sr : [SELECT Id, 
                             (SELECT Id 
                              FROM Subject_Areas__r 
                              WHERE Name LIKE '%Industry%') 
                      FROM Project__c 
                      WHERE Client__c = :clientId]) {
    if (sr.Subject_Areas__r.size() == 0) {
        // We've identified a Project on the client whose Id 
        // is `clientId` with no Subject Areas that contain 'Industry'.
        // (but potentially other Subject Areas)
        // Do something about it.
    }
}

This pattern is appropriate for use when the filtration criteria for child objects are very complex or involve relationships that cannot be easily expressed in SOQL, and for situations where we are looking for a zero count of child objects. Note that the WHERE filters on the parent and child query are, strictly speaking, optional, although performance in many cases will necessitate selective query filters. It is a simple pattern and can implement requirements 1-4, but not requirement 5, as only one level of child relationship can be traversed.

This can be an anti-pattern for situations with high data volume in the parent or child object, when query performance will be a huge concern and heap size could become an issue. Adding selective filters on the parent object, adding filters on the child object, and reducing the number of columns queried will help obviate these issues.

While parent-child queries can only descend one level of relationship in the object hierarchy, Apex post-processing can gather child object Ids and re-query to descend additional plies.

Parent Query with IN or NOT IN Child Subquery (Semi-Join/Anti-Join)

Parent queries with IN and NOT IN child subqueries are particularly useful for cases 3 and 4. This query format takes advantage of the fact that the subquery is treated as returning a typed Id value, not an sObject instance. If we ran the subquery below, on Subject_Area__c, separately in Apex, we’d get back a List<Subject_Area__c>, which we’d have to iterate over and accumulate parent Project__c Ids before re-querying that object. When we present it as a subquery, no intermediate steps or type conversion are required; the Ids are used directly. (Salesforce does require that they be Ids of the correct type of object, however).

SELECT Id 
FROM Project__c 
WHERE Id NOT IN (SELECT Linked_Project__c 
                 FROM Subject_Area__c 
                 WHERE Name LIKE '%Industry%')

See Semi Joins and Anti-Joins for more in-depth information, including the numerous restrictions that apply to this type of query. In particular, note that you can only use two IN semi-joins or anti-joins in a single query.

It’s also important to note that this pattern isn’t limited to parent-child filtration. The semi-join and anti-join can be used to filter Object A based upon Object B using any shared reference field, including reference fields on the two objects that both point to some other Object C. See the Salesforce documentation linked above for in-depth discussion of more use cases.

This pattern is particularly suitable for case 3 and case 4, where we’re looking for parent objects based on specific criteria on (but not volume of) child objects. Note that it can return parent object data but doesn’t include child object data unless we include a separate sub-select.

In particularly complex cases or if multiple levels of subquery are required, it’s necessary to run the subqueries separately in Apex and accumulate relevant Ids in a Set for inclusion in the next query layer. Salesforce only allows one level of semi-join or anti-join, so we perform a sort of synthetic join in Apex of two separately expressed SOQL queries. For example, we can cover case 5 in a fashion like this:

// 5. Locating `Project__c` records with at least one `Subject_Area__c` record that itself has at least one `Subject_Area_Expert__c` assignment with current dates to a Contact whose Title is "Solution Architect".

List<Subject_Area_Expert__c> experts;

experts = [SELECT Subject_Area__c
           FROM Subject_Area_Expert__c
           WHERE Contact__r.Title = 'Solution Architect'
                 AND Start_Date__c <= TODAY
                 AND End_Date__c >= TODAY];
Set<Id> subjectAreaIds = new Set<Id>();

for (Subject_Area_Expert__c e : experts) {
    subjectAreaIds.add(e.Subject_Area__c);
}

List<Project__c> = [SELECT Id 
                    FROM Project__c 
                    WHERE Id IN (SELECT Linked_Project__c 
                                 FROM Subject_Area__c 
                                 WHERE Id IN :subjectIds)];

The same Apex/SOQL pattern can be extended to cover a more or less arbitrary depth of complexity, up to the point where governor limits are implicated.

Child Aggregate Query

The child aggregate query is suitable for locating (and ordering) parent records based on a non-zero count of child records, optionally matching the child records against some criterion. As such, it can cover our Cases 2 and 3 well. It’s not suitable for any situation where parent records without children should be included. It can provide a count of child records matching specific criteria, but doesn’t return the child or parent record data itself. The query can express some filtration that would otherwise need to be performed in Apex.

SELECT count(Id), Linked_Project__c 
FROM Subject_Area__c 
WHERE Name LIKE '%Industry%' 
GROUP BY Linked_Project__c 
HAVING count(Id) > 2 
ORDER BY count(Id) DESC

This query will give us a List<AggregateResult> with the Ids of every Project__c having at least two associated Subject_Area__c records whose Names contain ‘Industry’, in descending order of the count of such records. We’ll have to re-query to get more information about the Projects themselves or otherwise post-process the AggregateResult list in Apex.

In large data volume environments, selectivity may be a challenge because the filters on the child object that are relevant to the desired parent object set may not be especially narrow relative to the overall child object data set. Testing and query planning will help pinpoint any performance issues.

Headed for the Next Milestone

For 2018, I’ve set my sights on becoming a Salesforce Certified Application Architect. (My 2017 goal was Platform Developer II, which I did complete). To reach Application Architect, I need three certifications: Sharing and Visibility Designer, Data Architecture and Management Designer, and App Builder.

Yesterday, I passed the first milestone: Certified Sharing and Visibility Designer, my first architect-tier certification.

Sharing and Visibility Designer

It was a challenging exam and pushed me to study and implement a number of areas of functionality, like Territory Management, with which I hadn’t previously gotten hands-on experience. I’m excited to tackle Data Architecture and Management Designer for the second half of the year.

Talk on Continuous Integration Now on YouTube

My presentation from PhillyForce ‘18, “Continuous Integration with Salesforce DX: Practices and Principles for All”, is now available on YouTube.

This talk draws on several past articles published here:

Some additional resources and examples are available on my GitHub:

  • circleci-sfdx-examples, a compendium of CircleCI/Salesforce DX examples, including a basic project, using the Lightning Testing Service, testing against multiple org shapes, and using PMD static analysis.
  • sfdx-simplesalesforce, demonstrating how to test integrated code written in Python with simple_salesforce via Salesforce DX scratch orgs.
  • septaTrains, my toy Lightning project (ever wanted your SEPTA regional rail commute on your Lightning homepage?) and testing project for different CI and automated testing solutions.
  • DMRNoteAttachmentImporter, a slightly more useful package also building with SFDX on CircleCI.