Lookup Filter on Junction Object

Question

I have a Junction Object : Authorities

This has a lookup (MD) to Accounts and another lookup (MD) to Policy.

Accounts and Policy both have date fields called: Inception__c and Expirty__c

When I am on a Policy record, I click on "New Authority" and the Policy field is prepopulated. I now want to put a lookup filter on the Account lookup to restrict it to show only Accounts that are within the date range of Policy. This seems easier than it is, as I don't seem to be able to do this using the normal lookup filters?

My requirement in summary:

Accounts 1 ------ * Authority * ------- 1 Policy
Expiry__c                                 Expiry__c
Inception__c                              Inception__c

--> Authority is added from Accounts
--> When Policy is chosen, this needs to fall within Inception and Expiry date of account.

Any help is much appreciated.

Answer 1

It's not the most elegant solution but it wouldn't require code: you could create the Expiry and Inception date fields on the Authority object and pre-populate it using a custom new Authority button. Then you could use those fields in your filter criteria.