DYNAMICS 365 FILTER LOOKUP FIELD BASED ON N:N RELATIONSHIP

How to filter a Lookup field based on a N:N relationship of a parent entity using JavaScript.

To show you this, I will use the Account entity, the Contact entity, and a custom entity Conference.

• The contact entity has a N:1 relation with the account entity (lookup field)
• The contact entity has a N:1 relation with the conference entity (lookup field)
• The account entity has a N:N relation with the conference entity (sub-grid)

I need to filter the conference lookup of the contact entity to list

• The conference records that are related to the contact parent account or
• The conference records that are not related to any account

Based on the above conditions, the users will not be able to choose a conference record that is related to other accounts.

Data Readiness > End-Result:

1. I created sample conference records
   
   
   
2. I associated Conference 1, Conference 2, and Conference 3 to 2 accounts records as shown below
   
   
   
   
   
3. I created a contact record and switch between the account records
4. Based on the account selected, the Conference lookup will list the relevant records
   • Conference 1 and Conference 2 records are related to the chosen account
   • Conference 3 record is related to the chosen account
   • Conference 4 and Conference 5 records are not related to any account
   
   
   
   
   

To apply this, you can call the below function

• On the onload event of the contact form
• On the onchange event of the account lookup

function addCustomViewConference() {
var parentAccount = Xrm.Page.getAttribute("parentcustomerid").getValue();
var fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">';
fetchXml += '<entity name="new_conference">';
fetchXml += '<attribute name="cak_conferenceid" />';
fetchXml += '<attribute name="cak_name" />';
fetchXml += '<attribute name="createdon" />';
fetchXml += '<order attribute="cak_name" descending="false" />';
fetchXml += '<link-entity name="cak_cak_conference_account" from="cak_conferenceid" to="cak_conferenceid" alias="ab" link-type="outer">';
fetchXml += '<attribute name="cak_conferenceid" />';
fetchXml += '</link-entity>';
fetchXml += '<filter type="or">';
fetchXml += '<condition entityname="ab" attribute="cak_conferenceid" operator="null" />';
if (parentAccount != null) {
fetchXml += '<condition entityname="ab" attribute="accountid" operator="eq" value="' + parentAccount[0].id + '" />';
}
fetchXml += '</filter>';
fetchXml += '</entity>';
fetchXml += '</fetch>';

var layoutXml = '<grid name="resultset" jump="cak_name" select="1" icon="1" preview="1">';
layoutXml += '<row name="result" id="cak_conferenceid">';
layoutXml += '<cell name="cak_name" width="200" />';
layoutXml += '<cell name="createdon" width="125" />';
layoutXml += '</row>';
layoutXml += '</grid>';

// RANDOM GUID
var viewId = '{55DCAB5F-CBCB-4A34-88F3-A4489FF9A8DB}';
Xrm.Page.getControl("cak_conferenceid").addCustomView(viewId, 'cak_conference', "Available Conferences", fetchXml, layoutXml, true);
}

Hope This Helps!