People

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

Other Uses

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.

https://i.tpsdb.com/2017-03-30_16-42-50-1.png

Sample Master Pager List

https://i.tpsdb.com/2017-03-30_17-23-53.png

Sample Extra Values

See also

Extra Values

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.

Note

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 Pager and Leader.

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.

Note

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.

Code

;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


Latest Update

6/23/2022

Fix typo