Filter Account Lookup by Record Type to match correlated Opportunity Record Type

Question

Notice: The below question does not address the issue we were trying to solve. The real issue had to do with a Visualforce component (see the update). Jenny B's answer provides good information for anyone seeing similar issues with standard Page Layouts. A question about this issue occuring in Visualforce can be found here.

Requirement

In our Salesforce Org, Accounts have 2 record types: Investor and Lender and Opportunities have 2 correlated record types: Equity and Debt.

We have the following business requirement: The Account on an Opportunity must be of type Investor for Opportunities of type Equity and must be of type Lender for Opportunities of type Debt.

Of course, we could use a validation rule to enforce this, but a Lookup Filter would be best as users should only be presented valid Accounts for the Opportunity.

Lookup Filter

The lookup filter we are using has the following criteria:

1. Opportunity: Opportunity Record Type equals Equity
2. Account Name: Account Record Type equals Investor
3. Opportunity: Opportunity Record Type equals Debt
4. Account Name: Account Record Type equals Lender

With filter logic of (1 AND 2) OR (3 AND 4).

The Issue

If the Opportunity is created from the Account, the Account lookup field is filled automatically and the lookup filter criteria correctly enforce the business requirement.

However, if the user attempts to lookup an Account manually from the New Opportunity page, no search results are displayed in the lookup dialogue. (See update)

How can this requirement be enforced in a lookup filter that correctly displays search results to the user?

Update

I was wrong about the source of this issue. On the standard Account page layout, the lookup filter works correctly. The real issue lies in a Visualforce page we are using to lookup the Account.

<apex:inputField value="{!Opportunity.AccountId}" required="true"/>

For some reason, the lookup to AccountId does not function the same as the standard Account lookup. A question about this issue can be found here.

Answer 1

I did a quick test in my Developer org and it looks like you are on the right track. Remember that when you use a lookup filter, if you haven't visited an account that matches the criteria recently it will not display anything. Users will need to search for an account that matches the criteria.

Found another possible reason for returning 0 results from a help article:

Description

I used a search term on a lookup search field for records I know exist but I get no results. Is there some trick to this or is the tool not working correctly?

Resolution

An entity-specific search (e.g. Case, Account, Custom Object) will return at most 250 results. Lookup Filters will then only apply to the top 200 search results. This filtering is performed post-query as it relies on information present in the database but not in the search index. As a result of this, if the desired result does not appear within the top 200 search results, it will not appear in the Filtered Lookup list. This means that the combination of a high-hit-count search (e.g. a search for “Sales*” that returns a thousand results) and a Lookup Filter can cause many “expected” results which are retrievable through more restrictive queries to not show up when using the Lookup search.

Here is a real-life example of this as-designed behavior:

An org has over 10,000 records that start with the word "Spanish". A search for "sp" returns a max of 250 records and then the Lookup Filters evaluate those results according to the filter criteria. If the 250 records returned in the initial query do not meet the criteria of the Lookup Filters, the lookup search will return 0 results and make it appear as though it is not working as expected even though it is.

Workaround: Either remove the Lookup Filters, make the Lookup Filters less restrictive or use more restrictive search terms.