ArticlesAC: How to Use Unique References in Datastream to Resolve Duplicate Lookups

• Certinia Accounting
• Certinia Foundations

Notes: 

• Salesforce allows duplicates in the Account Name field
• Additionally, standard Salesforce does not have a unique identifier for Accounts (outside of Salesforce's standard 18-digit ID)
• Further, Salesforce has a field that looks as though it would be one: Account Number but it is a text field (rather than autonumber) and not unique

The simple version of this procedure is to replace a column header title with one that includes a unique reference. This can be done for any field, but the two use cases discussed here are for Account and General Ledger Account:

Account:

1. Change the existing column header from Account to Account[Account_Unique_ID__c] (a custom field)
2. Change the existing column header from General Ledger Account to General Ledger Account[c2g__ReportingCode__c]
3. Create a new custom field with the following details:
   • Object: Account
   • Field Name: Account Unique ID
   • Data type: autonumber display format of “A-{0000000}”
   • External ID: True
   • Populate to all existing records
   • Visibility: visible to all profiles
     
     

General Ledger Account:

• The accounting GLA object already has a unique identifier called Reporting Code (c2g__ReportingCode__c)
• This the prefix to the General Ledger Account Name
• As this should be fully populated it is a good candidate for unique reference.
• Note that it is a text field, not numeric, so a cell entry should have an apostrophe: “‘1200”

Example:

The example below shows a Datastream loading a Journal with Account - Customer line items using the unique reference fields (within square braces):

From the Datastream page these fields resolve to the Account Name and General Ledger Account:

Using unique identifiers resolves issues around duplicate values and provides familiar labels especially when a column is referencing a lookup field.

Datastream typically identifies a field through the text in the column header (this text can be either the label shown on a page layout or the API Name). There are times when this field is not unique (such as the case of an account name) or cumbersome (such as a fully qualified GLA name).

An alternative way to identify a field is through a unique reference, for instance using the External Id field on Account (which is unique) or the GLA Reporting Code (also unique).