San Francisco, CA. Happy.

SOQL

A fun thing about starting new jobs is being exposed to different tools & ways of doing things. This is my first job where my computer is a Mac instead of PC. A popular tool in the toolkit of Salesforce Admins is DemandTools. It’s a pretty nifty software but at its core it’s a GUI for using SOQL to find and update records. And it only runs on PC. What’s an enterprising Salesforce user supposed to do?

I was introduced to SoqlX, an open source tool for Mac users to edit, update, and query data. I’m going to walk through some of things I’ve learned since I’ve started using it. Between SoqlX and Dataloader, I’m pretty set and don’t miss DemandTools at all.

Ever since I started using SoqlX I pretty much avoid using Salesforce’s report builder. I find SOQL faster and easier overall, plus the added benefit of being able to edit a record directly within the UI of SoqlX.

Getting familiarwith SoqlX

The SoqlX UI

I use the tool 100% for querying data, but you can also explore the schema and run anonymous Apex. All your objects (and fields!) are on the left. You write your query in the white box at the top.

The Recent button in the upper right shows you your past ~3 most recent queries. Which is super helpful if you forget to save your queries.

You can save your queries (DemandTools users might know these as “scenarios”). I highly recommend doing this when you think you’ll use it again. Especially if you took the time to write out a bunch of custom fields.

SOQL stands for Salesforce Object Query Language, it’s basically the command you tell Salesforce to give you the data you want. If you’ve ever used report builder in Salesforce it’s SOQL that’s running it under the hood.

Here’s a simple query, where I’m asking Salesforce to give me all the leads where Smith is the Last Name.

SELECT Id, LastName, State
FROM Lead
WHERE LastName = 'Smith'

You can edit records in-line too. So I could change MICHIGAN to MI.

Once I run the query, I’m given all the records that fit the criteria, up to 2000.

You can also count how many records that fit a certain criteria as well by modifying the query (the answer is 1,362)

SELECT COUNT()
FROM Lead
WHERE LastName = 'Smith'

SOQL is pretty powerful. If you aren’t super familiar with it I recommend reading SFDC’s developer docs, which are quite clear and useful.

Searching against the database

This is obviously one of the main benefits of DemandTools, but can be achieved using SoqlX as well if you have a little bit of a patience and a coworker (thanks Leon!) who can help you write a nifty Excel formula.

I usually need to search against Salesforce using email addresses. This is a common situation where a user extracts a list, but then they want to know more attributes about the records.

“Do you have record IDs?” I ask. “No just emails,” they say with a shrug. 🙄 (Note: I’d use the same steps below if they had record IDs, I just prefer them over emails because they are unique.)

When writing a query using emails, it needs to be formatted like this.

SELECT ID, FirstName, LastName, State
FROM Lead
WHERE Email in ('testemail@gmail.com','testemail2@gmail.com')

You don’t write an equals sign because you’re looking for many records. The syntax is pretty straight forward, but what do you do when you have a 100+ emails? Certainly you’re not going to write them out by hand.

The Excel formula allows you to take a row of emails and immediately format them so they are read-for-SOQL. I made a Google Sheet that you can copy here.

The formula concatenates all your emails while respecting the single quote comma syntax that SOQL wants. Note: SOQL has a 20,000 character limit for queries so keep that in mind–if you try to shove too many emails into a single search you’ll get an error.

Let me know if you try out any of the above or if you have better tips that I’m missing! 👋🏻