Field Rules are OPTIONAL. If all fields should be selected from the chosen Master record, then Field Rules are NOT needed. 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 Master record but use the phone number and email from the newest record etc. A Use Field Rules checkbox brings up a grid to set rules on a field by field basis. Rules can be easily replicated across multiple fields by right-clicking on a field name and selecting “Replicate Rule”.
Field Rules are invoked by checking the Use Field Rules option just below the list of Master Rules:
Unchecked Checked
Field Rules Pop-up when Use Field Rules checked and New Rule or Edit Rule Selected
Right click on a field to replicate the rule to other like fields
Field Rule Options include:
- Master, Set to Null, Add Constant, Oldest Record, Newest Record, Most Recently Modified, Regex Match
- Largest/Smallest/Average (number fields), Oldest/Newest (date fields), Most Common
- ONLY looks at populated fields
- Custom Scoring
- Update if Empty Scoring
- FieldCopyUnique/FieldCopyUniqueScoring
- ONLY copies unique values that do not match the master
- Great for populating alternate email or phone fields!
- Update if Empty, Combine Field Values and Overwrite Data options where applicable
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).
Example Applications:
- 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 Owner from the record where the Account Type is "Customer"
- Track merged details on the master record in custom DemandTools merge fields, e.g.:
- Use Add Constant to set the "MergedByDemandTools" field to "True" on the master record
- Use Add Constant to set the "MostRecentDemandToolsMergeDate" to the date of the merge using the Load Date option
- Use FieldCopyUniqueMerge to copy all the non-master id's to a "MergedIDs" Memo Field
- Use FieldCopyUnique to automatically populate custom "Alternate Email" and "Alternate Phone" 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.
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
- 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, i.e. keep the data from the master record over any data in non-master records. 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" 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 two option fields), pick from the list of valid options (for option set and multiselect option set field) or pick a date from a calendar (for date/datetime fields). Click Set.
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
- 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
- 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 FieldCopyUniqueScoring 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.
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 option set 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 option set fields anyway to avoid losing any values.
Additional Option For Memo 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. There are several free regex tutorial websites online that users may want to reference to learn more. 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 option set fields, memo (long 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
- Memo fields will be separated by a carriage return
- Multi-select option set 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
- e.g. 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 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 Account Type
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)
