Microsoft CRM 4.0 - Advanced find returns duplicate records

  • Article

Based on your attribute configuration you might have experienced the following in the past.

The Advanced Find will return duplicate records. This could happen if the main entity has multiple related entities associated. The following screenshots (for the account entity) should help to have a better understanding of the scenario.

The below sample uses the account “Active Cycling” with two associated contacts. The contacts are setup like this:

clip_image002

clip_image004

The following Advanced Find query will return duplicate records for the single account:

clip_image006

clip_image008

The reason for this behaviour is the configuration of the AccountID attribute in the account entity. For this attribute the searchable setting was set to “no”. If this is the fact, the following SQL statement will be created when you perform the Advanced Find.

select top 51 account0.Name as 'name', account0.Address1_City as 'address1_city' ….

If you configure the searchable setting for the attribute AccountID with ”yes”, the following SQL statement will be created when you perform the Advanced Find.

select DISTINCT top 51 account0.Name as 'name', account0.Address1_City as 'address1_city' ….

If you change the searchable setting for lookup or uniqueidentifier attributes to “No” this can cause duplicate results. But this only happens if you select related entity types that join multiple entity types together.

clip_image010

Greetings

The CRM Team