Master Pager List¶
This was initially created for a church that wanted to have a list of children enrolled in an organization, and the Page Number that was on the mom’s record as an ad hoc Extra Value. They also want to know the mom’s group leader, which they had also put as an ad hoc EV on the mom’s record.
The org was a childcare org they were using for the children of women that were participating in a Bible study group. The Master Pager List was a handy reference when there was an emergency with a child. The leaders could see how to contact the mom (pager number) or which class to go to in order to go speak to her in person (Leader’s name).
You could modify this to include any Extra Value that would be on the record of the mother and run the report on any organization with children. You could also run it using a Search Builder results as long as the people in the results were children and their moms had the Extra Value you were looking for.
Example: If you have a childcare org for children whose mom’s were attending an ESL class, you might have Extra Values for Country of Origin and Primary Language on the mom’s record. This report will give you the child’s name, the mom’s name, the Country of Origin and the mom’s Primary Language.
The bottom line is that the report is run for a group of children and presents both the child’s name, the mom’s name and whatever Extra Values you want to see that are on the mom’s record.
Create a Master Pager List Script¶
- Step 1
Go to Administration > Setup > Special Content > SQL Scripts and press the green + New Sql Script File button.
Enter the name as MasterPagerList and press the Submit button.
If you are using this for another purpose enter a name that fits the report.
- Step 2
Select and copy the entire code listed below and paste it into the new file you created.
Edit line 7 and 8 by entering the name of the Extra Value you want included on the report in place of
Press the Save Sql Script button at the bottom of the file.
- Step 3
Now run the script by pressing the Run Script button at the top of the file and after the report runs (it will not have any data yet) select Add Report to Menu at the bottom of the report. This will add the report to the blue Toolbar on an organization, a Tag, Search Builder results, or even a people record.
If you were using this report as originally intended, you would go to the org containing the children and select Master Page List from the code icon on the blue Toolbar. Or you could run a search for children enrolled in multiple orgs and then select it from the blue Toolbar in Search Builder.
If the child does not have a mother (i.e. a female Primary Adult) in the famly his name will still be in the list, but without any other data.
;WITH hh AS ( SELECT ChildId = p.PeopleId ,Child=p.Name ,MotherId = IIF(m.PeopleId = p.PeopleId, NULL, m.PeopleId) ,Mother = IIF(m.PeopleId = p.PeopleId, NULL, m.Name) ,Pager = (SELECT IntValue FROM PeopleExtra WHERE Field = 'LH Pager Number' AND PeopleId = m.PeopleId) ,Leader = (SELECT Data FROM dbo.PeopleExtra WHERE Field = 'HH Leader' AND PeopleId = m.PeopleId) FROM dbo.People p JOIN dbo.Families f ON f.FamilyId = p.FamilyId JOIN dbo.People m ON m.PeopleId = ISNULL(f.HeadOfHouseholdSpouseId, f.HeadOfHouseholdId) ) SELECT LinkForNext = '/Person2/' + CONVERT(VARCHAR, ChildId), Child, LinkForNext = '/Person2/' + CONVERT(VARCHAR, MotherId), Mother , Pager , Leader FROM hh JOIN dbo.TagPerson tp ON tp.PeopleId = hh.ChildId WHERE tp.Id = @qtagid ORDER BY Child