Preparing the workbook data

The Contact worksheet type allow any iMIS system defined Status Code to be used.

For the Contact WorkSheetType, the following properties can only be added or inserted, not updated through the Workbook Converter:

• PaidThruDate
• JoinDate
• RenewedThruDate

For more information, see Creating the contact date file.

Be sure that each worksheet that contains data to be imported is listed on the Contents worksheet, and that there is an entry for all appropriate worksheet types.

If you are importing contacts with only one address, you can include all address and name information in a single worksheet. For example, create a worksheet called Individuals that is listed twice on the Contents worksheet as both a Contact-type worksheet and an Address-type worksheet; however, if you have a worksheet called Individual that is both a Contact-type and an Address-type worksheet but do not provide the AddressPurpose information on the Individual worksheet (which the iMIS Workbook Converter is expecting), a warning is displayed during a Test Only validation run.

If you are adding a worksheet that sets the various available options, this worksheet must be identified as the Options-type worksheet in the Contents worksheet.

When updating an existing physical address, be sure to re-enter the data for all of the address columns (AddressLine1, AddressLine2, AddressLine3, CityName, CountrySubEntityCode, and so forth).

If any columns are left blank (for example, AddressLine2 and AddressLine3) and the previously existing address for that Address Purpose contained values in those fields, those values will be overwritten and cleared. To update contact information without modifying any address information, ensure that the worksheet being processed is identified in the Contents worksheet as only being a Contact-type worksheet.

If the primary organization ID for a contact is updated as a result of the import, the default address for that contact is overwritten with the corresponding address for the new primary organization, unless the automatic flow down of company address information is disabled. Automatic flow down of company address information is disabled by default.

In the Contents worksheet, list the worksheet names in the same order that worksheet types are listed on the Instructions tab. For example, list all Contact-type worksheet names first, then Address-type worksheets, and so forth.

Important! Remove any trailing spaces from all entries in the Contents worksheet.

Refer to comments on the Options worksheet in the sample workbook for more information.

Note: If you are adding a worksheet that sets the various available options, this worksheet must be identified in the Contents worksheet as an Options-type worksheet.

• CreateAccounts allows you to create accounts for new contacts.

The Test Only option on the utility must be disabled, and the CreateAccounts setting in the Options worksheet must be set to TRUE.

New accounts are not created if the Test Only option is enabled. Disabling Test Only and setting CreateAccounts to TRUE allows the conversion process to generate usernames based on the preferred mailing address email addresses.

Note: Set CreateAccounts to FALSE to improve performance.
Accounts can be created automatically if a contact does not already have user credentials and submits a Forgot my username request.
For more information, refer to Allow "Forgot my username" to automatically create user credentials for existing contacts.

• Duplicates: If the email address already exists in the iMIS database, the duplicate is flagged in your error log.
• No email: If the contact does not have an email address, no username is created for them.
• Passwords: The password is a random value, so the user must go through the Forgot my password route to reset the password to one of their choosing.

Note: iMIS will only create a new user account if a contact is being added. Modifying a contact will not result in a new user account.

• UseMultitasking allows multiple requests to be made when converting contact or related data instead of processing each item in sequence. This process results in multiple concurrent SOA requests in order to convert the various data.

Note: If you are importing recurring members using the Auto Renew Members worksheet, then UseMultitasking must be set to FALSE.

• UseLargeWorkbookSupport supports importing workbooks exceeding 70 MB in size.

The primary workbook is able to point to CSV files, and the UseLargeWorkbookSupport option is used to support large conversions. The UseLargeWorkbookSupport option does not load the entire workbook into memory for processing in order to optimize conversion time.

• For jobs that do not exceed 10,000 entries (names, addresses, related data, activities and so forth), a single workbook will be sufficient.
• For jobs that exceed 10,000 entries, it is recommended that you use external comma-delimited (CSV) files.

Note: The UseLargeWorkbookSupport option does not support coloring error cells.

• UseVerboseLogging adds significant extra logging messages.
• UseiMISId defaults to TRUE
  • When UseiMISid is set to TRUE, the ID entered in the Individual/Organization sheet will be added to iMIS as the contacts' primary Name/Party.ID. The Name.MAJOR_KEY is not populated. When an ID in the workbook matches the Name/Party.ID value for an existing contact in the database, that contact’s data is updated with the data from the workbook.
  • When set to FALSE, the ID entered in the Individual/Organization sheet is added to iMIS as the contacts' Name.MAJOR_KEY. The primary Name/Party.ID is auto-assigned. When an ID in the workbook matches the Name.MAJOR_KEY value for an existing contact in the database, that contact’s data is updated with the data from the workbook.

• Specify details for each contact, defining which contacts are created as persons or organizations, and redirect activity, history, and user-defined data to the appropriate ID.
• If an ID in the workbook matches an ID for an existing contact in iMIS, then that contact’s data is updated with the data from the workbook.
• Phone numbers, email addresses, fax numbers, and so forth are stored in their associated address records.
• Contacts whose status is either Inactive or Marked for Deletion should be reviewed.

• If the UseiMISid parameter is set to FALSE and the ID field is left blank, an error message is triggered during the import process. The system will not execute any updates until the error is resolved. This is only applicable to rows with a valid MAJOR_KEY. All errors must be resolved before any updates continue.

Create one or more worksheets with this worksheet type. The sample workbook instructions show which fields apply to each type of contact. You must have at least one Contact-type worksheet. Follow the instructions provided in the worksheet comments.

Warning! When creating a new worksheet, copy the entire first row containing the column headers from the sample workbook with a matching worksheet type into the first row of your new worksheet. The column headers must be on the first row, and all column headers must appear. You do not have to fill out every column, but do not delete or rename any column headers.

Enter all of the addresses for each contact (Persons or Organizations) into the appropriate columns of the Contact-type worksheets.

If a contact has more than one address, you might enter additional addresses into any Address-type worksheet (as defined on the Contents worksheet). Be sure to list all of the worksheets that contain address information in the Contents worksheet.

Note: The first address for each ID must be marked as the default address. All other addresses can be flagged to override that default with a specific value for various communication reasons (for example, preferred mail, bill, and ship).

You can use this worksheet type to define the structure of an associated RelatedData-type worksheet. The example workbook contains the following worksheets as examples of this worksheet type:

• Education Info Definition – Defines the fields used in the associated Education Info Data worksheet. Follow the instructions provided in the worksheet comments.

• Personal Info Definition – Defines the fields used in the associated Personal Info Data worksheet. Follow the instructions provided in the worksheet comments.

Worksheets of this type are user-defined. Their structure can be defined by an associated RelatedDataDefinition-type worksheet, or by a table or Panel Editor data source that already exists in iMIS.

The example workbook contains the following worksheets as examples of this worksheet type:

• Education Info Data – This worksheet’s structure is defined by the associated Education Info Definition worksheet (RelatedDataDefinition-type worksheet). This worksheet contains multi-instance data; it can contain multiple rows of data for a single contact. Enter the educational information for each contact into the appropriate columns.
• Personal Info Data – This worksheet’s structure is defined by the associated Personal Info Definition worksheet (RelatedDataDefinition-type worksheet). This worksheet contains single-instance data; it can only contain one row of data for a single contact. Enter the personal information for each contact into the appropriate column.
• Activities – This worksheet is an example of a RelatedData-type worksheet that does not have an associated RelatedDataDefinition-type worksheet. The table referenced by the worksheet already exists in iMIS. Enter information about the activities, such as sales, for each contact in the appropriate column. In order to import the data, the activity type must be valid in iMIS.

Note: On the Activities tab, the Tickler_Date is the follow up date. Imported activities will appear in the Follow up activity alert on the Staff site.

Your workbook might contain RelatedData-type worksheets that are defined by existing tables in iMIS. If a RelatedData-type worksheet contains multiple rows of data for a single contact (multi-instance table data), enter True for that worksheet in the MultiInstance column on the Contents tab. This specification ensures that the iMIS Workbook Converter does not produce errors for these rows when run in Test Only mode. The Activities worksheet in the sample workbook is an example of a predefined multi-instance table.

If the worksheet can only contain one data row per contact (single-instance table data), leave the MultiInstance column blank. When in Test Only mode, the iMIS Workbook Converter checks for multiple occurrences of an ID in single-instance table data and produces errors for those rows.

You can use this worksheet type to define the structure of an associated RelatedData-type worksheet. The example workbook contains the following worksheets as examples of this worksheet type:

• Education Info Definition – Defines the fields used in the associated Education Info Data worksheet. Follow the instructions provided in the worksheet comments.

• Personal Info Definition – Defines the fields used in the associated Personal Info Data worksheet. Follow the instructions provided in the worksheet comments.

Worksheets of this type are user-defined. Their structure can be defined by an associated RelatedDataDefinition-type worksheet, or by a table or Panel Editor data source that already exists in iMIS.

The example workbook contains the following worksheets as examples of this worksheet type:

• Education Info Data – This worksheet’s structure is defined by the associated Education Info Definition worksheet (RelatedDataDefinition-type worksheet). This worksheet contains multi-instance data; it can contain multiple rows of data for a single contact. Enter the educational information for each contact into the appropriate columns.
• Personal Info Data – This worksheet’s structure is defined by the associated Personal Info Definition worksheet (RelatedDataDefinition-type worksheet). This worksheet contains single-instance data; it can only contain one row of data for a single contact. Enter the personal information for each contact into the appropriate column.
• Activities – This worksheet is an example of a RelatedData-type worksheet that does not have an associated RelatedDataDefinition-type worksheet. The table referenced by the worksheet already exists in iMIS. Enter information about the activities, such as sales, for each contact in the appropriate column. In order to import the data, the activity type must be valid in iMIS.

Note: On the Activities tab, the Tickler_Date is the follow up date. Imported activities will appear in the Follow up activity alert on the Staff site.

Your workbook might contain RelatedData-type worksheets that are defined by existing tables in iMIS. If a RelatedData-type worksheet contains multiple rows of data for a single contact (multi-instance table data), enter True for that worksheet in the MultiInstance column on the Contents tab. This specification ensures that the iMIS Workbook Converter does not produce errors for these rows when run in Test Only mode. The Activities worksheet in the sample workbook is an example of a predefined multi-instance table.

If the worksheet can only contain one data row per contact (single-instance table data), leave the MultiInstance column blank. When in Test Only mode, the iMIS Workbook Converter checks for multiple occurrences of an ID in single-instance table data and produces errors for those rows.

Important! Gift Aid Declarations must be imported at the same time the party is being imported.

The GiftAidDeclaration-type worksheet allows you to import existing Gift Aid declarations. The Id, DeclarationReceived, and MethodOfDeclaration fields are all required. The Future and Past columns cannot be blank and must either be TRUE or FALSE.

Read the tool tips in the Gift Aid Declaration sample worksheet for more information. Also, review the Gift Aid documentation for more information.

The Communication Type Preferences worksheet allows you to import the preferences of members for certain types of communications, and to also comply with various anti-spam laws. You can refer to a contact by ID and then define that contact’s communication preference for each existing communication type.

To be compliant with specific spam legislation (for example, Canada's Anti-Spam Law or the CAN-SPAM Act) you must configure these communication preferences. Refer to the specific spam legislation with which you are attempting to comply in order to understand which communication preferences you must enable.

As you configure new types of communications that can be sent by your organization, you can add those communication types to this worksheet.

For each communication type defined in iMIS for which you want to modify a value, add a column in the Communication Type Preferences worksheet. Make sure the name in the column header exactly matches the name of the communication type in iMIS, including spaces.

• You must have an Id column. This is the ID of the contact for whom you are updating communication preferences.
• The Communication Type Preferences worksheet can be run iteratively. For example, if you had already defined the preference as opted-out (FALSE) for one communication type, you can then edit that preference in the worksheet and import again to update preferences or to add preferences for new communication types.
• When importing CommunicationTypePreferences, set the UseiMISid to true.
• On the Contents tab, the Worksheet Type does not have to be defined as Contact. Instead, under Options, add or update CommunicationTypePreferences and a corresponding worksheet with the mapped-out properties.

For each communication type, enter one of the following values:

• FALSE – Subscribe by default (Opt-out of receiving the communication).
• TRUE – User must subscribe (Opt-in to receive the communication).