Difference between revisions of "Family Management"
(→Family Representative) |
(→Family_Ident field) |
||
(17 intermediate revisions by the same user not shown) | |||
Line 25: | Line 25: | ||
The Family_Ident field exists as a legacy field in the database that is no longer used by the application. We've added it back to the "Modify Info" screen. It is key to our family management that every student from the same family shares the same family_ident value. | The Family_Ident field exists as a legacy field in the database that is no longer used by the application. We've added it back to the "Modify Info" screen. It is key to our family management that every student from the same family shares the same family_ident value. | ||
− | The following | + | The following plugin will add the family_ident field to the "Modify Info" screen along with a link to assign the next available Family ID when needed. |
+ | *[[Media:Familyid_23.03.zip|Familyid_23.03.zip]] | ||
− | The file needs to be | + | The file needs to be installed via the Plugin Management Dashboard. It was enhanced during the March 2023 PSUG Middle East & Africa conference in Amman, Jordan to correctly get a true max and ignore any alpha characters that may exist in your family_ident field. Thanks for Vance Allen for the improvements to the code. |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
[[File:Familyidentscreenshot.png|frame|none|Family ID Screenshot]] | [[File:Familyidentscreenshot.png|frame|none|Family ID Screenshot]] | ||
Line 85: | Line 50: | ||
family_rep# | family_rep# | ||
− | To maintain the accuracy of the family_rep setting, we rely on some sqlReports. Click | + | To maintain the accuracy of the family_rep setting, we rely on some sqlReports. |
− | + | {| class="wikitable" | |
− | <pre class="mw-collapsible mw-collapsed | + | |- |
+ | ! colspan="3" | | ||
+ | sqlReports for Family Management<br /> | ||
+ | ''Click "expand" next to any of these reports, then copy/paste the block of code into sqlReports import area.'' | ||
+ | |- | ||
+ | ! Name !! Description !! Code to copy/paste into sqlReports import box | ||
+ | |- | ||
+ | | style="width: 18%;| | ||
+ | '''FamilyID and Rep Audit v2''' | ||
+ | | style="width: 20%;| | ||
+ | Finding families and students with no Family ID or where they have more or less than one Family Representative | ||
+ | | | ||
+ | <pre class="mw-collapsible mw-collapsed"> | ||
<ReportName>Family Id and Rep Audit v2</ReportName> | <ReportName>Family Id and Rep Audit v2</ReportName> | ||
<ReportTitle>Family ID and Rep Audit v2</ReportTitle> | <ReportTitle>Family ID and Rep Audit v2</ReportTitle> | ||
Line 196: | Line 173: | ||
<NoHeaderRow></NoHeaderRow> | <NoHeaderRow></NoHeaderRow> | ||
</pre> | </pre> | ||
− | + | |- | |
− | + | | | |
− | + | '''Youngest FamilyRep Errors''' | |
− | < | + | | |
− | + | Finding all students in families whose family_rep does not match the "Youngest in Family" rule. Useful for selecting these students and clearing their family_rep value. Then use the report below to set the correct representative. | |
+ | | | ||
+ | <pre class="mw-collapsible mw-collapsed"> | ||
<ReportName>Family Rep Youngest Errors</ReportName> | <ReportName>Family Rep Youngest Errors</ReportName> | ||
<ReportTitle>Family Rep Youngest Errors</ReportTitle> | <ReportTitle>Family Rep Youngest Errors</ReportTitle> | ||
Line 291: | Line 270: | ||
<NoHeaderRow></NoHeaderRow> | <NoHeaderRow></NoHeaderRow> | ||
</pre> | </pre> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
+ | | | ||
+ | '''FamilyRep Youngest Selector''' | ||
| | | | ||
Finding students who should match the "Youngest in Family" rule but do not for the purpose of selecting and mass modifying their family_rep setting via Student Field Value | Finding students who should match the "Youngest in Family" rule but do not for the purpose of selecting and mass modifying their family_rep setting via Student Field Value | ||
Line 382: | Line 357: | ||
</pre> | </pre> | ||
|- | |- | ||
+ | | | ||
+ | '''Youngest at Campus''' | ||
| | | | ||
The system described above selects the youngest in the family for the District. If a school needs to select one student per family for just their campus, this sqlReport can be used, assuming family_ident is properly maintained for all students. | The system described above selects the youngest in the family for the District. If a school needs to select one student per family for just their campus, this sqlReport can be used, assuming family_ident is properly maintained for all students. |
Latest revision as of 09:29, 22 March 2023
Family Management as practiced by Valley Christian Schools. This does not pretend to be a prescription for "HOW IT SHOULD BE DONE", but rather simply shares how one school does it's best to manage families.
Introduction
Identifying related students and the ability to share data among those students is the goal of family management, which allows schools to enter student data once and share data among family members.
Learn more about Family Management Setup in PowerSchool by searching for "Family" in the Help area.
Once identified, shared data can be copied without additional data entry. Additionally, edited data can be dynamically updated for all students with the predetermined family relationship.
The Family Management function enables the district to:
- Control which student fields are copied from one student to another.
- Limit the scope of student records visible to a school administrator.
- Limit the scope of family associations to only those student records within the same school.
At Valley Christian we work to manage families by:
- Allowing district-wide scope for family associations.
- Adding all our custom guardian fields to the list of family management fields.
- Continue using the Family_Ident field still in the database but not used by the application.
- Diligently marking the youngest student as the "Family Representative".
- Annual review of family relationships when the family directory is published.
- Use of sqlReports to identify problem areas in family setup.
- Training and reviewing with secretaries how to link families on registration and how to update siblings when demographic changes are made.
Family_Ident field
The Family_Ident field exists as a legacy field in the database that is no longer used by the application. We've added it back to the "Modify Info" screen. It is key to our family management that every student from the same family shares the same family_ident value.
The following plugin will add the family_ident field to the "Modify Info" screen along with a link to assign the next available Family ID when needed.
The file needs to be installed via the Plugin Management Dashboard. It was enhanced during the March 2023 PSUG Middle East & Africa conference in Amman, Jordan to correctly get a true max and ignore any alpha characters that may exist in your family_ident field. Thanks for Vance Allen for the improvements to the code.
Validation
The family_ident field is a text field in the database. For the fragment to work, you need to make sure only integer values are stored. If you already have content in family_ident, review the contents and make corrections as needed. A useful search followed by a quick export can help: /family_ident#
To ensure the family_ident field remains integer values, we added a text mask:
- District - Data Validation Configuration - Text Mask Administration
- Name: Numeric 5 digits
- Validation Feedback Hint: Must be a 5-digit numerical value.
- Format/Value: #####
- District - Data Validation Configuration - Data Validation Rule Status
- Find the "STUDENTS FAMILY_IDENT" entry
- Assign the "Numeric 5 digit" mask
Family Representative
Our practice is to mark the "youngest in the family not in Preschool" as the "Family Rep" on the "Modify Info" screen. If their only student is in Preschool, then they are the family rep.
To search for all unique families in the district, one can then type the following search from district office: family_rep#
To maintain the accuracy of the family_rep setting, we rely on some sqlReports.
sqlReports for Family Management | ||
---|---|---|
Name | Description | Code to copy/paste into sqlReports import box |
FamilyID and Rep Audit v2 |
Finding families and students with no Family ID or where they have more or less than one Family Representative |
<ReportName>Family Id and Rep Audit v2</ReportName> <ReportTitle>Family ID and Rep Audit v2</ReportTitle> <AfterTitle></AfterTitle> <ReportGroup>Family</ReportGroup> <ReportDescription><textarea name="PF-RptDescr-00601219166">Reports issues with Family IDs and family rep. Identifies families or students where: - The family has more or less than one family representative - The family_ident field is blank</textarea></ReportDescription> <SQLQuery><textarea name="UF-00600519166">SELECT s.dcid, s.lastfirst, s.grade_level as grade_level, s.family_ident as family_ident, sc.family_rep as family_rep FROM students s LEFT OUTER JOIN studentcorefields sc ON s.dcid = sc.studentsdcid INNER JOIN (SELECT s.family_ident as family_id FROM students s LEFT OUTER JOIN studentcorefields sc ON s.dcid = sc.studentsdcid WHERE s.enroll_status IN (0~[if#prereg.%param1%=Yes],-1[/if#prereg]) GROUP BY s.family_ident HAVING sum(nvl(sc.family_rep,0)) != 1) f ON s.family_ident = f.family_id UNION ALL SELECT s.DCID, s.lastfirst, s.grade_level as grade_level, s.family_ident as family_ident, sc.family_rep as family_rep FROM students s LEFT OUTER JOIN studentcorefields sc ON s.dcid = sc.studentsdcid WHERE s.enroll_status IN (0~[if#prereg.%param1%=Yes],-1[/if#prereg]) AND s.family_ident IS NULL ORDER BY family_ident, grade_level</textarea></SQLQuery> <ReportHeader><th class="DCID">DCID</th><th>Student</th><th>Grade</th><th>Family Ident</th><th>Family Rep</th></ReportHeader> <CreateStudentSelection>0</CreateStudentSelection> <StudentSelectionQuery></StudentSelectionQuery> <CreateStudentSelectionB>1</CreateStudentSelectionB> <IncludeRowNumber>0</IncludeRowNumber> <OpeninNewWindow>0</OpeninNewWindow> <HideCopyButton></HideCopyButton> <HideCSVButton></HideCSVButton> <HideTabButton></HideTabButton> <HidePDFButton></HidePDFButton> <ShowSearchBox></ShowSearchBox> <ShowResultsInfo></ShowResultsInfo> <ParameterName1>Include pre-registered students?</ParameterName1> <ParameterVal1>No</ParameterVal1> <ParameterCal1></ParameterCal1> <ParameterOpt1>yesorno</ParameterOpt1> <ParameterName2></ParameterName2> <ParameterVal2></ParameterVal2> <ParameterCal2></ParameterCal2> <ParameterOpt2></ParameterOpt2> <ParameterName3></ParameterName3> <ParameterVal3></ParameterVal3> <ParameterCal3></ParameterCal3> <ParameterOpt3></ParameterOpt3> <ParameterName4></ParameterName4> <ParameterVal4></ParameterVal4> <ParameterCal4></ParameterCal4> <ParameterOpt4></ParameterOpt4> <ParameterName5></ParameterName5> <ParameterVal5></ParameterVal5> <ParameterCal5></ParameterCal5> <ParameterOpt5></ParameterOpt5> <PdfOrientation></PdfOrientation> <NoCSVQuotes></NoCSVQuotes> <NoRowSel></NoRowSel> <ShowSHC></ShowSHC> <UseColRe></UseColRe> <UseFixHdr></UseFixHdr> <HidePrint></HidePrint> <ReportNotes></ReportNotes> <UseColFilt></UseColFilt> <UseRowGroups></UseRowGroups> <UseRowGroupsOptions></UseRowGroupsOptions> <DisplayRowGroupsCol></DisplayRowGroupsCol> <UseRowGroupsCounts></UseRowGroupsCounts> <sqlChartsReport></sqlChartsReport> <InitialsqlChart></InitialsqlChart> <InitialChartName></InitialChartName> <RowGroupsDesc></RowGroupsDesc> <sqlExportsReport></sqlExportsReport> <FLSMapField></FLSMapField> <ExcFrmList></ExcFrmList> <ExecGrp></ExecGrp> <ValueLi3>0</ValueLi3> <NoHeaderRow></NoHeaderRow> |
Youngest FamilyRep Errors |
Finding all students in families whose family_rep does not match the "Youngest in Family" rule. Useful for selecting these students and clearing their family_rep value. Then use the report below to set the correct representative. |
<ReportName>Family Rep Youngest Errors</ReportName> <ReportTitle>Family Rep Youngest Errors</ReportTitle> <AfterTitle></AfterTitle> <ReportGroup>Family</ReportGroup> <ReportDescription><textarea name="PF-RptDescr-00601219167">(PSUG Version) Finds families where the family rep does not match the rule of "Youngest in Family". After reviewing, the best way to correct is to clear the Family_Rep flag for this report's current selection. Then run the "Family Rep Youngest Select" report to efficiently select the correct family_rep students.</textarea></ReportDescription> <SQLQuery><textarea name="UF-00600519167">SELECT s.DCID, s.lastfirst, s.grade_level as grade_level, s.family_ident as family_ident, sc.family_rep as family_rep FROM students s LEFT OUTER JOIN studentcorefields sc ON s.dcid = sc.studentsdcid INNER JOIN (SELECT s.family_ident as family_id FROM students s LEFT JOIN studentcorefields sc ON s.dcid = sc.studentsdcid WHERE s.id IN ( SELECT min(id) keep (dense_rank first order by grade_level) FROM students WHERE enroll_status=0 GROUP BY family_ident ) AND nvl(sc.family_rep,0) != 1) fr ON s.family_ident = fr.family_id ORDER BY s.family_ident, s.grade_level</textarea></SQLQuery> <ReportHeader><th class="DCID">DCID</th><th>Student</th><th>Grade</th><th>FamilyID</th><th>FamilyRep</th></ReportHeader> <CreateStudentSelection>0</CreateStudentSelection> <StudentSelectionQuery></StudentSelectionQuery> <CreateStudentSelectionB>1</CreateStudentSelectionB> <IncludeRowNumber>1</IncludeRowNumber> <OpeninNewWindow>0</OpeninNewWindow> <HideCopyButton></HideCopyButton> <HideCSVButton></HideCSVButton> <HideTabButton></HideTabButton> <HidePDFButton></HidePDFButton> <ShowSearchBox></ShowSearchBox> <ShowResultsInfo></ShowResultsInfo> <ParameterName1></ParameterName1> <ParameterVal1></ParameterVal1> <ParameterCal1></ParameterCal1> <ParameterOpt1></ParameterOpt1> <ParameterName2></ParameterName2> <ParameterVal2></ParameterVal2> <ParameterCal2></ParameterCal2> <ParameterOpt2></ParameterOpt2> <ParameterName3></ParameterName3> <ParameterVal3></ParameterVal3> <ParameterCal3></ParameterCal3> <ParameterOpt3></ParameterOpt3> <ParameterName4></ParameterName4> <ParameterVal4></ParameterVal4> <ParameterCal4></ParameterCal4> <ParameterOpt4></ParameterOpt4> <ParameterName5></ParameterName5> <ParameterVal5></ParameterVal5> <ParameterCal5></ParameterCal5> <ParameterOpt5></ParameterOpt5> <PdfOrientation></PdfOrientation> <NoCSVQuotes></NoCSVQuotes> <NoRowSel></NoRowSel> <ShowSHC></ShowSHC> <UseColRe></UseColRe> <UseFixHdr></UseFixHdr> <HidePrint></HidePrint> <ReportNotes></ReportNotes> <UseColFilt></UseColFilt> <UseRowGroups></UseRowGroups> <UseRowGroupsOptions></UseRowGroupsOptions> <DisplayRowGroupsCol></DisplayRowGroupsCol> <UseRowGroupsCounts></UseRowGroupsCounts> <sqlChartsReport></sqlChartsReport> <InitialsqlChart></InitialsqlChart> <InitialChartName></InitialChartName> <RowGroupsDesc></RowGroupsDesc> <sqlExportsReport></sqlExportsReport> <FLSMapField></FLSMapField> <ExcFrmList></ExcFrmList> <ExecGrp></ExecGrp> <ValueLi3>0</ValueLi3> <NoHeaderRow></NoHeaderRow> |
FamilyRep Youngest Selector |
Finding students who should match the "Youngest in Family" rule but do not for the purpose of selecting and mass modifying their family_rep setting via Student Field Value |
<ReportName>Family Rep Youngest Selector PSUG</ReportName> <ReportTitle>Family Rep Youngest Selector PSUG</ReportTitle> <AfterTitle></AfterTitle> <ReportGroup>Family</ReportGroup> <ReportDescription><textarea name="PF-RptDescr-00601219168">Reports students who should be family rep but are not for mass modifying. Make students the current selection and use student field value group function to set their family_rep field to 1. </textarea></ReportDescription> <SQLQuery><textarea name="UF-00600519168">SELECT s.dcid, s.family_ident as family_id, s.lastfirst as lastfirst, s.grade_level as grade, sc.family_rep FROM students s LEFT OUTER JOIN studentcorefields sc ON s.dcid = sc.studentsdcid WHERE s.id IN (SELECT min(id) keep (dense_rank first order by grade_level) FROM students WHERE enroll_status=0 GROUP BY family_ident) AND nvl(sc.family_rep,0) != 1 AND s.family_ident IS NOT NULL ORDER BY s.lastfirst</textarea></SQLQuery> <ReportHeader><th class="DCID">DCID</th><th>Family ID</th><th>Student</th><th>Grade</th><th>FamilyRep</th></ReportHeader> <CreateStudentSelection>0</CreateStudentSelection> <StudentSelectionQuery></StudentSelectionQuery> <CreateStudentSelectionB>1</CreateStudentSelectionB> <IncludeRowNumber>0</IncludeRowNumber> <OpeninNewWindow>0</OpeninNewWindow> <HideCopyButton></HideCopyButton> <HideCSVButton></HideCSVButton> <HideTabButton></HideTabButton> <HidePDFButton></HidePDFButton> <ShowSearchBox></ShowSearchBox> <ShowResultsInfo></ShowResultsInfo> <ParameterName1></ParameterName1> <ParameterVal1></ParameterVal1> <ParameterCal1></ParameterCal1> <ParameterOpt1></ParameterOpt1> <ParameterName2></ParameterName2> <ParameterVal2></ParameterVal2> <ParameterCal2></ParameterCal2> <ParameterOpt2></ParameterOpt2> <ParameterName3></ParameterName3> <ParameterVal3></ParameterVal3> <ParameterCal3></ParameterCal3> <ParameterOpt3></ParameterOpt3> <ParameterName4></ParameterName4> <ParameterVal4></ParameterVal4> <ParameterCal4></ParameterCal4> <ParameterOpt4></ParameterOpt4> <ParameterName5></ParameterName5> <ParameterVal5></ParameterVal5> <ParameterCal5></ParameterCal5> <ParameterOpt5></ParameterOpt5> <PdfOrientation></PdfOrientation> <NoCSVQuotes></NoCSVQuotes> <NoRowSel></NoRowSel> <ShowSHC></ShowSHC> <UseColRe></UseColRe> <UseFixHdr></UseFixHdr> <HidePrint></HidePrint> <ReportNotes></ReportNotes> <UseColFilt></UseColFilt> <UseRowGroups></UseRowGroups> <UseRowGroupsOptions></UseRowGroupsOptions> <DisplayRowGroupsCol></DisplayRowGroupsCol> <UseRowGroupsCounts></UseRowGroupsCounts> <sqlChartsReport></sqlChartsReport> <InitialsqlChart></InitialsqlChart> <InitialChartName></InitialChartName> <RowGroupsDesc></RowGroupsDesc> <sqlExportsReport></sqlExportsReport> <FLSMapField></FLSMapField> <ExcFrmList></ExcFrmList> <ExecGrp></ExecGrp> <ValueLi3>0</ValueLi3> <NoHeaderRow></NoHeaderRow> |
Youngest at Campus |
The system described above selects the youngest in the family for the District. If a school needs to select one student per family for just their campus, this sqlReport can be used, assuming family_ident is properly maintained for all students. |
<ReportName>Youngest at Campus</ReportName> <ReportTitle>Youngest at Campus</ReportTitle> <AfterTitle></AfterTitle> <ReportGroup>Family</ReportGroup> <ReportDescription><textarea name="PF-RptDescr-00601215828">Selects the youngest student per family (based on family_ident) at the current school. If at District Level, selects the youngest in the District.</textarea></ReportDescription> <SQLQuery><textarea name="UF-00600515828">SELECT s.dcid, s.lastfirst, s.grade_level, s.family_ident FROM students s WHERE s.id IN ( (SELECT min(id) keep (dense_rank first order by grade_level) FROM students WHERE enroll_status=0 ~[if.is.a.school]AND schoolid = ~(curschoolid)[/if.is.a.school] GROUP BY family_ident) ) ORDER BY s.lastfirst</textarea></SQLQuery> <ReportHeader><th class="DCID">DCID</th><th>Student</th><th>Grade</th><th>Family ID</th></ReportHeader> <CreateStudentSelection>0</CreateStudentSelection> <StudentSelectionQuery></StudentSelectionQuery> <CreateStudentSelectionB>1</CreateStudentSelectionB> <IncludeRowNumber>0</IncludeRowNumber> <OpeninNewWindow>0</OpeninNewWindow> <HideCopyButton></HideCopyButton> <HideCSVButton></HideCSVButton> <HideTabButton></HideTabButton> <HidePDFButton></HidePDFButton> <ShowSearchBox></ShowSearchBox> <ShowResultsInfo>1</ShowResultsInfo> <ParameterName1></ParameterName1> <ParameterVal1></ParameterVal1> <ParameterCal1></ParameterCal1> <ParameterOpt1></ParameterOpt1> <ParameterName2></ParameterName2> <ParameterVal2></ParameterVal2> <ParameterCal2></ParameterCal2> <ParameterOpt2></ParameterOpt2> <ParameterName3></ParameterName3> <ParameterVal3></ParameterVal3> <ParameterCal3></ParameterCal3> <ParameterOpt3></ParameterOpt3> <ParameterName4></ParameterName4> <ParameterVal4></ParameterVal4> <ParameterCal4></ParameterCal4> <ParameterOpt4></ParameterOpt4> <ParameterName5></ParameterName5> <ParameterVal5></ParameterVal5> <ParameterCal5></ParameterCal5> <ParameterOpt5></ParameterOpt5> <PdfOrientation></PdfOrientation> <NoCSVQuotes></NoCSVQuotes> <NoRowSel></NoRowSel> <ShowSHC></ShowSHC> <UseColRe></UseColRe> <UseFixHdr></UseFixHdr> <HidePrint></HidePrint> <ReportNotes></ReportNotes> <UseColFilt></UseColFilt> <UseRowGroups></UseRowGroups> <UseRowGroupsOptions>Opt1</UseRowGroupsOptions> <DisplayRowGroupsCol></DisplayRowGroupsCol> <UseRowGroupsCounts></UseRowGroupsCounts> <sqlChartsReport></sqlChartsReport> <InitialsqlChart></InitialsqlChart> <InitialChartName></InitialChartName> <RowGroupsDesc></RowGroupsDesc> <sqlExportsReport></sqlExportsReport> <FLSMapField></FLSMapField> <ExcFrmList></ExcFrmList> <ExecGrp></ExecGrp> <ValueLi3>0</ValueLi3> <NoHeaderRow></NoHeaderRow> |