Hidden Logic in Visual Flow Lookup Filters

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.

Typical Lookup Filter AND Logic

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.

Lookup to Picklist Field with OR Logic

Lookup to Text Field with 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.

The Old Way

---

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.

Lookup to Text Field with 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.

Lookup Filter with Mixed Operators 1

Lookup Filter with Mixed Operators 2

---

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!

---

Reference:

Salesforce Chatter Post