Finalsite Open Integration

Integration method

Overview

Finalsite Open integrations are a way to integrate data that is exported from another database such as an SIS or MIS system that Finalsite does not offer direct integration with. This includes constituent data, group data, and roster data. For supported fields, you can reference the data upload templates (see Upload constituent data to Constituent Manager).

In the deployment of the integration, a Finalsite deployment specialist will ensure that the data in your exports is mapped into Finalsite appropriately and also work to set up the means to pass your datasheets to our servers, securely.

As long as the generation of the exports from the source system can be automated, and one of the available methods to send the datasheets to Finalsite is viable for your organization, this integration can be fully automated.

Deployment steps

1. Kickoff discussion to:

• Review the desired data to be integrated (e.g., what roles are we populating).

• Ensure any questions or concerns about the process are discussed.

• Determine the datasheets and specific datapoints to be used in the integration.

• Understand how the datasheets will be delivered to Finalsite’s servers.

• Generate sample exports from the source system, ensuring it meets the requirements detailed in the Data Requirements section below.
• Finalsite will configure the mapping and process the data (either in a clone site or your live site, depending on the situation).
• Review and adjust the export/integration mapping as needed.
• Pre-deployment work (as prudent based on the specifics of the situation).
• Deploy integration on live site.
• Schedule datasheets (see Sending datasheets to Finalsite).
• Done!

Sending datasheets to Finalsite

There are several options for getting the datasheets generated to Finalsite’s servers, including:

1. A Java utility provided by Finalsite (see the Upload Utility section below).

2. An SFTP location that Finalsite has access to can be used, and the data will be “pulled” from that location on a scheduled basis.

3. Files can be delivered ("pushed") to Finalsite via SFTP.

Sync frequency

Integrations can be scheduled to run hourly, within Finalsite. However, the true frequency will be determined by the updating of the datasheets themselves, and their delivery to our servers.

If using the Finalsite Upload Utility, you will want to upload the updated datasheets on a schedule that makes sense for how frequently your export is generated. Delivery of those files is contingent upon that schedule. The integration will process the most recently updated datasheet each hourly run.

If Finalsite is “pulling” the datasheet from an SFTP location, the data will be retrieved once an hour on a scheduled basis. This retrieval of the data is not tied to the integration processing the data.

Data requirements

1. The supported file formats are: .xls, .xlsx, .txt (tab-delimited), .csv, .tsv.

• The recommended format is .csv.

• If using .xls or .xlsx, the files must not have rich formatting.

• Each datasheet must be named consistently.

• Each individual record, (a student/parent/etc.) must have a unique, persistent ID (ImportID).

• The left-most column in the datasheet must have a populated value. We recommend using this for the ImportID value, if possible.

• Column headers should not have rich formatting, or contain non-alphanumeric characters (spaces, underscores, and hyphens are acceptable). There cannot be empty column headers in the middle of populated columns.

• There should not be empty rows of data in the middle of the files. If there are more than 5 consecutive empty rows, the datasheet will be determined to be complete and processing of the datasheet will cease.

• Date fields must be formatted consistently (e.g. all dates should be mm/dd/yyyy or dd/mm/yyyy).

• Datasheets should be UTF 8 encoded (without BOM).

• The contact portion of the export should be arranged so that each column contains a specific “type” (for example, home phone number).

• A column may contain one type of data only. (For example, if column A contains the home phone number for the first record, it may not contain the business phone number for the next record.)

• Data columns need to remain consistent once deployment of the integration occurs. Removing or renaming a column will cause the integration to halt processing until that change is accounted for in the configuration of the integration.

• Departments and locations are supported for faculty, as well as the option to remove users from locations/departments not present in the data. However, these must be consistently named. Ideally, the location/department name would be an exact match for the corresponding group in Finalsite, but we can do some translation, if necessary, as long as guidance is provided for the desired translation.

• Data files should not be further modified or manipulated after exporting them from the SIS. This means no changes to column headers or manual edits to the data of any kind are allowed once the file has been exported.

• Username support must be decided on a case-by-case basis. We can auto-populate the username data based on the raw data (for example, by concatenating the constituent’s first and last names). Alternatively, you can provide constituent usernames in the export files.

• Password synchronization through the integration is not supported. Finalsite recommends allowing users to set their passwords on their initial login via “Forgot Password” functionality.

Upload Utility

The Upload Utility that Finalsite can provide you with is a simple Java based utility that can be configured to send files via https to Finalsite. This needs to reside in a directory with a subdirectory containing your files. That subdirectory should be named in accordance with the service type of the integration (e.g. finalsiteopen). Your deployment specialist will tell you the correct name when configuring the integration with you.

This tool can be run manually to verify it is working as expected, and to fully automate the integration, you can schedule that to run on the system where the exports are generated. How to schedule that may vary based on the operating system, but guides can typically be found online.