LIMIT N and WHERE clause together in flows! #Spring20Delight

Hello there! 😀

Before we start, let’s take a quick trip down the memory lane. In one of my recent posts, I shared how we can Query only first N records in Flows. Soon after, I received a request from few folks saying what if we wanted to have an additional filter on a field. For example, get first 50 Accounts with Rating ‘Cold’.
That’s exactly what we are going to address in today’s article.

I would recommend reading the aforementioned post before continuing further because I will be skipping some ‘must know’ details about the Spring 20 updates, which are important to this understand this solution.

Safe harbor, stuff that you are going to see in this post is not GA yet. But soon you will be able to use it once the Spring 20 is released to production environments. I am using a pre-release org for the demo purpose.

How do I start?

Well, we start by creating an Apex class with the following code.
FlowLIMITandWhereClauseActionClass.apxc

public class FlowLIMITandWhereClauseActionClass {
    static final integer MAX_QUERY_RECORDS_LIMIT = 50000;
    
    /* Class definition for throwing custom exceptions */
    public class FlowApexActionException extends Exception{}
    
    @InvocableMethod(label='Get N records with WHERE clause filter')
    public static FlowOutputParameters[] filterRecords(FlowInputParameters[] input){
        FlowOutputParameters[] result = new FlowOutputParameters[]{};
            
        if(input[0].numberOfRecords > MAX_QUERY_RECORDS_LIMIT)
            throw new FlowApexActionException('You cannot query more than 50000 records.');
        
        string query = 'Select '+ input[0].fieldsToQuery + ' FROM ' +  input[0].objectApiName + ' WHERE '+input[0].whereClause +' LIMIT ' + input[0].numberOfRecords;
        system.debug(query);
        
        try{
            sObject[] recordList = database.query(query);
            
            FlowOutputParameters obj = new FlowOutputParameters();
            obj.records = recordList;
            
            result.add(obj);
        } catch(Exception e){
            //system.debug(e.getMessage());
            throw e;
        }
       return result;
    }
    
    /* Input parameters for the Apex action */
    public class FlowInputParameters{
        @InvocableVariable(label='Api name of the Object' required = true )
        public string objectApiName;
        
        @InvocableVariable(label='Fields to query(Comma separated)' required = true)
        public string fieldsToQuery;
        
        @InvocableVariable(label='Where Clause' required = true)
        public string whereClause;
        
        @InvocableVariable(label='Number of records to query' required = true)
        public integer numberOfRecords;
    }
    
    /* Output parameters of the Apex action */
    public class FlowOutputParameters{
        @InvocableVariable(label='Record Collection')
        public sObject[] records;
    }
    
}

Now you should be able to use this class as an Apex action in your flows.

How to use in flows?

** {!varNumberOfRecordsToQuery} is number type flow variable. To understand more about the usage of WHERE clause refer this article: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_conditionexpression.htm

And that brings us to the end of this post. I hope you find this useful!

Thank you for being an awesome reader! Subscribe to this blog for receiving all the latest updates straight to your inbox. 🙂

10 thoughts on “LIMIT N and WHERE clause together in flows! #Spring20Delight

  1. Hi, this is a really good solution for a lot of uses!
    I use it to get the translated value of a picklist into a flow – and it’s working very good.
    I have only limited apex experience and I’m trying to build a test method for this but without success.
    Basically, I wrote some test data for objects and then I’m trying to use the class that you built in order to test it. This is what I have done so far:

    FlowLIMITandWhereClauseActionClass.FlowInputParameters[] input = new FlowLIMITandWhereClauseActionClass.FlowInputParameters[]{};
    input[0].objectApiName = ‘Case’;
    input[0].fieldsToQuery = ‘AccountId’;
    input[0].whereClause = ‘AccountId=ac.Id’;
    input[0].numberOfRecords = 1;

    sObject[] queryResult =new sObject[]{};
    queryResult.add(FlowLIMITandWhereClauseActionClass.filterRecords(input[0]{}));

    Test.startTest();
    System.assertEquals(queryResult.size()>0, true) ;

    Would you be able to assist?

    Thank you.

    Yanir

    Like

    • Hi,

      You can work with this code:
      @IsTest
      private class FlowLIMITandWhereClauseActionClassTest {

      @TestSetup
      static void createData(){
      Case[] casesToCreate = new Case[]{
      /* Preparing Test Data */
      new Case(Subject = ‘Test Case 1’),
      new Case(Subject = ‘Test Case 2’)
      };

      insert casesToCreate;
      }

      testmethod static void TestMaxQueryRecordLimitException(){
      FlowLIMITandWhereClauseActionClass.FlowInputParameters[] inputs = new FlowLIMITandWhereClauseActionClass.FlowInputParameters[]{};

      FlowLIMITandWhereClauseActionClass.FlowInputParameters input = new FlowLIMITandWhereClauseActionClass.FlowInputParameters();
      input.objectApiName = ‘Case’;
      input.fieldsToQuery = ‘Id,Subject’;
      input.whereClause = ‘Subject LIKE \’Test%\”;
      input.numberOfRecords = 50001;
      inputs.add(input);
      FlowLIMITandWhereClauseActionClass.FlowOutputParameters[] outputs = new FlowLIMITandWhereClauseActionClass.FlowOutputParameters[]{};

      Test.startTest();
      try{
      outputs = FlowLIMITandWhereClauseActionClass.filterRecords(inputs);
      }catch(Exception e){
      system.debug(‘Exception Message: ‘+e.getMessage());
      system.assert(e.getMessage() == ‘You cannot query more than 50000 records.’);
      }
      Test.stopTest();
      }

      testmethod static void TestQueryException(){
      FlowLIMITandWhereClauseActionClass.FlowInputParameters[] inputs = new FlowLIMITandWhereClauseActionClass.FlowInputParameters[]{};

      FlowLIMITandWhereClauseActionClass.FlowInputParameters input = new FlowLIMITandWhereClauseActionClass.FlowInputParameters();
      input.objectApiName = ‘Case’;
      input.fieldsToQuery = ‘Id,Subject’;
      input.whereClause = ‘Id LIKE \’Test%\”;
      input.numberOfRecords = 2;
      inputs.add(input);
      FlowLIMITandWhereClauseActionClass.FlowOutputParameters[] outputs = new FlowLIMITandWhereClauseActionClass.FlowOutputParameters[]{};

      Test.startTest();
      try{
      outputs = FlowLIMITandWhereClauseActionClass.filterRecords(inputs);
      }catch(Exception e){
      system.debug(‘Exception Message: ‘+e.getMessage());
      system.assert(e.getMessage().contains(‘invalid operator on id field’));
      }
      Test.stopTest();
      }

      testmethod static void testFilterRecordsMethod(){

      FlowLIMITandWhereClauseActionClass.FlowInputParameters[] inputs = new FlowLIMITandWhereClauseActionClass.FlowInputParameters[]{};

      FlowLIMITandWhereClauseActionClass.FlowInputParameters input = new FlowLIMITandWhereClauseActionClass.FlowInputParameters();
      input.objectApiName = ‘Case’;
      input.fieldsToQuery = ‘Id,Subject’;
      input.whereClause = ‘Subject LIKE \’Test%\”;
      input.numberOfRecords = 2;

      inputs.add(input);
      FlowLIMITandWhereClauseActionClass.FlowOutputParameters[] outputs = new FlowLIMITandWhereClauseActionClass.FlowOutputParameters[]{};

      Test.startTest();
      outputs = FlowLIMITandWhereClauseActionClass.filterRecords(inputs);
      Test.stopTest();

      System.assert(outputs[0].records.size() == 2 );

      }

      }

      Like

      • Thank you so much for this. I wouldn’t be able to write this test by myself(or it would have taken me at least a few days just to write a not so good test that would barely function as test coverage…).
        Thank you again for this wonderful code that I’m sure I’m going to use a lot in many different use cases.

        Like

  2. I am unable to create a lookup for the user/group field by using the OwnerId in the flow but I am able to create a lookup for a user in the flow. Can anyone help me out, please

    Like

  3. Hi- Super useful tool; thanks for putting this together. I am running it in one of my flows, and if I manually trigger the flow or do an update with Batch size=1 everything works well, but anytime it is triggered multiple times I get the following: “Error Occurred: The number of results does not match the number of interviews that were executed in a single bulk execution request.”

    I’m querying for a single record.

    Like

    • Hi,
      I wrote this one when I was a lil more stupid than I’m now. It needs some refactoring in order to make it work not just with Screen flows but others as well.

      So for now, you will have to manage by setting the batch size to 1. 😅🙈

      Like

  4. Pingback: Query only first N records in Flows |  forcePanda

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.