TransWikia.com

Filter for lookup on object in many-to-many relationship

Salesforce Asked by DGeorgiev on January 8, 2021

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:

SELECT Name from B WHERE B.ID IN (SELECT B_ID FROM A_B_Asoc WHERE A_ID = :Opportunity.A_ID)

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.

One Answer

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.

Answered by crmprogdev on January 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP