Custom Lookup For Flows to Avoid "Disjunctions Not Supported"

Because custom metadata and Flow don't mix, I had to create a service that would run the custom metadata lookup in Apex, where I can control the bulkification.

Code

Here is some code that will achieve this. It takes a list of field names and a list of values, and a table name, and returns the value from the lookup. To install the code, open Developer Console, and create four files:

• FlowLookup
• FlowLookupRequest
• FlowLookupResult
• MyException

You can copy/paste the code from Github into those files, then save them. That will make the plugin available to your Flow.

Step-By-Step Usage

Here's an example that looks up a value called a "fireball" from a custom metadata table and assigns the value to a record called iLead__c.

Request and Response Formats

The process has a request and a response. The request contains:

• Query Fields - a list of the fields you want to query
• Query Values - another list of the values for the above fields
• Result Field - the field to pull the result from
• Table Name - The API name of the object/table you want to query from
• Record Id - the Id of the Record that this query is associated To
• Where Clause - Optional - If you want to add additional WHERE logic, you can do so here (SOQL format)

The Request needs to be enclosed in a wrapper, because there could be multiple requests in a transaction.

In this case you want to find the Fireball_Quantity__c from the Fireball_Mapping__mdt metadata table where Call_To_Action__c = 'Contact Us' and Campaign_Member_Status__c = 'Filled Out Form', and the result is destined for record with ID a6x2M0000004Xt3QAEQAE.

Important: The query fields and query values must be in the same order. In the above example, it will put the 'Contact Us' field in the filter for 'Call_To_Action__c' because they are in the same slots in the list.

If you specify a Result Field, the class will return that single piece of data. If you leave it blank, it will return the entire record that matches the criteria.

The Result is similar:

The Result will contain:

• HasValue - a Boolean to let you know if a result was found
• Record ID - the ID of the record this result is for
• If there is a result, you will get either:
• A single data value in the Result Text, Result Boolean, etc., fields. The system will know what type the result is, and put it in the appropriate slot. For example, if your lookup returns a whole number, results will come back in Result Number.
• OR
• An entire record in the Result Record field.

Sample Flow

The way Flow works, you will need to assemble your request in three Assignment shapes.

Shape #1 - First, using an Assignment shape, create the two lists of variables for the fields and values to query for. Note that this is done in careful order - Call to Action is added to the Query Field, and then Campaign Member Status, and likewise the CTA and Member Status are added to the Query Values list.

Shape #2 - Second, add the field lists to a Request along with the other non-List data needed.

Shape #3 - Third, add your request to a Wrapper. This step doesn't add much value here but is required by the Flow. We could add multiple requests to the Wrapper if necessary. Remember, the way Flow works, we'll get one Request for each input record.



Shape #4 - Now your request is ready, so you can fire the Apex class to run the query.

Process Results

The results will come back in the ResultWrapper variable. You will need to Loop over them to process them.

Shape #5 - We create a simple Loop shape:

Note: if you are deploying this from a Spring '20 sandbox from a Summer '20 sandbox, you will need to modify the loop behind the scenes once you're done. See this post.

In this example we aren't guaranteed that the lookup will return a value. So in the Flow, we'll check if HasValue is true and if so, assign the returned value to a local variable. If not, we'll set the default value.

Shape #6 - Did we get a result?


Shape #7  - No result, assign default value to the local variable "Fireball Value"

Shape #8 - We got a result, so assign the Result to the local variable "Fireball Value"

Shape #9 - At this point, we either have an actual result or a default value in the "Fireball Value", so we can put it in the field on the iLead. We've got an iLead record that we're going to re-use for each result in the loop.

On that record, we'll set the Fireball Value and the ID that came back from the class.

This is why we need the Record ID in the request - the ID basically passes through the Apex process, so when you're evaluating results, you know which value to attach to which record.

Shape # 10 - Add the iLead to a collection of records that we will update all at once once we've gone over all the results.

Use a Collection variable:

Then in Shape #10 we assign the current iLead to the Collection.

In Shape 11, we're just clearing out the result value from the single iLead we're using for each result. This isn't strictly necessary but it's included here because if your query could return null on purpose, you'd need to clear it out here since the iLead record variable is going to be re-used the next time the loop goes through.

The only way to clear out (reinitialize) a variable in Flow is to create a variable of that type, and never put anything in it. So here we've created NullString and NullNumber variables.

Finally, in Shape 12, we update all the iLeads we've collected on the list in one transaction.