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

12/10/2019 by Mario Di Genio

In the second post of this two-part series, we’ll dive into how an admin could retrieve complex record lists with the help of some simple coding.

In the first part of this series we explored how to retrieve records using lists and reports, and their limitations to solve our problem. Now let’s explore the coding way. As a quick refresher, 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 four things we need to remember for our record list are:

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

The Coding Way

How Does a List View or Report Work in the Background?

When you create a list view or report in Salesforce, the criteria you enter is essentially a question you’re asking, and the results returned are the answer. In order to ask a question, you have to speak the database’s language. As seen before, Salesforce comes with built-in translators (either the list view or the report builder), allowing you to ask your question to the database through a point-and-click interface, and get the answers you need about your data.

When you enter your criteria, Salesforce transforms it into a query (your question in database language). This language in Salesforce is called Salesforce Object Query Language, or SOQL in short, and you can use this to read saved records. The basic syntax for any query is:

SELECT fields FROM ObjectName [WHERE Condition]

SELECT clause: Fields is the name of the fields you want displayed as columns in your results (separated by comma).
- For example: Name, Id, BillingAddress.
FROM clause: ObjectName is the name of the object from which you want to retrieve the results from. For example: Account.
WHERE clause: Conditions to filter by concatenated by logical operators (and, or). For example: BillingState = ‘NY’ and BillingCountry = ’USA’

In a query, the names are the API names of the fields and objects, the ones we can call the “real names” of the fields (that are in the database language); as opposed to the labels, which are just to show on screen to end users. For example: a field can have the label “Sales Amount” and the API name could be “Sales_Amount__c”, we would use the latter for a query.

If you go back to the figures to set up a report in our first post you will see a section of the list view or report builder that sets every clause of the query.

But why are we going so deep into this? If what report builder does is to put together a query based on the parameters we enter on screen, maybe there’s a way we can put together the query ourselves, directly in the database language, to get our results.

Remember the structure of our query is SELECT fields FROM ObjectName [WHERE Condition]

The following video show you how to locate the API names of the objects and how to build the clauses for the conditions in the Developer Console. Click to download this for stop-start player capabilities.

To sum up the main steps:

Get the Id of the Household Record Type from the url. From here we have our first condition: SELECT Id FROM Account WHERE RecordTypeId = '0121U000000FpLuQAK' This gives us all the accounts of Household Record Type.
We would need to get all the accounts that are referenced by a Contact. For that we can get the API Name of the lookup to Account field in the Contact: To retrieve the Accounts that are referenced by a Contact we have: SELECT AccountID FROM Contact.
We repeat the same process to get the Accounts that are referenced by an Opportunity: SELECT AccountID FROM Opportunity.
If we had an operator to say that the ID of the Accounts in our original query is not in those 2 groups we could resolve the problem. If we look at the reference of comparison operators for queries there’s one for that specific purpose.
So with the NOT IN operator, we can create the condition to exclude those 2 groups: NOT IN (SELECT AccountID FROM Contact) AND NOT IN (SELECT AccountID FROM Opportunity) We put it all together and the following query retrieves the Accounts of type Household that are not referenced by either Contacts or Opportunities: SELECT Id, Name FROM Account WHERE RecordTypeId = '0121U000000FpLuQAK' AND Id NOT IN (SELECT AccountID FROM Contact) AND Id NOT IN (SELECT AccountID FROM Opportunity)

In query language, when you get records and their related records, it’s called a join. When you get records that are not referenced by other records, it’s called an anti-join. The anti-join is the one that is usually not easily accessible in point-and-click tools, yet many times it is needed to perform certain tasks. And ain’t no joy like the anti-join.

How to Test our Solution

So how do we test this query? There are several ways to test and finally get the file with all the results we’re looking for.

Test with Developer Console

To test it out as in the video, we can go to the Developer Console. Then go to the Query Editor tab at the bottom, paste your query and click Execute.

Exporting is harmless, you won’t be causing any data loss or corruption. Worst case scenario the query is not correctly formed or accurate and the results are not the ones you expected.

If the query’s syntax is correct, then the results will appear at the top. Otherwise, it will tell you what you are missing, usually, it’s a comma between the fields to retrieve, or an operator in the conditions, or a parenthesis that is not correctly closed.

Test with Workbench

Another way is to use Workbench, an external tool provided by Salesforce to retrieve data.

Go to https://workbench.developerforce.com/login.php
Select your environment (Production or Sandbox).
The API Version is usually the one selected by default, so you don’t need to change it.
Check the “I agree to the terms of service” checkbox
Click Login with Salesforce.
Click Allow to allow Workbench to access the environment you are logged in. If you are not logged in yet, it will show you the Salesforce login page for you to enter username and password as usual.
Select Jump to: SOQL Query.
Select the object you want to query (in this case Account).
Click Select button
Workbench provides a point-and-click interface to create the query just like a Report Builder or List View. Paste the query you built in the “Enter or modify SOQL query below” box
Click on the Query button and you will see the results displayed below. Select Bulk CSV and click the Query button again. Workbench will generate a CSV file with all the results you can download.

Test with Data Loader

Install Data Loader in your computer (go to setup in your org and search for Data Loader, the downloadable program is available for Windows and Mac).
Open Data Loader.
Click the Export button.
Enter your credentials, the password must be your password concatenated with your security token. If you don’t have your security token, you can get it by email by going to the Setup in your org or to your Profile page and search for Reset my Security Token.
If the login is successful it will allow you to continue to select the object you are trying to query.
Select the location where you want to store the file once downloaded.
Click Next.
Data Loader provides a point-and-click interface to create the query just like a Report Builder or List View. Paste the query you built in the box.
Click Finish and the file will be downloaded to the location you selected

Make it Happen

Here are some interesting ideas about data loader you can vote for:

Resources

Data Loader
Data Loader
Use Data Loader to Export Data Trailhead
Workbench
Introduction to SOQL and SOSL
Developer Console Query Editor
Example SELECT Clauses
Write SOQL Queries Trailhead
Write SOSL Queries Trailhead
Execute SOQL and SOSL Queries Trailhead

How would you resolve this request: coding or admin tools? Tell me all about it in the comments below, in the Salesforce Trailblazer Community, or tweet directly at me @mdigenioarkus. Get on our newsletter list to have the top blog posts, including more Admin Tools vs Coding posts, sent direct to your inbox each month.

More by
Mario Di Genio