Import Student Details - Custom Import

Music Monitor can import student details directly from your school database using a custom import script. The FileMaker script uses an SQL query to connect to your database via ODBC. Generally, the script is then put on a daily schedule, so that the data can be keep up-to-date automatically. Otherwise, it makes importing student details a one-click, secure process.

In order to use a custom import script, your IT department must set up the appropriate ODBC Data Source on the computer the script will be executed from (generally, the Music Monitor server). Additionally, the IT department will need to provide an SQL query to execute.

ODBC Data Source

The ODBC Data Source should be configured as 64-bit and a System DSN.

For more information see the Claris help documentation:

https://help.claris.com/en/pro-help/content/configuring-odbc-driver.html

SQL Query

A query (or several queries) will need to be constructed to extract the data. These queries are pasted into our script. Examples of queries are below.

Sample Synergetic query

SELECT DISTINCT vStudentsAll.StudentID AS 'IDStudent', vStudentsAll.StudentBoarder AS 'Boarder', vStudentsAll.StudentTitle AS 'Title', vStudentsAll.StudentPreferred AS 'Name Preferred', vStudentsAll.StudentGiven1 AS 'Name 1', vStudentsAll.StudentGiven2 AS 'Name 2', vStudentsAll.StudentSurname AS 'Name LAST', vStudentsAll.StudentBirthDate AS 'DATE OF Birth', vStudentsAll.StudentGender AS 'Gender', vStudentsAll.StudentYearLevel AS 'YEAR LEVEL', vStudentsAll.StudentTutor AS 'Form', vStudentContactAllAddress.StudentContactAddress1 AS 'Address 2', vStudentContactAllAddress.StudentContactSuburb AS 'Suburb', vStudentContactAllAddress.StudentContactState AS 'State', vStudentContactAllAddress.StudentContactPostCode AS 'Postcode', vStudentContactAllAddress.StudentContactPhoneActual AS 'Home Phone', vStudentContactAllAddress.StudentContactTitle AS 'Parent 1 Title', vStudentContactAllAddress.StudentContactPreferred AS 'Parent 1 Name 1', vStudentContactAllAddress.StudentContactSurname AS 'Parent 1 Name 2', vStudentContactAllAddress.StudentContactRelation AS 'Parent 1 Relship', vStudentContactAllAddress.StudentContactSpouseTitle AS 'Parent 2 Title', vStudentContactAllAddress.StudentContactSpousePreferred AS 'Parent 2 Name 1', vStudentContactAllAddress.StudentContactSpouseSurname AS 'Parent 2 Name 2', vStudentContactAllAddress.StudentContactSpouseRelation AS 'Parent 2 Relship', vStudentContactAllAddress.StudentContactMobilePhone AS 'Mobile 1', vStudentContactAllAddress.StudentContactSpouseMobilePhone AS 'Mobile 2', vStudentContactAllAddress.StudentContactMailName AS 'Parent Mailing Names', vStudentContactAllAddress.StudentContactMailSalutation AS 'Salutation', vStudentContactAllAddress.StudentContactDefaultEmail AS 'Email 2', vStudentContactAllAddress.StudentContactSpouseDefaultEmail AS 'Email 3', vStudentsAll.StudentOccupEmail AS 'Email',vStudentContactAllAddress.StudentContactID AS 'IDFamily', vStudentsAll.StudentMedicalAlert AS 'Medical Alert' FROM SynergyOne.dbo.vStudentContactAllAddress vStudentContactAllAddress, SynergyOne.dbo.vStudentsAll vStudentsAll WHERE vStudentsAll.ID = vStudentContactAllAddress.ID AND vStudentsAll.ID = vStudentContactAllAddress.StudentID AND vStudentsAll.FileYear= (select top 1 FileYear from FileSemesters where systemcurrentflag=1) AND vStudentsAll.FileSemester = (select top 1 FileSemester from FileSemesters where systemcurrentflag=1) AND vStudentContactAllAddress.StudentContactNormalMailFlag > 0 ORDER BY vStudentsAll.StudentYearLevel, vStudentsAll.StudentSurname

Sample SAS query

SELECT "Student"."Year" AS [Year Level],

"Student"."Code" AS [Student ID],

"Student"."FamilyID"AS [Family ID],

"Student"."FirstName" AS [Name 1], "Student"."LastName" as [Name Last], "Student"."MiddleName" AS [Name 2], "Student"."DOB" AS [Date Of Birth], "Student"."Sex" AS [Gender], "Student"."Class" AS [Form], "Student"."House" AS [House], "Student"."BoarderType" AS [Boarder], "Student"."Email2" AS [Email], "Student"."PreferredName" AS [Name Preferred], "Family"."Greeting" AS [Salutation], "Family"."MailTo" AS [Parent Mailing Names], "Family"."PhoneFamily" AS [Home Phone], "Family"."PhoneMobile" AS [Mobile 1], "Family"."PhoneWork" AS [Mobile 2], "Address"."Address1" AS [Address 1], "Address"."Address2" AS [Suburb], "Address"."Address3" AS [State], "Address"."PostCode" AS [Postcode], "Contact"."Email" AS [Email 2], "Contact"."Email2" AS [Email 3]

FROM "Student", "Family", "Address", "Contact"

WHERE "Student"."EnrolmentStatus" = 'ACC' AND "Student"."FamilyID" = "Family"."ID"

AND "Student"."AddressID" = "Address"."ID"

AND "Student"."AddressID" = "Contact"."HomeAddrID"

Sample TASS query

SELECT  s.stud_code as 'Student ID',  s.surname as 'Name Last',  s.preferred_name as 'Name 1', s.given_name as 'Name 2', s.preferred_name + ' ' +  s.surname as FullName, s.year_grp as 'Year Level',  s.house as House,  s.pctut_grp as 'Home Group',  s.sex as Gender,  s.dob as 'Date of Birth',  s.e_mail as Email,  s.campus_code as Campus,  s.par_code as 'Family ID',pa.par_name + ' ' + pa.par_name2 as 'Parent 1 Name 2',  pa.home_phone as 'Home Phone',  pa.salutation as 'Salutation',  pa.e_mail as 'Email 2',  pa.mobile1 as 'Mobile 1',  pa.mobile2 as 'Mobile 2',  t.tch_name as 'Form teacher' FROM  student AS s,teacher as t,paraddress as pa WHERE s.cmpy_code = '01'  AND s.dol Is Null and s.pctut_grp = t.pctut_grp and s. par_code = pa.par_code and pa.add_num = '1' ORDER BY s.stud_code

Alternative Sample TASS query

A custom TASS view can alternatively be used in conjunction with the SQL query. The query below is an example:

SELECT "uLJBC_MusicMonitor"."Address 2", "uLJBC_MusicMonitor"."Boarder", "uLJBC_MusicMonitor"."Date of Birth", "uLJBC_MusicMonitor"."Email", "uLJBC_MusicMonitor"."Email 2", "uLJBC_MusicMonitor"."Email 3", "uLJBC_MusicMonitor"."Form", "uLJBC_MusicMonitor"."Gender", "uLJBC_MusicMonitor"."Home Phone", "uLJBC_MusicMonitor"."Mobile 1", "uLJBC_MusicMonitor"."Mobile 2", "uLJBC_MusicMonitor"."Name 1", "uLJBC_MusicMonitor"."Name Last", "uLJBC_MusicMonitor"."Name Prefered", "uLJBC_MusicMonitor"."Parent 1 Name 1", "uLJBC_MusicMonitor"."Parent 1 Name 2", "uLJBC_MusicMonitor"."Parent 1 Relship", "uLJBC_MusicMonitor"."Parent 1 Title", "uLJBC_MusicMonitor"."Parent 2 Name 1", "uLJBC_MusicMonitor"."Parent 2 Name 2", "uLJBC_MusicMonitor"."Parent 2 Relship", "uLJBC_MusicMonitor"."Parent 2 Title", "uLJBC_MusicMonitor"."Postcode", "uLJBC_MusicMonitor"."Salutation", "uLJBC_MusicMonitor"."State", "uLJBC_MusicMonitor"."Student ID", "uLJBC_MusicMonitor"."Suburb", "uLJBC_MusicMonitor"."Title", "uLJBC_MusicMonitor"."Year Level"

FROM "uLJBC_MusicMonitor"

Custom Script Setup

Once a SQL query has been provided, a member of the Music Monitor Team can create the FileMaker script and insert the SQL query. This process usually takes about an hour and can be done on site or via TeamViewer. Somebody from your IT department should be present for this session. We will copy and paste your query into a script, test it, and modify if necessary. We will then connect the script with a button in Music Monitor and/or set up an import schedule.

The script will need to be provided with an account name and password to access the school database via ODBC. We do not need to know this information; it can be entered by a member of your team. This account name and password will need appropriate access to extract all the data in the query.