Alternate key fields

AD
DM

Alternate keys can be used to define a unique ID and format for each entity. When you create an alternate key custom field, you can use rules to define the key format, range, and name for that format and range. The rules are defined when you select Alternate Key as the type on the Create Custom Field page.

The following examples illustrate how to define specific formats and keys for different countries. These methods can be used in other scenarios; for example, to create keys with different formats to distinguish IDs for different entity (HCO or HCP) type.

Generate a simple pre-formatted alternate key

The following simple rule generates a pre-formatted alternate key using the format US{###}-{###}-{###}; for example, USCX3-F8X-RET:

NEWALTERNATEID('US{###}-{###}-{###}')

Generate a pre-formatted alternate key for multiple conditions

This example generates alternate keys for multiple countries (United States, China, Germany, and Other).

IF(primary_country__v == 'US',NEWALTERNATEID('US{###}-{###}-{###}'),
IF(primary_country__v == 'CN',NEWALTERNATEID('CN{#####}-{####}', 'SEQ:1,10000000,CN'),
IF(primary_country__v == 'DE',NEWALTERNATEID('DE{#####}-{####}', 'SEQ:9999999,999999999,DE'),
NEWALTERNATEID('OTHER{#####}-{####}-END', 'SEQ:9000,100000000,OT'))))

In the example, where the alternate key for the United States (US) uses a simple rule to create a random ID, the other countries require more detail:

NEWALTERNATEID('CN{####}-{####}','SEQ:1,10000000,CN')

This line indicates that China uses the specified format with a number in the range of 1 to 10000000, Additionally, the CN after the range indicates a name by which the range is tracked. Specifically, the range here is tracked using field_name:CN.

The last line generates an alternate key for any other countries (OT) not defined in your system.

Alternate keys that would result from these rules might be US2E6-FED-2ER (since no range is specified, a random alphanumeric value is produced), CN00000-0001, CN00000-0002, DE01000-0000, DE01000-0001, OTHER00000-9000-END, and OTHER00000-9001-END.

Note: When defining numeric ranges, ensure that the maximum number is sufficient for the amount of data you're working with. You should allow for at least eight digits.

Duplicate alternate keys

The rules that you define should generate unique keys to ensure data quality. However, duplicate alternate keys might be found in your Network instance because of the following situations:

  • The rule defined to generate alternate keys doesn't allow for enough digits to ensure unique keys. For example, if the rule allows for only three digits and over a thousand keys are generated, generating a unique key is not possible.
  • Network does not check for duplicate alternate keys during data load. If a source subscription has duplicate alternate keys, the duplicates are loaded into Network.

Alternate key survivorship

Administrators can apply logic to custom fields to tell Network which alternate key to keep when records are merged. When records are merged, the default survivorship rules decide that the alternate key of the winning record remains. Sometimes this behavior is not desirable and the workaround to recover the lost alternate key is time-consuming. You can control which alternate key stays on the merged record by specifying survivorship based on the value of a specific field. For example, you can tell Network to keep the alternate key of the highest ranked or targeted HCP on the merged record.

This feature is not enabled by default. To enable this feature for your Network instance, contact Veeva Support.

Define survivorship rules

When records are merged, the default survivorship rules keep the alternate key of the winning record. Administrators can update the custom fields for alternate keys to tell Network to override the merge survivorship rules by a specific field and value.

  1. In the Admin console, click Data Model > Data Domains.
  2. Select a data domain (for example, Customer Master) and choose the object.
  3. Find and click on the alternate key field; for example, altkey__c.

  4. In the Survivorship Rules section, select the Override by Value option.

  5. In the Dependent Field list, select the field that will determine survivorship of the alternate key. The dependent field that you choose for determining alternate key survivorship is only looked when the records are being merged.

    The list contains all of the fields for the Network objects defined for each entity type that are enabled and are one of the following types:

    • Date (no time)
    • Date and Time
    • Integer Number
    • Decimal Number

  6. In the Winning Criterion field, select the appropriate value.

    For example, if the Dependent Field is rank__c, the Winning Criterion should be Lowest Value so that the alternate key on the HCP record considered highest ranked (lowest integer value) in the merge remains.

    Example

    Record A
    Alternate Key / Rank     		Record B
    Alternate key / Rank     		Winning Criterion Alternate Key on Merged Record
    VSEB-78D-2S3
    rank__c: 5     		VT8B-2ZK-GA6
    rank__c: 3     		Lowest Value VT8B-2ZK-GA6
  7. Save your changes.

When the supported merge actions run, survivorship of the alternate key will be based on the dependent field that you specified.

Supported merge actions

Alternate key survivorship logic is supported on the following merge actions:

Merge Action Result using default survivorship rules Result using Survivorship by Value
Find Suspect Match Alternate key takes the value from the winning record. The winning alternate key is determined by the dependent field.
Veeva OpenData merge Alternate key takes the value from the winning record. The winning alternate key is determined by the dependent field.
Bulk Merge (includes data deduplication and data maintenance jobs) Alternate key takes the value from the winning record. If the subscription property is set to true: job.merge.overrideAlternateIdentifier": "true" survivorship is applied and the winning key is determined by the dependent field.
Unmerge

The alternate key on the original record is retained.

Source survivorship will be run to either use an alternate key from the source or to generate a new alternate key.

 The winning alternate key is determined by the dependent field.
Gray (Under Review) record merges into orange record The alternate key from the record that is Under Review is retained. The alternate key from the record that is Under Review is retained.
Source subscription

The alternate key from the highest ranked source is retained.

No merge or survivorship rules are applied unless the advanced subscription property is set to true: job.merge.overrideAlternateIdentifier": "true"

The winning alternate key is determined by the dependent field.

No merge or survivorship rules are applied unless the advanced subscription property is set to true: job.merge.overrideAlternateIdentifier": "true"

Examples

Example 1 - Veeva OpenData merge

A Veeva OpenData data steward merges two records together (Bob Woods into Robert Woods) in the OpenData master instance. When the record comes down to Verteo's customer instance during an OpenData subscription job or an ad hoc download, the records are updated and the merge is performed there.

In the Verteo Network instance, alternate key survivorship is defined using a last_contact_date__c field (custom field); where, the alternate key value on the most recently contacted record should be retained on the winning record.

Result: The value of the alternate key on the winning record is VT74-ZNH-W63 because Bob Woods was most recently contacted.

Example 2 - Find Suspect Match merge

A Verteo customer data steward merges two records. In the Verteo Network instance, alternate key survivorship is defined using a tier__c field (custom field); where, the alternate key on the record with the highest tier (lowest integer value) should be retained on the winning record.

Result: The value of the alternate key on the Livia Roy record is retained because it has the highest tier.

Tiebreaker rules

In situations where the alternate keys of both records have the same value for the dependent field, the surviving alternate key will be determined using tie breaker rules.

Merge action Tiebreaker rules
Bulk merge, Unmerge, OpenData merge, Find Suspect Match The alternate key from the merge winner is retained.
Source subscription

The following rules are applied in order:

  1. The alternate key from the newest record (last modified date) is retained.
  2. The alternate key from the merge winner is retained.

Disabling dependent fields

If a field that you are using as a Dependent Field to determine survivorship for alternate keys is going to be disabled, a confirmation dialog displays. The dialog contains the impacted fields.

If the field is disabled, the default rules will be used to determine survivorship again; the alternate key from the winning record in a merge is retained.

Fixing alternate keys from a merge

If merging records results in having the wrong alternate key on the winning record, it can be fixed using subscription jobs.

To update alternate keys:

  1. Using a target subscription, export the applicable records. Ensure that the exported .csv file includes the following data:
    • Network entity ID (VID)
    • Alternate key
    • Dependent field (for example, rank__c)

    You can also use Network reports to query the applicable records for the required data. For example, if you know that there was a problem with merged HCPs within the past month, you could run the following query to find the HCPs that were updated within the last 30 days.

    Download the results.

  2. Make the appropriate updates to the alternate keys in the exported .csv file, or create a new .csv file and add the required data.
  3. Create and run a source subscription to import the .csv file to your Network instance. Ensure that the following property is set in Advanced Mode:
    "job.merge.overrideAlternateIdentifier": "true"

The alternate keys will be updated from the source subscription.

Swap alternate IDs between records

The ID on the merge winner might not be the surviving ID that is already used in your downstream systems. To switch the alternate IDs between two records (for example, swap the alternate IDs between the losing record and the winning record), two source subscriptions must be run. If the changes are attempted using one data loading job, an error will occur because Network checks for unique alternate IDs.

Create a .csv file that includes the Network entity ID (VID) and alternate ID for the two records.

Run two source subscription jobs:

  • Job 1 - In the .csv file, clear the values for the alternate IDs on the affected records. Run the source subscription. Your Network instance will be updated so the alternate IDs are removed from those records.
  • Job 2 - In the .csv file, assign the alternate key IDs to the appropriate records. Run the source subscription. Your Network instance will be updated so the records show the new alternate key ID values.