A Graphical SOQL Query Builder for Flow, plus an Execute SOQL Action

With Spring ’20, it’s possible to create a Process Action (i.e. an Invocable Action running in Flow) that can return any kind of SObject. That means it’s finally possible to create an Action that executes any SOQL query it gets passed.

The video below has a good introduction, but also check out this excellent demonstration of Execute SOQL being used with subqueries and merge fields and without a graphical front end.

In this instance, an ExecuteSOQL action has been configured to take a SOQL string as an input and return a collection of Accounts:

This milestone means it’s now worthwhile to build a Flow Screen component that can be used to generate SOQL queries:

The SOQLBuilder LWC can take an existing SOQL string and will try to parse it and display it in the UI. It will output a string based on whatever is generated in the builder.

Note that when configuring the Execute SOQL Action, it’s necessary to specify the object type that will be returned. Make sure that the Object Type specified in the SOQLBuilder matches. In other words, if you have an Execute SOQL action in your flow that’s hard configured to return Contact records, don’t pass it a string that queries on Accounts.

Inserting Dynamic Mergefields into your SOQL Queries

You can make a dynamic date part of your SOQL query, taking the date from upstream in your Flow. Just insert standard mergefield syntax: myquery='[SELECT Account.Name FROM Contact WHERE startdate__c > {!myUpstreamFlowDate}]’ . Important: Note that you do not delimit the mergefield with an additional set of quote marks.

Using Date Literals and Ids

Query on Id string literal:
SELECT LastName from Contact WHERE Id = '1123xxx333e3e'

Query on date literal:
SELECT Id from Contact WHERE CreatedDate = LAST_n_DAYS:14

Handles nested relationship queries in the WHERE clause

The action will first check to see if at least one of those queries exists and if true, will recursively search the nested queries and do the same formatted on those; If false, it will continue on with the single query search.

SELECT Id, Name,Status_DD__c,Region__c FROM Object2__c WHERE Status_DD__c in (‘Option 1′,’Option 2′,’Option 3’) and id not in (SELECT Id FROM Object1__c WHERE CreatedDate >= 2020-04-12T15:24:00Z) and Field1__r.Custom__c = ‘1234’

SELECT Id, Status__c, Contact__c , Custom__c FROM Object_1__c WHERE Status__c like ‘%Active%’ AND Account__r.Custom__c = ‘1234’ AND Custom__c not in (select Custom__c from Object_2__c where Contact__c = ‘1234’)

Secondary Fixes

  • Strip unwanted formatting characters from the query string that come in when text template is used in flow: ‘\n’, ‘\r\n’, ‘\t’
  • Fix time stamp formatting on date strings with a time: ’00:00 am’ is an invalid format. The equivalent, valid format using regular time is ’12:00 am’. ’00:00′ is only valid for 24hr formatting.
  • Fix month name to month number conversion: prepend a 0 on the month number if the string value is < 10

Improvements

  • Throw some exceptions for more informative error handling
  • Cleaned up unit suite and fixed failing tests

Potential issues not yet fixed:

It appears that after the ‘ order by ‘ substring is removed from the query, it is never added back before querying.

Use Cases:

New Features in V1.2

You can make a dynamic date part of your SOQL query, taking the date from upstream in your Flow. Just insert standard mergefield syntax: myquery='[SELECT Account.Name FROM Contact WHERE startdate__c > {!myUpstreamFlowDate}’

Works properly with Schedule Triggers

New Features in V1.1

  • You can now traverse to select related fields via relationships (example: [SELECT Account.Name FROM Contact...]
    • SELECT Id, Name,Status_DD__c,Region__c FROM Object2__c WHERE Status_DD__c in (‘Option 1′,’Option 2′,’Option 3’) and id not in (SELECT Id FROM Object1__c WHERE CreatedDate >= 2020-04-12T15:24:00Z) and Field1__r.Custom__c = ‘1234’
    • SELECT Id, Status__c, Contact__c , Custom__c FROM Object_1__c WHERE Status__c like ‘%Active%’ AND Account__r.Custom__c = ‘1234’ AND Custom__c not in (select Custom__c from Object_2__c where Contact__c = ‘1234’)
  • An ‘Add All’ button lets you easily create the equivalent of a Select *
  • You can lock down the object that the query is returning by setting the attribute disableObjectTypeSelection to True. This is useful when you’re pairing this component with an Execute SOQL action, because the action has to be locked to a specific action type in the Flow Builder, and this avoids the user selecting a different action and ending up with a mismatch.

Future Developments

By the end of 2020, it will be possible to add Custom Property Editors to Collection Processor Actions. That will allow the SOQL Builder UI to not just exist in running Flows but also in the Execute SOQL property editor in Flow Builder.

Installation

Unmanaged V 1.3.3 6/27/20 Bug fixes and hardening.

  1. Fixed bulkification bug – moved location of String soqlQuery = curRequest.soqlQuery;
  2. getFormattedValue – adding missing comma in fieldValue += ', 12:00 am';
  3. Increased testing to 100% code coverage
  4. Renamed invocable method to execute from getEligibleProducts and added getEligibleProducts to support any legacy users.

View Source

Source

Old Versions

Unmanaged V 1.3.2 5/5/20

Unmanaged V 1.3.1 4/30/20

Unmanaged V 1.2

Unmanaged V 1.1

Unmanaged Packages V 1.0

Subscribe
Notify of
guest
29 Comments
Inline Feedbacks
View all comments
Adam White

It’s… beautiful.

Chris V

Wow this is awesome. I can string those together with a few other actions to build a mass call campaign creation tool for the business.

One question, no test class for ExecuteSOQL.cls?

Jake Bullard

This is amazing and fits in perfectly with my next project which is a tool to allow a sales manager to create lists of given contacts for dialing. Very nice work. The only thing that is really a must for me is related fields but I can add that myself working from what you built here. Inner and our joins are also would be cool but I think it’s fine to leave that to the manual editing.

Adam White

Alex question for you – how are relationship queries handled? Lets say you wanted to do something like SELECT id, (select Account.Contacts) from Account where Id = ‘xxx’… what happens? Is there a plan to support that?

Adam White

Totally agree I was talking about the UI challenges of that with a client. You almost need a second invoked action to parse the nested objects and turn them into another collection.

Could be a great option to save on SOQL queries.

Rick HAag

Hi Alex, I have run into a few other issues with this action. I hope by highlighting, this will contribute to the development. ISSUE 1: WHERE Clause using Id returns no records Sample Debug Log EXECUTESOQL (APEX): SOQL_Contacts Inputs: soqlQuery = {!test.queryString} (SELECT Id FROM Contact WHERE AccountId = ‘001i000000LVaCUAA1’ ) Outputs: sObjects ([]) ISSUE 2. “NOT IN” Operator w/SOQL generates Error EXECUTESOQL (APEX): SOQL_Contacts Inputs: soqlQuery = {!test.queryString} (SELECT Id FROM Contact WHERE Account.Name = ‘Account_Name_Here’ AND Id NOT IN (SELECT ContactId FROM OpportunityContactRole WHERE IsPrimary = FALSE)) Error Occurred: An Apex error occurred: System.ListException: List index out of… Read more »

Rick Haag

Thank you for “V 1.3.2” & “V 1.3.1”! These updates resolved the two issues in my testing.

Josh Dayment

Trying to get the latest version to my production org from my sandbox and am getting this error. I installed in my Sandbox without issues and try installing the package as well straight into production as well.

lwc/flowCombobox/flowCombobox.js-meta.xml: You can’t remove the following public properties: builderContextFilterType,builderContextFilterCollectionBoolean,builderContext, because the component is part of a managed package.

Josh Dayment

correct

Josh Dayment

Hey Alex I gave it another shot this morning still no luck still getting the same error around the flowComboBox xml file

Jon Joseph

Trying to get the Execute SOQL component to work. I am passing in a Flow Builder formula field with a merge field at the end: Select id, PriceBook2.Name, Product2Id, currencyisocode, Product2.Name from Pricebookentry where Product2Id IN (Select SBQQ__Product__c From SBQQ__QuoteLine__c where SBQQ__Quote__c=’a0z3O000000HKCzQAO’) AND currencyIsocode=’GBP’ AND Pricebook2.Name='{!PricebookSelected}’ I don’t get an error but I also get no records returned. When I replace Pricebook2.Name='{!PricebookSelected}’ with Pricebook2.Name=’Standard Price Book’ it works – but my use case needs the last parameter to bde based on user input. I believe the ‘{!PricebookSelected}’ is being treated as a literal. Is my use case supported and what… Read more »

Raz Gat

Hi Alex, can you SUM in this tool?

[…] action. We built this to fully utilize some of UnofficialSF’s awesome custom actions like ExcecuteSOQL, Send Rich Text Email, and Get Field Names from a […]

James Cripe

When installing SOQLBuildAndExecute, do package permissions need to be set to “Install for All Users” to allow the components to be accessed in a Flow executing in User scope, or is “Install for Admins Only” Ok?

My use case for this is I have a “New or updated records—flow makes fast field updates” flow and need to “OR” fields in the Get Records field filter conditions in the flow. Hopefully this will provide that capability.

Gidi Abramovich

Alex Hi,
I’m trying to use the following query with the ExecuteSOQL Apex Action:
SELECT Id, Field, SObjectType, PermissionsRead, PermissionsEdit FROM FieldPermissions WHERE parentId IN ( SELECT id FROM permissionset WHERE PermissionSet.Profile.Name = ‘[Profile Name]’)
 
The problem is that I receive the following error:
Error Occurred: An Apex error occurred: ExecuteSOQL.ExecuteSOQLException: Unable to get sObject Type for name: FieldPermissions WHERE parentId IN ( SELECT id FROM permissionset
 
The error is received also when I’m using a static Profile Name.
 
Am I missing something here or is it an Apex Action limitation?
 
Thanks!
 

[…] run the ExecuteSOQL action to check if the running user has ‘Edit’ permission for the Account Rating […]

[…] run the ExecuteSOQL action to check if the running user has ‘Edit’ permission for the Account Rating […]

Callum

I think this question has been asked before, but do SUM or other aggregate queries work with this tool? I’m trying to utilise it to avoid looping through a large amount of records, but not sure I can use aggregate functions?

Josue G.

I’m using ExecuteSOQL Apex action, and the soqlQuery contains SUM(field) and group by, something like this: SELECT CustomField1__c, SUM(CustomField2__c) FROM CustomObject__c Where filter=x group by CusotmField1_c But I realized that the group by sentence is causing an error: When I click on “Next” on an screen (Named “Data Table” Screen -see image-), the flow ends in “Invalid Interview State“, so, I was not able to continue and see the next screen. But, if I remove the sum() and group by from the query, I was able to continue. (In fact, I have removed any reference of “Get all RF records”… Read more »