Admin Tools vs Coding: Data Loader and the Secret of the Anti-Join Part One
Admin Tools vs Coding: Data Loader and the Secret of the Anti-Join

Admin Tools vs Coding: Data Loader and the Secret of the Anti-Join Part One

11/29/2019 by Mario Di Genio
In the first of this two-part series, we’ll take a look at how an admin can retrieve a list of records with point-and-click features.

Lists and reports are a fundamental part of any software system. They allow users to access data, know the current status of their organization, spot trends early, and make the best decisions for business. Data volume factors in heavily when it comes to this kind of analysis, so the ability to filter and sort is essential to the process.

Salesforce provides list views, reports, and dashboards within an intuitive point-and-click interface. Sometimes there is required filter criteria that is not easy (or possible) to build with admin tools. Whether or not you can drill down to certain fields to filter by will depend on the relationship between the objects and how they are defined. In this case study we’ll review the limitations of certain tools and workarounds to get the information we need.

The Request

Our client wants to get the ID and Name of all Household Accounts that no Contact or Opportunity is related to. The desired output is a .csv. Ultimately, these Accounts will be deleted. 

The Analysis

The 4 things we need to remember for our implementation are:

  • Account Record Type Name = Household
  • Account has zero Contacts directly related to it (via AccountId lookup field in Contact object)
  • Account has zero Opportunities directly related to it (via AccountId lookup field in Opportunity object)
  • Output should be csv.

The Research

What are List Views and Reports?

A list view selects records that meet a user-defined criteria and displays them in rows and columns. List views can be easily accessed from object home pages (1). The user can choose the fields to display as columns (2), the filter criteria (3), sort the results (rows), and as of Lightning include a chart (4), and display all of it in Kanban mode (5).

A report is like a list view with additional powerful capabilities. Reports reside in the Reports tab.

In a report, a user can determine which fields to display as columns (1-2-3) based on the relationship between a primary object and its related object(s). This differs from list views that operate on a one-object basis. With reports the user can group results (4), sort the results (rows) (5), and include a chart (6), and enter filter criteria.

When to Use List Views or Reports?

List views are either paginated (as in Classic) or loaded on demand (as in Lightning), which means they are not meant to be used with a large amount of results, as you wouldn’t be able to see them all at once. They are meant for an easy snapshot of a limited group of records that you need quick access to. They show data from only one object and not from objects related to it. They are also not exportable to formats such as csv or Excel.

Reports, on the other hand, show all records that match the filter criteria at once, no matter how many there are, and let you export all records into a file if there are more than 2,000. Reports will show fields based on the relationships present between a primary object and its related object(s). This makes it possible to view different information from related records in reports, opposed to list views that only work on a one-object basis. 

The ability to group results provides an additional way to order the data, allowing you to examine it in an easy-to-understand format. And lastly, reports can be exported in .csv and Excel format, and scheduled to run and be emailed at particular times, therefore sharing the resulting insights with others.

The Admin Tools Way

Now that we know what options we have for viewing records from Salesforce, let’s see which admin tool will work best for this case.

If we want to know the count of Contacts and Opportunities for each Account (to find out which one has zero in both), we can look to roll-up summaries to do that for us. Roll-up summary fields can be created on:

  1. Any custom object on the master side of a master-detail relationship
  2. Any standard object on the master side of a master-detail relationship with a custom object
  3. Opportunities using the values of opportunity products related to the opportunity
  4. Accounts using the values of related opportunities
  5. Campaigns using campaign member status or the values of campaign member custom fields

Out of all the above, only the fourth option works for us to directly get the count of Opportunities for each Account. We cannot create a roll-up summary on Account from the Contacts. Accounts using the values of related Opportunities gets us half-way to our goal.

To get the count from the Contacts (to see if there is any), we’ll need to pull their information. Since it is only possible to retrieve information in list views from one object (and we cannot have a roll-up summary from the Contacts), they cannot be used to get the results we need. Also, list views cannot be exported into a csv, so reports provide a direct way to export results from multiple objects to the .csv format needed.

Report Types that exist by default in any Salesforce org are always defined as “one object that has records of another object”. For example:

We need to identify Accounts without Contacts, and to do so we can create our own Report Type:

Go to Setup, navigate to Report Types and click New Custom Report Type.

Select Account as the Primary Object and the option Deployed:

In the next screen is the crucial step to achieve our goal. Click “Click to relate another object”, select Contacts, and make sure you mark the option “"A" records may or may not have related "B" records.”. This last option is the one that will make it possible in a report to see the Accounts with no Contacts.

Once we have saved the Report Type we can now create our Report. After creating a report based on the Report Type just created, clean up the filters so that it shows all accounts from all time.

Add the Account ID as a column, a filter for the Record Type = “Household” and a filter for Opportunity Count = 0 to the report:

If you group by the Full Name of the Contact, all Accounts with no Contacts will be at the top of the report.

If you export the report as Details Only in csv format.

You can manually delete all the rows with Full Name empty and you will have the result: all Account IDs with no Opportunities and no Contacts associated.

There were several pieces along the way we had to have in place to get our results:

  • Have the possibility to create the roll-up summary for at least one of the criteria
  • Create the Report Type for the other criteria
  • Be able to group by null values in the report.

In addition, some manual work was needed in order to get to these results.

If we didn’t have a way to get the roll-up summary for the Opportunities (or if we needed to filter by other related objects that would not allow us to create roll-up summaries for), then we would have had to:

  1. Build a report that gets all Accounts in the system
  2. Build a report that gets all Accounts in the system that are referenced by a Contact.
  3. Build a report that gets all Accounts in the system that are referenced by an Opportunity.
  4. Remove from the first report the ones from the second report.
  5. Remove from the first report the ones from the third report.

There’s no way to do this solely with report filters in one report, so this would involve a lot of manual work, which could be done if you export the results of all reports and work some Excel magic to do the filtering. 

UPDATE: Despite searching, I hadn’t found a way to do this until after the publication of this post and received some great feedback from a reader. The out-of-the-box solution to this issue is adding Cross Filters to the report:

You can apply them in any report without the need to have all related objects included in your Report Type. This would meet our requirements in one single report.

The following video shows you how to easily add Cross Filters to any report: Video here

What about DLRS?

DRLS may provide a way to count the Contacts related to an Account. So if you set this up with a scheduled calculation, then you can have a report that only shows the Accounts with 0 in both values (Opportunity and Contact counts). This is helpful if you have the package already installed and ready to be used. But if you have to do it in a production environment without having to install an entire package for this, we need to look for another solution.

In the second post of this series we’ll dive into how you can use some coding to create a solution to the same problem. Stay tuned...

Make it Happen

Here are some interesting ideas about list views and reports you can vote for:

Resources

Have you had any cases in which reports and lists have not been enough to get what you want? Tell me all about it in the comments below, in the Salesforce Trailblazer Community, or tweet directly at me @mdigenioarkus.

More by
Mario Di Genio