How to Use Generative AI Tools to Write SOQL Queries

Salesforce Object Query Language (SOQL) is a powerful tool that allows you to retrieve data from Salesforce. You can use SOQL to query any Salesforce object, including custom objects, custom fields, and user permissions like profile and permission set perms.

As a Salesforce Admin, I know that writing SOQL queries can be a pain. Not only do you have to know the syntax, but you also have to make sure your queries are efficient and accurate.

That’s where generative artificial intelligence (AI) tools come in. Generative AI tools are a type of AI that can be used to generate text, code, and other creative content. There are a number of different generative AI tools available, but I’ve found that both Bard and ChatGPT are very helpful for writing SOQL queries. In this blog, I’ll show you how to use prompts to generate SOQL queries with generative AI tools, and provide some query examples and resources to get started using SOQL in your org.

Writing SOQL queries with generative AI tools

To start, I need to provide the generative AI tool with some information about the query I want to generate. For example, I might tell the tool that I want to generate a query that returns all of the open opportunities. So, if I prompt:

Write SOQL that returns all open opportunities in Salesforce.

ChatGPT responds:

To retrieve all open opportunities in Salesforce using SOQL (Salesforce Object Query Language), you can execute the following query:

SELECT Id, Name, StageName, CloseDate, Amount
FROM Opportunity
WHERE IsClosed = false

This query selects the Opportunity records and retrieves their Id, Name, StageName, CloseDate, and Amount fields. The WHERE clause filters the results to only include open opportunities by checking the IsClosed field, which should be set to false for open opportunities.

Please note that this query assumes the standard Opportunity object in Salesforce. If you have a custom object or have customized the Opportunity object, you may need to adjust the query accordingly.

Bard offers a similar SOQL:

SELECT
Id,
Name,
StageName,
Amount,
CloseDate
FROM Opportunity
WHERE IsClosed = false

However, this is where prompt writing and critical thinking can be applied. Using this as a starting point, you might want to tweak what the response would be. For instance:

Write SOQL that returns all open opportunities in Salesforce that includes all standard fields and uses StageName to filter results.

And ChatGPT responds with:

SELECT Id, Name, AccountId, Amount, CloseDate, CreatedBy.Name, CreatedDate, LastModifiedBy.Name, LastModifiedDate, StageName
FROM Opportunity
WHERE StageName != 'Closed Won' AND StageName != 'Closed Lost'

One thing to note is that ChatGPT doesn’t know your org shape and won’t know what custom fields you have. As a conversational model, though, you can tell it:

My Salesforce org includes a custom field with the API name of NonProfit__c for Opportunity. It’s a boolean field. Please include it in all future SOQL queries on Opportunity.

And ChatGPT will respond:

Certainly! To include the custom field NonProfit__c in all future SOQL queries on Opportunity, you can modify the query as follows:

“SOQL
SELECT Id, Name, AccountId, Amount, CloseDate, CreatedBy.Name, CreatedDate, LastModifiedBy.Name, LastModifiedDate, StageName, NonProfit__c
FROM Opportunity
WHERE StageName != 'Closed Won' AND StageName != 'Closed Lost'“

By adding NonProfit__c to the SELECT statement, you include the custom field in the query results.

You can now use this modified query to retrieve all open opportunities in Salesforce while including the NonProfit__c custom field. Remember to adjust the field names accordingly if you have different custom field API names in your Salesforce org.

And now, when I use the same prompt as before, it offers the following SOQL:

SELECT Id, Name, AccountId, Amount, CloseDate, CreatedBy.Name, CreatedDate, LastModifiedBy.Name, LastModifiedDate, StageName, NonProfit__c
FROM Opportunity
WHERE IsClosed = false

Using SOQL to report on user permissions

One of my favorite areas to use SOQL as a Salesforce Admin is to report on user permissions. Using the SOQL Query tool in Code Builder or the Developer Console, you can report on permissions assigned to a user by permission sets, profiles, object, and field. This can be useful when auditing user access or object security. Here are some examples of user permission SOQL queries written with the help of ChatGPT.

Check user permissions on an object

This SOQL query checks a user’s permission level on a specific object in Salesforce.

SELECT PermissionsRead, PermissionsCreate, PermissionsEdit, PermissionsDelete
FROM ObjectPermissions
WHERE ParentId IN (SELECT Id
FROM PermissionSet
WHERE PermissionSet.Name = 'Your_Permission_Set_Name')
AND SObjectType = 'Your_Object_Name'
AND PermissionsRead = true

Check user permissions on a field

This SOQL query checks a user’s permission level on a specific field in Salesforce.

SELECT PermissionsRead, PermissionsEdit 
FROM FieldPermissions 
WHERE ParentId IN (SELECT Id 
                   FROM PermissionSet 
                   WHERE PermissionSet.Name = 'Your_Permission_Set_Name')
AND SObjectType = 'Your_Object_Name'
AND Field = 'Your_Field_Name'
AND PermissionsRead = true

See which permission sets give Edit access for a particular field, rather than looking at it by user

SELECT ParentId, Parent.Name, Parent.Type, Field, PermissionsEdit, PermissionsRead, SobjectType
FROM FieldPermissions
WHERE Parent.IsOwnedByProfile = true 
AND Field = 'ADM_Work__c.Subject__c'
AND PermissionsEdit = True

Identify which users were assigned managed packages

I could export the results and then do a data load to delete people I don’t want to have it anymore.

SELECT Id, UserId, PackageLicense.NamespacePrefix
FROM UserPackageLicense 
WHERE PackageLicense.NamespacePrefix = 'YOUR_PREFIX_HERE'

Start generating more efficient SOQL queries today

With generative AI, I can quickly and easily get started with sample SOQL queries, not have to worry about the syntax, and have a conversation with the AI to adapt to my specific needs. If you’re an admin who only needs SOQL from time to time and finds themselves jumping from docs and references to start, then using generative AI is a great alternative.

Here are some additional tips for using generative AI tools to write SOQL queries.

• Be specific. The more specific you are in your instructions to the tools, the more accurate and efficient the queries they generate will be.
• Use examples. If you can provide the tools with examples of the types of queries you want them to generate, they will be able to generate better queries.
• Test your queries. Once the tools have generated a query, be sure to test it to make sure it returns the results you expect.
• Remember that you can leverage SOQL in Code Builder, SFDX, or Developer Console to see quick results.

Resources

• Salesforce Admins Blog: Be an AI-Minded Salesforce Admin
• Salesforce Admins Blog: Understanding What Generative AI Means for Salesforce Admins
• AppExchange: Code Builder (Beta)
• External Site: YouTube: Getting Started with Code Builder (Beta)
• Salesforce Admins Blog: Video: Check Lightning Permissions with SOQL and the Dev Console