CSV Imports: Updating Existing Records

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

There are two ways to update existing records in Faculty Success, one is by using the Record ID and the other by Primary Key. For updates regularly sent from another source system, we recommend configuring a unique primary key for the screen. For areas where updates are less common or not from a different system, updates by Record ID may be performed. Keep in mind that a CSV import is always one activity screen per file.

 

Update 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 an Activity 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."
Screenshot

By default, only a small subset of screens have a primary key assigned. These include:

  • Yearly Data (Primary Key: Academic Year)
  • Workload Information (Primary Key: Academic Year)
  • Academic Advising (Primary Key: Academic Year)
  • Scheduled Teaching (Primary Key: Year, Term, Course Prefix, Course Number, Section)

How to Add or Update the Primary Key on a Screen

If you intend to regularly load or update data in a screen , you should ensure that the proper primary key is set. To do this, submit a screen revision work request indicating which field(s) should be utilized for the primary key. A user identifier is always considered part of the primary key.

Example: It's common for institutions to regularly import data to the Contracts, Grants and Sponsored Research screen from a source system that tracks grants on campus. If that source system has a unique Grant ID number, Faculty Success can create a corresponding Grant ID field and set that as the primary key.

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 existing records when using a Primary Key. The data items that should be included in your Export Raw Data report are the following which can be included in Step 3: Data to Include of your report's parameter:   

  • 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
  • Primary Key fields: The fields that make up your primary key can be selected by exploring the data items to find the fields and then checking the box in the field to include in your report. If you have customized screens or fields by unit, you will find these customized screens or fields separated from the Common Items.
  • Fields to update: Choose the fields for which you want to make changes.

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 data desired based on your report parameters, as well as the field codes for the fields your data is in as column headers.

ScheduledTeachingupdate.png

 

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)

 

Update by Record ID

It is best to update existing records that are part of your primary key, are within a Dynamic Sub-Answer (DSA) field or if you simply need to update specific field(s) using the 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.

The Record ID can be retrieved from an Export Raw Data Report and 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 based on 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 formatted the Record ID in Scientific Notation, which hides all of the unique identifying numbers that make up the record ID.

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. 

Once you've corrected the Excel behavior, you can begin to make any necessary changes for your update to the CSV, and we have found that the only columns that are necessary to be included in your CSV are:

  • 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. 
  • Record and DSA ID: A record's unique identifier which can be included from "System Details."
  • Primary Key Fields (if applicable): Fields that make up your primary key.
  • Fields to update: Choose the fields for which you want to make changes.

All other columns can be removed including the "SURVEY_ID."


AWARDandHONOR.png

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?
1 out of 2 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.