Microsoft CRM Advanced Find – Powerful Queries through Regarding and Related Records

Our CRM product includes advanced find functionality that allows users to create complex queries in order to return specific datasets.  Once a set of data is isolated, the user can do many different CRM actions.  Several common examples are listed below:

  • Work with the precise set of data in a variety of ways (ex. edit, bulk email, etc.)
  • Export the data to Excel to do further analysis. If the data is exported in a “dynamic” fashion, users can refresh the data within Excel. Many users also use the export to Excel feature in order to create a template to use when importing data in the future.
  • Create a saved view. A saved view allows you to see your queries criteria in a personal view to use in the future. If others would find it useful, then it’s very easy to share with other users.

When building advanced find queries, you can work with attributes directly on the entity that you are working with, as well as attributes from, related entities.  These related entities are most commonly linked through the regarding attribute that is displayed on forms.  For example, it’s very easy to create an advanced find query on the contact entity that has tasks that contain “Potential Prospect” in the subject line.  As long as the tasks contain the subject and an account in the regarding attribute, the following query would work well:

Image #1

Image #2

I’ve recently had a request from a customer to assist them in creating an advanced find query that was a bit trickier to construct. 

Issue –

A marketing list consisting of contacts was created and used on a campaign.  CRM letters were created through the campaign as campaign activities.  By accident, the marketing list was deleted.  They later found that they needed the marketing list for future use.  Their hope was to use an advanced find on the contact entity based off letters regarding the campaign activity.  The data looked like this:

Image #3

Image #4

It seemed that this would be an easy query to accomplish.  They assumed that since the letters in question were showing under the related contact’s activities, the advanced find listed below would work:

Image #5

The above query actually won’t work as the contact isn’t technically related to the letter through the regarding attribute.  Microsoft CRM applies special functionality to activities.  If there are CRM records listed as parties (To, Cc, Bcc, Sender, Recipient, etc.) to the activity, the activity will still show up in the related entities activities section.  If you take a look at the letter shown in Image #4, you’ll see that it is listed in the activity view of the contact in Image #3.  This is because Jon Doe is listed as the Recipient on the letter.

Resolution –

In order to create an advanced find that returns contacts that are related to activities that are related to a specific campaign activity (wow…that was a mouthful), you must use the Activity Parties (Party) related entity when building the query.  This is necessary because the contact is related to the letter through the activity party, rather than through the regarding attribute.  The screenshots below show the final results:

Image #6

Image #7

Image #8

– Jon Strand


Comments (2)

  1. Engagedots says:

    <a href="">CRM software</a> an essential thing to meet up the business needs. when it exactly matches our business. It enhances business ROI.

  2. fed up says:

    Why can't you simply add columns of related entities