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 '14 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 '14 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 '14 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 '14 at 9:44

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..

|improve this answer|||||
  • 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 '14 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 '14 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 '14 at 9:36
  • @lily, Yes, I just tried it and it works. – human Dec 11 '14 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.