I have the following structure:

Authority__c-----------------ContractToAuthotiy__c --------------------Contract__c
StartDate__c (Date)           Authority__c (MD Lookup)                  ContractStart__c
                              Contract__c (MD Lookup)       

So, Authority__c and Contract__c are custom objects, and ContractToAuthority__c is the junction object. When I create a Contract, I need to add authorities to the contract, and these authorities must fulfill the following criteria: Authority__c.StartDate__c < Contract__c.ContractStart__c. When the user adds a new authority to the contract, I want to filter the Authority__c Lookup field based on that criteria. Is that possible? Tia.

  • You can configure filter on Authority__c (Master Detail relationship) field on ContractToAuthotiy__c object. So after creating Contract__c record, you will be creating Junction record by picking Contract__c through lookup and use this Authority__c filtered lookup.
    – Vignex
    Dec 11, 2014 at 9:19
  • @Vignex -When I create a filter on the Authority__c lookup field, I can get to Authority__c.StartDate__c, but I cannot compare this to the contract fields.
    – Lily
    Dec 11, 2014 at 9:25
  • Please try creating a formula field of Contract__c.ContractStart__c in the Junction object and then you can use that field to compare with Authority__c.StartDate__c
    – Vignex
    Dec 11, 2014 at 9:41
  • @Vignex - I already tried that, and for some strange reason, first of all formula fields don't display on the junction object page (even though they are on the page layout), and in the filter, they also don't show. I created a test date (date field), and that shows on the page and is available in the filter, so I assume that formulas for some reason don't work on junction objects?
    – Lily
    Dec 11, 2014 at 9:44

1 Answer 1


I believe there are 2 ways to do this - 1. Bad one: use custom settings to store Contract__c.ContractStart__c and use it in the lookup filter for Authority__c (MD Lookup) 2. Better one: create a direct lookup relation between Authority__c and Contract__c so you will have both the fields in the lookup filter for Authority__c (MD Lookup)

let me know which one you use and why..

  • I am not sure if I can do solution 2, because Contract ---- Authority is a many to many relationship, hence the junction object. ...or am I misunderstanding something?
    – Lily
    Dec 11, 2014 at 9:27
  • @Lily, both are custom objects and even if they are many to many, SF doesnt understand that. That is why we use a junction object. But plz corect me if I am wrong
    – human
    Dec 11, 2014 at 9:29
  • So, I will leave the junction object in place and create an additional direct lookup relationship? I don't know whether it works, I just don't know how to implement this?
    – Lily
    Dec 11, 2014 at 9:36
  • @lily, Yes, I just tried it and it works.
    – human
    Dec 11, 2014 at 9:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .