One of the nice things about working with lookup fields on entity forms is the ability to filter the results programmatically via a form level JScript function. The steps for doing this, thankfully, are rather straightforward:

1. Prepare a FetchXML filter snippet that applies the type of filtering you want to perform. This can either be written manually, or you can build your filter within Advanced Find, download the FetchXML and then copy + paste the … node.
2. Access the control using the Xrm.Page.getControl object, and then use the addCustomFilter method to apply the filter
3. Finally, setup your function to fire on the OnLoad event, using the addPreSearch method to actually apply the filter to the lookup control.

If you are still scratching your head at the above, then fortunately there is an MSDN article on the subject which provides further guidance, including a handy example snippet that you can modify and apply straight to your CRM instance. What I like most about this method is that its application is simple, but its potential is quite huge. Utilising conditional logic within your Jscript function means that you could potentially filter results based on values on the form, via a web service request to another CRM record or based on the value in an external CRM/ERP system. Used correctly and prudently, it can help to make form level data inputting much more easier and context sensitive.

CRM has a number of special field types that are reserved for certain system entities. One example is the Customer Data Type field. This is, in essence, a special kind of lookup control that spans two entities - Account & Contact. System customisers are able to create additional fields with this data type, but are unable to create a customer lookup control that spans across 2 entities of choice. This is a real shame, as I think this feature would be really welcome and be widely applicable to a number of different business scenarios.

I was recently working with this particular field on the Contact entity in order to meet a specific requirement as part of a solution - for only certain Account records to appear and for no Contact records to be made available to select. One potential work-around would be to just create a new 1:N relationship between Account:Contact, but this seems rather unnecessary when there is already a relationship in place that could be modified slightly in order to suit requirements. I was therefore interested in finding out whether the addCustomFilter/addPresearch methods could be utilised in this case. After a small, yet sustained, period of severe head-banging, I was able to get this working; although the solution could be argued as being less than ideal.

For the above requirement, lets assume we have a custom field on our Account entity - Company Type - which we are using to indicate what type of UK company an Account is:

Once created, we then populate our CRM sample Account records with the value of ‘Limited Company (Ltd.) and run a quick Advanced Find to confirm they return OK:

Now, for this example, we want to apply a very basic filter on the Customer field on our Contact entity to only show Account records that equal ‘Limited Company (Ltd.)’. Here is our “first attempt” JScript function(s) to achieve this:

function onLoad() {
    Xrm.Page.getControl("parentcustomerid").addPreSearch(modifyCustomerLookupField);
}

function modifyCustomerLookupField() {
	fetchXML = "<filter type='and'><condition attribute='jg_companytype' operator='eq' value='140250000' /></filter>";
	Xrm.Page.getControl("parentcustomerid").addCustomFilter(fetchXML);
}

After creating a JScript Library, adding it to a form, calling the onLoad() function as part of the OnLoad event and, finally (whew!), pressing the magnifying class on the control, we immediately get an error message:

Here’s the error message:

As a next step, to try and resolve this, I remembered within the FetchXml … node, you can specify the name of the entity that you want to filter on. This is typically required when you are using in order to join together multiple records. So I modified my JScript function to include the entity name within the fetchXML filter:

function onLoad() {
    Xrm.Page.getControl("parentcustomerid").addPreSearch(modifyCustomerLookupField);
}

function modifyCustomerLookupField() {
	fetchXML = "<filter type='and'><condition entityname='account' attribute='jg_companytype' operator='eq' value='140250000' /></filter>";
	Xrm.Page.getControl("parentcustomerid").addCustomFilter(fetchXML);
}

With fingers crossed, I then tried again…but still got an error message, but a slightly different one. Some progress at least!

Once my head had returned from the desk in front of me, I looked back at the original error message, in particular the fact that the Contact entity does not contain the new field we have on our Account. This is to be expected, but is there some way we can “fool” CRM by also having a field with the same name on Contact, but which is not used for anything? Going back into my solution, I created a field with the exact same logical name on the Contact entity, making it clear that this field should not be used. The data type, description etc. does not matter, so long as the logical name is the same as our field above:

Going back to our form and trying again, we see that this looks to have done the trick - our three records are now returning successfully when we use the control! 🙂

Another good thing about this is that the custom filter will also apply when the user clicks on ‘Look Up More Records’; although the Contact entity can still be selected, no records will be returned for obvious reasons.

I was glad this was ultimately possible to get working; however, to play devil’s advocate for a few moments, it is worth noting the following if you choose to set this up within your own environment:

• Simply creating a new 1:N relationship between Account:Contact would be a more straightforward and less technical approach of achieving the above; you should review your requirements carefully and consider whether this approach would satisfy your objectives.
• By adding form level JScript to this entity, you may start to impact on your form load times, particularly if you have lots of other functions running on the form or if you implement additional logic into your function.
• It is arguably not best practice to have a field within CRM that is being used in a “hacky” manner, as outlined above. If you are happy to have such a field within your CRM environment, then you will need to ensure that you take a number of steps to clearly label this field and its purpose within the CRM. The last thing you want is for someone to delete it by accident.

If anyone else has found a better or alternative way of achieving the above, please let me know in the comments below!