Importing Related Records

Overview

While individual relationships between records can be created in the Sugar® user interface, there may be occasions when you want to relate many records at a time. Users can create relationships between records using the import tool. This article covers some best practices and walks through example steps for importing related records.

For the complete guide on importing, please refer to the Import documentation.

Best Practices

Refer to the following sections for best practice advice that you can use in the Application section later on this page.

Importing Relationships

The type of records you can relate and how they relate is unique to each module. For a list of the types of records you can relate, please refer to the Import documentation.

Keep in mind these important rules when relating records via import:

• When relating records using the existing Sugar record's name, use the exact name as displayed on the existing Sugar record.
• When relating records using the existing Sugar record's ID number, use the exact record ID number with no spaces before or after the number. To obtain a record's ID number, export the desired record from Sugar to a CSV file; the record ID number will be displayed in the first column of the spreadsheet.
• To prevent overwriting existing data on a "Create new records and update existing records" import, always import as few field columns as possible. For most scenarios, that would be the module's required fields, the appropriate relate or ID fields, and the fields you would like to update. All other columns can be deleted from the import file.
• Relate fields may vary based on established or custom relationships specific to your instance.

Importing Records Related to Accounts

If you are importing data into a module that allows you to relate records to accounts, import the account records first if they are not already in Sugar. Accounts are considered the "parent" records of most other Sugar records because of their one-to-many (e.g. one account to many contacts) relationship.

Create a spreadsheet that contains only the account information and import the account records into the Accounts module. This will assign a unique ID to each new account in Sugar, which can be used for subsequent imports of records related to the accounts. For examples of this process, please refer to the Application section.

For more information on importing with record IDs, please refer to the Importing With Record IDs Using Excel's VLOOKUP article.

Handling Duplicate Record Names

As a best practice, you should try to name all records in your Sugar instance uniquely. However, if two or more records such as accounts share the same name in Sugar and one or more of those accounts are involved in an import of related records, you must use the Account ID column in place of or in addition to Account Name for the affected rows of your import file. You only need to use the Account ID for the records that are related to accounts that have the same name as another account in Sugar (e.g. your instance contains an account named "Acme Inc" that is in Atlanta and another named "Acme Inc" that is in Pittsburgh). 

For example, in the contacts import file pictured below, the Account ID field can remain empty for the gray rows because the contacts relate to the same account in Sugar. But the Account ID field is required for the yellow rows because they relate to two different Sugar accounts that happen to have the same name. Furthermore, the green row requires an Account ID because, in this theoretical scenario, there is already at least one account in Sugar named "Acme Inc". 
 

Importing Common Relate Fields

The following table contains fields that are commonly used to create a relationship between Sugar records and modules that may be hidden from most regular users such as the Teams or Users module. More comprehensive details about relate-fields for specific modules can be found in the Import documentation.

Application

The following sections provide general instructions on how to use import for relating new records to other new records, existing records to other existing records, and new records to existing records in Sugar.

Relating New Records via Import

This section explains how to import new accounts and new contacts and create a parent-child database relationship between the records (i.e. parent accounts and child contacts).

For this example, we have a list of contacts in a spreadsheet that we would like to import. Our list contains the people's names, company names, addresses, and other typical contact information. When we import the contacts, we want to be sure that the people and companies are imported as contacts and accounts, respectively, and that each new contact record relates to the appropriate new account record. To achieve this, we need to separate the contacts from the accounts and import the parent accounts first, as explained earlier in the Importing Records Related to Accounts section.

Use the following steps to import a list of accounts followed by a list of contacts and create relationships between the contacts and accounts. For more detailed instructions on importing, please refer to the Import documentation.

1. First, prepare to import the company information into the Accounts module. Navigate to the Accounts module in Sugar and select "Import Accounts" from the module's tab menu.
2. Download the template provided by Sugar in the first step of the Accounts Import Wizard to prepare your import file. 
   Note: The template file is available in step one or two depending on the type of records you are importing.
   
3. Open the Accounts.csv template file and remove any sample data and help text, leaving the column headers intact.
   
4. Copy the company information from your import list into the appropriate columns of the template. Note that, at this point, you can ignore the person-specific information such as First Name, Last Name, Title, Email Address, or any other information that does not directly pertain to the overall company.
5. Save the Accounts.csv file and complete the steps of the Import Wizard to import the accounts.
6. Next, prepare the contact import. Navigate to the Contacts module in Sugar and select "Import Contacts" from the module's tab menu.
7. Download the template provided by Sugar in step two of the Contacts Import Wizard to prepare your import file.
   
8. Open the Contacts.csv template file and remove any sample data and help text, leaving the column headers intact.
9. Copy the contact information from your import list into the appropriate columns of the template. There may be some data that you imported with the accounts that you can omit from this list such as the company's website or industry.
10. Ensure that, for each contact row, the Account Name column contains the company name exactly as you imported it earlier. Be sure to insert the Account ID for any account name that is not unique in Sugar, as explained in the Handling Duplicate Record Names section.
    
11. Save the Contacts.csv file and progress thru the steps of the Import Wizard to the "Field Mappings" step.
12. Double check all of the field mappings and confirm that the Account Name and Account ID fields are mapped to the correct Sugar fields.
    
13. Complete the import and evaluate the results.

Because the Contacts.csv file contains exact-spelling matches of the account names imported earlier, Sugar will automatically relate the new contacts to their appropriate accounts.

Relating Existing Records via Import

This section explains how to use the import tool to relate existing accounts to existing contacts, creating a parent-child database relationship between the records (i.e. parent accounts and child contacts).

If you want to create relationships between existing records, for example, between existing contacts and accounts in Sugar, use the following general steps. For more detailed instructions on importing, please refer to the Import documentation.

1. Export the contacts that you want to update from the Contacts module's list view.
2. In the Contacts.csv file, update the Account Name and Account ID columns, following the best practices covered in the sections above.
3. Save the updated file.
4. Re-import the updated contacts using "Create new records and update existing records" in the Import Wizard.
   
   

Because the .csv file contains the Sugar ID for each contact, Sugar will create relationships with the existing contacts instead of creating new contact records. For more information on using imports to update existing records, please refer to the Updating Records Via Import article.

Relating New Records to Existing Records via Import

This section explains how to import new contacts that relate to existing accounts, creating a parent-child database relationship between the records (i.e. parent accounts and child contacts). Simply perform a traditional import to the Contacts module with the Account Name or Account ID columns populated with the value as it appears in Sugar. For large or diverse lists, it may require a bit of manual work to populate the Account fields  for import. This exercise will drive home the importance of adopting and enforcing a consistent naming convention in your database (e.g. create rules related to the use of punctuation or suffixes like "Inc." and "Corporation").

1. Navigate to the Contacts module in Sugar and select "Import Contacts" from the module's tab menu.
2. Download the template provided by Sugar in step two of the Contacts Import Wizard to prepare your import file.
   
3. Open the Contacts.csv template file and remove any sample data and help text, leaving the column headers intact.
4. Copy the contact information from your import list into the appropriate columns of the template.
5. Ensure that, for each contact row, the Account Name column contains the company name exactly as it appears in Sugar. Be sure to insert the Account ID for any account name that is not unique in Sugar, as explained in the Handling Duplicate Record Names section.
   
6. Save the Contacts.csv file and progress thru the steps of the Contacts Import Wizard to the "Field Mappings" step.
   • Note: For this use case, our list of contacts are all new to Sugar so we will leave "Create new records only" selected in Step 2 of the Import Wizard; If you are not sure whether all of the contacts are new, you can change this option to "Create new records and update existing records" to account for both new and existing contact data. Be aware that the import file's data will override any existing data that may be in Sugar for an imported contact.
7. Double check all of the field mappings and confirm that the Account Name and Account ID fields are mapped to the correct Sugar fields.
   
8. Complete the import and evaluate the results.

As long as the Contacts.csv file contains exact-spelling matches of the account names or the Sugar account ID, the new contacts will match with their appropriate accounts.