Single Table Dedupe - Field Rules

Field Rules allow for an automated field-based custom merge, users pick a master record rule then set up rules for how to handle each field individually during the merge process.  For example, use the Oldest record as the Master record, but use the phone number and email from the newest record.  A new Use Field Rules checkbox brings up a grid to set rules on a field by field basis.

If ALL mergeable fields should be combined by default, use the new Combine ALL checkbox options FIRST then create a new Field Rule (all "Combine Field Value" checkboxes will be checked in the Field Rule). This ONLY works with a new Field Rule (not with editing an existing rule).

Common Uses

• Keep the oldest record as the master but pull all 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
• Track merged details on the master record:
  • Use Add Constant to set the "MergedByDemandTools__c" field to "True" on the master record
  • Use Add Constant to set the "MostRecentDemandToolsMergeDate__c" to the date of the merge using the Load Date option
  • Use FieldCopyUniqueMerge to copy all the non-master id's to a "MergedIDs__c" Text Area(255) or Long Area Text Field
• Use FieldCopyUnique to automatically populate "Alternate Email__c" and "Alternate Phone__c" fields with unique values from the non-master records

Using Field Rules

Step 1:  Find Duplicates and Select Master Records

Master records can be manually selected for each group or chosen by applying a Master Rule. When a new Field Rule is created the default Rule Setting for each field will be to keep the data from the Master Record with the ability to pick other options as desired.

Step 2:  Create a New Field Rule

1. Check the Use Field Rules checkbox just below the list of Master Rules
2. Selecting New Rule will generate a pop-up window where the details of the rule can be customized
3. For each field in the table, pick the Rule from the drop-down list that should be applied
4. Additional Options will be enabled based on the selected Rule
5. Enter a Name for the Rule
6. Save the Field Rule

Replicating a Rule Option to Other Fields

When creating or editing an existing Field Rule, users may want to use the same Rule option (i.e. Newest, Oldest, Most Common, etc.) on multiple fields.  Instead of having to manually update the Rule option for each field, we provided a Replicate Rule option.  To invoke this option simply right click on one of the fields where the desired rule option is selected and click Replicate Rule, then check the fields the rule option should be replicated to.

Note:  This option IS NOT available for the Use Constant and FieldCopy options, due to the need to populate the Value option.

1.  Set up the rule option for one of the fields
2.  Right click on the field name and select Replicate Rule

A list of fields that allow the replicated option will be shown

3.  Check the fields to replicate

• To select all fields select Check All

4.  Click Use Field(s)



5.  The Rule Option will be updated for all chosen fields

Rule Option Details

When creating a new Field Rule, the default rule option for each field will be Master.  Other options can be selected based on the type of field being updated.

Note:  The only time a blank value will be retained over a populated value is if the "Set to Null" option is used.   A populated field on the master record WILL NEVER be overwritten with a blank value with any other option.  For example when picking the "Newest Record" option for a field and the value on the newest record is blank, but populated on the master, the populated value will remain.

Non-Scoring Options for ALL Field Types

• Master:  Keep the data from the master record over any data in non-master records
• Set to Null:  Set the field to blank or null on the master record
• Use Constant:  Populate the master 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
    • Click in 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).  Click Set
    • The selected constant value will now be shown in the grid                       
• 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
• 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 master record with unique values
  • The check for uniqueness is done by comparing data in the from field for all non-master records in the group to the value in the from field on the master record
  • Unique values are then written to the target field
  • If there are multiple unique values in the non-master records it 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                             
• 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 master then that value will win
    • If neither tied value is on the master 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

Most of the time users will select Combine Field Values for multi-select picklist fields anyway to avoid losing any values.

Additional Option For Text Fields ONLY

RegEx Match:  Using .NET's regex engine 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 customers are responsible for building their own expressions.  As a courtesy we have provided a few example expressions. Users should test with their data and adjust as needed.

For example:
US zip code 5-4:  \b\d\d\d\d\d-\d\d\d\d\b

This would mean that if the field matched this regex (5 digits followed by a hyphen and then 4 digits) it would use the first one it found. If nothing is found it would not change anything (would keep what was on the master record on that case).

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 RegEx's 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)$

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

Additional Option for Mergeable Field Types

Applies to multi-select picklist fields, text area, long area text and rich text fields.

• FieldCopyUniqueMerge:  Same as the FieldCopyUnique option EXCEPT that it will copy MULTIPLE UNIQUE values to the target field
  • Designed to auto-populate "alternate" fields with as many unique values as exist across all records in the group
  • The target field will need to be one of the types noted above that will support the multiple values
    • Text area, long area text and rich text fields will be separated by a carriage return
    • Multi-select picklist fields will be separated by a ;
  • If a value currently exists in the target field, it will ONLY update this field if the Overwrite checkbox on the right is selected
    • Example, target field "Alternate_Emails__c" (text area field)

                           Master Email: jdoe@xyz.com
                           Non-Master1 Email:  jon.doe@xyz.com
                           Non-Master2 Email:  jdoe@yahoo.com
                           Non-Master3 Email:  jon.doe@xyz.us.com
                           Non-Master4 Email:  jdoe@xyz.com

                           Result Alternate_Emails__c:
                           jon.doe@xyz.com
                           jdoe@yahoo.com
                           jon.doe@xyz.us.com

• Selecting the FieldCopyUnique Merge 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 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. 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 master 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 master 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

Scoring Options

Allows users to create their own logic to determine which value should be populated on the master record using the same type of criteria allowed in Master Rules.

To OR multiple text values use a | (pipe) symbol (vs. a comma) between each value. Detailed information on OR'ing text values in a custom scoring rule can be found in the Validity Community Forum Post: Field Rule Custom Scoring: Using OR in a condition for multiple text values.

• Custom Scoring:  Custom rules to determine which value should be populated on the master record
• Update if Master Empty Scoring: Applies only when the master field is empty, otherwise will keep the data on the master record
  • Instead of the tool arbitrarily picking any of the non-master values, provide logic to determine which non-master record value is populated when the field on the master is empty
  • For Example: Pick the value from the "Oldest" non-master record
• FieldCopyUnique Scoring:  Applies the FieldCopyUnique rule logic, then based on the scoring logic determines which unique value will be populated on the master
  • If a value currently exists in the target field, it will ONLY update this field if the Overwrite checkbox on the right is selected

If the logic in the scoring rule could result in a tie score, it is recommended that a "tie breaker" condition be added such as Oldest, Newest or Most Recently Modified Record, otherwise the selection of which data to keep from the tied records will depend on the scoring option used.  For Update if Empty/FieldCopyUnique Scoring the selection will be arbitrary.  For Custom Scoring, if the master is one of the tied records the master value will win, otherwise the selection will be arbitrary.

Examples:

1.  Keep the data from the record with a particular RecordType
2.  Keep the data from the record that is Owned By a particular user
3.  Prioritize which Lead Source should be kept when different values exist on each record (done by assigning point scores to each value, the value with the highest score will be retained)


 
Checking Field Rules for Validity

An automatic check is done when editing a saved field rule or merging records with a saved field rule to ensure that all fields in the table are accounted for.

• If fields have been added or deleted from the object in Salesforce since the last time the rule was saved, a pop-up will be displayed noting the discrepancies
• Clicking OK will allow editing/re-saving the rule
• When edit mode is invoked, additional details noting the added and deleted field names will be displayed
• Any new fields will be added with the default Rule of Master, but can be updated as needed

Example:  When the field Primary__c has been deleted and LeadSourceHistory__c has been added

Pop-up when attempting to merge records with an invalid field rule.  Click OK to edit the rule.

Pop-up when editing an invalid field rule.

New field, LeadSourceHistory__c added with the default rule option Master.  Update rule option as desired and re-save the rule.

Loading a Saved Scenario With A Field Rule

Field Rules are saved locally on the PC as an xml file, but the logic within the field rule is ALSO saved as part of a saved scenario.  A pop-up will be displayed if there is an existing Field Rule (xml file) with the same name as the one in the saved scenario but the criteria is different.  The pop-up will have an option to Show Rule Differences with various options to resolve the conflict or exit loading the saved scenario altogether.

1.  Show rule differences:  Click to show any discrepancies:

2.  Options to proceed:

• Save as new rule and keep
• Overwrite criteria in existing rule
• Ignore saved scenario rule and use existing
  • Ignore the rule criteria in the saved scenario and use the criteria in the existing rule
  • Will need to re-save the scenario to update the saved scenario rule logic to match the updates to the existing rule
  • The check for missing fields or fields that no longer exist will still be done when attempting to merge the records and the user will need to re-save the rule to add/remove the fields accordingly
• Exit and Clear Scenario
  • Abort loading the saved scenario altogether