Historically filtering the N:N Add Existing lookup has not been something that we could do in a supported way. It’s been possible to do this using unsupported JavaScript, which originated back in CRM 4.0, and has needed to be rewritten for CRM 2011, CRM 2011 UR12, CRM 2013, and CRM 2016 because of CRM changing the way things work. So pretty much every major release has broken this.
Now that version 9.0 has the ability to show custom lookup objects in a supported way, it’s about time we solved this problem using supported code. By the way, the unsupported CRM 2016 code still works fine with v9… for now.
The idea of making this use supported code is pretty simple. First, replace the existing Add Existing command function with a custom function. Have the custom function launch a lookup window where we have control over the filters etc. Take the selected records and associate them with our record using the Web API (unfortunately there’s no nice “Associate” wrapper in the Xrm.WebApi, so it’s the long way for this).
THE CODE
If you can’t copy the code below, you can also get it from the GitHub project I created to keep this code in a central location:
// Custom function to call instead of the OOTB Add Existing button/command - all 3 parameters can be passed as CRM Parameters from the ribbon
function filterAddExistingContact(selectedEntityTypeName, selectedControl, firstPrimaryItemId) {
if (selectedControl.getRelationship().name == "account_contact_consultant" {
// Custom Account -> Contact N:N - filters to show only contacts with this account as the parentcustomerid
var options = {
allowMultiSelect: true,
defaultEntityType: "contact",
entityTypes: ["contact"],
disableMru: true,
showNew: true,
searchText: "\n", // Search by default
filters: [{
entityLogicalName: "contact",
filterXml: "<filter type='and'> <condition attribute='contacttypemulti' operator='contain-values'> <value>123750001</value> <value>123750002</value></condition> </filter>"
}]
};
lookupAddExistingRecords(selectedControl.getRelationship().name, "account", "contact", firstPrimaryItemId, selectedControl, options);
}
else {
// Any other contact relationship (N:N or 1:N) - use default behaviour
XrmCore.Commands.AddFromSubGrid.addExistingFromSubGridAssociated(selectedEntityTypeName, selectedControl);
}
}
// relationshipName = the schema name of the N:N or 1:N relationship
// primaryEntity = the 1 in the 1:N or the first entity in the N:N - for N:N this is the entity which was used to create the N:N (may need to trial and error this)
// relatedEntity = the N in the 1:N or the secondary entity in the N:N
// parentRecordId = the guid of the record this subgrid/related entity is used on
// gridControl = the grid control parameter passed from the ribbon context
// lookupOptions = options for creating the custom lookup with filters: https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-utility/lookupobjects
function lookupAddExistingRecords(relationshipName, primaryEntity, relatedEntity, parentRecordId, gridControl, lookupOptions) {
Xrm.Utility.lookupObjects(lookupOptions).then(function (results) {
if (results.length > 0) {
// Get the entitySet name for the primary entity
Xrm.Utility.getEntityMetadata(primaryEntity).then(function (primaryEntityData) {
var primaryEntitySetName = primaryEntityData.EntitySetName;
// Get the entitySet name for the related entity
Xrm.Utility.getEntityMetadata(relatedEntity).then(function (relatedEntityData) {
var relatedEntitySetName = relatedEntityData.EntitySetName;
// Call the associate web api for each result (recursive)
associateAddExistingResults(relationshipName, primaryEntitySetName, relatedEntitySetName, relatedEntity, parentRecordId.replace("{", "").replace("}", ""), gridControl, results, 0)
});
});
}
});
}
// Used internally by the above function
function associateAddExistingResults(relationshipName, primaryEntitySetName, relatedEntitySetName, relatedEntity, parentRecordId, gridControl, results, index) {
var formContext = gridControl.formContext;
if (index >= results.length) {
// Refresh the grid once completed
formContext.ui.setFormNotification("Associated " + index + " record" + (index > 1 ? "s" : ""), "INFO", "associate");
if (gridControl) { gridControl.refresh(); }
// Clear the final notification after 2 seconds
setTimeout(function () {
formContext.ui.clearFormNotification("associate");
}, 2000);
return;
}
formContext.ui.setFormNotification("Associating record " + (index + 1) + " of " + results.length, "INFO", "associate");
var lookupId = results[index].id.replace("{", "").replace("}", "");
var lookupEntity = results[index].entityType || results[index].typename;
var primaryId = parentRecordId;
var relatedId = lookupId;
if (lookupEntity.toLowerCase() != relatedEntity.toLowerCase()) {
// If the related entity is different to the lookup entity flip the primary and related id's
primaryId = lookupId;
relatedId = parentRecordId;
}
var association = { '@odata.id': formContext.context.getClientUrl() + "/api/data/v9.0/" + relatedEntitySetName + "(" + relatedId + ")" };
var req = new XMLHttpRequest();
req.open("POST", formContext.context.getClientUrl() + "/api/data/v9.0/" + primaryEntitySetName + "(" + primaryId + ")/" + relationshipName + "/$ref", true);
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.onreadystatechange = function () {
if (this.readyState === 4) {
req.onreadystatechange = null;
index++;
if (this.status === 204 || this.status === 1223) {
// Success
// Process the next item in the list
associateAddExistingResults(relationshipName, primaryEntitySetName, relatedEntitySetName, relatedEntity, parentRecordId, gridControl, results, index);
}
else {
// Error
var error = JSON.parse(this.response).error.message;
if (error == "A record with matching key values already exists.") {
// Process the next item in the list
associateAddExistingResults(relationshipName, primaryEntitySetName, relatedEntitySetName, relatedEntity, parentRecordId, gridControl, results, index);
}
else {
Xrm.Utility.alertDialog(error);
formContext.ui.clearFormNotification("associate");
if (gridControl) { gridControl.refresh(); }
}
}
}
};
req.send(JSON.stringify(association));
}
The first function is an example of how to use this. In this example it’s filtering the contact lookup on a custom N:N between account and contact, which only shows contacts associated to the account via the parent customer. The bottom two functions are generic functions that perform the lookup/associate logic, so they don’t need to change.
HOW TO USE IT
First add the code above into a JavaScript web resource. The first function will need to be tweaked based on your filtering requirements. In my example, the selectedEntityTypeName, selectedControl, and firstPrimaryItemId are CRM parameters passed in from the ribbon context. We’re checking the relationship matches what we’re expecting, then we create the lookup options by specifying the entityTypes, and customFilters (as well as other options defined here: http://butenko.pro/2017/11/22/microsoft-dynamics-365-v9-0-lookupobjects-closer-look).
The primaryEntity and relatedEntity parameters will depend on which side you created the N:N from. Typically the primary will be the one which comes first in the relationship schema name, but if it doesn’t work just switch them around (blame Web API for this).
If we needed to filter N:N’s for other entities, we’d just need to copy this first function and modify it as needed for our other requirements. If we need to filter multiple N:N’s or 1:N’s for the same entity, just add an “else if” on the relationship name check to perform your specific logic.
Once you’ve got the JavaScript sorted, you need to customize the ribbon button (using the ribbon workbench). If you’re filtering a N:N, you need to customize the command for the N:N button (called AddExistingAssoc) on the SubGrid ribbon for your related entity. If you’re filtering a 1:N, you need to customize the command for the AddExistingStandard button.
The code in this blog works the same for both 1:N and N:N, so you can use the same functions for both, and just check the relationship name so you know which is which. Also make sure to use the relationship schema name from the 1:N relationship, not the lookup name.
Change the command to use your custom function and library, and then tweak the parameters to pass in the required CRM values. In my case I just needed to add the FirstPrimaryItemId. Also remember to update all the Mscrm display rules/enable rules to “Un-customised = True”.
Publish, and that’s it. The rest should just work. I also added a custom notification indicator to show the associate progress, since Web API does the associates asynchronously, if there’s a lot of records being associated it can take a while to complete, so the notification makes the user experience a bit nicer.
