Lookup Filter: How To Display No Result If Dependent Fields Are Empty And All Related Result if Dependent Fields Are Present

Question

I am setting up a lookup filter to meet the condition below under Opportunity object in a custom lookup field PSource:

1. If both Account and Contact is empty, then don't display result in lookup
2. If Account is only present, then display result related to Account only
3. If Contact is only present, then display result related to Contact only
4. If both Account and Contact is present, then display results related to both Account and Contact.

The filter conditions I have create so far are:

a PSource: Account ID EQUALS Opportunity: Account Name ID
b PSource: Contact ID EQUALS Opportunity: Contact ID

I've done (1 AND 2) AND (1 OR 2) and it worked for #1, #2, #3 but not on #4.

I've done (1 OR 2) and it worked only for #4, #3, #2, but not on #1.

Answer 1

Our team leader has solved the problem. Below are the filter conditions:

1. Opportunity: Account Name ID EQUALS PSource: Account ID
2. Opportunity: Contact ID EQUALS PSource: Contact ID
3. Opportunity: Account Name ID NOT EQUAL TO
4. Opportunity: Contact ID NOT EQUAL TO

And the filter logic is (1 AND 3) OR (2 AND 4).

Note: 3 and 4 are where I went wrong. When I researched and saw <blank>, I thought I would literally put <blank>. Unfortunately, it simply means blank (don't put anything in the value field).

Explanation: With the filter logic above, because it is being AND, a field who is empty will automatically exclude the result of its filter. OR is used because the 2 fields are independent of each other, thus, if one is empty but the other is not empty, then result of non-empty field is being displayed, while the result of the empty field is excluded.