Lookup Filter on Junction Object - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Traci RiceTraci Rice 

Lookup Filter on Junction Object

I created an object called "Pitches" and linked it to the opportunity object. I then created a junction object for Contacts and Pitches to mimic the Contact Roles on the opportunity. I'd like to filter the available contacts on the junction object to only return the contacts from the related account rather than all contacts. I tried setting a filter on Contact:AccountID to Contact: Account: AccountID and every variation of those two but I still get all contacts. Is it possible to filter to just the related account? I've done this in other places but never on a junction object. 
Geoffrey FlynnGeoffrey Flynn
Hi Traci,

Related Account as in the Account on the Opportunity?  The problem with a junction object is that the lookup hasn't been committed to the database yet, so there is nothing to filter.  
Jon TreskoJon Tresko
If one of the objects has a relationship to Account you can, but that would already need to be saved. If you click create on your junction from a related list on something like opportunity, you could match the opportunity account. As Geoffry said, the relationship needs to be established already to apply a filter.
Jeff MayJeff May
Instead of a Lookup filter, you can use a Validation Rule.  The Rule will only work if the first relationship is already in place, and it won't 'filter' the Lookup dialog.  However, it will make sure that the Contact actually saved in the Junction object is on the same Account.
Traci RiceTraci Rice
Yes, the account on the opportunity. The flow is that once the opportunity is created the user goes tot eh Pitches related list and creates a new pitch. It is tied to the opportunity since it was created from the opportunity. I then want the user to be able to add contacts from the account to the pitch as participants with participant roles, just like the contact roles on the opportunity works. I have gone into pitches that were already created and saved and I can't get the filter to work.
Jeff MayJeff May
The Validation Rule approach will work for you.
Traci RiceTraci Rice
What is the validation rule validating? Will it allow me to add multiple contacts from the account to the pitch in a related list?
Jeff MayJeff May
The validation rule will be on your custom object and will check to make sure that the Contact.AccountId you are trying to add in the custom object record matches the Opp.AccountID that is already related to the custom object record.
Jon TreskoJon Tresko
Ya, I think validation is the only way to go here. The validation rule would say something like 
 
AND(Opportunity__r.AccountID = Contact__r.AccountID

The only other way to go is to try a Workflow rule that pulls down the account Id into a text field, then use that in the lookup filter. However, I think there's some limitations with using text fields? Seems like everytime I try to create one, I expect it to do more than possible, but don't remember exactly.

This page lays out when to use lookup filter vs validation: https://help.salesforce.com/apex/HTViewHelpDoc?id=fields_lookup_filters_notes.htm
Traci RiceTraci Rice
Maybe it’s getting late and my brain is fried but I don’t understand how a validation rule would work on the related list. Here’s a screenshot of the object (1). When I click New Pitch Participant – Client it takes me to a screen where I click a lookup where I select the contacts that were in the pitch (2). This is where I only want to see related contacts. Screenshot 1: [cid:image002.jpg@01D02EA4.F869EC70] Screenshot 2: [cid:image003.jpg@01D02EA4.F869EC70]
Traci RiceTraci Rice
screenshot 1
Screenshot 1

screenshot 2User-added image
Jon TreskoJon Tresko
So, the main problem it that you can only go to one-level of relationships in lookup filters. You could use fields on the object (opportunity), but not a parent of the object (account). So, since your requirement is not possible via lookup filters, we're suggesting a validation rule which will prevent a user from saving unless the contact is within the same account as the opp's account. I agree, the user experience is not as good as it's reactive vs proactive, but this is the best that can be done I think.

You could try a url hack to pre-populate an account field lookup. Save, then use the account id in your contact lookup field.

http://raydehler.com/cloud/clod/salesforce-url-hacking-to-prepopulate-fields-on-a-standard-page-layout.html
Jeff MayJeff May
@Traci -- the Validation Rule will be defined on the junction object and will check the Account related to the parent of the first lookup field record and make sure that the Account related to the record in the 2nd lookup matches.