Duplicate rule tables
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
ANDoperation. 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's potential for one or more duplicate records to be created if they're processed at the exact same moment. In addition to detecting duplicates as they're 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 don't 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: 0InvariantName:ActiveValue: 1InvariantName:Inactive
The label of the choice may be changed, but the InvariantName value won't.
Some system tables 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 are 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 nonduplicate values. Note: The default value of this column is false. Set it to true if you don't 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's 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's 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 using code
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