Enhancing Quick Find in CRM with configurable custom query language

Unlike Advanced Find, Quick Find in CRM is pretty rudimentary. While you can specify the search columns in the Quick Find View, the Quick Find text query does not allow you to specify field filtering on the target record, or on one or more levels of related records.

Advanced Find is great for performing these types of more complex searches, but it requires multiple clicks and is time consuming to setup. This is not ideal in scenarios where users need to be able to find records quickly and there are a high number of records and enquiries, e.g. in call centres.

Here are some examples of searches that are not possible with Quick Find:

  • Find Accounts where “McCloud” is a Contact (in First Name, Last Name or Personal Notes fields)
  • Find Accounts where Account Name contains “tech”, and “McCloud” is a Contact (in First Name, Last Name or Personal Notes fields), and that Contact has a Case that contains “laptop problem” in Case Title or Description
  • Find Accounts where any associated Contact has a Case regarding the Product “Laptop”
  • Find Accounts where any associated Contact has a Case regarding the Product “Laptop”, and where the Account has an associated Opportunity that contains “laptop” in the Description

I have developed a solution that allows users to perform searches similar to the above using Quick Find. The solution essentially augment Quick Find with a custom query language that allows users to specify filtering in the query text. With exception for syntax, the query language is configurable and can be extended to OOTB and custom entities. In fact, the query syntax is similar to that used by Outlook, Gmail and SharePoint.

For example, the enhanced query allows users to perform the above searches using the following:

Search Enhanced Query Examples (Configurable)
Find Accounts where “McCloud” is a Contact (in First Name, Last Name or Personal Notes fields)  contact:mccloud
Find Accounts where Account Name contains “tech”, and “McCloud” is a Contact (in First Name, Last Name or Personal Notes fields), and that Contact has a Case that contains “laptop problem” in Case Title or Description  *tech contact:mccloud\case:”laptop problem”
Find Accounts where any associated Contact has a Case regarding the Product “Laptop”  contact\case\product:laptop

or shortcut:

case-prd:laptop

Find Accounts where any associated Contact has a Case regarding the Product “Laptop”, and where the Account has an associated Opportunity that contains “laptop” in the Description case-prd:laptop opp:laptop

So how does it work?

After installing the solution (download at the end of this post), the Quick Find Configurations entity is available to system administrators under Settings.

Create a Quick Find Configuration record for each entity where you want to enable the enhanced Quick Find.

The following fields are required:

Field Description
Entity Name The CRM name of the target entity. Type-ahead is enabled to help you locate the correct entity.
Configuration Name The name of the Quick Find Configuration record. For descriptive purpose only. Defaults to the display name of the selected target entity.
Configuration XML that describes the search schema for the target entity. A skeleton XML is automatically generated for you by default but you will need to update it. The schema of this XML is described in details later in this post.

Enhanced Quick Find is automatically enabled for the target entity once you save the Quick Find Configuration record.

Once enabled, users can start using the enhanced query in the target entity’s Quick Find:

You can update the enhanced Quick Find configuration for any entity at anytime by editing the relevant Quick Find Configuration record. To disable enhanced Quick Find for an entity, delete or deactivate the relevant configuration record. Reactivating a Quick Find Configuration record re-enables enhanced Quick Find for the relevant entity.

Hmm… what is the query syntax?

The syntax for the enhanced query is as follow:

The query can contain text criteria and filter parts.

  • Text criteria:   – this is searched against the Quick Find columns of the target entity as per OOTB behaviour
  • Filter parts:     – these apply additional filters to the result set that would be returned by the text criteria

Text criteria and filter parts are optional and can be specified in any order. A query can contain multiple filter parts. The AND operator is used between the text criteria and specified filter parts.

Filter part

A filter part consists of an alias for a related entity, and a criteria for that entity. The alias, and the fields to search against for that alias, are configurable in the configuration schema (more on this later). A colon (:) is used to separate the alias and its criteria. Wrap the criteria in double quotes (“) if it is more than one word. Criteria are searched against defined search fields using the LIKE operator.

A filter part can target a related entity at any level of the relationship hierarchy (grandchild, great grandchild etc.). This is done by using the backslash character (\) to specify the relationship path.

Here are some examples.

Example Effect
contact:abc Filter the result set to those with a related ‘contact’ where ‘contact’ matches ‘abc’
contact\case:xyz Filter the result set to those with a related ‘contact’ where ‘contact’ has a related ‘case’, AND ‘case’ matches ‘xyz’
contact:”abc 12″\case:xyz Filter the result set to those with a related ‘contact’ where ‘contact’ matches ‘abc 12’, AND contact has a related ‘case’ that matches ‘xyz’

Ok… tell me about this configuration schema!

The XML that you specify in the Configuration field of the configuration record defines the search schema and query language available to the users for the target entity.

Below is the annotated XML configuration that would enable the example searches earlier on in this post.

<?xml version="1.0" encoding="utf-8" ?>

<!-- enableLogging: Optional. When set to true, details of how 
the query was interpreted are logged to CRM's tracing service.
Default is false. -->
<QuickFindConfiguration enableLogging="false">
	<QueryTransformation>
		<AliasReplacements>

			<!-- Defines a set of alias replacements for the 
query. Aliases matching the list below will be replaced with the 
specified values before the query is processed. Multiple alias 
replacements are supported. 

	replace: Required. The alias to look for. 
	with: Required. The value to replace the match with. -->
			<AliasReplacement replace="case-prd" with="contact\case\product"/>
		</AliasReplacements>
	</QueryTransformation>
	
	<!-- Defines an entity for quick find augmentation. The 
target entity of the quick find MUST be defined, as well as 
other relationships that are allowed in the augmented query. 

	entityName: Required. The CRM name of the entity. -->
	<Entity entityName="account">
		<Relationships>

			<!-- Defines a relationship from/to the entity 
that can be searched in the query. Multiple relationships are 
supported. 

	alias: Required. The alias used in the query to refer to the 
related entity, e.g. contact:ABC. 

	entityName: Required. The CRM name of the related entity. 
An Entity element with a matching entityName MUST also be 
defined. 

	fromField: Required. The name of the field that establishes 
the relationship between the parent entity and the related 
entity. 

	toField: Required. The name of the field that establishes 
the relationship between the parent entity and the related 
entity. -->
			<Relationship alias="contact" entityName="contact" fromField="parentcustomerid" toField="accountid"/>
			<Relationship alias="opp" entityName="opportunity" fromField="parentaccountid" toField="accountid"/>
		</Relationships>
		<FieldGroups>

			<!-- Defines a virtual grouping of fields 
on the target entity of the quick find. The grouping can be 
referred to in the query using an alias, e.g. profile:"XYZ". 
Groupings will only be applied against the target entity of the 
quick find. Multiple groupings are supported. 

	alias: Required. The alias used in the query to refer to 
this group of fields on the target entity. -->
			<FieldGroup alias="profile">
				<SearchFields>

					<!-- Defines a field on 
the target entity that belongs to this grouping. Multiple 
fields are supported and are applied to the search using the OR 
operator. 

	name: Required. The CRM name of the field. -->
					<Field name="industrycodename"/>
					<Field name="sic"/>
				</SearchFields>
			</FieldGroup>
		</FieldGroups>
	</Entity>
	<Entity entityName="contact">
		<SearchFields>

			<!-- Defines a field on this particular 
entity that will be used in the query for filtering. Multiple 
fields are supported and are applied to the search using the 
OR operator. 

	name: Required. The CRM name of the field. -->
			<Field name="firstname"/>
			<Field name="lastname"/>
			<Field name="description"/>
		</SearchFields>
		<Relationships>
			<Relationship alias="case" entityName="incident" fromField="customerid" toField="contactid"/>
		</Relationships>
	</Entity>
	<Entity entityName="incident">
		<SearchFields>
			<Field name="title"/>
			<Field name="description"/>
		</SearchFields>
		<Relationships>
			<Relationship alias="product" entityName="product" fromField="productid" toField="productid"/>
		</Relationships>
	</Entity>
	<Entity entityName="product">
		<SearchFields>
			<Field name="name"/>
		</SearchFields>
	</Entity>
	<Entity entityName="opportunity">
		<SearchFields>
			<Field name="description"/>
		</SearchFields>
	</Entity>
</QuickFindConfiguration>

Entity element

Entity elements define the entities that may be involved in the enhanced Quick Find for the target entity. An Entity element must exist for the target entity. An Entity element must also exist for any relationship that the user should be able to filter on in the enhanced query.

Relationship element

Relationship is a child element of an Entity element, and defines the related entities that could be used in the query for that parent entity.

The Relationship element defines the alias that the user can use to refer to the related entity in the query. A corresponding Entity element must exist for the related entity identified by the Relationship element.

SearchFields/Field element

The SearchFields/Field element is a child element of an Entity element, and is used to define a field that should be searched against when filtering on that entity. Multiple SearchFields/Field elements can be specified, in which case the OR operator will be used when applying the filter criteria against that particular entity.

FieldGroups/FieldGroup element

The FieldGroups/FieldGroup element is a child element of the Entity element, and allows you to define an alias that users can use to apply filtering criteria directly to the target entity (rather than related entities).

Normally the filter ‘alias:criteria‘ is applied to a related record type (identified by alias). The FieldGroup element allows to you define an alias that refers to a group of fields on the target entity.

For example, Account has an Industry and SIC Code fields. Using the FieldGroup element, you can enable users to filter against these particular fields when searching for Accounts.

Given the XML above, users can search for Accounts with ‘software’ in Account Name and ‘tech’ in the Industry or SIC Code fields using:

*software profile:tech

A FieldGroup element contains one or more SearchFields/Field elements that define the fields belonging to this group. When multiple fields are specified, the OR operator will be used when applying the filter criteria.

Note that FieldGroup elements are only applicable to the target entity.

QueryTransformation/AliasReplacements/AliasReplacement element

This element allows you to apply transformation to the user’s query before it is processed. This allows you to define shortcuts that users can use in their query. This is particularly useful when filtering against many-to-many related records, as without shortcuts, users would need to filter from the target entity to the many-to-many joining table, and then to the intended related record.

Filtering against OptionSet fields

Criteria are applied using the LIKE operator. When including an OptionSet field in the configuration schema you therefore should use the corresponding name field of the OptionSet field.

In case you are not already aware, all OptionSet fields in CRM has a corresponding ‘name’ field, which stores the text value of the selected OptionSet value. For example, Account has an Industry pick-list field. The name of this field is ‘industrycode‘ and the name of the corresponding OptionSet name field is ‘industrycodename‘.

Filtering against many-t0-many relationships

Each many-to-many relationship in CRM consists of two relationships under the hood: a 1-to-many relationship from entity A to the joining table, and a many-to-1 relationship from the joining table to entity B.

In order to filter against a many-to-many relationship in the enhanced query, you would need to specify both of these underlying relationships in the configuration schema. It is also recommended that you add a query transformation to give users a shortcut to filter the related entity more naturally.

For example, the configuration XML below can be used to enable filtering of Lead by Competitor.

<?xml version="1.0" encoding="utf-8" ?>
<QuickFindConfiguration enableLogging="false">
	<QueryTransformation>
		<AliasReplacements>
			<AliasReplacement replace="comp" with="leadcomps\comp"/>
		</AliasReplacements>
	</QueryTransformation>
	
	<Entity entityName="lead">
		<Relationships>
			<Relationship alias="leadcomps" entityName="leadcompetitors" fromField="leadid" toField="leadid"/>
		</Relationships>
	</Entity>
	<Entity entityName="leadcompetitors">
		<Relationships>
			<Relationship alias="comp" entityName="competitor" fromField="competitorid" toField="competitorid"/>
		</Relationships>
	</Entity>
	<Entity entityName="competitor">
		<SearchFields>
			<Field name="name"/>
		</SearchFields>
	</Entity>
</QuickFindConfiguration>

The query would be:

leadcomps\comp:”Competitor A”

or more naturally (thanks to QueryTransformation):

comp:”Competitor A”

How does it work underneath the hood?

When a Quick Find Configuration record is created, a plugin is triggered. This plugin automatically registers a new plugin on RetrieveMultiple of the target entity. The XML configuration is passed to the RetrieveMultiple plugin via its unsecured configuration.

The RetrieveMultiple plugin intercepts users’ queries and transforms it according to the XML configuration.

How about performance impact?

Performance impact of this solution is virtually none to minimal. This is because the majority of the work is in-memory string parsing and manipulation of the query object. There are no additional service calls made to CRM.

Where can users use this?

The enhanced query will be recognised in the following places:

  • Quick Find on the grid of enabled entities
  • Searching in look up forms

Unfortunately it won’t be recognised in the multi-entity search.

Support for OnPrem and Online

This solution works on both OnPrem and Online.

Download

You can download the solution ZIP here:

Note that only the managed solution adds the Quick Find Configuration entity to the sitemap. For the unmanaged solution, you will need to do this manually, or use Advanced Find to retrieve and create new records of this entity.

So in conclusion…

This solution enhances the OOTB Quick Find with a configurable query language and search schema that allow users to use text query to perform advanced searches. Users with exposure to search query in SharePoint, Outlook or Gmail should find this enhanced query syntax familiar.

Advertisements

About Bernado

Based in Australia, I am a freelance SharePoint and Dynamics CRM developer. I love developing innovative solutions that address business and everyday problems. Feel free to contact me if you think I can help you with your SharePoint or CRM implementation.
This entry was posted in CRM. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s