[SalesForce] Lookup Filter on Junction Object referencing ID Field

I have a Master-Detail Relationship From an Object Flat to an Object Expense and also from Flat to an Object Housemate. Furthermore Expense and Housemate have a Many-To-Many Relationship through Junction Object ExpenseToHousemate.

                    ----------Flat__c---------
            -------|                          |---------
           |                                            |
      Expense__c---------ExpenseToHousemate__c------Housemate__c
       Flat__c(MD)       Expense__c(MD)              Flat__c(MD)
                         Housemate__c(MD)

I want to create a Lookup Filter on the junction object, where I only show Housemates which are part of the same flat of which the expense belongs to.
So far i cant reference the Flat__c Field on Expense from the junction Object, only the Flat__c field on housemate is available.

The Question is: Can i solve this with an Lookup Filter and how, or are there alternative ways, if possible without code since i strive to avoid code where its possible.

Thanks for Helping

Best Answer

Sadly this is not possible:

https://success.salesforce.com/answers?id=90630000000CohIAAS

The first relationship needs to be established in order to apply Lookup-Filter logic on the second relationship.

The Workaround is (like mentioned in the link) a validation rule to prevent Users from saving wrong relationships (at least correct data even if the experience is not the same) or custom code.

Related Topic