CSV Imports: Updating Yearly Data

Access to this product feature is dependent on your institution's Watermark agreement
Base ☑ | Faculty Accomplishments ☑ | Reviews, Promotion and Tenure ☐ | Web Profiles ☐

Throughout a user's academic career, a unit change or addition of responsibilities may occur. Faculty Success stores this information at a high level on the Yearly Data screen, which determines how users are included in reports or assigned in the Workflow (Reviews, Promotion, and Tenure) module. A user's appointment information can be changed manually using the Manage Data utility if the field is not locked due to access restrictions. If the appointment fields are locked, you can update their department via CSV import using one of the two methods, the Primary Key or Record ID.

This article goes into detail about how to update a users existing Department using the Primary Key and Record ID.

Example 1: Updating by Primary Key 

Primary keys are made up of a set of fields and are used to ensure that each record on a screen is distinct from other records for a given user, reducing the possibility of creating duplicate data or modifying the incorrect record. For more information on how Primary Keys are used to identify, add, delete, and modify records, see this article. 

How to Find the Primary Keys for the Yearly Data Screen

Download the Configuration Report for your Institution located under Tools > Home > Click the "Download" hyperlink within the Your Configuration area. The report will then be automatically downloaded.

The first page of the Configuration Report will be the Overview page which will show the Primary Key(s) for each screen in the column with the header "Primary Key." Look for primary key for the screen with the code 'ADMIN." The field corresponding to the primary key can be found by navigating to the screen from the tabs and searching for the primary key code there to view the corresponding field.

The ADMIN screen in the example below has the Academic Year field as its primary key. However, depending on your institution's configuration, the Department and College fields may also be part of the primary key on the Admin screen. 

Creating a CSV with a Primary Key

It is recommended to run an Export Raw Data report to retrieve a CSV file that will contain the following necessary information to update an existing department. The report parameters can be modified as follows.

Step 1: Date Range

The date range entered in this step should correspond to the academic year for which you want to update the user's departmental assignment.

Step 2: Whom to Include 

Choose the users whose departments you want to update by Group or Individual.

Step 3: Data to Include 

In this step, we will only select the data items required for our report, such as the user identifier, fields that make up your primary key, and the department field.

• User Identifier: This item can be included in your report by selecting it from the item "System Details" and then selecting the User ID and Secondary ID(s) boxes. The User ID is an ID assigned to the user by the application, and the Secondary ID(s) are the user's username. 
  
• Fields to include: The fields that make up your primary key and the Department field can be selected by going to Common Items and navigating to Yearly Data. Expand the Yearly Data item to view and select the fields that comprise your primary key and the Department field so that they can be included in your report.
  

After retrieving the CSV file from the Export Raw Data report, you can begin making the necessary changes to the file for your import. The provided CSV file will look like the following, displaying the Primary Key as well as the department field that we will then be updating. 

After the existing department data in the CSV file has been updated, we will save our CSV file and continue to import it using the Data Import utility. Refer to this article for an overview of how to perform a Data Import using your CSV file.

Example 2: Updating by Record ID

The Record ID is a number generated automatically by the application when record is created, serving as a unique identifier for each record and useful to use in an import so that the system knows which record to specifically update. Similarly to the first example, the Record ID can be retrieved from an Export Raw Data Report. We use the same report parameters from the first example and include the Record IDs by selecting "Record or DSA IDs" from Step 3: Data to Include of your report's parameter within System Details.  

When you open the CSV file generated by running the Export Raw Data report, the fields and users from your report parameter selections will be displayed, and each record will have a Record ID that can be found in the "ID" column. Here you will find an Excel behavior that formats the Record ID in Scientific Notation, which hides all of the unique identifying numbers that comprise the Record ID. Fields within Dynamic Sub-Answer (DSA) fields will also have their own unique Record ID, as shown below.

This is an Excel behavior that can be easily corrected by changing the format to Number, which will display all of the numbers of our record ID so that the record can be located to update. The steps for changing the format can be found in this article,  Helpful Excel Formulas and Tips to Format CSV Data Imports. 

Once you've corrected the Excel behavior, you can start making the necessary changes for the user Department and then save your CSV. This modified CSV can then be used for your Data Import. For an overview of how to perform a Data Import using your CSV file, see this article.

Keep in mind that once you save your file, it is best practice to not reopen it because it will trigger the excel behavior again, and the Record ID column format will be reverted to scientific notation.