Populate constituent datasheet templates

This article explains how to complete the datasheet templates for faculty, students and parents, and alumni, which you can then upload to Constituent Manager. Each datasheet is slightly different, so instructions are provided separately for faculty, students and parents, and alumni.

Only one role can be uploaded at a time, so begin by opening the datasheet for the role you wish to upload in your favorite spreadsheet software, such as Microsoft Excel.

For each of the datasheets, note the specific instructions contained in the top rows of the spreadsheet, as well as in comments on the column headers and the “Instructions” worksheet at the beginning of the file.

How to fill out the template for faculty

The first column of the faculty, student, and alumni datasheets is the ImportID. (Columns K and AK are the Import IDs for the parent datasheet.) ImportIDs can be any combination of letters and numbers, as long as they are unique to each constituent.

The Username and Password columns will be used to generate portal logins for constituents. Usernames must be unique across all roles (e.g., you cannot have one “jsmith” in the Parent role and another “jsmith” in the Faculty/Staff role.) For some guidance about how to create usernames and passwords, check out the article in our Knowledge Base called Creating Usernames and Passwords.

The rest of the columns in the faculty datasheet correspond to profile fields in Constituent Manager. Take a look at the profile fields for the role in Constituent Manager > Settings > Constituent Roles > Faculty & Staff > Profile Fields. Compare these fields to the column headings on the spreadsheet. You may delete any columns that you won’t be using. If there are fields in your profile that aren’t included on the datasheet, you can make a new column for them. Click on the edit pencil next to the profile field to find and  copy the “Field Name” to use as the new column’s header.

After the portal login fields, you’ll see columns for Location and Department. Completing these columns will create corresponding groups for the constituents in Group Manager, where you can manage the membership later. Be careful that the data in these columns is correct and consistent, so that you don’t create two different groups for “Art Department” and “Arts Department.” Faculty constituents can be members of up to three Locations and up to three Departments. They can also have up to five titles.

The next columns on the datasheet are for email addresses. Constituents can have up to four email addresses in their profiles: one each for home, school, work, and a generic email. You’ll notice a fifth email column, however; this is to designate which of the other four email addresses is the “primary email.” (The primary email address is largely used to determine where bulk messages from the eNotify module will be sent.) Copy the email address from one of the other columns into the email_primary column to set this value in the constituent profile.

The next columns to take note of are “Image_S” (for small) and “Image_L” (for large). To add faculty photos to your profiles, upload the image files to File Manager and take note of their file paths. Copy the relative file path for each photo (the part of each URL that starts after “/uploaded/”) into these fields.

How to fill out the student and parent datasheet

The student and parent datasheet has three worksheets to it instead of one: Students, Household-Parents, and Relationships.

Student datasheet

Most of the columns on the student worksheet will look familiar from the faculty datasheet, with one difference: There are two fields for “HomeroomTeacherID” and “AdvisorID.” If used, these should be filled out using the ImportIDs from each teacher and advisor’s constituent profiles in order to link them correctly.

Household-Parent datasheet

The next worksheet is for Household-Parents. Each row of the spreadsheet is for a household, which means it has fields for both a primary parent and their spouse.

Each household has a household ID, a household name, an address, and a salutation (such as Mr. and Mrs. Smith). Following these fields, you’ll have the columns for the primary parent’s information, and then for the spouse’s information. The spouse columns begin with “sp_.”

Relationships Datasheet

You’ll notice that the Household datasheet doesn’t include any information about the children. This is where the third worksheet come in, the “Relationships” sheet. This worksheet will define the relationships between the students on the first worksheet and the parents on the second worksheet.

Enter the student's ImportID in column A of the Relationships worksheet. Enter the ImportID for the parent that lives with the student in column B. The student will be added to this parent's household. Enter that parent's relationship to the student (e.g., Mother, Father) in column C. If the first parent has a spouse in the same household, enter that parent's relationship to the student (e.g., Father, Step-Mother) in column D.

If the student has a secondary household, enter the import ID of the first parent in that household in column E. Enter that parent's relationship to the student in column F and their spouse's relationship to the student in column G.

If necessary, complete columns H through J for additional parents.

How to fill out the alumni datasheet

The “Alumni” worksheet of the alumni datasheet looks like the student datasheet, except that it includes household information in columns L through T. In addition, there is an optional “status code” field indicating whether an alumnus is deceased (D) or lost (L).

To create portals for each alumni class, use the “Class Groups” worksheet. Complete the year and name of each class for which you want to make a group space. Optionally, assign up to two moderators to act as admins of the group space, using their Import ID.

How to save a datasheet for upload

Once you’ve finished populating any of these datasheets, you’re ready to save it for upload.

First, delete the instructions rows so that the first row contains the column headers. Then, export each page of the datasheet as a tab-delimited text file, or “Unicode” text format file.

After the datasheet is saved as a .txt file, it’s time to upload it to Constituent Manager. Refer to the article Upload constituent profiles to Constituent Manager or the video “Uploading Constituent Data to Constituent Manager” for how to complete this process.