In order to retrieve and sync data between your Synergetic Student Information System (SIS) and Reach, we need to establish an ODBC connection to your SIS, execute a pre-set SQL statement, prepare that data as a JSON payload and securely transmit that payload to Reach for processing.

The steps work as follows:

1. Review a boilerplate SQL statement that Reach provides for your Synergetic SIS
   

2. Either install the SQL statement as a view within your Synergetic database or embed the SQL statement within the Perl script in order for it to be executed
   

3. Create a System DSN on the target server that will run the script in order for the Perl script to connect to the ODBC data source
   

4. Update the Perl script with the correct details in order for it to connect and retrieve the appropriate data
   

5. Run the Perl script in DRY RUN mode to export the data into CSV format for vetting
   

6. Run the Perl script in LIVE mode and confirm the Reach portal has received the update Reviewing the Boilerplate SQL statement
   

Reach as template SQL statements that work with most major Student Information Systems.  The boilerplate SQL statement is designed to get just the basic contact and relationship details from your SIS only.  If you’re looking to pass additional information then it will need to be done as a separate sync as this sync endpoint will not handle the additional data.

SQL View or Embedded

It’s completely up to you how we handle the SQL after it’s been structured correctly.  Most schools are happy to create a SQL user account, install this SQL as a view and lock down access to the sync script to just this view, but if you’re unable to provide this, we can simply embed the SQL within the Perl script itself.

System DSN

In order to connect we require an ODBC connection called a System Data Source Name which allows you to enter the Server, Username, Password and Default Database that the Perl script will only ever be able to access.  This is done through Control Panel > Administrative Tasks > ODBC Connections.

Updating the Perl Script 

In a decent text editor, you can open the Perl script and look at lines 9 through 16 as the places where you’ll directly configure variables and lines 160 through 164 as the place where you’ll update the POST variables. 

• Line 9: Portal URL - This is your Reach portal that you are sending data to 
  

• Line 10: DSN - This is the DSN Name, User ID and Password you created for Perl to access 
  

• Line 16: SQL Statement - This will either be a select * from view that you created in your database or the FULL embedded SQL statement
  

If you’re embedding the SQL statement be aware of your QUOTES as this will cause Syntax Errors if you don’t escape them correctly.

Post Variables 

Below are the descriptions of the potential post variables that you can send: 

• data - This is a JSON Array as a String URI Component Encoded 
  

• hostsIncluded = [true|false] Are hosts included in the JSON Data String? If so, we will get rid of any Hosts that are NOT in the data you provide. 
  

• exclusionList = [] Array of PK's that you wish to remain even if you're not passing their data in the JSON Data String (usually just an empty array) 
  

• defaultNotifyValue = [1|0] 1 = Set Notify to Yes; 0 = Set Notify to No; For NEW contacts only. Existing contacts remain untouched 
  

• mi = [0|1] This is a Mapping Item flag to determine the data structure of your JSON Data String. 0 = Boarder, Contact 1 and Contact 2; 0 = Boarder and Contact 
  

• dsp = [0|1] This is a flag for Do Second Parse of the data. The second parse handles the deletion of data so if you DON'T want the sync to attempt to delete people, set this to 0, otherwise it will be set to 1 by default.
  

Running in DRY RUN mode 

• Line 13 in the script has a variable called $dryRun and by default it is set to 1 (true) which means if you accidentally run the script before updating everything correctly it will just output your data to CSV. 
  

• Setting this value to 1 will ensure that all output comes to the screen locally rather than pushing it live to the server. 
  

• Use this variable to ensure that you have your data coming through correctly. You can then pipe the output of this script to a CSV file and confirm. 
  

Running LIVE 

After you’ve configured and vetted the data you can set the $dryRun variable to 0 (false) and run the script. It will then connect to the Reach data centre and update your data as below.

Data Sync Workflow 

In order to allow a single posting of data to handle inserts, updates and deletes the sync endpoint works as follows: 

1. Any contact provided that has a Primary Key that we cannot find during lookup will result in an INSERT 
   

2. Any contact provided that has a Primary Key that we can find during lookup will result in an UPDATE 
   

3. Any contact provided that has a Primary Key that we can find during lookup AND is currently set as deleted will be reinstated (ie. undeleted) and an UPDATE will occur 
   

4. After all data provided is processed a reverse process (DSP variable for Do Second Parse) will run through all contacts in Reach and compare them against the data you have provided. Anyone in Reach that no longer exists in your data will be flagged as deleted. 
   

If you are sending through Boarders and Parents only then we would expect that you want to keep your Hosts, therefore the hostsIncluded POST flag should be set to true in which case the Do Second Parse phase will only delete Boarders and Parents. If you wish to prevent the Second Parse completely you can simply send dsp = 0 as a POST variable and no data will ever be deleted.

Mapping Items 

The SyncData endpoint accepts data in two formats. The original format respected the original data import template of Boarder, Contact 1 and Contact 2 for each row and would allow multiple Boarder rows for additional contacts. The new format allows for a more efficient Boarder to Contact relationship so you are generating a 1:1 mapping instead of a 1:2 mapping. 

Original JSON Format 

{ 

"bpk": "PRIMARY KEY", 

"bfname": "Boarder First Name", 

"blname": "Boarder Surname", 

"bpname": "Boarder Preferred Name", 

"badd1": "Boarder Address 1", 

"badd2": "Boarder Address 2", 

"bsub": "Boarder Suburb", 

"bstate": "Boarder State", 

"bpcode": "Boarder Post Code/ZIP Code",

"bcountry": " Boarder Country", 

"bhphone": "Boarder Home Phone", 

"bwphone": "Boarder Work Phone", 

"bmobile": "Boarder Mobile/Cell Phone", 

"bemail": "Boarder Email", 

"bdob": "Boarder DOB (yyyy-mm-dd format ONLY)", 

"bhouse": "Boarder House/Dorm", 

"byear": "Boarder Year/Grade", 

"broom": "Boarder Room", 

"bgender": "Boarder Gender (0 = Female; 1 = Male; 2 = Unspecified)", 

"c1rel": "Contact 1 Relationship Type (Mother|Father|Host|Relative|Guardian|Loco Parentis|Other|Parent|Emergency Contact)", 

"c1pk": "Contact 1 PRIMARY KEY", 

"c1fname": "Contact 1 First Name", 

"c1lname": "Contact 1 Surname", 

"c1pname": "Contact 1 Preferred Name", 

"c1add1": "Contact 1 Address 1",

"c1add2": "Contact 1 Address 2", 

"c1sub": "Contact 1 Suburb", 

"c1state": "Contact 1 State", 

"c1pcode": "Contact 1 Post Code/ZIP Code", 

"c1country": "Contact 1 Country", 

"c1hphone": "Contact 1 Home Phone", 

"c1wphone": "Contact 1 Work Phone", 

"c1mobile": "Contact 1 Mobile/Cell Phone", 

"c1email": "Contact 1 Email", 

"c2rel": "Contact 2 Relationship Type (Mother|Father|Host|Relative|Guardian|Loco Parentis|Other|Parent|Emergency Contact)", 

"c2pk": "Contact 2 PRIMARY KEY", 

"c2fname": "Contact 2 First Name", 

"c2lname": "Contact 2 Surname", 

"c2pname": "Contact 2 Preferred Name", 

"c2add2": "Contact 2 Address 1", 

"c2add2": "Contact 2 Address 2",

"c2sub": "Contact 2 Suburb", 

"c2state": "Contact 2 State", 

"c2pcode": "Contact 2 Post Code/ZIP Code", 

"c2country": "Contact 2 Country", 

"c2hphone": "Contact 2 Home Phone", 

"c2wphone": "Contact 2 Work Phone", 

"c2mobile": "Contact 2 Mobile/Cell Phone", 

"c2email": "Contact 2 Email", 

"busername": "Boarder Username", 

"bpassword": "Boarder Password", 

"c1username": "Contact 1 Username", 

"c1password": "Contact 1 Password", 

"c2username": "Contact 2 Username", 

"c2password": "Contact 2 Password" 

}

New JSON Format 

{ 

"bpk": "PRIMARY KEY", 

"bfname": "Boarder First Name", 

"blname": "Boarder Surname", 

"bpname": "Boarder Preferred Name", 

"badd1": "Boarder Address 1", 

"badd2": "Boarder Address 2", 

"bsub": "Boarder Suburb", 

"bstate": "Boarder State", 

"bpcode": "Boarder Post Code/ZIP Code",

"bcountry": " Boarder Country", 

"bhphone": "Boarder Home Phone", 

"bwphone": "Boarder Work Phone", 

"bmobile": "Boarder Mobile/Cell Phone", 

"bemail": "Boarder Email", 

"bdob": "Boarder DOB (yyyy-mm-dd format ONLY)", 

"bhouse": "Boarder House/Dorm", 

"byear": "Boarder Year/Grade", 

"broom": "Boarder Room", 

"bgender": "Boarder Gender (0 = Female; 1 = Male; 2 = Unspecified)"

"crel": "Contact Relationship Type (Mother|Father|Host|Relative|Guardian|Loco Parentis|Other|Parent|Emergency Contact)", 

"cpk": "Contact PRIMARY KEY", 

"cfname": "Contact First Name", 

"clname": "Contact Surname", 

"cpname": "Contact Preferred Name", 

"cadd1": "Contact Address 1", 

"cadd2": "Contact Address 2",

"csub": "Contact Suburb", 

"cstate": "Contact State", 

"cpcode": "Contact Post Code/ZIP Code", 

"ccountry": "Contact Country", 

"chphone": "Contact Home Phone", 

"cwphone": "Contact Work Phone", 

"cmobile": "Contact Mobile/Cell Phone", 

"cemail": "Contact Email", 

"busername": "Boarder Username", 

"bpassword": "Boarder Password", 

"cusername": "Contact Username", 

"cpassword": "Contact Password" 

}