Print      Open PDF Version of Online Help

Administering Oracle CRM On Demand > Data Management Tools > Import and Export Tools > About Record Duplicates and External IDs

Previous Topic

Next Topic

About Record Duplicates and External IDs

When you import records, you can choose one of the following options to handle duplicate records:

  • By not importing the duplicate ones
  • By overwriting the existing ones

    NOTE: The overwriting option is not available when you import Appointment User, Task User, or Notes. New records are always created for these record types.

  • By creating additional records for the duplicates (Contacts, Leads, Appointments and Tasks only)

If you choose to overwrite existing records when you import your data, Oracle CRM On Demand checks whether the record in your import file is a duplicate of an existing record in Oracle CRM On Demand by checking the values in the fields that are used to check for duplicate records. If your record is a duplicate, Oracle CRM On Demand updates the fields on the existing record in Oracle CRM On Demand with the values from the record that you import, with the exception of the fields that are used to check for duplicate records.

On many record types, you can use either the External ID field or a predefined set of fields to check if the record in your import file is a duplicate of an existing record in Oracle CRM On Demand. If you use the External ID as the field to check for duplicate records, then when a duplicate record is found, all of the fields on the record in Oracle CRM On Demand are updated, with the exception of the External ID field. Similarly, if you use the predefined set of fields to check for duplicate records, all fields other than the duplicate-checking fields are updated on the record in Oracle CRM On Demand.

For example, on the Account record type, you can use the External ID field, or a combination of the Account Name and Location fields to check for duplicate records. If the record in your file is a duplicate record, the existing record in Oracle CRM On Demand is updated with the data from your import file as follows:

  • If you used the External ID field to check for duplicates, all fields other than the External ID field are updated on the record in Oracle CRM On Demand.
  • If you used the Account Name and Location fields to check for duplicate records, all fields other than the Account Name and Location fields are updated on the record in Oracle CRM On Demand.

To update records

  1. Set up your import CSV file with a column for the External IDs, or for all of the duplicate checking fields for the record type.
  2. Make sure that the External IDs for the duplicate checking field values are exactly the same as the values for the existing records. If you are using External IDs, each one must be unique in the system for the update to work.
  3. Include at least all required fields in your import CSV file.
  4. Select the Overwrite Existing Records option in Step 1 of the import assistant.
  5. Make sure that you map the External ID field or all of the duplicate checking fields in the Field Mapping step of the import assistant.
  6. Complete all of the import assistant steps to import the data.

When you use the import assistant to update records, only fields that you map in the Field Mapping step are overwritten. Therefore, if you have fields that do not need to be updated, and they are not required for duplicate checking, you do not have to map those fields.

If External IDs exist and you have mapped them during the import process, the application uses them to determine if a record is a duplicate. When importing other record types, you can use those External IDs as references so that the associations are carried over to Oracle CRM On Demand.

For example, if you have an account_id column in your account.csv file, you want to map it to the External Unique ID in Oracle CRM On Demand. When importing contacts, if your contacts.csv file contains a contact_id column (the unique ID for the contact) and an account_id column (a reference to the IDs in the account.csv file), you should map the contact_id to External Unique ID and account_id to Account External ID. During the import process, the application checks the account_id field in each contact record to determine the existing account and link the account to the contact.

If no External IDs exist or you do not map External IDs in your file to the External ID fields in the application, the application determines duplicates by comparing certain fields. The following table lists the fields that are used to determine record duplicates.

Records of This Type

Are Duplicates If These Fields Match

Account
  • Account Name and Location

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Account Address

Account, Address, and Address Type

Account Book

Account and Book

Account Contact
  • Account Name and Contact Full Name

    OR

  • Account External ID and Contact External ID (Created by another software system)

    OR

  • Account ID and Contact ID (Oracle CRM On Demand Internal ID)

Account Team

Account and User

Accreditation
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Accreditation Request
  • Id

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Address
  • Address Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Allocation
  • Product, Type, Owner, and Start Date

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Application
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Appointment
  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

    NOTE: Oracle CRM On Demand does not check for duplicate records when appointments are created through the user interface.

Appointment Contact
  • Appointment External ID, Contact (Contact External ID or Contact Full Name)

Appointment User
  • Appointment External ID, User (User Sign In ID or User External ID)

Asset
  • External ID (Created by another software system)

Blocked Product
  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Book
  • Book Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Book User
  • Book ID, Book External ID, or Book Name if you are identifying existing books
  • User Sign In ID, User External ID, or User ID if you are identifying book users within the book

Broker Profile
  • Broker Profile Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Business Plan
  • Plan Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Campaign
  • Source Code

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Campaign Recipient

Campaign (Campaign External ID or Campaign ID), Contact (Contact Full Name or Contact External ID), Source Code

NOTE: You must provide either Contact Full Name or Contact External ID, but not both.

Certification
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Certification Request
  • Id

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Claim
  • Claim Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Contact
  • First Name, Last Name, Work Phone #, and Email

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

    NOTE: Oracle CRM On Demand does not check for duplicate records when contacts are created through the user interface.

Contact Address

Contact, Address, and Address Type

Contact Book

Contact and Book

Contact State License
  • License Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Contact Team

Contact (Contact Name or Contact External ID) and User (User ID)

Course
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Course Enrollment
  • Id

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Coverage
  • Coverage Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Custom Objects 01, 02, 03

External ID (Created by another software system)

NOTE: Oracle CRM On Demand does not check for duplicate records when custom object records are created through the user interface.

Custom Objects 04 and higher
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

    NOTE: Oracle CRM On Demand does not check for duplicate records when custom object records are created through the user interface.

Damage
  • Damage Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Dealer
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Deal Registration
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Event
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Exam
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Exam Registration
  • Id

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Account
  • Financial Account

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Account Holding
  • Financial Account Holdings Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Account Holder
  • Financial Account Holder Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Plan
  • Financial Plan Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Product
  • Financial Product Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Financial Transaction
  • Transaction ID

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

HCP Contact Allocation
  • Contact ID, Product ID, Allocation Type, and Start Date

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Insurance Property
  • Type

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Inventory Audit Report
  • Reported Date

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Inventory Period
  • Start Date

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Invitee
  • Event and Contact Name (Contact Full Name or Contact Full Name and Contact ID)

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Involved Party
  • Involved Party Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Lead
  • First Name, Last Name, Company, Primary Phone #, and Email

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

    NOTE: Oracle CRM On Demand does not check for duplicate records when leads are created through the user interface.

MDF Request
  • Request Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Messaging Plan
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Messaging Plan Item
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Messaging Plan Item Relations
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Note
  • No duplicate checking (Always creates new records)

Objective
  • Objective Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Opportunity
  • Opportunity Name and Account (Account Name or Account External ID)

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Opportunity Contact Role
  • Opportunity Name and Contact Full Name

Opportunity Product Revenue

External ID (Created by another software system)

NOTE: Oracle CRM On Demand does not check for duplicate records when opportunity product revenue records are created through the user interface.

Order

  • Order Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Order Item
  • Order Item Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Partner
  • Partner Name and Location

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Partner Program
  • Partner Program Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Plan Account
  • Relationship Code

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Plan Contact
  • Contact Full Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Plan Opportunity
  • Opportunity

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Policy
  • Policy Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Policy Holder
  • Policy Holder Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Portfolio
  • Account Number and Product

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Price List
  • Price List Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Price List Line Item
  • LineItem

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Product
  • Product Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Product Indication
  • Product Indication Name
    OR
  • Row ID (Oracle CRM On Demand internal ID)

Product Category
  • Category Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Disclaimer
  • Relationship Code

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Inventory
  • Relationship Code

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Lot
  • Lot #

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Request
  • Order Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Request Item
  • Order Item Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Sample Transaction
  • Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Service Request
  • SR Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Social Profile
  • Author and Community

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Solution
  • Title

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Special Pricing Product
  • Item Number

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

    NOTE: Oracle CRM On Demand does not check for duplicate records when special pricing product records are created through the user interface.

Special Pricing Request

NOTE: In the import assistant, this record type is shown as SP Request.
  • Request Name

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Task
  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Task Contact
  • Task External ID, Contact (Contact External ID or Contact Full Name)

Task User
  • Task External ID, User (User Sign In ID or User External ID)

Transaction Item
  • Sample

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

User
  • User ID

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Vehicle
  • VIN

    OR

  • External ID (Created by another software system)

    OR

  • Row ID (Oracle CRM On Demand internal ID)

Vehicle Contact

Vehicle and Contact

CAUTION: When updating files, it is recommended that you map either the External ID or the other set of fields for determining duplicates; if you map both of them, you risk overwriting fields used for duplicate checking that you might not want to overwrite. For example, if you map the External ID, Account Name, and Location when updating account records, and a duplicate is found based on the External ID, the Account Name and Location overwrite the existing values in the database. If no duplicates are found based on the External ID, the system checks for duplicates based on Account Name and Location, and if found, overwrites the External ID in the database.

Account Import and External ID Summary

When importing accounts, you specify how you want the application to handle duplicate records:

  • Don't Import Duplicate Records

    If duplicates are found, the duplicate record is not imported.

  • Overwrite Existing Records

    If duplicates are found, the existing record is updated. If more than one duplicate is found, then no records are inserted or updated.

After selecting the behavior, you have the option of mapping fields, including these two external IDs available with account imports:

  • External Unique ID. Refers to the external ID of the current record to be imported. The External Unique ID is directly involved in duplicate checking, as described in this section.
  • Parent Account External ID. References the external ID of the parent record for the current record.

The behavior surrounding each of these external IDs is independent of each other.

Scenarios for External Unique IDs

Scenario A - External Unique ID is not mapped

Duplicate checking is based on Account Name and Location. If a duplicate is found, the behavior is determined by the selected duplicate checking option.

Scenario B - External Unique ID is mapped

Import first tries to find a duplicate record using the External Unique ID.

  • Scenario B1 - Single Duplicate External ID Found

    Account Name and Location are checked for duplicates outside of the currently matched record, and if a duplicate is found, no records are updated or inserted.

    If the duplicate checking option is set to Overwrite Existing Records, then the record is updated (including Account Name and Location).

    If the duplicate checking option is set to Don't Import Duplicate Records, then no records are updated or inserted.

  • Scenario B2 - Multiple Duplicate External IDs Found

    No Records are inserted or updated.

  • Scenario B3 - No Duplicate External ID Found

    Import now checks duplicates based on the Account Name and Location.

    • Scenario B3a - No Duplicate Account Name and Location Found.
      Record is inserted.
    • Scenario B3b - Single Duplicate Account Name and Location Found
      If the duplicate checking option is set to Overwrite Existing Records, the record is updated, including the External Unique ID. If the duplicate checking option is set to Don't Import Duplicate Records, no records are updated.
    • Scenario B3c - Multiple Duplicate Account Name and Locations Found
      No records are inserted or updated.

Scenario C - External Unique ID is not Mapped

The Account Name and Location are used to perform duplicate checking.

  • Scenario C1 - Single Duplicate Account Name and Location found

    If Duplicate Checking option is set to Overwrite Existing Records, then the record is updated.

    If Duplicate Checking option is set to Don't Import Duplicate Records, then no records are updated or inserted.

  • Scenario C2 - Multiple Duplicate Account Name and Location found.

    No records are updated or inserted.

  • Scenario C3 - No Duplicate Account Name and Location found

    Record is inserted.

Scenarios for Parent Account External IDs

The Parent Account External ID is only used to set the associated Parent Account Record. It has no impact on duplicate checking or updates.

Scenario A - Parent Account External ID is Mapped

Import uses the Parent Account External ID only to determine the Parent Account.

  • Scenario A1- Multiple records match Parent Account External ID

    Parent Account is not set.

  • Scenario A2 - Single Record matches Parent Account External ID

    Parent Account is set.

  • Scenario A3 - No records match the Parent Account External ID

    Parent Account is not set.

Scenario B - Parent Account External ID is not Mapped

Import uses the Parent Account Name and Parent Account Location to determine the Parent Account.

  • Scenario B1 - Multiple records match Parent Account Name and Parent Location.

    Parent Account is not set.

  • Scenario B2 - Single Record matches Parent Account Name and Parent Location

    Parent Account is set.

  • Scenario B3 - No records match the Parent Account Name and Parent Location

    Parent Account is not set.
Published 1/9/2017 Copyright © 2005, 2017, Oracle. All rights reserved. Legal Notices.