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

/48 Comments/in /by

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: [SELECTAccount.Name FROMContact...]
    • 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.

V1.3.6 12-21 Update on Date Locales:

There were 2 problems:

  1. Datetime.parse() can only parse dates in the locale of the user, the unit test just assumed it will be en_US and failed for everyone else. (our default is de_DE for example, dates look completely different)
  2. Salesforce is about to enable this critical update for everyone on January 31st: https://help.salesforce.com/s/articleView?id=release-notes.rn_forcecom_globalization_enable_icu_cruc.htm&type=5&release=224

Before this update Datetime.parse() could parse en_US dates without a comma (04/12/2020 03:24 PM) just fine, but not with a comma (04/12/2020, 03:24 PM)

After the update it is the other way around.

The changes introduced with #974 made it work in orgs without the update enabled, but broke it for orgs with the update.
The scratchOrg that is used by the force:package:version:create command has it enabled by default, therefore the command fails.

In order to support both formats, i’ve added a regex to parse en_US dates regardless of the comma.
Also the monthDayCommaYear format is now parsed with a regex.

Datetime.parse() is kept as fallback for people using their locale formats.
But If they have a multilingual org with users having different locales, it will still not work for them.Using locale formats is not recommended and users should stick to ISO dates.

Installation

V1.3.8 1-12-23 Production Sandbox Adds an onchange handler

Troubleshooting

View Source

Source

Old Versions

V1.3.7 Unlocked 8/14/22

V1.3.6 Unlocked 12-17-21

V1.3.5 Unlocked 1-20-21 Update to work with base packs

V1.3.4 Unlocked 9-6-20 Bug Fix

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