Skip to main content

Posts

Modify Salesforce Data from the Command Line

When doing Salesforce data loads, how often are you putting the same value in an entire column? I find this happening when I need to:
Move all accounts from one user to anotherAssign a set of leads to a specific userSet a field to a specific value Let's use the example from my other post: Salesforce Data Loads from the Command Line - Move all accounts owned by one user to another user. Magic Let's break down each piece of this: soql This is an alias I set for sfdx force:data:soql:query --query. It saves me from having to type this out each time and let's me query from the command line by just typing soql and my query.

This is a popular StackExchange post that talks about how to set an alias.
cat This command outputs the contents of a file. It is common to use this command with other commands by piping the output into another command. In our example, we are piping the output into the cut command.
This post gives more examples of how to use the cat command. cut This is a comm…
Recent posts

Salesforce Data Loads from the Command Line

Whether it's creating, updating or deleting records, it seems like we're constantly being asked to do data loads. Trouble is, that this takes away from our core project tasks. There must be a faster way...

Use Case: Move all accounts owned by one user to another user.
Export Data from the Command Line The first step in loading data is to prepare the import file. Sometimes we are given a nicely formatted import file, but let's pretend you need to pull the data out of Salesforce first.
In our scenario, we need to export the accounts that need to be moved. To do this, we need to get the Id of the user who currently owns the accounts.

You'll notice that this command starts with soql instead of sfdx force:data:soql:query --query. That is because I have set soql as an alias for the sfdx query command. Check out my other post on SOQL from the Command Line for more details on this technique.
Now that we have the Id of the user who currently owns the accounts, let's get the…

How to Install the Salesforce Command Line Interface

Follow these instructions to install the Salesforce Command Line Interface (CLI):
Open the Salesforce CLI download pageDownload the package needed for your operating systemAuthenticate with an org by running these commands: Here's a video walk-through:

Joins in SOQL

If you are familiar with SQL, the biggest gotcha when writing SOQL is how joins are done. You'll see the term Sub-Query used instead of JOIN because of how queries are done on the Salesforce platform. You can still do joins; it's just done a little differently than you're used to.
Sub-Query (Outer Join)In SQL, a join is when rows are selected from multiple tables and joined together on common columns. Thinking about your tables like a Venn Diagram is a great analogy for this.

Let's say you want to query all Accounts and also include their related Opportunities.

This is an example of an Outer Join because it selects all Accounts and will include their related Opportunities via the sub-query. If an Account does not have an Opportunity, it will still be returned here.

An Outer Join is similar to selecting one whole circle in a Venn Diagram, including the area that overlaps with the other circle. Sub-Query (Inner Join)An inner join can seem a little more complex. This is …

SOQL Basics - WHERE, ORDER BY and LIMIT

WHERE, ORDER BY and LIMIT are three of the most commonly used techniques in SOQL. In this post we'll dive into them one at a time and look at some examples.
WHERE When you need to filter records, you can use a WHERE clause to define which records should be returned. You can use the AND, OR and NOT operators to define your filter. If you need to use multiple filters you can use parentheses to control how the filters are applied.

Let's look at some examples:

This query will return all Opportunities with an Amount greater than $50,000.

This query will return all Leads with a Lead Source of Web that were created This Week. The THIS_WEEK keyword is an example of a Date Literal and makes filtering on date and datetime fields much simpler.

This query will return all Accounts in CA, NY or FL based on Billing State. The IN keyword allows you to filter for records that have any of the values you define. Using NOT IN is a common technique and it has the opposite affect - returning recor…

SOQL Gotchas

There are a few things that trip me up when writing SOQL. I hope that sharing these gotchas will save you from making the same mistakes. Maybe writing this post will even help me remember this stuff too 🤔
Single QuotesNOT LIKEFiltering a DateTime with a Date Single Quotes It seems silly, but single quotes always trip me up. For example, I get an unexpected token error when querying for an Account named: Brett's Awesome Company

This error happens because SOQL only sees: WHERE Name = 'Brett'.
The s Awesome Company' part is hanging off the end, making it an unexpected token.

We need to escape the single quote with a backslash (\) so SOQL can see WHERE Name = 'Brett\'s Awesome Company'.


SOQL requires you to escape single quotes and a few other characters. A complete list with some examples can be found in the docs.
NOT LIKE The LIKE comparison operator in SOQL will let you filter with a wildcard by using either the % or _ character. This is a powerful techniqu…

SOQL as a Summary Report

When learning SOQL, I found it helpful to relate things back to something I was familiar with. I'll be using Summary Reports to help explain how to use GROUP BY and GROUP BY ROLLUP in SOQL.
First, a Salesforce Report Let's start with a Salesforce Report of Accounts grouped by Billing State/Province:


To get this, I created a Report with the Accounts standard report type. Then I created the grouping, added some fields and ran the report. Writing SOQL I'll be executing my SOQL via the SFDX Command Line Interface (CLI). If you aren't familiar with this, check out my other post - SOQL from the Command Line
Feel free to use your preferred method to run your SOQL. If you don't have a preferred method, I talk about my favorites in the last section of another post - Why should I use SOQL? GROUP BY Here's our example Salesforce Report done via SOQL:


The key difference is that the Salesforce Report gives us data about each record and SOQL just gives us the totals for each…