Transformations are a way to reformat data when importing or exporting data to/from Enroll using the Sync Service feature (example: a date stored in Enroll as 09/01/2007 should be exported to my SIS as 2007-09-01).
To perform these transformations you must have a Sync Connection created and configured.
Transformation Types
-
Date: Modify date format
- Today: Obtain the current date
-
Lower: Makes strings lowercase
-
Upper: Makes strings uppercase
-
Direct: Returns the value that it receives
-
Value Input: Enter a specific value
-
Join: Links a series of strings with a delimiter
-
Pick: Selects the first of a set of values that contains a non-empty value
-
Split: Divides text around one or more characters and puts a specified fragment into a field. (i.e., split an address from a single field into multiple fields.)
-
Substitute: Substitute one value or character for another
-
If Then Else: If a condition is true then a field is populated with a value. If it is not true it is populated with a different value
-
Phone Number: Transform a phone number format between (999) 999-9999 and 9999999999.
-
Enroll School Year: Populates the field with the current school year in Enroll.
-
Match Codes: Transform a value to a different value.
- Transliterate: Only allows for upper or lower case alphabetic characters, apostrophes, dashes, and spaces
Transformation Examples
Date Transformation
Transform a date from several different formats into whatever format you would like. (Enroll requires YYYY-MM-DD)
- In the “To SchoolMint Enroll Field” column, scroll down to the date field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping Type field.
- From the drop-down menu select Date transformation.
- Select the Source Data field data is pulled from (e.g., DOB).
- Select the Source Data Format.
- Select the Destination Date Format.
- Click Save.
- Reference the Mapped Value column to see what the final date will look like.
Supported Date & Time Transformations:
-
YYYY/MM/DD
-
MM-DD-YYYY
-
MM/DD/YYYY
-
DD-MM-YYYY
-
DD/MM/YYYY
-
YYYY-MM
The timestamp HH:MM:SS or THH:MM:SS can be added to any of the above formats.
Transform DOB Format from mm/dd/yyyy to yyyy-mm-dd
Import DOB data that is in the format mm/dd/yyyy (e.g., 09/01/2007) and transform it into the format yyyy-mm-dd (e.g., 2007-09-01). This transformation will nest three “split” transformations inside a “join” transformation. The “split” transformation breaks a value into pieces and the “join” takes the pieces and puts them back together.
From the Sync Service Login page:
- Click Configure Mapping on the connection you have set up.
- Select the Import Student Mapping tab.
- Open the Student Information section.
- In the “To SchoolMint Enroll Field” column, scroll down to the students.DOB field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
-
Add a “Join” transformation from the Transform & Send Data window.
- Open the drop-down menu and select Join.
- In the Delimiter field, enter a hyphen (-). (Hyphens are required in the date format in Enroll.)
-
Add a Split transformation.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select DOB.
- In the Separator field, enter a forward slash (/). (The forward slash “/” is part of the date format being imported.)
-
Enter 3 in the Index field. (Because this is the first split, this specifies that the 3rd value in the original date, in this case the year, is pulled out and placed in the first position, yyyy.)
-
Add a second Split transformation
- Click the Input button to add another input type.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select DOB.
- In the Separator field, enter a forward slash (/).
- Enter 1 in the Index field. (Because this is the second split, this specifies that the 1st value in the original date, in this case, the month, is pulled out and placed in the next, or second position, after the year, yyyy-mm.)
-
Add a third Split transformation
- Click the Input button to add another input type.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select DOB.
- In the Separator field, enter a forward slash (/).
- Enter 2 in the Index field. (Because this is the third split, this specifies that the 2nd value, in this case the day, is pulled out and placed in the next, or third position, after the month, yyyy-mm-dd.)
- Verify that the new date format to be output, shown at the top right section of the Transform & Send Data window, is correct.
- Click Save.
- Verify the date format to the right of the Students.DOB field.
- Click the “Send Student Record” button, and verify that the DOB date format looks correct.
DOB format transformation screenshot
Obtain the Current SIS Year Using the “Today” Transformation
This transformation obtains the current year for the students.sis_school_year field.
This transformation is useful because the school year is required in Enroll and this transformation pulls the year from the current system date.
From the Sync Service Login page:
- Click Configure Mapping on the connection you have set up.
- Select the Import Student Mapping tab.
- Open the Student Information section.
- In the “To SchoolMint Enroll Field” column, scroll down to the “students.sis_school_year” field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
- Add a Split transformation from the Transform & Send Data window.
-
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select Today. The Date Format field with the format YYYY-MM-DD is displayed.
- In the Separator field, enter a hyphen (-).
- Enter 1 in the Index field. (This specifies that the first value in the system date, which is the year, is used as today’s date.)
- Verify that the new date format to be output is correct. The year is displayed at the top right section of the Transform & Send Data window.
- Click Save
Today transformation screenshot
Split an Address into Multiple Fields when the Imported Address is in a Single Field
This transformation uses splits to pull out parts of an address that are in a single field separated by commas, (e.g., “123 Main St., Lafayette, LA, 70501”) and map them to the correct fields in Enroll.
From the Sync Service Login page:
- Click Configure Mapping on the connection you have set up.
- Select the Import Student Mapping tab.
- Open the Student Information section.
-
Add a Split transformation to grab the street address.
- In the “To SchoolMint Enroll Field” column, locate the “student_annual_address” field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select “address”.
- In the Separator field, enter a comma (,).
- Enter 1 in the Index field. (This specifies that the first value of the address, which is the street address (123 Main Street), is entered into the student_annual_address field.)
- The transformed address is displayed at the top right section of the Transform & Send Data window.
- Click Save
-
Add a Split transformation to grab the city.
- Locate the “student_annual_city” field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select “address”.
- In the Separator field, enter a comma (,).
- Enter 2 in the Index field. (This specifies that the second value of the address, which is the city (Lafayette), is entered into the student_annual_city field.)
- The city is displayed at the top right section of the Transform & Send Data window.
- Click Save
-
Add a Split transformation to grab the state.
- Locate the “student_annual_state” field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select “address”.
- In the Separator field, enter a comma (,).
- Enter 3 in the Index field. (This specifies that the third value of the address, which is the state(LA), is entered into the student_annual_state field.)
- The state is displayed at the top right section of the Transform & Send Data window.
- Click Save
-
Add a Split transformation to grab the zip code.
- Locate the “student_annual_zip” field.
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping type field.
- Open the drop-down menu and select Split.
- Open the Source Data drop-down menu, and select “address”.
- In the Separator field, enter a comma (,).
- Enter 4 in the Index field. (This specifies that the fourth value of the address, which is the zip code, is entered into the student_annual_zip field.)
- The zip code is displayed at the top right section of the Transform & Send Data window.
- Click Save
Concatenate Transformation (Brings together two or more fields)
This transformation brings two fields together and puts them into a single field.
(e.g., Doctor’s name needs to be exported as a single field. Enroll has the Doctor’s name in two fields, “doctor_first_name” and “doctor_last_name”.)
Within your Sync Connection on the Export Student Mapping tab:
- Click Add File and input your file settings.
- Click the Add Field button at the bottom of the page.
- In the “File Column Name” field enter the field to be exported. (e.g., doctor_name)
- Open the Mapping Type drop-down menu, and select Transformation.
- Click the Configure button to the right of the Mapping Type field.
-
Add the Concat Transformation.
- Open the drop-down menu and select Concat.
- Open the next drop-down menu, and select the first field to export, “doctor_first_name”.
-
Click the + Input button.
- Open the drop-down menu and select the transformation “Value Input”.
- Enter a space in the field. (This will put a space between the first and last name.)
-
Click the + Input button.
- Open the next drop-down menu, and select the field “doctor_last_name”.
- Click Save.
- Once you have all of the fields added click the Save button in the top right corner of the page to save your mappings.
- Click the send student record button to test exporting one student record.
To Import an address that is in Multiple Fields into a Single Field
This transformation uses Concatenation when the address is split into three separate fields HouseNumber, Street, Tag (e.g. Rd., St., Pl., etc.) but you want it imported and combined into a single field.
-
Open the Mapping Type drop-down menu, and select Transformation.
-
Click the Configure button to the right of the Mapping Type field.
-
Add the Concat Transformation.
-
Open the drop-down menu and select Concat.
-
Open the next drop-down menu, and select the first field to import, “HouseNumber”.
-
Click the + Input button.
-
Open the drop-down menu and select the transformation “Value Input”.
-
Enter a space in the field. (This will put a space between the HouseNumber and Street.)
-
-
Click the + Input button.
-
Open the next drop-down menu, and select the field “Street”.
-
Click Save.
-
-
Click the + Input button.
-
Open the drop-down menu and select the transformation “Value Input”.
-
Enter a space in the field. (Adds a space between Street and the Tag(e.g. Rd., St., Pl., etc.).)
-
-
Click the + Input button.
-
Open the next drop-down menu, and select the field “Tag”.
-
Click Save.
-
-
-
Once you have all of the fields added click the Save button in the top right corner of the page to save your mappings.
-
Click the “Send Student Record” button to test exporting the student record that is visible in the “Mapped Values” column.
Phone Number Format Transformation
Enables transforming a phone number format during import or export.
On the Field Mapping tab:
-
-
Locate the phone number field to be mapped (e.g. student_annual.guardian1_phone).
-
Open the Mapping Type drop-down menu, and select Transformation.
-
Click the Configure button to the right of the Mapping type field.
-
Open the drop-down menu and select Phone.
-
Open the “Source Phone” menu and select the phone number to transform.
-
Open the “Phone Format” menu and select the destination phone number format either (999) 999-9999 or 9999999999
- Click Save.
-
Value Input Transformation
The Value Input transformation allows you to enter a specific value instead of an imported value. (e.g., 2023 for the school year)
-
In the “To SchoolMint Enroll Field” column, scroll down to the “students.sis_school_year” field.
-
Open the Mapping Type drop-down menu, and select Transformation.
-
Click the Configure button to the right of the Mapping Type field.
-
Select “Value Input” from the drop-down menu.
-
Enter the specific data in the field, such as the year “2023”.
- Click Save.
Enroll School Year Transformation
Enters the current Enroll school year into a field such as “students.sis_school_year”.
-
Open the Mapping Type drop-down menu, and select Transformation.
-
Click the Configure button to the right of the Mapping Type field.
-
Select “Enroll School Year” from the drop-down menu.
-
Click Save.
Match Codes Transformation
Use the Match Codes transformation to change a value coming from the SIS to a different value going into Enroll.
-
Scroll down to the field you are mapping (“student_annual.grade”)
-
Open the Mapping Type drop-down menu, and select Match Codes (there must be fields selected in both of the From and To columns)
- Click Configure
4. Enter the value coming from the SIS (ex: -1)
5. Enter the value going into Enroll (ex: PK)
6. Click “+” to enter another set of values
7. Enter the next value coming from the SIS (ex: 0)
8. Enter the value going into Enroll (ex: K)
9. Continue until all of the incoming values from the SIS are mapped to the new value going into Enroll.
10. Click “Save Match Set”.
Comments
0 comments
Please sign in to leave a comment.