Importing Data and Syncing Brick River With Other Systems
Brick River is an excellent platform to sync and import data from other systems into the system. You can import content and contacts into Brick River from any correctly formatted .csv (comma separated values) file. This process involves the following steps:
- Create a Brick River Import Definition document to store settings for the import.
- Prepare a .csv file for import
- Import a .csv file
- View Fields and Instructions
Step 1 - Create the Import Definition
- On the main menu, click Admin->Imports. Click New to create a new import.
- The Import screen is displayed.
- Name: Enter a name for your identification.
- Table/View: Select the view from which the records will be imported. In our example, choose Table - View.
- RunAs: Select a RunAs name - always select a user with administrator permission.
- Matching Mode: The matching mode determines how existing import records will be compared to existing records. If the import process locates a match, the existing record can be updated by the import file record. When there is no matching record a new record can be created. There are three matching modes:
- Brick River Id: Each record in a Brick River table has a unique record Id. These Ids are rarely displayed to users. If you choose the option Include Brick River Id, the resulting file includes a column with the field name Id. If you run an import using this matching method, the .csv file to be imported must include an Id field. This option is useful when you want to export a view, edit the resulting .csv in another application, and then import the updated .csv back into the Brick River system.
- External Source: Each record in Brick River can optionally store a SourceName and SourceId. These fields can be used to match Brick River records to records in an external system. If you select this option you will be prompted for a source name. The file to be imported must have a column named Id. The import process will match records base on matching both SourceName and Id. This option is useful when you need to regularly sync records between Brick River and external applications. See additional information and an example further down in the tutorial.
- Use deduping fields: Each Brick River view defines Deduplication Fields. If two records are found with matching values in all fields, the system identifies them as matching records. Each view can have a unique set of deduplication fields. It is common for contacts views to use Title and PubDate.
- Options: Select the desired Options.
- Add: The matching mode will be used and any record determined to be new will be added to Brick River.
- Update: The matching mode will be used and existing records in Brick River will be updated.
- Delete: Any records in Brick River that do not match a record in the import file will be deleted.
- Aggregate Relations: This setting applies to fields which may store multiple values such as category and relationship Fields. Check this option to have any values in the import file and add to existing values in fields. Uncheck this option to have current values in fields deleted and replaced by values in the import file. You almost always want to check this, if you are importing Contacts with a SubscriptionList field. If unchecked, contacts may be removed from subscription lists. It is most often the case that you want contacts left on current lists - and added to any additional lists included in the import file.
- Click Save to save the document. You can always open it to edit the document.
Step 2 - Prepare a.CSV File for Import
A .csv file as a simple spreadsheet which stores data in rows and columns. These files can easily be opened and edited using Microsoft Excel and other applications.
- For a Brick River import, in the .csv file, you must include a header row with the name of each column exactly matching the field Id used in Brick River.
- Click on the View fields and information link provided on the Import Definition form described below in step 4, to ensure that the header row contains valid field Ids.
- You must always direct the records to be imported to a Brick River Table / View. For example, an import file containing 1000 new names to include in email campaigns may be directed to the Contacts - People View.
Step 3 - Upload a CSV File
- Click Admin->Imports to open the saved import definition.
- Click Upload a .csv file now to select your import file, upload it to Brick River, and perform the import. An option to 'run in test mode' will process the import document without making any changes to Brick River content. It will report to you the number of additions, updates, deletions, and failures that will result from running an actual import.
Step 4 - View Fields and Instructions
- Click Admin->Imports to open the saved import definition.
- Click View fields and instructions to access the URL that is needed to upload an import file from outside Brick River and a field list with the field Id of each field that may be included in the import.
- Data Syncing: The URL provided can be used to post .csv files to the correct Brick River URL to perform an import.
- Columns: This table provides a list of each file defined by the view that is used in the import process. The Column Id field displays the values that must be used in the .csv header row to match import fields to Brick River fields. Other columns display details about each field.
- Click Save to save the changes made to the document.
Understanding External Source Name and Id
Each Brick River record may store a SourceName and SourceId field. These fields are useful for synchronizing data between Brick River and an outside application. For example, in Brick River, you have views to define volunteers and programs. You also use an Access database with a volunteers table and programs table.
Let us understand how an import is performed with an example below.
- In Brick River, volunteer Eric Zheng is a contact with a record Id of '123456'. This id is not displayed to users and may not be edited. It is used by the Brick River system for a variety of system tasks. In the Access database - Eric Zheng is at the volunteer table with a Volunteer Id of VOL-55567.
- In Brick River, Eric's record displays the source Name and source Id in the External Source section.
- You can now run imports to update Brick River using the external source matching method. For example, a .csv file contains the following row.
- The import uses match External Source Id and the Source Name: AccessDatabase. This import will update the FirstName, LastName, and Email address of Eric Zheng in Brick River.
- Note that the Source Name must be used consistently and should point to the external system, not tables or other entities in that system. In our example - both volunteers and programs should use the Source Name: AccessDatabase.
- This allows Brick River to maintain relationships between volunteers and programs using their Access record Ids. If different source names are used, AccessDatabase-Volunteers and AccessDatabase-Programs, then Brick River cannot maintain the relationship between volunteers and programs using the Access record Ids.
Adding and Updating Records
The import process can be used to add, update, or delete records, or any combination of the three.
- Adding records: When the match method determines that a record in the import file does not exist in Brick River, a new record is added and populated with whatever field values are supplied in the import.
- Updating records: When the match method identifies an existing Brick River record, the field values in the import file overwrite corresponding field values that exist in Brick River. Fields not included in the import file are not updated. Choose Aggregate Relations to have values for multi-value fields ADD TO current field values rather than OVERWRITE existing values.
- Deleting Records: Any records in Brick River that do not match a record in the import file are deleted.
A .csv file containing the following rows is imported to Brick River - matching on External Source Id and performing both ADD and UPDATE actions:
An edited .csv is imported using the same settings:
||PreferredName is updated
||this record would be added