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?