QueryExpression Filter Lookup by Name instead of ID CRM 2016

Paul Nieuwelaar, 12 April 2016

In CRM if we want to filter a query using a particular lookup value, e.g. if you had a lookup to Country and you wanted to query all Accounts where the Country is New Zealand, you could achieve this in a couple of different ways.

The first and most obvious way, is to directly reference the country in your query. If you're adding conditions to a view, advanced find, workflow, or business rule, this means selecting the lookup value, which stores the ID of the selected record in the condition.

If you're writing a QueryExpression in a C# plugin for example, you'd need to get the ID of the country and reference this in the query, e.g. 'new_countryid' equals '<guid>'.

Referencing the record by ID may work ok in your development environment, but as soon as you deploy the solution into a new environment where the reference data has different ID's, the query will break, as that ID is no longer valid.

The other common way to achieve the desired filtering, is to join on the reference entity, in this case Country, and filter on the Name directly, in this case New Zealand. This is much more reliable as unless the name of the reference data changes, the queries will always reference the same record regardless of changing ID's in different environments.

The above method works ok most of the time, but it's annoying to have to add a link entity, and it doesn't always work if you're doing complex filters, or if you're doing business rules where you can't filter on related entities.

An easier solution is to leverage the lookupidname attribute, which is available in fetch and query expressions when you're creating views, advanced find queries, workflows, business rules, and also plugins. This attribute is only available in conditions, i.e. you can't add it to your columnset, but it can be queried directly through code, e.g. 'new_countryidname' equals 'New Zealand'.

Using advanced find, views, workflows, or business rules is a bit different, since you can't directly select this attribute for conditions. However, if you query the lookup field using the Contains, Begins With, or Ends With operators, you'll be able to provide the text value to query against. When you download the FetchXML you'll notice it's actually querying the lookupidname attribute.

Querying like this essentially gives us the same result as adding a join, however the query is faster to create and execute, and we can build more complex filters without worrying about the limitations of business rules or using 'Or' conditions across entities.