Creating a Source Data Spreadsheet to Use with Datastream

Foundations Datastream creates records from data you copy and paste from a table. This section provides information about basic import scenarios as well as more complex scenarios, such as creating data for multiple objects at once, batching imports, and updating records.

Basics

To import data, essential information must be provided in a specific format.

Refer to the following criteria and examples when creating your data import spreadsheet:

  • Specify the object you want to load data into using the first cell in the table. 
    You must use the object's API name.
  • Define the fields you want to provide data for in the following columns. You can identify the fields using unique field labels. If a field has a common label value, provide the API name instead.
  • Include columns and values for all required fields.
  • Simplify your data using formulas when creating data in your spreadsheet. When copying data that includes formulas, Datastream only copies the resulting values.
  • Provide values for date and date time fields using ISO 8601 format to avoid ambiguity. For example, YYYY-MM-DD for dates.
Note:

If you import data using Datastream features on a custom page, you might be restricted by which object you can import data for. There are no object restrictions when using the Foundations Datastream tab.

This example shows data to import several account records. The object API name is specified in the first column and field labels are used for fields that have a unique label. A field API name is used for the final column as its corresponding label is not unique to the object.

Sample Data Table to Create Accounts
Account Name Account Number Type ACV SCMC__Active__c
  Account One 111111111 Customer 1,000,000 Yes
  Account Two 222222222 Competitor 20,000,000 No
  Account Three 333333333 Partner 300,000,000 Yes
  Account Four 444444444 Customer 406,000,000 Yes
  Account Five 555555555 Competitor 555,500,000 No
  Account Six 666666666 Partner 705,000,000 Yes
  Account Seven 777777777 Customer 854,500,000 Yes
  Account Eight 888888888 Competitor 406,000,000 No
  Account Nine 999999999 Partner 1,153,500,000 Yes
  Account Ten 000000000 Customer 1,303,000,000 Yes

Record Types

You can specify different record types, if required. If you do not specify a record type, the user's default record type is used when the record is created.

Tip:

To include a record type, ensure the relevant table includes a column called Record Type ID.

This example shows data to import several account records, specifying the record type of either Customer or Partner.

Sample Data Table to Create Accounts with a Specific Record Type
Account Name Record Type ID Account Number Type ACV SCMC__Active__c
  Account One Customer 111111111 Customer 1,000,000 Yes
  Account Three Partner 333333333 Partner 300,000,000 Yes

Lookups to Existing Records

You can reference existing related records using the record's name, ID, or any field that contains a unique value.

For example, you want to create a billing document that contains a lookup to an existing account. To identify the account, you can specify any of the following:

  • Account name, such as Account One
  • Account record ID, such as 0018Z00002iFSBaQAO
  • Any field that contains a unique value, such as account number, such as 12345

To specify an identifier that is not the name or record ID in the spreadsheet, use the following format when naming the column header. Provide the label of the field you want to populate followed by the API name of the unique identifier on the referenced object in square brackets, including the relevant namespace.

For example, to identify an account by its account number, name your column Account[AccountNumber]. To identify a company by its country, name your column Company[fferpcore__Country__c].

These examples show different ways to import billing documents that include lookups to account and company records. Notice how the column headings change.

In this example, the account and company are identified by the record name.

Sample Data Table to Create Billing Documents that Reference Lookups by Name
fferpcore__BillingDocument__c Currency ISO Code Document Date Document Type Account Company
  AUD 2022-12-20 Invoice Account One Merlin Technologies Canada

In this example, the account and company are identified by the record ID.

Sample Data Table to Create Billing Documents that Reference Lookups by Record ID
fferpcore__BillingDocument__c Currency ISO Code Document Date Document Type Account Company
  AUD 2022-12-20 Invoice 0018Z00002iFSBaQAO aDi8Z000000wrW2SAI

When you import the data, the record ID is replaced with the record name, if available.

In this example, the account is identified by the account number and the company is identified by country.

Sample Data Table to Create Billing Documents that Reference Lookups by Different Field
fferpcore__BillingDocument__c Currency ISO Code Document Date Document Type Account[AccountNumber] Company[fferpcore__Country__c]
  AUD 2022-12-20 Invoice 12345 Canada

When you import the data, the account number and company country are replaced with the respective record names, if available.

Unrelated Objects

You can load multiple tables for unrelated objects in a single action.

Tip:

To do this, use an empty row to separate tables in your spreadsheet.

This example shows data to import several account records and several company records in a single action. An empty row separates the last account record from the company header information.

Sample Data Table to Create Accounts and Companies in One Action
Account Name Account Number Type ACV SCMC__Active__c
  Account One 111111111 Customer 1,000,000 Yes
  Account Two 222222222 Competitor 20,000,000 No
  Account Three 333333333 Partner 300,000,000 Yes
  Account Four 444444444 Customer 406,000,000 Yes
  Account Five 555555555 Competitor 555,500,000 No
  Account Six 666666666 Partner 705,000,000 Yes
  Account Seven 777777777 Customer 854,500,000 Yes
  Account Eight 888888888 Competitor 406,000,000 No
  Account Nine 999999999 Partner 1,153,500,000 Yes
  Account Ten 000000000 Customer 1,303,000,000 Yes
           
fferpcore__Company__c Company Name Accounting Company Country    
  Merlin Technologies Canada Merlin Technologies Canada Canada    
  Merlin Technologies ES Merlin Technologies ES Spain    

Related Objects

You can import data for related objects by specifying a temporary surrogate ID, which is populated with a Salesforce record ID when the data is uploaded successfully.

To do this, we recommend that you complete the following steps:

  • Specify a simple identifier for the parent record in an ID column. For example, specify BD1 in the Billing Document table.
  • In the child object table, enter the same ID in the field that looks up to the parent record. For example, in the Billing Document Line Item table, specify BD1 in the Billing Document column.

The steps described above are also valid when the relationship between objects is one other than a parent-child relationship.

Tip:

If you import a table for related objects that doesn't include a specified relationship between parent and child records, Datastream provides a suggested surrogate ID to link the records. You are prompted to check that this is the expected relationship.

This only occurs if your import contains a single parent row with no ID column present and no lookup to the parent on the child table.

This example shows data to create parent and child records. An ID column is included in the parent table and a lookup to the parent object is included in the child table. The ID value for the parent matches the ID specified on the parent lookup on the child. Tables for multiple objects are separated by an empty row.

Sample Data Table to Create Billing Documents and Related Billing Document Line Items in One Action
Relationship Specified
fferpcore__BillingDocument__c ID Currency ISO Code Document Date Document Type Account[AccountNumber]  
  BD1 AUD 2022-12-20 Invoice 12345  
             
fferpcore__BillingDocumentLineItem__c ID Currency ISO Code Billing Document Unit Price Quantity Product or Service
    AUD BD1 1 1 Accounting Engine -Monthly

When you save the records, BD1 is replaced with the record ID of the parent.

For this example, the data does not include an ID column in the parent table or any lookups to the parent in the child table. Datastream provides a suggestion to link the records as the objects are related.

Sample Data Table to Create Billing Documents and Related Billing Document Line Items in One Action
Relationship Not Specified
fferpcore__BillingDocument__c Currency ISO Code Document Date Document Type Account[AccountNumber]
  AUD 2022-12-20 Invoice 12345
         
fferpcore__BillingDocumentLineItem__c Currency ISO Code Unit Price Quantity Product or Service
  AUD 1 1 Accounting Engine -Monthly
  AUD 3 1 Accounting Engine -Monthly

When these records are imported, Datastream provides a surrogate key in the following tables:

  • A record ID surrogate key on the parent record, such as Surrogate Key 1
  • A matching surrogate key on the child record in any fields that contain a lookup to the parent record

When you save the records, Surrogate Key 1 is replaced with the record ID of the parent.

Self-Relationships

You can also use temporary surrogate IDs to create records for objects with a self-relationship. Self-related objects are those that link to themselves. For example, the Customer Quotation Line object includes a field called Parent Customer Quotation Line. This is a lookup field that links the Customer Quotation Line object to itself, enabling you to link multiple customer quotation lines with a parent customer quotation line.

To create records for objects with a self-relationship, we recommend that you complete the following steps:

  • Specify identifiers for the records that relate to records within the same object in an ID column. For example, if you want to create four customer quotation lines where two of them are the parent lines for the other two, you must specify four identifiers such as QL1, QL2, QL3, and QL4.
  • In the column of the lookup field that defines the self-relationship, use the IDs that you have previously specified in the ID column to indicate the records you want to relate to each other. For example, if you want to define QL1 as the parent customer quotation line for QL3, and QL2 as the parent customer quotation for QL4, specify QL1 in the Parent Customer Quotation Line field of QL3, and QL2 in the Parent Customer Quotation Line field of QL4.

This example shows data to create records for objects with a self-relationship. An ID column is included in the table and also a lookup to the same object. The values in the ID column are used in the lookup column to indicate the records that must relate to each other.

Sample Data Table to Create Customer Quotation Lines That Relate to Each Other
SCMC__Customer_Quotation_Line__c ID Customer Quotation Delivery ARO Item Number Quantity Item Cost Supplier Site IsChildRecord Parent Customer Quotation Line
  QL1 Q-00000001 7 AC Duct 3 180 Widgets Are Us FALSE  
  QL2 Q-00000001 7 Bearing Set 4 100 Widgets Are Us FALSE  
  QL3 Q-00000001 7 Fan 3 110 Widgets Are Us TRUE QL1
  QL4 Q-00000001 7 Impeller 1 4 112 Widgets Are Us TRUE QL2

Batching

Datastream imports data in batches, according to the batch size specified in setup. However, you can also specify your own batches based on custom values. To do this, provide a batch name in the first column.

If the number of rows in your explicit batches exceed the batch size specified by your administrator, each batch is split according to the specified batch size.

The following example shows data for journals and journal line items, batched by the following values:

  • Merlin Technologies Canada
  • Merlin Technologies Ltd.
Tip:

If you want to batch by a value that already appears in the row, quickly set this up using formulas in your spreadsheet.

For example, here we want to batch by the name of the company, so we can populate our batch names using a formula that references the cell that contains the company name.

Sample Data Table to Provide Batches Manually
c2g__codaJournal__c ID Company Journal Description Reference Journal Date Journal Currency Currency ISO Code
Merlin Technologies Canada JNL1 Merlin Technologies Canada Loaded from Datastream R-1120567 2022-12-20 GBP GBP
Merlin Technologies Ltd. JNL2 Merlin Technologies Ltd. Loaded from Datastream R-1120567 2022-12-20 GBP USD
               
c2g__codaJournalLineItem__c ID Company Line Description Journal General Ledger Account Line Type Value
Merlin Technologies Canada   Merlin Technologies Canada Loaded from Datastream JNL1 1010 - Cash in bank- CAD General Ledger Account 13346
Merlin Technologies Canada   Merlin Technologies Canada Loaded from Datastream JNL1 1015 - Cash in bank-EUR General Ledger Account -13346
Merlin Technologies Ltd.   Merlin Technologies Ltd. Loaded from Datastream JNL2 1010 - Cash in bank- CAD General Ledger Account 13346
Merlin Technologies Ltd.   Merlin Technologies Ltd. Loaded from Datastream JNL2 1015 - Cash in bank-EUR General Ledger Account -13346

Updating Existing Records

You can update any existing records using Datastream as long as you provide the record's unique identifier in a column called ID.

Tip:

You can import new and existing records in the same table.

In this example, Datastream updates three accounts where record IDs are specified for the existing account. A new account is created for the row that doesn't contain a record ID.

Sample Data Table to Update Existing Accounts and Add a New One
Account ID Name Account Number Type ACV SCMC__Active__c
  0018Z00002i4Z3PQAU Account One 111111111 Customer 1,000,001 Yes
  0018Z00002i4Z3QQAU Account Two 222222222 Competitor 20,000,001 No
  0018Z00002i4Z3RQAU Account Three 333333333 Partner 300,000,001 Yes
    Account Eleven 100000000 Customer 23,000,000 Yes

Multiple Company Documents

You can use Datastream to create or update records for multiple companies in a single upload.

To do this, we recommend that you use batching, specifying one batch per company. For more information and an example of manual batches, see Batching.

When using Datastream for multiple company documents, ensure you are using multi-company mode and have all relevant companies specified in the upload data selected.

Depending on your version of Accounting, you might need to use use multi-company mode with all companies referenced in the spreadsheet. For the latest information about multi-company mode, see What is Multi-company Mode?.