ArticlesAC: How to Restart Uploads Using Surrogate IDs in Datastream

• Certinia Accounting
• Certinia Foundations

• It uses the same concept of unique identifiers discussed in How to Use Unique References in Datastream to Resolve Duplicate Lookups
• It blends with Datastream's ability to automatically link a child record to its parent (e.g. SIN1, SIN2)
• When an upload fails to insert child records - the header records are created but not child records - the upload of failed detail records (orphans) can be restarted using the unique reference of the parent

When importing high volumes of records, such as payable invoices and their associated expense lines, Datastream manages the inserts through batch processing to handle the large capacity. Because the data is split into these batches, it is possible for some operations to succeed while others fail. For example, the system might successfully create all invoice headers, but fail to insert a specific batch of line items for a required field (like a "line description" is missing). This partial failure leaves results in incomplete, zero-value headers that lack their corresponding line items. A common reaction to this is to discard the newly-created valid headers, fix the data issues in the spreadsheet, and restart the entire import process from scratch. However, this is inefficient.

To fix errors and attach corrected lines to these existing headers without starting over, use surrogate IDs to uniquely identify these records (using the External ID field).

Create the Surrogate ID

1. Before the initial import, add the surrogate ID field (External Id) to the header
2. Use it to reference the header from the detail line
3. To make sure the field is unique, use a formula field combining existing data, such as account name and vendor invoice number
   1. Create a column in the spreadsheet for the unique reference field named External ID
   2. Best practice for the formula is to concatenate existing data: = [Account Name]+[Vendor Invoice Number] (or as a spreadsheet formula =CONCAT(D2,E2))
   3. On the detail line, use this value when looking up to the header
      • For example: Payable Invoice Number[c2g__ExternalId__c]

About Automatic Linking

• When pasting data into the system, it uses the surrogate ID to automatically lookup and link the line items to the correct payable invoice headers
• By using surrogate IDs, the need for tedious manual ID mapping is eliminated, making it simple to reuse valid headers and recover from batch processing errors
• The Unique Reference feature in Datastream provides the capability to reference an item such as a document (Invoice, Journal, etc) or a related record (e.g. General Ledger Account, Dimension), by using any field on that record that serves as a unique ID

Troubleshoot Failed Batch Line Items 

If a batch of line items fails, do not manually copy system-generated IDs or names from the successfully-created headers back into the spreadsheet.

Instead:

1. Filter the spreadsheet to hide the records that already inserted successfully
2. Correct the missing data (for instance the line description (required) was omitted, then enter it)
3. Copy the remaining failed detailed records into datastream (including the line with the line item object name and column headers)
4. Click save 

About the Restart Process

When pasting the corrected batch into the system, the application uses External ID to quickly find the previously-created empty headers and attaches to the newly-corrected lines.

Example

Spreadsheet (link to sample) Remember that the Vendor invoice number must be unique, which means the External ID field is unique as well.

Datastream Draft:

Datastream Results:

By using Surrogate IDs, Datastream is able to save the data when operations succeed and it provides a mechanism for restarting uploads for those that fail. By mastering the Surrogate ID method, users are able to minimize workflow disruption. This knowledge article proposes a workflow that uses surrogate IDs to find source documents.