Using the Workbook Converter to import clubs and members

The Contents tab lists out all the Worksheet names and Worksheet Types. Any worksheet that contains data to be imported must be listed on this tab.

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.

📘 Note

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.

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.

📘 Note

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.

The Individuals tab is a worksheet that imports contact data. The Individual tab has a Worksheet Type of Contact. Typically, you will have one worksheet for all contacts that are Persons (PartyType=P), and another for Organizations (PartyType=O), but you can use mutliple worksheets as desired. 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.

Enter all individual contacts into a Contact-type worksheet, assigning the appropriate club by entering the club ID in the PrimaryClubID column.

The PrimaryClubID is the ID of a club that has already been created in iMIS. It can also be the ID of the new club you are creating and importing from the Organizations tab.

The Organizations tab is a worksheet that imports organization, company, and club data. The Organizations tab has a Worksheet Type of Contact.

To import new clubs, enter the ID and set PartyType=CLUB. Set the Status to A and enter the Club name in the OrganizationName column. Enter any other information about the club.

To import new schools, enter the ID and set PartyType=O.

To import new chapters, enter the ID and set PartyType=CHAPTER.

The Additional Addresses tab has a Worksheet Type of Address and is used for entering additional addresses for people or organizations.

Enter all the addresses for each contact (Persons or Organizations/Clubs) 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 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).

The Group Names tab is a worksheet that lists the names and associated information for each of your households, schools, or club memberships.

To import a Household, set GroupName='name of household' and GroupClass=Household.

To import schools, set GroupName=Schools and the GroupClass=School.

To import Club member, set GroupName='name of the group' and GroupClass='group class of the group'.

You might choose to create a single membership group and assign roles to differentiate between member types such as regular or student, or you might decide to create a separate group for each member type.

• GroupName must be unique.
• Subsequent conversions will utilize previously converted groups for group memberships and will ignore any GroupClass, Description, and GroupOwnerId values that are specified.
• Group memberships are added to existing groups.
• The GroupName defined in a worksheet type of Group Memberships should also be defined in a worksheet type of GroupNames.
• The GroupClass assigned here for each group must exactly match one of the GroupTypes as defined in iMIS prior to running the conversion.

📘 Note

The GroupOwnerId is only used to relate a relationship type of group to its owner Individual. The GroupOwnerId should correspond directly to a record Id on the Individuals worksheet.

The Group Membership tab is where you define which contacts are to be imported into iMIS. iMIS identifies which contacts to import through the ID column and which group to associate the contact with through the GroupName column.

• GroupName must be unique.
• Group memberships are added to existing groups.
• The GroupName defined in a worksheet type of Group Memberships should also be defined in a worksheet type of GroupNames. Often a separate worksheet is used for each group, but as long as the worksheet is listed in the Contents tab with a Groupmemberships worksheet type, the worksheet will be processed.
• When creating GroupMemberships, there is an effective date. The default effective date for any GroupMembership will be in the selected database time zone.

To import members into a membership group, enter the ID of the contact that will be added to the group. The GroupName must match one of the groups in the Group Names worksheet.

Enter a JoinDate for the member. If you are entering multiple rows of membership terms, it is recommended you enter the Join date that you want imported in the first row and leave the remaining rows blank.

For example, if you are importing three separate membership terms, you would enter the join date in the first row and then the next two rows would be blank in the JoinDate column.

If you enter the join date in a row other than the first, it will still import.

If the JoinDate column is left blank, the Workbook Converter will use the EffectiveDate as the join date. If the EffectiveDate column is blank, the join date and effective date will be the date the data was imported into iMIS.

The Join date appears in the group member details window of a group membership. Please see Editing group member details documentation for more information.

The Club Properties Data tab is a worksheet that imports panel data. The Club Properties Data tab has a Worksheet Type of RelatedData and lists the values for each contact for fields in the ClubProperties virtual table.

Enter the ID of the contact and any relevant information in the remaining columns.

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.For each user-defined table, create a RelatedData-type worksheet, with a
[Name of desired new table] data type, and add a line for each contact's records to be converted.
Each line must contain an ID that exists in one of the Contact-type worksheets. The Data Type column for each
RelatedDataDefinition/RelatedData pair must match exactly in order for the conversion to succeed, and the value in that
column will become the name of the virtual table. Note that no user-defined properties can be defined for an element that is a GUID.

The Medical Info Data tab is a worksheet that imports panel data. The Medical Info Data tab has a Worksheet Type of RelatedData and lists the values for each contact for fields in the MedicalInformation virtual table.

Enter the ID of the contact and any relevant information in the remaining columns.

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.For each user-defined table, create a RelatedData-type worksheet, with a
[Name of desired new table] data type, and add a line for each contact's records to be converted.
Each line must contain an ID that exists in one of the Contact-type worksheets. The Data Type column for each
RelatedDataDefinition/RelatedData pair must match exactly in order for the conversion to succeed, and the value in that
column will become the name of the virtual table. Note that no user-defined properties can be defined for an element that is a GUID.

A business object called LegacyActivity already exists in the standard iMIS database, and by entering data in this worksheet, data can be saved to that table.

Because the properties are already defined, you do not need a RelatedDataDefinition-type worksheet for LegacyActivity.

The Relationship Group Names tab is a worksheet that list contacts that have relationships to other contacts. The Relationship Group Names tab has a Worksheet Type of GroupNames and is similar to the Group Names tab.

Enter the name of each Relationship group along with the GroupClass, Description and GroupOwnerID.

The Relationship Group Member tab is a worksheet that imports the details of members that are connected to contacts. The Relationship Group Members tab has a Worksheet Type of GroupMemberships and is similar to the Group Memberships tab.

Enter the ID for each contacts that will own a Relationship group. Enter the GroupName from the Relationship Group Names tab. Enter the start and end date for each relationship and the role.

The Contact Attendance tab is a worksheet that imports the details of club attendance data. You can only import new records for club individuals and are not able to update existing attendance data.

The following columns are available in the Contact Attendance tab:

• Id - The contact's Id. The contact must already exist in iMIS in order to insert a new attendance record.
• AttendanceTypeCode - Should always be CLUB.
• IsCheckedIn - Enter True or False. This will indicate if the record that is being inserted for the individual is showing the contact as checked in or checked out. If this column is left blank, the value will be set to False.
• AttendanceDate - The attendance date and time. Enter the date and time in the following format:
  • 12/1/2019 8:15
  • 12/1/2019 15:51
• Description - The description assigned to the import in the Description column.
• OrganizationName - Enter the club associated with the individual. The row will not be submitted if the organization is not a valid club name.

The Program Attendance tab is a worksheet that imports the details of program occurrence data.

The following columns are available in the Program Attendance tab:

• Id - The contact's Id. The contact must already exist in iMIS in order to insert a new attendance record.
• AttendanceTypeCode - Should always be OCCURRENCE.
• IsCheckedIn - Enter True or False. This will indicate if the record that is being inserted for the individual is showing the contact as checked in or checked out of the program occurrence. If this column is left blank, the value is set to False.
• AttendanceDate - The attendance date and time. Enter the date and time in the following format:
  • 12/1/2019 8:15
  • 12/1/2019 15:51
• Description - The description assigned to the import in the Description column.
• OccurrenceKey – Enter the key associated with the program occurrence. To retrieve the occurrence key, do one of two things:
  • If you have access to the SQL database, query Occurrence table. Be sure the OccurrenceKey exists as an Occurrence in the database. The OccurrenceKey must be a GUID that matches the OccurrenceKey for an existing Occurrence in the system.
  • Create an IQA query and use the EventOccurrenceData business object to get a list of OccurrenceIds (OccurenceKeys).

A business object called SalesHistory already exists in the standard iMIS database, and by entering the data in the SalesHistory worksheet, data can be saved to that table.

Because the properties of this worksheet are already defined, you do not need a RelatedData-type worksheet for the Sales History worksheet.

📘 Note

The Sales History worksheet imports data for reporting purposes only. The conversion process does not create invoices and orders within iMIS for the imported contact.

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.