Lookup filter: Filter on shared parent object of another lookup on current record

Question

I have a 'joint' object between assets and contacts. The object contains two lookup fields, one to contacts, one to assets. The idea is to allow a many-to-many association between the two objects.

I'd like to make filters for these lookup field to only return records related to the mutual Account record, based on whichever lookup field has a value entered.

Example: If the asset lookup field is populated with an asset from Account X, I want the contact lookup field to only allow contacts from Account X. And vice versa, preferably.

However, I've discovered that while I can base filter rules on values of lookups on the current record, I can't base it on other fields of the related object. So I can't have the related AccountID of the asset lookup compared to the AccountID of the contact lookup!

Any way to get what I want?

Answer 1

While there is no direct solution to my scenario, here is the workaround I used:

1. I added a lookup field for the mutual object (in this scenario, an Account lookup)

2. I created custom buttons to replace the standard 'New' button for the joint object's related lists that appear on both the contact and asset page layouts.

3. I used URL hacking on these buttons to prepopulate the account lookup with the Asset or Contact's account info.

That way the Account lookup is already populated when someone clicks 'new' from either the asset or contact pages. And since the account lookup is now present on the joint object, lookup filters using that account info can be applied to the related contact and asset lookups.