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

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 filtering 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.

The Big Picture

Let us now get into the details.

Flow Setup

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. 🙂

12 Comments

  1. […] blogs via Salesforce Binge, and has posted an installable action that lets you build up IN queries using Assignment […]

  2. Very nice! Was looking for something to do this just now and, and just got a notification from UnofficialSF that you had posted this. Thanks much and greetings from Berlin!

  3. Looks great! Just one small suggestion, if I may. The action doesn’t seem bulkified. So if you can manage to do that as well, it’d be very cool.

    • Thank you for the feedback, ForcePanda. I have been working on this to make it bulkified. The original code also has a comment why I didn’t bulkify this yet.

  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));
    }
    }

Leave a Reply

%d bloggers like this: