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.

Example: Create Records for a Single Object

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
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.

Example: Create Records with a Specific Record Type

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

You can reference 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 could 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].

Example: Create Records that Reference Objects Using Various Fields

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.

Example: Create Records for Unrelated Objects Simultaneously

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.

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.

Example: Create Records for Parent and Child Objects Simultaneously
Surrogate Key Provided

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.

Example: Create Records for Parent and Child Objects Simultaneously
Surrogate Key Suggested

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.

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.

Example: Manual Batches

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.

Example: Updating Records

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
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 FinancialForce 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? in the Accounting Help.