Previous Sub Groups

This SQL Script will find Previous Members of an org and will display the sub-groups they were in when they were regular members of the organization.

Copy the script below and paste into a new Sql Scrpt file in your database. See How to create a SQL Script.

Note

This will not return any current members. So, if you mistakenly drop members you will want to run this report prior to adding them back into the organization.

You have several options for running this script:

  1. After saving the script in your database, run the script from Special Content, then add the Org ID# at the end of the URL and hit Enter to run it again. This will open the report in your browser and you can select Download as Excel.
  2. Use the URL mychurch.tpsdb.com/RunScrpt/Excel/PreSubGroups/216 where mychurch is your church’s name and 216 is the Org ID from your database. This one opens in Excel.
  3. You can choose to Add to Menu after running it initially from Special Content. Then it will display under Reports in the Main Menu. After running it there, just add the Org ID# are the end and hit Enter.

Sample Excel Report

Notice that the 2 sub-groups are on separate lines inside the cell. Select Alignment > Wrap Text to make that happen.

http://i.tpsdb.com/2017-09-25_09-57-07.png

Sample Browser Report

http://i.tpsdb.com/2017-09-25_09-55-50.png

Code

/*
To get sub-groups from OrgMembers who were dropped:
Use the following URL mychurch.tpsdb.com/RunScriptExcel/PrevSubGroups/216
Where 216 is the id of the organization.
After exporting in Excel, format the SmallGroups Column as Wrap Text,
then the small groups will be shown on separate lines.
You can also run the report Special Content > SQL Scripts,
then add the Org ID# at the end of the URL.
At that point you can Download as Excel.
*/
SELECT p.Name, p.PeopleId, e.SmallGroups
FROM dbo.EnrollmentTransaction e
JOIN dbo.People p ON p.PeopleId = e.PeopleId
WHERE OrganizationId = @p1 AND SmallGroups IS NOT NULL

© 2019 TouchPoint Software, LLC.  All rights reserved.