The Data Map you download from VerticalChange will be an xlsx file. The hierarchy of the Data Map Template designer can be seen in the Data Map. Each contact or relationship field you added in the template designer will create a merged cell in row 1. Everything under that merged cell will apply to the contact or relationship identified therein. In the representation of the structure below, CST 1, CST 2, Form 1, and Program Enrollment 1 will all be created or modified for Contact 1; the relationship created by Relationship 1 need not have anything to do with Contact 1.

The row-two subheader indicates more specific information about which of the fields in the Data Map Template the columns below it correspond to. If you are importing a form, (program) enrollment, staff assignment, or an individual or group CST, those will be specified in row 2. Programs are referred to by an ID number. If you have multiple program enrollments in a Data Map, it may be necessary to return to your Data Map Template to see which program slug corresponds to which program ID number.

The row-three subheader specifies the column’s datum by field number and field name. This is where you figure out which data goes into which column. The row-three subheader is formatted as follows: c[contact #]/[form template or CST id #]/[field id #]_[slug_or_field_name_with_spaces_and_some_characters_replaced_with_underscores]/[optional_additional_information_for_fields_using_multiple_columns]. So, reading from left to right, the row-three subheader “c1/1147/48839_site_s_physical_address_/street” indicates that this column is associated with the first contact in the Data Map (i.e. it is under a ‘Contact 1’ row-one header), that it is associated with the form template or CST with the id number 1147 (i.e. it is under a row-two subheader with that form template or CST’s name), that it imports into field 48839, that field 48839 is named “site’s physical address:”, and that it is specifically the street component of the address.

Columns in row 3 are organized alphabetically. You may notice that columns often appear in the order you see them in the form. This occurs because field numbers are an index assigned at the time of creation in the form designer and are at the beginning of the row-three header. Thus, a byproduct of the alphabetization is that columns will be ordered from the oldest field in the form to the newest (when a form template is saved, any new fields are numbered in ascending order from top to bottom). Therefore, in many form templates this ordering will mimic the data entry flow of the form (i.e. the topmost field in the form will be the left-most column in the Data Map), but in form templates with fields that were rearranged after the form was created, or in which new fields were added after the form’s initial creation, this relationship will not be preserved.

When you enter data into the template, keep in mind that each row is processed separately during the import process. Each row of data entered into a Form, Staff, Enrollment, or ContactSubType section in the Data Map Template will create (or modify - if an existing external ID or Unique ID is entered in the section) a form result, staff assignment, program enrollment, or ContactSubType respectively.

Before importing, it will be necessary to save your Data Map as a CSV and upload that into VerticalChange. When you convert from an xlsx to a csv, the merged cells in the row 1 and 2 headers will be reduced to single cells at the left-most side of their original range. It is much easier to tell which headers and subheaders a field falls under before this reduction happens, so saving as a CSV should generally be your last step. If you deleted columns, ensure that all of your row-one and row-two headers made it into the CSV before uploading. Sometimes deleting sections of a merged cell in Excel can delete the contents of the merged cell.

If your software allows it, save as a UTF-8 CSV to avoid problems with non-standard characters and differences in character encoding between systems.

VerticalChange allows you to import external ID (EID) fields for group and individual contacts and for specific form results and refer to those EIDs in future imports. This section of the guide will discuss their use in more detail.

When creating a contact or form-result-level external ID, do not use a simple number - use something descriptive that will not be duplicated even in subsequent imports into a different form template. There isn’t a one-size-fits all solution here, but it is important to think about future sources of duplication when designing a formula for your external IDs. In simple cases, it might be as simple as concatenating “[VC form name] + [source data record ID number]”. But if you believe there is a possibility that the source data ID number will have to be attached to multiple contacts in VerticalChange then you may need to concatenate “[VC form name] + [source data record ID number] + [source data individual ID number]” in order to ensure unique values.

If you feel any doubt about your process for creating external IDs, please reach out to VerticalChange for support.

Every CST or form you add to a Data Map Template will have an external ID field in the Data Map. They function much like Unique ID fields in that they can be used to identify records in subsequent imports. You can assign an external ID to a group or individual, a form result, enrollment in a session, or even a specific day’s attendance. Subsequent imports will update the data associated with that external ID or create a new result if the external ID cannot be found - just like they would if you left the UID field blank.

You can also enter external IDs into form fields intended to link two contacts (i.e. relationship or other contact fields embedded in forms).

Every CST, enrollment, form, and staff assignment field you add to a template will have its own UID field. If you find yourself looking at a row-three subheader labeled ‘uid_unique_id’ and asking yourself ‘UID for what?’ look at the row 2 header. In the pictured Data Map below, column C contains the UID that corresponds to group common fields (i.e. the contact’s UID). Column G contains the UID corresponding to the enrollment - that is the UID of the contact’s relationship to the program.

If you leave a Unique ID field blank and do not specify an external ID for a given row-two subheader-designated section of the import (e.g. a specific form) the importer will generally assume that you intend to create something new (e.g. create a new form result for a given contact). The only exception to this is that you can delete program enrollments by importing an end date without including the enrollment’s UID. If you populate a UID field or an external ID field, the importer will search for that UID/external ID and, if it exists, overwrite the data it finds there with the data you have entered into the Data Map under that row-two subheading.

If you have included an incorrect UID (for example a contact UID in a form result UID field) the import will not create new data but may still indicate that the import was a success.

Program_id fields (generally found immediately to the left of form UID fields in the downloaded Data Map) are required for importing form results. You will not be able to create a form result without specifying the program UID. If you specify a program UID that is not associated with that form template in the Forms Module, the import will succeed and will create the form results, but the results may not appear in the report builder or on the group or individual’s profile. You can find the program ID within the programs module, located on the programs tab in the left-hand sidebar. Select the program you want the form results associated with and copy the UID at the end of the URL (pictured below).

When entering phone numbers, separate extensions with a comma. The importer will assume that anything after a comma is an extension. Do not use more than one comma; everything after the second comma will be omitted. If you import 1234567890,1234 into VerticalChange, it will import as (123) 456-7890 ext. 1234.

Beware of spreadsheet software’s default number formatting. If you are not careful, Excel may assume that you meant 12,345,678,901,234, change your data silently, and the result of the import in VerticalChange will be as pictured.

The most certain way to avoid this problem is to format your phone numbers as follows: (123) 456-7890,1234. This format is especially important if you don’t have the ability to save as a CSV UTF-8.

Multiple select fields will be split up into a column per response option. Enter a 1 into any column you want the response option selected for and leave the rest blank. In the stylized representation below, options A and C will be checked in Field 2.

The importer can be used to fill in relationship or other contact fields. Assuming that the field does not have the “Restrict to one contact” box checked in the form designer, you can include multiple related contacts by separating UIDs or external IDs with a “,”. Don’t include a space after the comma.

Note: this refers to relationship/other contact fields in forms, not relationships added through the import template designer.

When importing into fields with specific response options and only one column in the Data Map, this includes single select fields (also known as radio button fields) and Likert fields, the data entered must match the response options exactly. Non-matching response options may lead to blanks in the imported data. If a field allows you to enter text data associated with a response option, that will appear as a separate column ending with “/tc”. For the example pictured right, there are five response options; “Other” allows additional text entry. In the Data Map section for this field pictured below, column X’s row-three subheader indicates that this column contains the responses for field 97569 - Guardian A Relationship to Child. The entries in this column MUST be one of the five responses in the image to the right. Column Y is where you would put the text data someone might enter for an “Other” response.

NOTE: When reporting a field with a text entry field like the one picture above in VerticalChange, the option selected will be separated from the additional text by a colon (:). If you plan to re-import response data from VerticalChange using the importer V2, the process of separating additional information from responses will be easier if you do not include a colon in any of the response options when designing the form in the Forms Module.

As of this writing, the importer will create or update Likert response values, but does not calculate the score assigned to those values.

Addresses in the Data Map will have two sets of redundant columns. The first, simply labeled ‘Address’ should be left blank. Separate the components of the address into the breakdown columns (e.g. Address_Street, Address_City) rather than using the column simply labeled Address as the ‘Address’ column is prone to errors.

You may enroll a group or individual in a program by specifying a start date for the enrollment (leaving end date blank), disenroll a group or individual by specifying an end date (you may leave start date blank), or record a past enrollment for a group or individual by specifying both a start and an end date. If you specify a start or end date for a group or individual who is already enrolled in that program, the import will update the existing date(s), replacing the values in the system with the values you specify.

When importing a group membership, add a relationship to the data map template. Then put the UID or external ID of the contact you are adding into the column with the row-3 subheading “r1/contact_id”. Put the group UID or external ID in the column with a row-3 subheading of “r1/relation_id”. If you are importing a relationship type (e.g. “Son” for a family) enter that into the column with a row-3 subheading of “r1/relation_relationship_type”.

NOTE: The column names above will be different if you have included multiple relationships in your data map template. The columns have row-3 subheadings with the relationship number - so replace 1 with 2 for the second relationship.

You may delete any column from a downloaded Data Map file with the following exceptions:

When deleting columns, make sure that all remaining row-three subheaders are still under their original row-one and row-two headers and that you have not accidentally deleted the contents of either the row-one or row-two header.

You may move any column so long as all row-three subheaders remain under the same set of row one and row-two headers and the row one and row-two headers remain in the same order. In the example below, the order of the row-three subheaders in columns J, K, and L are irrelevant. Fields 1, 2, and 3 could just as easily be ordered 3, 2, 1 or 2, 1, 3. However, switching the row-three subheaders in columns J and I will cause the import to fail because column J needs to be under the Enrollment 1 row-two subheader and column I needs to be under the Form 1 row-two subheader. Likewise, the import would fail if columns G, H, and I were cut and pasted to the right of Enrollment 1 (i.e. if Enrollment 1 and Form 1 switched places) because the order of the row-two subheadings must be maintained.