Playing around with SoqlX

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 familiar with 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.

All my saved queries so far.

Command + S saves a query for you & Command + Shift + S saves the query results into a CSV.

SOQL 101

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! 👋🏻

Lead Source quirks in Salesforce

The lead conversion process always is a bit stressful. Fields from one object are mapping to another and you want to make sure everything is working correctly and nothing is getting overwritten. Additionally, one of the most valuable fields for sales and marketers starts with the conversion process.

That field? Lead Source.

Understanding where your leads are coming from allows your team to focus their time (and marketing dollars!) on those areas. If you go to a lot of Trade Shows and *think* they’re valuable how would you know without marking the Lead Source as such?

With all that said, here are some brief notes regarding the Lead Source field and the conversion process:

  • Leads, Contacts, and Opportunities all have a field called Lead Source. This field maps automatically upon lead conversion and is default Salesforce behavior.
  • Accounts have a field called Account Source and that populates with the Lead Source during conversion.
  • Opportunities will inherit a Lead Source one of two ways: If a user creates an opportunity during the conversion process OR if a user later creates an opportunity from that contact’s record. The contact will also be added as a Contact Role to that opportunity.
  • Account Source will not map to a new opportunity if a user creates an opportunity from the account record.
  • Upon merge of duplicates, make sure to preserve the Lead Source data from the oldest record. This is the closest thing you to truth.

⚠️ If your Account Source field is blank and you convert a lead to that account, the account will inherit that lead’s source. This is bad for data attribution. I have created a default Account Source field called “Unknown” which will prevent the lead conversion process from overwriting the source.

This goes against what the Salesforce docs say but I tested it and so did Amnon Kruvi and we had the same experience. Check out this thread for more:

Anything I missed or that you wish you knew when you starting managing the lead conversion process in Salesforce? Let me know!

In-Line Editing in List Views in Salesforce

I used to shy away from List Views because I thought of them as a less robust version of reports, so why bother with them? Recently I had a discovery that made List Views worth it: in-line editing.

Every week Industry Dive has a process where we confirm that the advertising placements we sold to a client actually ran. Our Ad Operations team confirms the placements ran and then communicates to our Accounting team that the placements can be invoiced.

In order to designate between a placement that we think ran vs. a placement that has definitely ran we have a checkbox on the record named “Reconciled?” Once this box is checked it means we’ve confirmed it has run and Accounting can invoice.

We usually run 80–100 placements a week, so checking that box for every placement would really suck. Enter: in-line editing! 🎊

Now a user can quickly reconcile placements without going into each individual record.

There are some important limitations and things to know about in-line editing and the permission to do so must be turned on by your Admin on your Profile.

If you have a situation where you are exporting a group of records to make a change on all of them that is the same, give in-line editing a try. It’s easier and less error prone than exporting and updating via Excel.