Can we trick salesforce into doing reportable dynamic rollups?

Rollups. Why is it always rollups.

I hate rollups. I hate building them. I hate how they slow down things in Salesforce. I hate how they can accidentally block users from editing records. And I especially hate debugging them. “Why is this roll up field wrong?” I don’t know, there are so many points of failure when it comes to a roll up. Maybe the job hasn’t run yet and someone just added another record. Maybe an error was thrown and a record wasn’t accounted for. But sure, I’ll throw away a few hours to answer your question and by the time I have an answer the rollup corrected itself. I digress.

And I say all this as a contributor to the Declarative Rollup Summary open source project. I get why we need rollups. Not every org has access to an analytics tool and is limited to Salesforce reporting. And sometimes an orgs needs to answer the question “How many Account records have donated more than $1000?” and the person asking the question needs to do this with a report. So you build a rollup summary because the alternative is building a custom reporting tool and that’s it’s own set of problems. That’s why DLRS is such wonderful tool. I just hate it that it has to be like this. Or does it?

SOQL to the rescue

What infuriated me for years was that SOQL can actually do this for you with aggregate queries. For example, if you captured your donations for an Account in a child object called Donations, you could do the following with SOQL:

SELECT Account__c, SUM(Amount__c) FROM Donation__c GROUP BY Account__c HAVING SUM(Amount__c) > 1000

But the goal here is to make this reportable. Sure, you might get away with having devs and admins run these SOQL queries for the business, but part of scalability is providing a mechanism for self-service. So you make the rollup field.

Enter the Apex Connector Framework

I was playing with the Apex Connector Framework to pull some data into Salesforce from an API so that they could be reportable. The flow of this framework is as follows

  • Define fields on an external object (i.e. how will this object be represented in Salesforce?)
  • Extend the framework classes, which mainly allow you to interpret a SOQL query, sent the search criteria to an API with a callout
  • Parse the results and map to rows for the external object.

After you do this, the external object becomes reportable! So I thought, what if instead of doing a callout we just did the aggregate query from above and mapped those results to the external object. In other words, I have an external object called AccountRollup__x with a field TotalDonations__c, and every time it is queried, it does an aggregate of Account records?

Turns out, it kind of works and it’s reportable! You can run a report using filters on the TotalDonations field as if you were reporting on a regular rollup field.

See this repo for code samples of a prototype that does just this you can try in a scratch org: https://github.com/seanpat09/live-rollups

Caveats

I ran into a few issues while building this prototype.

  • For some reason, I could not display fields related Account fields on the report as I was getting a system error with a gack. That’s why I suggest linking to the summary field in the report with the Name column, which can give you access to the Account via the AccountRecord lookup field
  • When I tried to make the rollup summary record a parent to the Account, the report would just break, not allowing me to do the filtering at all
  • You’re limited to whatever SOQL limits you are normally limited to, so if you have a really high volume org, this might not work at all.
  • I’m not sure of the licensing on the Apex Connector Framework, but I was able to use it in an Enterprise org that does NOT have the license for Salesforce Connect.

So what does this mean? I’m not really sure yet to be honest. But if this works and scales scales, it might be another option to rollup summaries without having to run background jobs to maintain the data! Instead of debugging a job, you’re debugging SOQL queries instead, which might be much easier to maintain for your team.

Design Patterns in Salesforce: Abstract Factory

Design patterns are powerful tool to build reusuable code, but they are hard to implement in without examples and use cases. This is a series that follows the book Design Patterns: Elements of Resuable Object-Oriented Software a provides examples that relate to Salesforce

Abstract Factory

The Abstract Factory design pattern is useful for creating related objects that follow an interface. I find this pattern particularly useful for building resuable Lightning Web Components. Let’s use this pattern to build a resuable custom related list.

Use Case: Viewing grandchild records on a detail page.

Out of the box related lists in Salesforce are great for displaying child records on a detail page, but what if you wanted to display all the grand child records? Imagine a national charity that oversees local branches. Each branch is represented as an Account record, and the volunteers for that branch are represented as Contact records. When a volunteer is onboarded, there are several onboarding tasks required before they can start any work, such as a background check, filling out emergency contact info, and creating a user in their Salesforce Community.

Region managers oversee local branches in their assigned region, so in addition to other information on the Account, the Region Managers want to see all the outstanding tasks for all the Contacts on the Account detail page in a table (for the sake of this post let’s pretend this is the best solution).

A non-resuauble approach

If you didn’t care about resuability (which is ok if you don’t have that bandwidth!), you could build an LWC like this (I can’t guarantee this compiles)

//contactTasks.html -->

<template>
  <lightning-datatable
    key-field="id"
    data={data}
    columns={columns}>
  </lightning-datatable>
</template>

//contactTasks.js

import { LightningElement } from 'lwc';
import getTasks from '@salesforce/apex/ContactTasksController.getTasks';

const columns = [
  { label: 'Subject', fieldName: 'Subject' },
  { label: 'Assigned To', fieldName: 'WhoId'}
];

export default class ContactTasks extends LightningElement {
  data = [];
  columns = columns;
  recordId;

  async connectedCallback() {
      const data = await getTasks({ accountId: this.recordId });
      this.data = data;
  }
}

//ContactTasksController

public class ContactTasksController {
  @AuraEnabled
  public static List<Task> getTasks(Id accountId) {
    List<Task> allTasks = new List<Task>();
    for(Contact aContact : [SELECT (SELECT Subject, WhoId FROM Tasks) FROM Contact WHERE AccountId = :accountId]) {
      allTasks.addAll(aContact.Tasks);
    }

    return allTasks;
  }
}

A solution like this is fine, but in the future you might get a similar request where you want to display all tasks for all the Contacts related to an Opportunity. Or maybe just some other data. You could create a separate LWC with a separate Apex controller, but you might notice that the LWC isn’t doing all that much, just displaying data. Most of the differences is in building the columns and getting the data. Wouldn’t it be nice to abstract out the logic that builds the table data? Like some kind of abstract factory?

The Abstract Factory Pattern

The abstract factory pattern can be broken down into 3 parts:

  • A interface that specifies what an implementation must be able to build.
  • A consumer that can request a specific implementation, but can handle any implementation of the interface
  • Concrete implementations that fulfill the interface

So in this case we need an interface that can build the columns and rows for a table, a component that can specify which columns and tables to display and actually display them, and then 1 or more concrete implementations with the business logic. How would this look?

First let’s create some objects to define what we’re building:

public class CustomListColumn {
  public String label;
  public String fieldName;

  public CustomListColumn(String label, Object fieldName) {
    this.label = label;
    this.fieldName = fieldName;
  }
}

public class CustomListRow {
  public Object value;

  public CustomListRow(Object value) {
    this.value = value;
  }
}

Next our interface for our factory.

public interface ICustomListFactory {
  List<CustomListColumn> buildColumns();
  List<CustomListRow> buildRows(Id parentId);
}

And then our concrete implementation of that factory

public class AccountContactsTaskListFactory implements ICustomListFactory{
  public List<CustomListColumn> buildColumns() {
    List<CustomListColumn> columns = new List<CustomListColumn>();
    columns.add(
      new CustomListColumn('Subject', 'Subject'),
      new CustomListColumn('Assigned To', 'WhoId')
    );
  }
  public List<CustomListRow> buildRows(Id parentId) {
    List<CustomListRow> allTasks = new List<CustomListRow>();
    for(Contact aContact : [SELECT (SELECT Subject, WhoId FROM Tasks) FROM Contact WHERE AccountId = :parentId]) {
      for(Task aTask: aContact.Tasks) {
        allTasks.add(new CustomListRow(aTask));
      }
    }
    return allTasks;
  }
}

Next let’s make our component more generalized. It now gets the columns in addition to the rows from the Apex controller. It also passes a listType parameter to specify which implementation should be used

//customRelatedList.html -->

<template>
  <lightning-datatable
    key-field="id"
    data={data}
    columns={columns}>
  </lightning-datatable>
</template>

//contactTasks.js

import { LightningElement } from 'lwc';
import getTableData from '@salesforce/apex/CustomRelatedListController.getTableData';

export default class CustomRelatedList extends LightningElement {
  data = [];
  columns = columns;
  recordId;

  @api
  listType

  async connectedCallback() {
      const tableData = await getTableData({ recordId: this.recordId, listType: this.listType });
      this.data = tableData.rows;
      this.columns = tableData.columns;
  }
}

Finally we create a controller that will handle getting the correct implementation based on the listType passed from the LWC

public class CustomRelatedListController {
  @AuraEnabled
  public TableData getTableData(Id recordId, String listType) {
    ICustomListFactory factory = getCustomListFactory(listType);
    
    TableData data = new TableData();
    data.rows = factory.getRows(recordId);
    data.columns = factory.getColumns();

    return data;
  }

  private ICustomListFactory getCustomListFactory(String listType) {
    if(listType === 'AccountContactsTasks') {
      return new AccountContactsTaskListFactory();
    } else {
      throw CustomRelatedListControllerException('List Type: ' + listType + ' not supported');
    }
  }


  public TableData {
    @AuraEnabled
    List<CustomListColumn> columns;
    List<CustomListRow> rows;
  }

  public CustomRelatedListControllerException extends Exception {}
}

Benefits

This seems like a lot of extra code and for just a single use case it probably is. The value comes with its reuse. Let’s add another table, this time displaying Contact information related to an Opportunity. Specifically, the Contact’s full name, parent account, grandparent Account (i.e. up an additional Account level in the hierarchy), and the Account website.

Here’s the concrete implementation:

public class OpportunityContactListFactory implements ICustomListFactory{
  public List<CustomListColumn> buildColumns() {
    List<CustomListColumn> columns = new List<CustomListColumn>();
    columns.add(
      new CustomListColumn('Full Name', 'fullName'),
      new CustomListColumn('Parent Account', 'parentAccount'),
      new CustomListColumn('Grandparent Account', 'grandParentAccount'),
      new CustomListColumn('Website', 'website')
    );
  }
  public List<CustomListRow> buildRows(Id parentId) {
    List<CustomListRow> contacts = new List<CustomListRow>();
    for(OpportunityContactRole ocr :
      [ SELECT Contact.Fullname,
               Contact.Account.Name,
               Contact.Account.Account.Name,
               Contact.Account.Website
        FROM OpportunityContactRole WHERE OpportunitId = :parentId]
    ) {
        Map<String, String> contactRow = new Map<String, String>{
          'fullName' => ocr.Contact.FullName,
          'parentAccount' => ocr.Contact.Account.Name,
          'grandParentAccount' => ocr.Contact.Account.Account.Name,
          'website' => ocr.Contact.Account.Website,
        }
        contacts.add(contactRow);
    }
    return contacts;
  }
}

And then we update the controller to handle this new list type:

  private ICustomListFactory getCustomListFactory(String listType) {
    if(listType === 'AccountContactsTasks') {
      return new AccountContactsTaskListFactory();
    } else if(listType === 'OpportunityContacts') {
      return new OpportunityContactListFactory();
    } else {
      throw CustomRelatedListControllerException('List Type: ' + listType + ' not supported');
    }
  }

And now our LWC can handle a different use case given a different listType API value, without having to change any of its code! Arguably the getCustomListFactory method should be pulled into its own class so that the controller also does not have to be updated either.

I find myself using this pattern often with LWCs to take advantage of the reusability of components. It can take a little extra upfront work, but can save you a lot of time in the long run in terms of code you don’t have to write and also for creating small testable components.

Pull Requests are an Essential Part of Security

Separation of Duties

Before I became a software engineer, I studied accounting and even worked as an auditor for a brief period. Not much accounting knowledge remains, but a few nuggets stayed with me, particularly the internal control known as “Separation of Duties” (not to be confused with the design principle “Separation of Concerns”). To summarize, no one person should have complete control over an entire transaction. For example, the person collecting cash from a customer for a bill should not be the one to also record the transaction and deposit the cash. Otherwise, that person could collect funds, credit the customer’s account, and take a little off the top before making the bank deposit. Having another person in this process does not eliminate the risk, but colluding with someone else is harder to hide. You know what they say about the only way for two people keep a secret.

What does this have to with code and pull requests? Pull requests serve a similar security purpose. Not only do pull requests provide the nice to haves of allowing for asynchronous collaboration, a second set of eyes, etc, they are also a very important auditing artifact and should be considered an essential component of your delivery process.

Code controls business logic, often at a pace magnitudes faster than what any human can keep up with. And because of the complex nature of writing code, it poses a huge security vulnerability where malicious behavior can easily be obfuscated. For example a fork bomb looks like an inconspicuous line of code, but can actually crash your entire system. Because of which, every line of code that makes its way into any system of importance should be reviewed by someone else. Pull requests not only provide the mechanism to do this review, but also create the audit trail for every change.

Components of a Pull Request

Let’s examine the components of a pull requests and how they contribute to security.

First and foremost a pull request aggregates proposed changes via its commits. By using git blame you are able to find the latest commit attributed to every line in the codebase. With this you are able to follow the history of any line change. If you ever wondered why a change was made, following the commit history is a great start. With GitHub, you can even see which pull request was the one that brought that commit into the current branch.

Not only can you see the history of changes, but the commits attribute the changes to the person that introduced it. Commit history great, but also being able to talk to the person that made the change can help provide more context that’s not in the code.

So far these are just things you get from a commit history alone. Pull requests show their value in the additional layer of tracking they provide. With a pull request, we can see the reviewer that approved the merge. Now you have at least two people involved in the process of getting code into production (provided that you have configured your version control to not allow self-approvals).

The pull request itself also provides an area to have a discussion - on the features, the code decisions, tech debt introduction. By doing this in the pull request, this discussion is linked very closely to the code itself. You could have the discussion happen in some other document, but several months down the line that document may get lost to some obscure folder in your company’s google drive.

For extra credit, you could also tag pull requests with the work item associated to the pull request, so you know why a change was made.

Security Benefits and Beyond

With all of these ingredients together, you can trace from a single line change who made the change, approved the change, approved the item to be worked on at all, and any context capture in any comments for the PR! And with all theses duties separated by person, you’ve mitigated a lot of the risk of any one person trying to act maliciously within an organization.

As an auditor, this kind of paper trail is gold. In fact, I’ve been involved in security audits that went super smoothly because this process was in place. In addition to security benefits, it was so useful to be able to see the context of something far into the future. Have you ever looked at code that YOU wrote and wondered why you took some odd approach? With this trail you can find the context of the original change, hopefully with an explanation of why.

Constructive Review vs. Fodder for Punishment

I want to emphasize that this internal control is not about placing blame. Accountability is important, but if this process is wielded as a threat, your team will be reluctant to to adhere to it. Should something happen, a bug, security breach, or even just a missed feature, this process should be used to trace the source of the problem. Afterwards a constructive, blameless discussion can happen where processes can improved. What happened in the past happened - the only thing to do is move on and learn from it. If leadership uses this process to punish contributors for their mistakes, the engineers will find ways to not be associated to the changes. Like any tool, its value to the organization highly depends on the culture those using it.

There are not many things that I consider essential for an engineering org, but pull requests are something that is a must have for me, and possibly a deal breaker for me if an organization refused to use them without a much better alternative. Even when I was the only working on a project professionally, I STILL used pull requests so that I could have the benefits of the audit trail, both for my future self or anyone else who would have to inherit my work after I left.