I have two custom objects - A and B, which have many-to-many relationship, presented by a third custom object - A_B_Asoc. A ------> A_B_Asoc <------ B

In Opportunity, I have a lookup to A, whose value comes from the converted lead. I need to be able to select a value for B, the lookup being filtered only to B records, for which exists an association record in A_B_Asoc, for which A equals the value coming from the Lead. To clarify, the filter query would look like below in simple SQL:


The only working solution we have so far is embed a VF page in the standard layout, and in the extension controller get the list of relevant B records with a SOQL query. The downside is that in order to save the selected B value to the Opportunity record, we need to have a Save button in the VF page. Considering that the rest of the Opportunity record is presented in standard layout, which also has a default Save button, it is a very non-user-friendly solution.

What would be the best architectural/object-modelling approach to this use case, so that we could keep all in standard layout and yet achieve the requirement for such a filter?

I have looked at this similar question but no solution is provided there.

1 Answer 1


As I understand your question, you already have A.Id which is the Id of A_Id, and from the junction object that should provide you with one B.Id which is the Id of a B_ID record. At that point, it becomes a single record that you're looking for. If that's the case, I believe your query should look something like one of the two queries below:

B rcrdB = new rcrdB();

rcrdB = [SELECT B.Id, B.Name FROM B WHERE (B.Id =: B_ID__r) AND (B_ID__r IN: A_B_Asoc__r) 
     AND A_ID IN: A_B_Asoc__r) AND A_ID =: Opportunity.A_ID limit 1];

rcrdB = [SELECT B.Id, B.Name, A_ID__r, B_ID__r, A_B_Asoc__r FROM B WHERE (B.Id =: B_ID__r) 
     AND (A_ID__r =: Opportunity.A_ID) AND (B_ID__r IN: A_B_ASoc__r AND A_ID__r 
     IN: A_B_ASoc__r) limit 1]; 

I think the latter is closer to what you're looking for. Hopefully this will at least point you in the right direction.

You must log in to answer this question.

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