Lookup filter based on sibling objects

Question

Suppose we have two custom junction objects which link two Accounts via lookups.

Lets call them Looky1__c and Looky2__c.

We have a requirement to restrict the accounts selectable in Looky2__c based on existing relations described by Looky1__c.

Eg "Barnum Ltd" is linked to "Chesop Inc" via an instance Looky1__c. "Barnum Ltd" is also linked to "Waldon Plc" via another instance of Looky1__c.

When creating a new Looky2__c instance from "Barnum Ltd" the account list for the other end of the link is restricted to "Chesop Inc" and "Waldon Plc". That is, limited to its siblings.

Ideally I'd like this to appear just as simple filter options work; When a user clicks the magnifier lookup icon a filtered search list appears.

Is there a way to do this?

Answer 1

I think I get the essence of what you're trying to do but I don't think you can achieve this without adjusting your data model.

First, I'm assuming your current data model is something like this:

Looky1__c
    Parent_Account__c
    Related_Account__c

Looky2__c
    Parent_Account__c
    Related_Account__c *lookup filter goes here

Here's the adjustments you need to make:

Looky1__c
    Parent_Account__c
    Related_Account__c

Looky2__c
    Parent_Account__c
    Looky1_Lookup__c *lookup filter goes here

Your lookup filter for Looky2__c.Looky1_Lookup__c will be something along the lines of

Looky2__c.Parent_Account__c == Looky1_Lookup__c.Parent_Account__c

This filter will ensure that only Looky1__c records of the same parent account as Looky2__c will appear on the lookup dialog. By modifying the Lookup Dialog Search Layout of the Looky1__c object, you'll be able to give your users the effect of "looking up" an account record, even though they're actually looking up Looky1__c records.