Dynamics 365 – Filter lookup with FetchXML (and how to deal with invalid XML format)

By Ritchey Hazeu

In some situations you can’t use the standard filtering options Dynamics 365 has to offer. For example when you use the same entity twice on a form. And you would like to lookup a new field and only filter it on the second entity.

In these cases you can use Javascript to Filter the Lookup yourself. Let’s say we are using the Order entity and want to filter a new field, let’s say Contact and filter it only on the lookup to Order on the Order form, not to the Order itself.

We will create the webresource and place it on OnChange of the Lookup. Don’t forget to check both boxes:

And how does the Javascript looks like?

function filterContactLookup(executionContext){

var formContext = executionContext.getFormContext(); // get the form context

if(formContext.getAttribute("new_orderid") != null) {

var orderField = formContext.getAttribute("new_orderid").getValue();
var orderFieldID = orderField[0].id;
var orderName = orderField[0].name;

formContext.getControl("new_contact").addPreSearch(function (){

    var fetchQuery = "<filter type='and'><condition attribute='new_orderid' operator='like' uiname='" + orderName + "' uitype='salesorder' value='" + orderFieldID + "' /></filter>";

    formContext.getControl("new_contact").addCustomFilter(fetchQuery);
});

        formContext.getControl("new_contact").removePreSearch(function(){

        var fetchQueryRemove = ""

        formContext.getControl("new_contact").addCustomFilter(fetchQueryRemove);

    });

}
}

This piece of code work fine when there are no special characters being used (for example the & character). And it happens quite often that the & character is being used in a name. I’ve tried all kinds of different options to fix this, like:

var fetchQuery = fetchQuery.replace(“&”, “&amp”);

fetchQuery = “?fetchQuery=” + encodeURIComponent(fetchQuery);

All different Xrm.Encoding options as noted on https://docs.microsoft.com/hr-hr/powerapps/developer/model-driven-apps/clientapi/reference/xrm-encoding but without success

and more options which works perfectly in C#….

Gladly for me there was a much easier option, and that was simply to remove the uiname from the fetchxml, and it kept working!

function filterContactLookup(executionContext){

var formContext = executionContext.getFormContext(); // get the form context

if(formContext.getAttribute("new_orderid") != null) {

var orderField = formContext.getAttribute("new_orderid").getValue();
var orderFieldID = orderField[0].id;
var orderName = orderField[0].name;

formContext.getControl("new_contact").addPreSearch(function (){

    var fetchQuery = "<filter type='and'><condition attribute='new_orderid' operator='like' uitype='salesorder' value='" + orderFieldID + "' /></filter>";

    formContext.getControl("new_contact").addCustomFilter(fetchQuery);
});

        formContext.getControl("new_contact").removePreSearch(function(){

        var fetchQueryRemove = ""

        formContext.getControl("new_contact").addCustomFilter(fetchQueryRemove);

    });

}
}

It took me a few hours to notice this…. So hopefully it will save you some time.

And please let me know if there are other options, for example when you don’t got the GUID and only the uiname.