Oh My Gosh. I am so excited. I have just learned something that I did not know about lookup filter logic in Visual Flow that will make life a whole lot easier:
Lookup filter logic is sometimes “AND” and sometimes “OR“
Typical Lookup Filter Example
In a typical lookup you will filter on one or more fields to get the record(s) that match your criteria. Each unique filter serves to further narrow down the number of records that are located.
In this example the AccountId must match the given Id AND MembType must have a value of “SP”.
Lookup Filter With OR Logic
Here is the exciting piece of new information: When you list the same field more than once and use equals operator, the logic between those fields become “OR” logic.
.
So the full logic for these two examples would be
- AccountId AND ( MembType = SP OR MembType = CH )
- AccountId AND ( FullName = Sharon OR FullName = Sherrie )
In other words, if your filter is looking at the same field equaling different values, then the logic for those fields will be OR logic.
What I’ve Been Doing
What I’ve been doing prior to this revelation when I’ve needed records that match the above type of criteria is a general lookup to a larger set of records, then loop through that collection extracting only the records that meet my needs.
Equals Operator versus Contains Operator
Wait a minute…what if you’re looking for records that contain two or more values in a single field. For instance, let’s say you have the following values in a text field:
- Rocks a great hair-do and wears cut-off jeans
- Wears glasses and cut-off jeans
- Needs a hair-do makeover and new cut-off jeans
and you want to view the records where this field has both “hair-do” and “cut-off jeans”.
Well, as luck would have it, when you have the same field listed more than once and you use the contains operator versus the equals operator, then the logic between the fields is AND logic.
.
In fact, when you have the same field listed more than once and you use any of the operators other than “equals” the logic between the fields is AND logic.
Mixed Operators
Okay, the above is very cool, but what happens if you mix the operators, such as contains and equals, or equals and ends with?
The answer? The logic is once again AND logic, and your filter will not yield any records.
The Secret Logic Rule
So the secret logic rule appears to be that the logic between fields is always AND logic, except in one case where the same field is listed more than once and the operators are all “equals” operators and OR logic take placed.
What fun!
Mark Riley
November 17, 2016 at 7:54 pm
Wonderful. I have spent about 4 hours with premier support who were adamant that this logic was not supported. I posted this idea https://success.salesforce.com/ideaView?id=0873A000000cMqRQAU
and Then I stumbled across your post!!
LikeLike
Kim Dy
March 27, 2017 at 6:19 pm
This is awesome. Thank you for posting this. Saves a lot of time and cleaner flow process.
LikeLike
LT
November 15, 2017 at 9:11 pm
I love this! Is there a work around for when you want to filter fast lookups using OR in two totally different fields. For example, Email1 = latoya@myemail.com OR Email2 = latoya@myemail.com. Or will we need the loop and decision for this?
Thanks for sharing these nuggets!
LikeLike
Karen
November 15, 2017 at 9:15 pm
You’ll need to use loops and decisions for this. The logic as always AND logic except in the case where you’re filtering on the same field multiple times.
LikeLike
Chris
June 5, 2018 at 2:16 am
I see this functionality is now documented in the cloud flow designer guide: https://developer.salesforce.com/docs/atlas.en-us.salesforce_vpm_guide.meta/salesforce_vpm_guide/vpm_designer_elements_record_lookup.htm
Under the heading “Considerations for Defining Filter Criteria”
Great find Karen!
LikeLike