Administering Oracle CRM On Demand > Data Management Tools > Import and Export Tools > About Record Duplicates and External IDs
About Record Duplicates and External IDs
When you import records, you can choose one of the following options to handle duplicate records:
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.
If you use the predefined set of fields to check whether the record in your import file is a duplicate of an existing record, then Oracle CRM On Demand checks the value in all of the predefined fields to determine whether the record is a duplicate. If the values in all of the predefined fields in the CSV file exactly match the values in all of the corresponding fields on an existing record, then the record is a duplicate. For example, assume that you import a contact record, and the values in the First Name, Last Name, Work Phone #, and Email fields match the values in an existing record. The record you are importing is then considered a duplicate. However, if the values in the First Name, Last Name, and Email fields match the values in an existing record, but the Work Phone # field is blank in your CSV file and is not blank in the existing record, then the record in the CSV file is not considered a duplicate.
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
- 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.
- 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.
- Include at least all required fields in your import CSV file.
- Select the Overwrite Existing Records option in Step 1 of the import assistant.
- 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.
- 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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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 B - Parent Account External ID is not Mapped
Import uses the Parent Account Name and Parent Account Location to determine the Parent Account.
|