Field Rules

Field rules can be used to select specific field values from different duplicates to be merged into the winning record, overriding the winner rule.  In short, field rules allows users to create the perfect winner record with desired field values from all duplicates in a group.

Common use cases:

• Set your Address fields to Update on Empty, so if your winning record is missing a Street Address, you can pull it from a duplicate record.
• Use the oldest record as the winning record, but use the address, phone, and email fields from the newest record.
• Use Custom Scoring to keep the Lead source data from the record where the RecordType = X.
• Use FieldCopyUnique to automatically populate Alternate Email__c and Alternate Phone__c fields with unique values from duplicate records.

Field rules allow for an automated field-based merge. You can set a winner rule, then set rules for how to handle each field individually during the merge process.

To enable field rules:

1. Check the Use field rules checkbox on Step 3 of the Dedupe module.
2. When the Select rule override dropdown menu appears, open it and select the field rule you want to apply.
   • If no field rules are listed, follow the instructions below for creating a field rule.

To create a field rule:

1. Check the Use field rules checkbox on Step 3 of the Dedupe module.
2. Select the Manage button when it appears.
3. In the Manage Field Rules popup, select + Add New Rule.
4. Give your rule a descriptive name.
5. Search for the field you want to apply the override to.
   • You can apply a single rule to more than one field at once. For example, searching for Phone may bring up Mobile Phone and Business Phone.
6. Under the Rule Override dropdown menu on the row for each field, select the override you want to apply.
   • If more than one field is listed in a single rule, you can apply different overrides to each field.
7. Select Combine Field Value to merge the values for two similar fields.
8. Select Save Field Rule to complete the process.
9. Select Close to exit the Manage Field Rules popup.

Once a field rule is created, you can go to Manage Field Rules to edit, duplicate, or delete it.

• Select Duplicate if you want to clone a complex field rule and make a small change.

To edit a field rule:

1. Check the Use field rules checkbox on Step 3 of the Dedupe module.
2. Select the Manage button when it appears.
3. In the Manage Field Rules popup, select the rule you want to edit. 
4. Make any desired changes.
5. Select Save Field Rule to complete the process.
6. Select Close to exit the Manage Field Rules popup.

When you create a new field rule, the default rule option for each field is Update if empty. This option retains the value from the winning record unless that field value is empty. In that case, DemandTools will update the field with the value from a duplicate record.

Note: The only time a blank value will be retained over a populated value is if the Null rule option is chosen. Make sure you want to erase existing values in a given field before applying a field rule containing Null.

Non-scoring options for all field types

• Update if Empty: If the field value from the winning record is blank, update with a populated value from duplicate records
• Don't Update if Empty: If the field value from the winning record is blank, keep it blank
• Null: Set the field to blank or null on the winning record
• Oldest Record: Update the field with the data from the record with the oldest created date
• Newest Record: Update the field with the data from the record with the newest created date
• Most Recently Modified: Update the field with the data from the record with newest last modified date
• Most Common: Keep the most common populated value from all the records in the group
  • Only looks at populated values (blanks will never be considered as most common)
  • In the event of a tie:
    • If one of the tied values is on the winning record, then that value will win
    • If neither tied value is on the winning record, then the winner will be arbitrary
    • When selected for a multi-select picklist field, it will keep the most common value across all records, NOT the most common combination
      • Record 1: one
        Record 2: one;two
        Record 3: one;two
        Record 4: one;two;three
        
        Result: one
    • Selecting Combine Field Values for multi-select picklist fields will allow you to avoid losing any values.
• Use Constant: Populate the winning record with the constant value provided in the Value box
  • Date fields will have a "Load Date" option to load the current date
    • Could be used to populate a "Most_Recent_DemandTools_Merge_Date__c" field or similar
    • Selecting the Use Constant option will highlight the Value box in yellow
    • Select the yellow highlight and type in a valid value, e.g. string/number value (for text/number fields), select true/false (for boolean fields), pick from the list of valid picklist values (for a picklist or multi-picklist field) or pick a date from a calendar (for date/datetime fields). Select Set.
    • The selected constant value will now be shown in the grid.
• FieldCopyUnique: Update the field with a unique value from a selected field
  • Designed to auto-populate "alternate" fields. e.g. alternate email or alternate phone fields
  • Can only select like field types to copy from, e.g. text field to text field, number field to number field etc.
  • ONLY populates the selected field on the winning record with unique values
  • The check for uniqueness is done by comparing data in the from field for all duplicate records in the group to the value in the from field on the winning record
  • Unique values are then written to the target field
  • If there are multiple unique values in the duplicate records, DemandTools will arbitrarily pick which one to keep
    • To specify which value to keep when there are multiples, use the FieldCopyUnique Scoring option instead
  • If a value currently exists in the target field, it will ONLY update this field if the Overwrite checkbox on the right is selected
  • Selecting the FieldCopyUnique option will highlight the Value box in yellow
  • Click in the yellow highlight and select the field to copy unique data from
  • The selected field will now be shown in the grid

Additional option for text fields ONLY

RegEx Match: Provide a regular expression to evaluate the data in the records and keep the value that matches the pattern in the regex. Knowledge of regular expressions is required to use this mapping type, and you are responsible for building your own expressions. We have provided a few example expressions below. You should test with your data and adjust as needed.

For example:

• US zip code 5-4: \b\d\d\d\d\d-\d\d\d\d\b

This means that if the field matches this regex (5 digits followed by a hyphen and then 4 digits), it will use the first one it finds. If nothing is found, the value from the winning record will be kept.

Additional examples:

• Number between 0 and 49: \b[0-9]\b|\b[1-4][0-9]\b
• Number DOES not start with leading 0: ^[1-9]\d*$

Note: The two regular expressions noted above apply to numbers that are contained in a text field (i.e. and Account Number field).

• Canadian postal code: \b[ABCEGHJKLMNPRSTVXY][0-9][A-Z] [0-9][A-Z][0-9]\b
• Formatted Amex Card: ^3[47]\d{2}[ -]*\d{6}[ -]*\d{5}$
• Not a personal email: ^[\w\W]+(?<!\b(gmail.com|yahoo.com|comcast.net|aol.com)\b)$

You can add additional strings the email should NOT end with.

Additional options for date fields

Applies to date and date/time fields.

• Oldest Date: Keep the oldest date from all the records in the group
• Newest Date: Keep the newest date from all the records in the group

Additional options for number fields

Applies to Number, Currency, and Percent field types. All options apply to populated fields only.

Note: Null values will NOT be seen as 0. If 0's should be considered, then the records with null values will need to be updated with the value of 0.

• Sum: Populate the winning record with the sum of all populated records in the group
• Largest: Keep the largest populated value from all records in the group
• Smallest: Keep the smallest populated value from all records in the group
• Average: Populate the winning record with the average value from all populated records in the group
  • The sum of all the populated values will be divided by the number of populated values