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.

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.

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

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.

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.

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.

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.

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