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