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

You can 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

V1.3.4 Unlocked 9-6-20 Bug Fix

View Source

Source

Old Versions

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.

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