Hello everyone;
Recently while answering questions on the Microsoft Forums a question was raised about how to filter the query of an addPreSearch lookup event based on values in a relationship.
The scenario we’re looking at is we have a table called “Collection”. This table has a M:1 relationship with director and it has a 1:M relationship with a table called Movies. The Movies table has a M:1 relationship with the director table.
The requirement is to have the Director lookup prefilter and only show a list of directors from movies related to the collection. In the picture below when we click on the Directors lookup we would expect to see 6 directors selectable based on the associated movies.
How we go about this is using three specific methods:
- addCustomFilter (https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomfilter)
- addPreSearch (https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addpresearch)
- Xrm.WebApi.retrieveMultipleRecords(https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords)
The addPreSearch allows you to specify what event will fire when a user clicks on the lookup. In this function you to pass in a function and it must then add a custom filter.
– formContext.getControl(arg).addPreSearch(myFunction)
The addCustomFilter function is an event that executes when you click on the lookup field and executes the filter or fetchxml filter passed into it.
– formContext.getControl(arg).addCustomFilter(filter, entityLogicaName)
The Xrm.WebApi.retrieveMultipleRecords is a method that allows us to retrieve multiple records but this is an asynchronous call which causes the issue of building the proper filter in this scenario.
What we need to do to achieve the goal of filtering the directors lookup based on the related movies to the collection are the following steps:
- Execute a Xrm.WebApi.retrieveMultipleRecords allowing us to build the filter statement with the proper directors.
- Once the filter statement has been completed we need to call the addCustomerFilter function.
- We need to have our function that calls the addPreSearch when the lookup event is changed using the filter statement.
Let’s dive into the code. I’ll post my full web resource file and we can discuss it in more detail below. This web resource file can also be found on github at https://github.com/jasoncosman/Collections/blob/master/jcmvp_collection.js.
OnLoad: function (executionContext) {
JCMVP.Collection.Form.getCollectionsMovies(executionContext);
},
filterMovieDirectors: function () {
//https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomfilter
formContext.getControl("jcmvp_director").addCustomFilter(fetchDirectorFilter, "jcmvp_director");
},
getCollectionsMovies: function (executionContext) {
formContext = executionContext.getFormContext();
var collectionID = formContext.data.entity.getId().replace("{", "").replace("}", "");
//https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords
Xrm.WebApi.retrieveMultipleRecords("jcmvp_movie", "?$select=_jcmvp_director_value&$filter=(_jcmvp_collection_value eq " + collectionID + ")").then(
function success(result) {
debugger;
if (result.entities.length > 0) {
fetchDirectorFilter = "<filter type='and'> <condition attribute = 'jcmvp_directorid' operator = 'in' uitype='jcmvp_director'>";
}
for (var i = 0; i < result.entities.length; i++) {
//_jcmvp_director_value
fetchDirectorFilter += "<value>" + result.entities[i]._jcmvp_director_value + "</value>";
console.log(result.entities[i]);
}
if (result.entities.length > 0) {
fetchDirectorFilter += "</condition ></filter >";
//https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/controls/addcustomfilter
formContext.getControl("jcmvp_director").addPreSearch(JCMVP.Collection.Form.filterMovieDirectors);
}
},
function (error) {
console.log(error.message);
// handle error conditions
}
);
}We start with calling the getCollectionsMovies function on the form load event. This function will execute our query to get the related movies and director column filtering on the collection table id your currently on.
– Xrm.WebApi.retrieveMultipleRecords(“jcmvp_movie”, “?$select=_jcmvp_director_value&$filter=(_jcmvp_collection_value eq ” + collectionID + “)”).then(
This is an asynchronous call that when it completes it will work towards building our filter and storing it on our global variable “fetchDirectorFilter”. An example of the built filter statement looks like this:
“<filter type=’and’> <condition attribute = ‘jcmvp_directorid’ operator = ‘in’ uitype=’jcmvp_director’><value>889c9059-865d-eb11-a812-000d3a84f1a3</value><value>03fa2f78-865d-eb11-a812-000d3a84f1a3</value><value>889c9059-865d-eb11-a812-000d3a84f1a3</value><value>40d8e290-865d-eb11-a812-000d3a84f1a3</value><value>78d8e290-865d-eb11-a812-000d3a84f1a3</value><value>93034497-865d-eb11-a812-000d3a84f1a3</value><value>15920af3-6d5f-eb11-a812-000d3a84f1a3</value></condition ></filter >”.
After our filter statement has been created we’re going then go ahead and use the addPreSearch function. formContext.getControl(“jcmvp_director”).addPreSearch(JCMVP.Collection.Form.filterMovieDirectors);). This will allow for when the user clicks on the director lookup on the collection form it fires the filterMovieDirectors function.
When the user clicks on the director field it will execute the function filterMovieDiretors. This function will use our built filter and set it for the lookup. formContext.getControl(“jcmvp_director”).addCustomFilter(fetchDirectorFilter, “jcmvp_director”);
This code scenario is covering when the form loads or refreshes. What it doesn’t cover is updating the director list when a movie is added to the collection. I’ll work through this and post an additional blog post when I have that completed.