I’m working on creating a custom report template for student profiles and would like to display the phone numbers for both students and their guardians, as well as the student’s gender. However, I’m struggling to find the correct fields to access this information.
I’ve reviewed the available data sources (Student, Families, Tutors, etc.), but it seems like the phone number fields, and the gender field for students, are either missing or not documented.
Could someone please guide me on how to retrieve and display them in the report templates?
This query will give you a student’s phone number(s) and gender as well as both parent’s phone number(s).
SELECT DISTINCT
student.surname AS studentSurname,
student.preferredName AS studentFirstName,
student.gender AS studentGender,
student.phone1 AS studentPhone1,
student.phone2 AS studentPhone2,
CONCAT(parent1.phone1Type,' ',parent1.phone1CountryCode,' ', parent1.phone1) AS parent1phone1,
CONCAT(parent1.phone2Type,' ',parent1.phone2CountryCode,' ', parent1.phone2) AS parent1phone2,
CONCAT(parent2.phone1Type,' ',parent2.phone1CountryCode,' ', parent2.phone1) AS parent2phone1,
CONCAT(parent2.phone2Type,' ',parent2.phone2CountryCode,' ', parent2.phone2) AS parent2phone2
FROM
gibbonPerson AS student
JOIN gibbonStudentEnrolment ON (student.gibbonPersonID=gibbonStudentEnrolment.gibbonPersonID)
LEFT JOIN gibbonFamilyChild ON (gibbonFamilyChild.gibbonPersonID=student.gibbonPersonID)
LEFT JOIN gibbonFamily ON (gibbonFamilyChild.gibbonFamilyID=gibbonFamily.gibbonFamilyID)
LEFT JOIN gibbonFamilyAdult AS parent1Fam ON (parent1Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent1Fam.contactPriority=1)
LEFT JOIN gibbonPerson AS parent1 ON (parent1Fam.gibbonPersonID=parent1.gibbonPersonID AND parent1.status='Full')
LEFT JOIN gibbonFamilyAdult AS parent2Fam ON (parent2Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent2Fam.contactPriority=2)
LEFT JOIN gibbonPerson AS parent2 ON (parent2Fam.gibbonPersonID=parent2.gibbonPersonID AND parent2.status='Full')
WHERE
gibbonStudentEnrolment.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status='Current')
AND student.status='Full';