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.