Flow: Support equivalent of SOQL “IN” condition in Record Filters


Starting Winter’23, Salesforce has brought this feature into the flows. Please read my blog to know more.

If you have been working with Salesforce flows for a while, it is likely that you would have gone through the pain of using the “Get Records” element in the Loops.

One such example could be, that you wish to loop over 200 Contacts and further filter them with MailingCity = 'Munich', related to 200 accounts at one go. The numbers are certainly bigger in a real environment. And this is how you would do it in a traditional way that Salesforce offers right out of the box.

If we follow this flow setup, considering there are over 200 records (Accounts Loop), we are sure to hit the SOQL governor limit.

But had it been with apex, it would have been simple as below.

Set<Id> accountIds = new Set<Id>();
for (Account account : [SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = TODAY]) {
     accountIds.add(account.Id);
 }
List contactsList = [SELECT Id, AccountId, LastName, MailingCity FROM Contact WHERE AccountId IN :accountIds AND MailingCity ='Munich'];

Flows are really powerful and I have built this generic code to raise support equivalent of SOQL “IN” condition in record filters. And the best part is that I made this solution generic, which means you can use it for any object. Standard or custom, no problem.

GitHub & Installation:
https://github.com/salesforcebinge/flow-IN-filter

The Big Picture

Let us now get into the details.

Flow Setup

A glimpse of all the variables used in the flow.

Step 1:

Get account records created today and store them in accounts variable.

Step 2:

Loop on accounts collection variable.

Step 3:

Add the loop’s account.Id into the accountIds collection variable.

Step 4:

Now starts the main part where we construct our filters using the apex defined variables.

We use the apex class GetRecordsFilter to construct our filter variable. You can create as many such variables according to the number of filters you need in your GetRecords.

Here I am using two filters on my Contact (one for AccountId and second for MailingCity). So I have created two filter variables.

Implementation of Filter 1 where we assign out values for AccountId.

Step 5:

Implementation of Filter 2 where we assign out values for MailingCity.

Step 6:

filters is a collection variable of GetRecordsFilter.

We now simply add these filters to a common list so they can be combined automatically by our apex class behind and construct the final SOQL query.

Step 7:

This is the main part of all where we put everything together.

  • Choose the target object. Contact in our case since that’s where we are query data from.
  • Type in the fields (API names) to be queried.
  • Pass the filters variable we constructed in Step 6.
  • The target object API name itself.
  • Store the output data into a collection. contacts in our case.

And that’s how you save yourself from hitting SOQL governor limits by using Get Records elements in the loops. This solution can certainly save the day!

Apex Classes

/**
 * @description This APEX class is used to construct the GetRecordsFilter
 * @author      Salesforce Binge
 * @website     https://salesforcebinge.com/
 */
public with sharing class GetRecordsFilter {
    @AuraEnabled
    public String filterField;
    @AuraEnabled
    public String operator;  // =, !=, <, >, <=, >=, IN, NOT IN
    @AuraEnabled
    public String fieldValue;
    @AuraEnabled
    public List<String> fieldCollectionValue;
}
 
/**
 * @description This APEX class is used to construct the GetRecordsCriteria
 * @author      Salesforce Binge
 * @website     https://salesforcebinge.com/
 */
public with sharing class GetRecordsCriteria {
    @InvocableVariable(required=true)
    public String objectApiName;
    
    @InvocableVariable(required=true)
    public String fieldsToQuery;
    @InvocableVariable(required=true)
    public String filterOperator;  //OR, AND
    @InvocableVariable(required=true)
    @AuraEnabled
    public List<GetRecordsFilter> filters;
}
 
/**
 * @description GetRecordsByApex class provides a convenient facade to
 * the custom Get Records logic, e.g. Get records from an Object 
 * ‘WHERE <fieldname> IN :<collection>’, since this cannot be directly done in the 
 * flows as of today
 * 
 * This class is capable of having extra filters (Single/Collection) in addition to the
 * actual purpose stated above.
 * 
 * The class is bulkified but has it's own drawbacks. If there are more than 100 inputs, 
 * it will violate the SOQL 101 governor limits since the query is dynamic with different objects. 
 * 
 *
 * @author      Salesforce Binge
 * @website     https://salesforcebinge.com/
 */
public with sharing class GetRecordsByApex {
    private static final String IN_FILTER = 'IN';
    private static final String OPEN_PARANTHESES = '(';
    private static final String CLOSE_PARANTHESES = ')';
    private static final String COMMA = ',';
    private static final String STR_SELECT = 'SELECT ';
    private static final String STR_FROM = ' FROM ';
    private static final String STR_WHERE = ' WHERE ';

    private static final String LOGGER = 'GetRecordsByApex';
    
    @InvocableMethod(label = 'GetRecordsByApex' description = 'This APEX class serves as entry point from the flow')
    public static List<List<SObject>> getRecords(List<GetRecordsCriteria> inputParams) {

        try {
            List<List<SObject>> result = new List<List<SObject>>();
            Boolean isInvalidParameter = inputParams.isEmpty();

            if (isInvalidParameter) {
                return result;
            }

            for (GetRecordsCriteria criteriaEntry : inputParams) {
                List<SObject> partialResult = queryRecords(criteriaEntry);
                result.add(partialResult);
            }

            return result;
        } catch (Exception e) {
            throw e;
        }
    }
    
    /**
     * @description This function queries records dynamically
     * @author      Salesforce Binge
     * @website     https://salesforcebinge.com/
     * 
     * @param  criteriaEntry  GetRecordsCriteria
     * @return List<SObject>
     */
    public static List<SObject> queryRecords(GetRecordsCriteria criteriaEntry) {
        List<String> queryFilters = new List<String>();
        String filters = '';
        String filterOperatorWithSpace = ' ' + criteriaEntry.filterOperator + ' ';

        if (criteriaEntry.filters != null) {
            queryFilters = constructQueryFilters(criteriaEntry.filters);
        } else {
            throw new StringException ('No filters are specified to query records');
        }
        
        String queryString = STR_SELECT + criteriaEntry.fieldsToQuery + STR_FROM + criteriaEntry.objectApiName + STR_WHERE;
        
        if (! queryFilters.isEmpty()) {
            filters = String.join(queryFilters, filterOperatorWithSpace);
            queryString += filters;
        }
        
        List<SObject> recordList = database.query(queryString);
        return recordList;
    }

    /**
     * @description This function is constructs the query filters
     * @author      Salesforce Binge
     * @website     https://salesforcebinge.com/
     * 
     * @param  filters  List<GetRecordsFilter>
     * @return List<String>
     */
    public static List<String> constructQueryFilters(List<GetRecordsFilter> filters) {
        List<String> queryFilters = new List<String>();

        for (GetRecordsFilter filter : filters) {
            queryFilters.add(processRecordFilter(filter));
        }

        return queryFilters;
    }

    /**
     * @description This function extracts and constructs the query filter
     * @author      Salesforce Binge
     * @website     https://salesforcebinge.com/
     * 
     * @param  filter  GetRecordsFilter
     * @return String
     */
    public static String processRecordFilter(GetRecordsFilter filter) {
        String filterString = filter.filterField + ' ' + filter.operator + ' ' ;
        if (filter.operator.contains(IN_FILTER)) {
            List<String> quotedValues = getQuotedCollection(filter.fieldCollectionValue);
            String collection = OPEN_PARANTHESES;
            collection += String.join(quotedValues, COMMA);
            collection += CLOSE_PARANTHESES;
            filterString += collection;
        } else {
            filterString += getQuotedValue(filter.fieldValue);
        }
        return filterString;
    }

    /**
     * @description This function extracts and constructs the quoted value
     * @author      Salesforce Binge
     * @website     https://salesforcebinge.com/
     * 
     * @param  filter  GetRecordsFilter
     * @return String
     */
    public static String getQuotedValue(String fieldValue) {
        String quotedValue ='\''+ fieldValue +'\'';
        return quotedValue;
    }

    /**
     * @description This function extracts and constructs the quoted collection
     * @author      Salesforce Binge
     * @website     https://salesforcebinge.com/
     * 
     * @param  filter  GetRecordsFilter
     * @return String
     */
    public static List<String> getQuotedCollection(List<String> fieldCollectionValue) {
        List<String> quotedValues = new List<String>();

        for (String fieldValue : fieldCollectionValue) {
            String quotedValue ='\''+ fieldValue +'\'';
            quotedValues.add(quotedValue);
        }
        return quotedValues;
    }
}
/**
 * @description Used to test the class "GetRecordsByApex"
 * @author      Salesforce Binge
 * @website     https://salesforcebinge.com/
 */
@isTest 
public with sharing class GetRecordsByApexTest {
    private static Account account;
    @TestSetup
    private static void setup() {
        account = new Account (Name='Mock Test');
        insert account;
    }
    private static void loadSetup() {
        account = [SELECT Id, Name FROM Account ORDER BY CreatedDate DESC LIMIT 1];
    }
    @isTest
    private static void testQueryRecordsWithValidInputsOne() {
        loadSetup();
        Test.startTest();
        List<String> accountIds = new List<String> {account.Id};
        List<GetRecordsFilter> filters = new List<GetRecordsFilter>();
        GetRecordsFilter filterOne = new GetRecordsFilter();
        filterOne.filterField = 'Id';
        filterOne.operator = 'IN';
        filterOne.fieldCollectionValue = accountIds;
        filters.add(filterOne);
        GetRecordsCriteria criteria = new GetRecordsCriteria();
        criteria.objectApiName = 'Account';
        criteria.fieldsToQuery = 'Id, Name';
        criteria.filterOperator = 'AND';
        criteria.filters = filters;
        List<GetRecordsCriteria> criteriaInputs = new List<GetRecordsCriteria>();
        criteriaInputs.add(criteria);
        List<List<SObject>> result = GetRecordsByApex.getRecords(criteriaInputs);
        System.assertEquals(1, result.size());
        Test.stopTest();
    }
    @isTest
    private static void testQueryRecordsWithValidInputsTwo() {
        loadSetup();
        Test.startTest();
        List<GetRecordsFilter> filters = new List<GetRecordsFilter>();
        GetRecordsFilter filterOne = new GetRecordsFilter();
        filterOne.filterField = 'Id';
        filterOne.operator = '=';
        filterOne.fieldValue = account.Id;
        filters.add(filterOne);
        GetRecordsCriteria criteria = new GetRecordsCriteria();
        criteria.objectApiName = 'Account';
        criteria.fieldsToQuery = 'Id, Name';
        criteria.filterOperator = 'AND';
        criteria.filters = filters;
        List<GetRecordsCriteria> criteriaInputs = new List<GetRecordsCriteria>();
        criteriaInputs.add(criteria);
        List<List<SObject>> result = GetRecordsByApex.getRecords(criteriaInputs);
        System.assertEquals(1, result.size());
        Test.stopTest();
    }
    @isTest
    private static void testQueryRecordsWithInvalidInputsOne() {
        loadSetup();
        Test.startTest();
        String errorMessage;
        try {
            List<GetRecordsFilter> filters;
            GetRecordsCriteria criteria = new GetRecordsCriteria();
            criteria.objectApiName = 'Account';
            criteria.fieldsToQuery = 'Id, Name';
            criteria.filterOperator = 'AND';
            criteria.filters = filters;
            List<GetRecordsCriteria> criteriaInputs = new List<GetRecordsCriteria>();
            criteriaInputs.add(criteria);
            List<List<SObject>> result = GetRecordsByApex.getRecords(criteriaInputs);
        } catch (StringException e) {
            errorMessage = e.getMessage();
        }
        Test.stopTest();
        system.assert(errorMessage.contains('No filters are specified to query records'));
    }
}

I hope this helps. 🙂

25 Comments

    • Thanks a lot for this component! We use shield encryption and some flow features are not available with this, e.g. use an encrypted field in the filter of a get record element. Your component allows me to achieve this and identified existing contact emails based on the email entered in our ‘New Contact’ flow. So it works perfectly with the ‘=’ operator.
      However i have another use case for which i haven’t found a solution: in our ‘New Account’ flow, we do some soft-duplicate check on the Account Name (encrypted). The idea is to check if any of the existing Account Name CONTAINS the value entered by the user in the flow. If so, we list those accounts and let the user decide if an account already exists or not. I guess that I should use the IN operator and search if the entered Name is in a Collection that has Account Name.. however I can’t really query and save all the Account names because we will run into governor limits (max # of queried record = 50’000.. as we are close to that so not sustainable). Is there any way to achieve this?
      Thanks and greetings!
      Xavier

      • I ended up using the standard get record element and filter as much as possible there, and then the new filter element for CONTAINS. It would be nicer to be able to use the CONTAINS on Account name in the get record element but that’s not possible for us because of enryption.

      • Understood. So you used the Get Records element to get all the possible accounts and then used the Collection Filter element to further filter out the records based on the Account Name. Have you checked how many records the standard Get Record element is returning? Just curious to know.

      • That’s right. But in the Get Record element I also filtered as many records as possible (e.g. using record types) to limit the number of records returned. This will return about 30k account, which will then be filtered using the new element.

      • Hi Sheshant, i just wanted to ask if there is a plan to enhance your component with the ‘CONTAIN’ operator? I have a new case similar to the one described in 1st post, where it would be useful to use CONTAIN. Cheers and best regards

  4. Love it! Just found this through unofficialSF as well. Works like a charm. Thanks a lot.
    The only challange I ran into is comparing to boolean values (e.g. as part of the query I need to make sure my opps are won)
    I get this error “value of filter criterion for field ‘IsWon’ must be of type boolean and should not be enclosed in quotes”
    Any idea for a quick fix (besides checking for the StageName)?

    • Hello Norizinus,

      Thank you so much for the feedback. Unfortunately, the current version of the code does not support boolean filters because I somehow missed it until you brought this up. 🙂

      You already mentioned the quick fix using the StageName. Besides that, I don’t see another option right now.

      I shall add the boolean feature and release another version soon.

      Cheers!

  5. I want to display Opportunities in a list on the Product2 record. This tool looks like it could help me from querying in a loop, any idea how to structure this using your tool? I’m a bit stumped! The idea is to use a flow table component on the record page to display the records. My first query is to get all the OpportunityProduct records. Then I need to get the Opportunity records.

    • Hello Glenn,

      Apologies for the late response.

      Have you already solved this? I’d like to know about this a little more. You could use the standard GetRecords to pull all the related OpportunityProduct records, use a loop to add all the Opportunity Product record ids in a collection, and leverage the GetRecordsByApex component to fetch all the Opportunity records related to the OpportunityProduct.

      Please do let me know if I am getting this different than what you require.

      Thanks,
      Sheshant Kashyap

      • Thanks for getting back to me. Going back into the flow i realised I was using a “record collection variable”, not a “collection variable”. I’ve now resolved the issue and it’s working as expected…..thank you! Only problem is I’m running into loop iteration limit for certain Product records…..so it’s back to the drawing board.

        I’ve seen another post of yours about “collection sort” in summer ’21 release. perhaps this is something I could use to show the users only the most important records, sorted by opportunity close date. Though i’ll to wait for it to be released, unless there is another way?

      • I suggest we use an apex class to populate the “collection variable” (say: opptyProdIds) with all the product Ids. You can simply pass the filled “records collection” (from Get Records element) to the apex class. Have a small for loop which just iterates over this collection and returns the list of record Ids.

        You may then set the “opptyProdIds” directly with these values and go forward with whatever exists in your current build.

        So simply replacing the Loop element with this small apex action should certainly solve your problem. Do let me know if you require any assistance.

        Cheers!

  6. I had trouble with errors when a field comes back as null. For some reason, setting the fields to the values that should already be there prevents the null values from generating an error in the flow. I added the following lines after the database.query(queryString) line in GetRecordsByApex:

    List fieldList = criteriaEntry.fieldsToQuery.trim().split(‘\\s*,\\s*’);
    for(SObject myRecord : recordList){
    for(String myField : fieldList){
    myRecord.put(myField,myRecord.get(myField));
    }
    }

  7. Nicely done! I was able to use this new component to solution a complex requirement related to ProcessInstanceWorkItem records. Couldn’t have done it without this solution. Thanks!

  8. Hello, I’ve used this a few times, and it is very simple and easy to use! Thanks for making it. I am attempting to use this to format a soql query involving date time values, and I’m getting this error:
    An Apex error occurred: System.QueryException: value of filter criterion for field ‘Start_Date_Time__c’ must be of type dateTime and should not be enclosed in quotes

    Do you have any idea how to workaround this? Thanks again for creating this!

    • Hello Nich. Thank you for the feedback. Yes, the code currently does not handle date and boolean fields but that’s something I am working on this week. We will have to adjust the core classes that the flow calls to fix your issue.

Leave a Reply

%d bloggers like this: