How You Use Alternate Keys to Import Records
Alternate keys help you uniquely identify object records, so that you can create, update, delete, and manage relationships. This helps you import and build relationships to other objects, without the need to know the system-generated primary keys of each record.
Use of known alternate keys also eliminates the need to requery and remap data.
Here are some examples of Alternate Keys:
-
Public Unique Identifier (
PUID
) - supports the ability to define an auto-generated sequence number. -
Original System/Original System Reference (OS/OSR) - consists of two fields:
-
Original System - refers to the source system from which the record was sourced. Administrator must configure this field.
-
Original System Reference - refers to the unique identifier for the record in the source system.
-
-
Email Address - the email address value of a record can be used on some objects (such as Employee Resource) to identify a record.
The keys resolve in the following order of precedence:
-
Primary Key (PK)
-
Public Unique Identifier (
PUID
) -
OS/OSR (if applicable)
-
Any other alternate keys (
PUID
must be the first alternate key in the order of precedence if more than one alternate key is supported)
Here is the use case supported in Import Management, high-volume import mode, and External Data Loader Client:
What You're Trying to do |
How to do it |
---|---|
Update a value on a record |
|
When performing updates using Import Management, if you have multiple rows in the source CSV file, each using the same PK or PUID values, then the records in your source file may not be processed in the order in which they're listed.
Scenarios for Creating, Updating, and Deleting Records
Scenario A shows the different ways of creating, updating, and deleting records using different combinations of keys with account object as an example. The following image and tables highlight the use cases for this scenario:
![Scenario A for alternate key import](images/AlternateKeyScenarioA2_20069694.png)
Column |
Meaning |
---|---|
PartyId |
Primary key. Value comes from system-generated document sequence number. Never null. |
PartyNumber |
PUID. When record is created value is either passed in or system-generated. Never null. |
SourceSystem |
Source system name. Can be null. (Only a limited number of objects support OS/OSR construct) |
SourceSystemReferenceValue |
Source system reference. Can be null. (Only a limited number of objects support OS/OSR construct) |
CEOName |
This is a non-key data attribute on the object. Can be null. |
Use Case |
Source file contents |
Description |
PK |
PUID |
OS |
OSR |
Non-key Data |
---|---|---|---|---|---|---|---|
Create a record, pass in PUID on primary object |
PartyNumber: CA4139 CEOName: John Smyth |
A record created |
98769 (system-generated) |
CA4139 |
N/A |
N/A |
John Smyth |
Create a record, without passing PUID value on primary object |
CEOName: John Smyth |
A record created |
98770 (system-generated) |
CA4140 (system-generated) |
N/A |
N/A |
John Smyth |
Create a record, pass in OS/OSR on primary object |
SourceSystem: Siebel CRM SourceSystemReferenceValue: 3-0007 CEOName: John Smyth |
A record created |
98771 (system- generated) |
CF380A (system- generated) |
Siebel CRM |
3-0007 |
John Smyth |
Update data attribute on existing record, using PK to identify the record |
PartyId : 98769 CEOName: Soloman |
Record updated |
98769 |
N/A |
N/A |
N/A |
Soloman |
Update data attribute on existing record, using OS/OSR to identify the record |
SourceSystem: Siebel CRM SourceSystemReferenceValue: 3-0007 CEOName: Soloman |
Record updated |
98771 |
N/A |
Siebel CRM |
3-0007 |
Soloman |
Delete existing record, using PK to identify the record |
PartyId : 98769 |
Record deleted |
98769 |
N/A |
N/A |
N/A |
N/A |
Delete existing record, using PUID to identify the record |
PartyNumber : CA4140 |
Record deleted |
98770 |
CA4140 |
N/A |
N/A |
N/A |
Delete existing record, using OS/OSR to identify the record |
SourceSystem: Siebel CRM SourceSystemReferenceValue: 3-0007 |
Record deleted |
98771 |
N/A |
Siebel CRM |
3-0007 |
N/A |
Scenario B shows the different ways of creating child records, updating and deleting relationship to the parent record. Here account is the parent and address is the child object. The following image and tables highlight the use cases for this scenario:
![Scenario B for alternate key import](images/AlternateKeyScenarioB_20069695.png)
Column |
Meaning |
---|---|
PartyId |
Primary key. Value comes from system-generated document sequence number. Never null. |
PartyNumber |
PUID. When record is created value is either passed in or system-generated. Never null. |
SourceSystem |
Source system name. Can be null. |
SourceSystemReferenceValue |
Source system reference identifier. Can be null. |
CEOName |
This is a non-key data attribute on the object. Can be null. |
PartyId |
Foreign key to the parent record. This value contains the PK of the record in the parent object. |
PartyNumber |
Foreign key to the parent record. This value contains the PUID of the record in the parent object. |
AddressNumber |
This value contains the PUID of the record in the child object. |
PartySourceSystem |
Source system value of the parent object. |
PartySourceSystemReferenceValue |
Source system reference value of the parent object. |
Use Case |
Source file contents |
Description |
PK |
PUID |
Parent PUID |
Parent OS |
Parent OSR |
OS |
OSR |
Non-key Data |
Foreign Key |
---|---|---|---|---|---|---|---|---|---|---|---|
Create a child record, pass in custom PUID and parent object PK value |
PartyNumber: CA2700 CEOName: Rhode PartyId: 98770 |
A child record is created, relationship to parent is established through PK |
5001 (system- generated) |
CA2700 |
N/A |
N/A |
N/A |
N/A |
N/A |
Rhode |
98770 |
Create a child record, pass in parent object PUID value |
CEOName: Rhode PartyNumber: CA2700 |
A child record is created, relationship to parent is established through PUID |
5002 (system- generated) |
system- generated |
CA2700 |
N/A |
N/A |
N/A |
N/A |
Rhode |
N/A |
Update the relationship to point to a different parent, using the PUID of the child as the key |
PartyNumber: CA2700 AddressNumber: CF383 |
Existing child record updated relationship to a new parent established through PUID |
5001 |
CA2700 |
N/A |
N/A |
N/A |
N/A |
N/A |
Rhode |
98772 |
Delete the relationship to the parent |
PartyId: 5002 |
ERROR - child record should not exist stand alone. Must have relationship to a parent. (Address object is an exception to this.) |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Create a child record, pass in parent object OS/OSR value |
PartySourceSystem: Siebel CRM PartySourceSystemReferenceValue: 3-0001 SourceSystem: Siebel CRM SourceSystemReferenceValue: 3-9001 |
A child record is created, relationship to parent is established through OS/OSR |
system-generated |
CA2701 |
N/A |
Siebel CRM |
3-0001 |
Siebel CRM |
3-9001 |
N/A |
N/A |
Create a child record, pass in parent object PUID value |
PartyNumber: CDRM-2001 |
A child record is created, relationship to parent is established through Parent PUID |
system-generated |
CA2702 |
CDRM-2001 |
N/A |
N/A |
Siebel CRM |
3-9002 |
N/A |
N/A |
Create a child record, pass in parent object foreign key value |
PartyId: 1234 |
A child record is created, relationship to parent is established through foreign key |
system-generated |
CA2703 |
N/A |
N/A |
N/A |
Siebel CRM |
3-9003 |
N/A |
1234 |
Delete child record using PUID to identify the record |
AddressNumber: CDRM-9001 |
Delete a child record using the PUID of the record |
N/A |
CDRM-9001 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
Delete child record using OS/OSR to identify the record |
SourceSystem: Siebel CRM SourceSystemReferenceValue: 3-0007 |
Delete a child record using OS and OSR |
N/A |
N/A |
N/A |
N/A |
N/A |
Siebel CRM |
3-0007 |
N/A |
N/A |
Delete child record using primary key |
AddressId: 9898989 |
Delete a child record using primary key |
9898989 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |