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.
Screenshot

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. 

Screenshot

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. 
    Screenshot
  • 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.
    Screenshot

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. 

Screenshot

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.

Note: If attempting to update an existing records primary key values, the system-generated record ID will also be required as a column in the import. (See Below)

 

Example 2: Updating by Record ID

Note: Updating by Record ID is only applicable if the screen does not use a primary key, or if you need to update the values stored in the primary key

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.  

Screenshot

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.

Screenshot

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. 

Note: The column "SURVEY_ID" can be removed. 

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.

Was this article helpful?
0 out of 0 found this helpful

Articles in this section

How to Contact Support
There are many ways to reach out! Click here for our support options.
Watermark Academy
Click to access the Watermark Academy for consultation, training, and implementation companion courses.