Filtered lookup view on pre selected values on CRM form

Posted on Posted in MS CRM 2011, MS CRM 2013, MS CRM 2015, MS Dynamics, MS Dynamics CRM

Let’s say we have a scenario where we want user to see and selected filtered records on a lookup view on a CRM form.
In our example, we have a entity called ‘Contract Workstream’ which has a look up field called ‘PriceListId’ linked to ‘Price List’ entity.

When user creates a new Contract Workstream, he/she selects a price list item which is a lookup record of ‘Price List’ type.

We have an other lookup field on the form below that called ‘Default SOR’. This is a lookup field linked to ‘SOR’ entity.

When user clicks on SOR lookup icon, we only want user to see certain SORs records, in our case all those SORs which are part of above selected Price List.

First thing we need to is create a web resource on Contract Workstream entity and add following script/function (if you already have a JS file just add code to it)

function formOnLoad(priceListFieldName,sorFieldName) {
availableDefaultViewId = Xrm.Page.getControl(priceListFieldName).getDefaultView();
sorsUnderSelectedPriceList(priceListFieldName, sorFieldName, false);
}

This function will be passed with two field names from our Contract Workstream entity i.e. pricelistid and solid

next we declare our sorsUnderSelectedPriceList function as below

function sorsUnderSelectedPriceList(priceListFieldName, sorFieldName, resetSelection) {
var selectedPriceList = Xrm.Page.getAttribute(priceListFieldName).getValue();
if (selectedPriceList != null) {
var selectedPriceListId = selectedPriceList[0].id;
var selectedPricelistName = selectedPriceList[0].name;
if (resetSelection == true) {
Xrm.Page.getAttribute(sorFieldName).setValue(null);
}


var customViewId = "{455715e8-30b6-412b-83e9-dbc7db4c3554}";
var entityName = "ebecs_workordersorrevenue";

var customViewName = “Associated SORs for ” + selectedPricelistName + “”;

var fetchXml = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>” + “<entity name=’ebecs_workordersorrevenue’>” + “<attribute name=’ebecs_workordersorrevenueid’ />” + “<attribute name=’ebecs_name’ />” + “<attribute name=’createdon’ />” + ” <order attribute=’createdon’ descending=’false’ />” + “<filter type=’and’>” + “<condition attribute=’ebecs_pricelistid’ operator=’eq’ value='” + selectedPriceListId + “‘ />” + “</filter>” + “</entity>” + “</fetch>”; var layoutXml = “<grid name=’resultset’ ” + “object=’1′ ” + “jump=’ebecs_workordersorrevenueid’ ” + “select=’1′ ” + “icon=’1′ ” + “preview=’1′>” + “<row name=’result’ ” + “id=’ebecs_workordersorrevenueid’>” + “<cell name=’ebecs_name’ ” + “width=’200′ />” + “<cell name=’createdon’ ” + “width=’100′ />” + “</row>” + “</grid>”;

Xrm.Page.getControl(sorFieldName).addCustomView(customViewId, entityName, customViewName, fetchXml, layoutXml, true);
}
else {
Xrm.Page.getControl(sorFieldName).setDefaultView(availableDefaultViewId);

}
}

FetchXml query gets us results we want. We can change this as we need to.

Once all code is added to a web resource, go form properties of Contract Workstream, add web resource under Form Libraries and function name user Event Handlers

Screen Shot 2016-01-25 at 16.02.55

CRM JS Event Handler
CRM JS Event Handler

Save change and publish. Once publish and you go back to Contract Workstream record and open a look up view, you will notice you only see records which are part of selected Price List on the form.