Duplicate rule tables

Note

Unsure about entity vs. table? See Developers: Understand terminology in Microsoft Dataverse.

For information about how to configure duplicate rules in the application, see Administrators Guide: Set up duplicate detection rules to keep your data clean.

Duplicate detection rules are defined using the following tables:

  • DuplicateRule: To detect duplicates in the system, create a duplicate detection rule for a specific table type. You can create multiple detection rules for the same table type. However, you can publish a maximum of five duplicate detection rules per table type at one time.
  • DuplicateRuleCondition: A rule can have one or more duplicate detection rule conditions that are represented by the table. The conditions are combined by the system as in logical AND operation. A duplicate detection rule specifies a base table type and a matching table type. A duplicate rule condition specifies the name of a base column and the name of a matching column. For example, specify an account as a base table and a contact as a matching table to compare last names and addresses. The matching criteria consist of operators such as exactly match, first n-number of characters, or last n-number of characters.

These two tables are related using the DuplicateRule_DuplicateRuleConditions relationship.

Duplicate detection works by comparing generated match codes of existing records with each new record being created. These match codes are created as each new record is created. Therefore, there is potential for one or more duplicate records to be created if they are processed at the exact same moment. In addition to detecting duplicates as they are created, you should schedule duplicate detection jobs to check for other potential duplicate records.

The duplicate detection rules are system-wide. You must publish them before running a duplicate detection job to detect duplicates for bulk data or retrieve duplicates for a particular table record. To publish a duplicate detection rule, use the PublishDuplicateRule message(PublishDuplicateRule Action or PublishDuplicateRuleRequest). Duplicate rule publishing is an asynchronous operation that runs in the background.

The following writable columns in these tables control the behavior of duplicate detection rules.

DuplicateRule

Column Description
BaseEntityName Record type of the record being evaluated for potential duplicates.
Description Description of the duplicate detection rule.
DuplicateRuleId Unique identifier of the duplicate detection rule.
ExcludeInactiveRecords Determines whether to flag inactive records as duplicates.
Note:
The default value is false. Set it to true if you do not want inactive records to be flagged as duplicates, even if they meet duplication detection rule criteria.
More information: Inactive states
IsCaseSensitive Indicates if the operator is case-sensitive.
MatchingEntityName Record type of the records being evaluated as potential duplicates.
Name Name of the duplicate detection rule.
OwnerId Unique identifier of the user or team who owns the duplicate detection rule.
OwnerIdType Whether the owner is a user or a team.
StatusCode Reason for the status of the duplicate detection rule.

Inactive states

Most system tables and all custom tables have two StateCode column choices:

  • Value: 0 InvariantName: Active
  • Value: 1 InvariantName: Inactive

The label of the choice may be changed, but the InvariantName value will not.

Some system tables will have more than one active or inactive state.The following table lists examples of tables with more than one active or inactive state.

StateCode Active State(s) Inactive State(s)
Appointment.StateCode Open, Scheduled Completed, Canceled
CampaignActivity.StateCode Open Closed, Canceled
CampaignResponse.StateCode Open Completed, Canceled
Contract.StateCode Draft, Invoiced, On Hold Canceled, Expired
ContractDetail.StateCode Existing, Renewed Canceled, Expired
Email.StateCode Open Completed, Canceled
Fax.StateCode Open Completed, Canceled
Incident.StateCode Active Resolved, Canceled, Closed
Invoice.StateCode Active Closed, Paid, Canceled
KbArticle.StateCode Draft, Unapproved, Published N/A
Lead.StateCode Open Qualified, Disqualified
Letter.StateCode Open Completed, Canceled
Opportunity.StateCode Open Won, Lost
PhoneCall.StateCode Open Completed, Canceled
Quote.StateCode Draft, Active Won, Closed
SalesOrder.StateCode Active, Submitted, Invoiced Canceled, Fulfilled
ServiceAppointment.StateCode Open, Scheduled Closed, Canceled
Task.StateCode Open Completed, Canceled

For example, if you set the ExcludeInactiveRecords column to true, only Active, Submitted, and Invoiced sales orders will be considered for matching during duplicate detection.

Note

You can review the available StateCode choices for a table using the Metadata Browser described in Browse the table and column definitions for your organization.

To retrieve the StateCode choices for a table you can use the following Web API query by substituting the LogicalName of the table with appointment used below:

GET [organization URI]/api/data/v9.0/EntityDefinitions(LogicalName='appointment')/Attributes(LogicalName='statecode')/Microsoft.Dynamics.CRM.StateAttributeMetadata/OptionSet?$select=Options

DuplicateRule Special messages

DuplicateRule is a user-owned table and normal create, retrieve, update, assign, and delete operations are allowed as well as operations to control access. More information: DuplicateRule Messages.

The following special messages can also be used:

Message Web API Operation SDK Assembly
CompoundUpdateDuplicateDetectionRule CompoundUpdateDuplicateDetectionRule Action CompoundUpdateDuplicateDetectionRuleRequest
PublishDuplicateRule PublishDuplicateRule Action PublishDuplicateRuleRequest
PublishXml PublishXml Action PublishXmlRequest
UnpublishDuplicateRule UnpublishDuplicateRule Action UnpublishDuplicateRuleRequest

DuplicateRuleCondition

Column Description
BaseAttributeName Field that is being compared.
DuplicateRuleConditionId Unique identifier of the condition.
IgnoreBlankValues Determines whether to consider blank values as non-duplicate values.
Note:
The default value of this column is false. Set it to true if you do not want the duplicate detection rule to consider null values as equal.
Important:
For a duplicate detection rule with one condition, if you set the column value to false, it is treated by the system as a true value.
MatchingAttributeName Field that is being compared with the base field.
OperatorCode Operator for this rule condition.
Important:
If you set the OperatorCode column to ExactMatch, don’t set the OperatorParam column to any value
OperatorParam Parameter value of N if the operator is Same First Characters or Same Last Characters.
Important:
Don’t set the OperatorParam to zero during create or update operations.
RegardingObjectId Unique identifier of the object with which the condition is associated.

DuplicateRuleCondition Special messages

DuplicateRuleCondition is a child table to DuplicateRule. Access to retrieve or modify these tables is dependent on access to the DuplicateRule it is associated with. More information: DuplicateRuleCondition Messages.

The following special messages can also be used:

Message Web API Operation SDK Assembly
CompoundUpdateDuplicateDetectionRule CompoundUpdateDuplicateDetectionRule Action CompoundUpdateDuplicateDetectionRuleRequest

See also

duplicaterule EntityType
duplicaterulecondition EntityType
Detect duplicate data
Enable and disable duplicate detection
Run duplicate detection
Duplicate detection messages
Sample: Enable duplicate detection and retrieve duplicates
Sample: Use duplicate detection when creating and updating records
Sample: Detect multiple duplicate records