Difference between revisions of "Family Management"

From California PowerSchool User Group
Jump to: navigation, search
(→‎Family_Ident field)
(→‎Family_Ident field)
 
(36 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 page fragment 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 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 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]]
 +
 +
===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.
 
{| class="wikitable"
 
{| class="wikitable"
 
|-
 
|-
−
! File !! Code
+
! 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%;|
−
[[Media:Modifydata.familyid.content.footer.zip|modifydata.familyid.content.footer.txt]]
+
'''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">
 
<pre class="mw-collapsible mw-collapsed">
−
<!-- create a hidden table with added rows, use jQuery to move the inserted rows to target table -->
+
<ReportName>Family Id and Rep Audit v2</ReportName>
−
<table id="familyidhiddentable" style="display: none;">
+
<ReportTitle>Family ID and Rep Audit v2</ReportTitle>
−
<tr id="familyidrow">
+
<AfterTitle></AfterTitle>
−
    <td class="bold">Family ID</td>
+
<ReportGroup>Family</ReportGroup>
−
    <td><input type="text" id="familyidfield" name="[Students]Family_Ident" value="">
+
<ReportDescription><textarea name="PF-RptDescr-00601219166">Reports issues with Family IDs and family rep. Identifies families or students where:
−
    <a href="javascript:void(0)" id="usenextfamilyid"><strong>Copy next available Family ID: <span id="nextfamilyidval">~[tlist_sql;select max(family_ident)+1 from students]~(maxFamilyID;t)[/tlist_sql]</span></strong>
+
- The family has more or less than one family representative
−
    </td>
+
- The family_ident field is blank</textarea></ReportDescription>
−
</tr>
+
<SQLQuery><textarea name="UF-00600519166">SELECT
−
</table>
+
s.dcid,
−
<script>
+
s.lastfirst,
−
$j(function() {
+
s.grade_level as grade_level,
−
    /* place the family id row at the beginning of the table */
+
s.family_ident as family_ident,
−
    $j("table:first").prepend($j("#familyidrow"));
+
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])
  
−
    /* remove the hidden table */
+
GROUP BY s.family_ident
−
    $j("#familyidhiddentable").remove();
 
  
−
    /* Function to add click listener to copy the next available ID */
+
HAVING sum(nvl(sc.family_rep,0)) != 1) f
−
    $j("#usenextfamilyid").click(function() {
 
−
        if($j("#familyidfield").val() == "") {
 
−
            $j("#familyidfield").val( $j("#nextfamilyidval").html() );
 
−
        }
 
−
    });
 
  
−
});
+
ON s.family_ident = f.family_id
−
</script>
+
 
 +
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>
 +
</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>
 +
<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 &amp;quot;Youngest in Family&amp;quot;. After reviewing, the best way to correct is to clear the Family_Rep flag for this report's current selection. Then run the &amp;quot;Family Rep Youngest Select&amp;quot; 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>
 +
</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
 +
|
 +
<pre class="mw-collapsible mw-collapsed">
 +
<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>
 +
</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.
 +
|
 +
<pre class="mw-collapsible mw-collapsed">
 +
<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>
 
</pre>
 
</pre>
 
|}
 
|}
−
 
  
 
[[Category:Contents]]
 
[[Category:Contents]]

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.

Family ID Screenshot

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
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

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 &quot;Youngest in Family&quot;. After reviewing, the best way to correct is to clear the Family_Rep flag for this report's current selection. Then run the &quot;Family Rep Youngest Select&quot; 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>
Retrieved from "https://www.psugcal.org/index.php?title=Family_Management&oldid=1134"