Updating the Organizations Dataset

The Organizations Dataset Updates query in the Standard Query Library compares the standard Technolutions Organization list against the existing Organization dataset in the partner database. The standard list is updated annually by Technolutions based on an amalgamation of data from The College Board and The Common Application. In addition, Technolutions uses USPS address cleansing software to clean up the standard Organization dataset list. Partner schools may choose to not update their database, update their database Organization records with the Technolutions Standard Organization dataset list, or a combination of both. Any updates are entirely up to the partner institution.

Downloading the Proposed Changes
  1. Select Database on the top navigation bar and select Standard Query Library.
  2. From the list, select Organization Dataset Update.
  3. Select Download to Excel to download the proposed changes.
Reviewing the Proposed Changes

The columns included in the spreadsheet results are:

  • Type: The type of update proposed:
    • Delete: Records with this type are those that are currently in your Organizations Dataset that were not included in the updated list. These may include international and domestic institutions that do not have an assigned CEEB code that must be manually created.
    • Update: Records with this type are in both your current Organizations dataset and the updated list.
    • Insert: Records with this type do not exist yet in your Organizations dataset.
  • Key: The CEEB code of the record.
  • Local Name: The current name of the record. For insert types, this is blank, since the record does not yet exist in your database.
  • Shared Name: The proposed name for the record. For delete types, this is blank, since the record does not exist in the updated list.
  • StreetCityRegionPostalCountry: The proposed address for the record.
  • Category: The proposed institution category of the record (High School or College).
  • Type (this appears as Type 2 when the file is uploaded to Slate): The proposed type of institution (Charter, Home School, Independent, Public, or Religious).
  • # Records with School Key in Database
  • # Active Applications with School Key in Database

 Tip

While accurate, the USPS address cleansing process may result in the standard Organization Dataset Updates query to appear inconsistent with partner school datasets. Partner institutions have the choice to select the changes from the standard Organization Datasets Updates Query or to keep the the data as it exists in the database.

Remove any records from the downloaded Organizations Dataset Updates spreadsheet that should not be added, updated, or deleted from the database. Any changes are made at the discretion of each Partner institution. Manual adjustments can be made to the data in the spreadsheet, so as not to overwrite data. Some records associated with an event or person school record can be marked for deletion in the spreadsheet. Review the proposed changes carefully to avoid unintended deletion of data.

The import will match on the CEEB code, and the potential updates to records with the update type include:

  • Name
  • Address
  • Category
  • Type

Important

Pay close attention to the Deletes and Updates. Partner institutions may have records in the Organizations dataset that were created manually, including organizations that do not have an assigned CEEB code or records that function as an organization that are used only as a location for events or a container for contacts. Remove any records from the spreadsheet that you do not wish to update or delete.

 Tip

Use more than one spreadsheet to review the data. There are thousands of records to review, so consider using at least one spreadsheet for each type of update. For example, one spreadsheet for records to delete, another spreadsheet for records to insert, and another to handle updates. When the review for each type is complete, process only those updates.

Processing the Proposed Changes in Slate
  1. Select Database on the top navigation bar and select Source Format Library.
  2. Add the Organizations Dataset Updates source format (confirm that the Dataset is scoped to Organizations) and select Save.
  3. Select Database on the top navigation bar and select Format Definitions.
  4. Select the Remap link for the Organizations Dataset Updates (Excel) source format.

    The source fields are pre-mapped.

 Tip

Until a file has been uploaded using this source format, you will not see any source fields on this page. Partners may wish to upload a copy of the spreadsheet with everything but the header row removed to allow making changes to the mappings prior to uploading a file with real data.

  What is Custom: Delete Entire Record?

This destination tells Slate to delete the dataset record entirely when the source data in the file is mapped to Delete entire record including fields, materials, tags, and interactions. Be sure to only upload dataset record data with the type Delete if the record should be deleted permanently.

  1. Within Format Definitions, navigate to Value Mappings.
  2. Map the Category and Type 2 source data to prompts in Slate.
  3. Refresh the Test Environment so that the import can be tested without affecting production data. Note: Refreshing a test environment will permanently delete all data and configurations in the current test environment and will replace the test environment with a copy of the current production environment.
  4. In the Test Environment, navigate to the Source Formats page and update the Remap Active flag to Active.
  5. Once the data has successfully imported, confirm that the intended updates made were successful.
    • If there were unintended changes, update the spreadsheet(s) or the mappings in production, then repeat steps 7 - 9 until the changes are correct.
    • When completely satisfied with the updates, perform steps 8 and 9 in production environment.
Custom Fields for Matching
The key to the Organization records in Slate is CEEB code. In general, dataset records can only have one dataset row key. No additional keys can be added however you can use other custom fields for matching.
Federal Interagency Committee on Education (FICE) Codes
If you have a file with only FICE codes and are trying to match on Organization records in Slate without the CEEB code, you will need to create a crosswalk between FICE and CEEB before importing into Slate. In this case, we encourage creating a new custom unique-for-merging field. Unique-for-merging fields will either match an existing record or create a new record from data entry or import. Also, setting fields as unique-for-merging will cause Slate to exclude null values when attempting to match values in these fields.

To add the FICE code to the organization's records, you will need a spreadsheet or data file with both the CEEB and the FICE code. You will then map the CEEB code to the Record Key (CEEB) field and the FICE code to the new custom field. The CEEB will then match the Organization record, and the FICE code will be added. Following that, uploads containing only a FICE code can be imported and associated with Organization records. However, note that Technolutions does not maintain a list of CEEB to FICE codes. There is a Community Forum post where Slate users have posted lists of school FICE and CEEB codes.

Was this article helpful?
2 out of 10 found this helpful