Create a Dedicated "Small Group" Section of Rock

  • By Michael Garrison One Year Ago
Note:
There is a LOT of content here, as we're duplicating entire interfaces in Rock for your users and exploring features suchas Common Table Expressions, injecting custom one-page styles and scripting blocks to do things they were never expected to. If you don't know what some of that means, don't worry- this post will assume that you're comfortable with creating sub-pages and working with blocks instead of hitting every single step like I usually do, but unusual tasks for even a normal Rock admin will still be explained so that you can follow along. Jump into Slack and post in the CMS channel if you need help with anything covered in this post.

Every church has their own organization for categorizing their Small Group ministry. Some of those organizations make finding groups within Group Viewer rather difficult; for instance, my church has a pastor over each region of the city, and then Small Groups are within named neighborhoods (our city is not laid out on a grid, and the residents actually know the name of most of the neighborhoods, so it's how people think of the city).

But that organization means a LOT of clicks through the group viewer when you are looking for a particular group.

Yes, you can search for a group name in Rock's search bar. If you know who's leading a group you can also get to the group from their profile (either from the "Groups" tab or from a label that you may have created. But there are other factors- it's not easy for a pastor to e-mail all their leaders without having lots of data views laying around that need manual upkeep.

So, our church recently launched a new Rock menu on the internal site dedicated to Small Groups. Right now, we're offering three ways to identify people and groups: by district, by pastor and by map. Having come through the process and out the other side, I thought I'd share what I learned.

First, a note on view security permissions while setting up a new menu:

Start by creating a new child page of the main internal page. Give it an icon (we used fa-home), and then create at least one more child page for your header (we used the title "Viewing" but also created another second header page called "Administration" (you may prefer "Tools") for some other pages we've developed already) before going back to your root menu page and denying everyone but you view rights to the page (so it's not showing up for anyone else until you're ready to launch it).

Why do you want to create your header page(s) before setting up security? Because contrary to file systems and other inheritance models you may be used to, when you create child pages or blocks on pages with explicit permissions, Rock will actually COPY the explicit permissions from the containing or parent page to the block or new page, rather than simply letting the permissions be inherited. So if you set up View permissions to "Allow" you and then "Deny" all users initially and THEN create the sub-pages, you're going to be unwittingly copying those permissions to all blocks and pages as you go. Which makes for a lot of work at the end resetting the permissions on every block and every page we're about to create.

So create the menu and header pages with no explicit permissions set, and then any sub-pages you create under the header will likewise ONLY inherit permissions. THEN set the View "Deny" permissions for everyone else on the menu page, which will be inherited by the pages and blocks under it, but not COPIED to them. This will give you only one place to remove that Deny permission in order to light up the whole menu once it's ready.

smallgroup-hierarchy.JPG
(obviously you don't need to create the Administration page if you don't have anything to go there. The point is to make sure you create your menu header pages BEFORE setting deny permissions so others can't see what you're doing while you're still building out these pages).

Create a dedicated "Group Viewer" page just for your Small Groups.

For our first actual page with content, let's create a duplicate "Group Viewer" page under your "Viewing" header page that's dedicated to just our Small Group hierarchy

Start by creating a "Left Sidebar" layout page- we called it "Districts" since this is how the groups are actually organized in Rock.

Add a "Group Tree View" block to the Sidebar 1 page zone. By default this is showing you all of your groups, so let's get into the Block Properties and limit what it shows. You can do this by ticking the "Small Group Section" and Small Group group types in "Group Types Include", or if your organization is like ours, you can pick an alternate Root Group instead, choosing just the container with all of your regions inside.

At the bottom of the Block Settings is a picker labeled "Detail Page". Here you'll want to choose the page you're currently on- that way when a group is picked, it will load this page again with the GroupId parameter set in the URL. Click save

Now you may want to add a "Group Detail" block to the "Main" page zone, if you like the header of the page from the normal Group Viewer page- this is the block with the "Edit" and "Delete" buttons on it, as well as the map thumbnail, the attendance button (for groups configured to take attendance) and some extra information on the group. No configuration necessary here- it's ready to go.

The only thing missing now is the Group Member List block below the detail block in the "Main" zone. You can absolutely use the normal block with no configuration required, but our church wanted something ... a little different. We wanted to display contact information for each member on this page, without having to use a sub-page. So instead of using the Group Member List block, I put a Dynamic Data block here instead.

(I should note, if you use the regular Group Member List block here, you can skip the rest of this section, which is mostly re-creating what's already built into this block).

Here's the query I used:

SELECT  
    P.[Id],
    P.[NickName] + ' ' + P.[LastName] AS [Name],
    P.[Email] AS [Email],
    ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 13 ) AS [Home],
    ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 12 ) AS [Cell],
    ( SELECT ISNULL([NumberFormatted],[Number]) FROM [PhoneNumber] WHERE [PersonId] = P.[Id] AND [NumberTypeValueId] = 136 ) AS [Work],
    ( SELECT TOP 1 ISNULL([Street1],'') + ' ' + ISNULL([Street2],'') + ' ' + ISNULL([City],'') + ', ' + ISNULL([State],'') + ' ' + ISNULL([PostalCode],'') 
        FROM [GroupMember] FM                                                                           -- Family Member
        INNER JOIN [Group] F ON F.[Id] = FM.[GroupId] AND F.[GroupTypeId] = 10                          -- Family
        INNER JOIN [GroupLocation] FL ON FL.[GroupId] = F.[Id] AND FL.[GroupLocationTypeValueId] = 19   -- Home Location
        INNER JOIN [Location] L ON L.[Id] = FL.[LocationId]                                             -- Address
        WHERE FM.[PersonId] = P.[Id] 
    ) AS [Address]
FROM [GroupMember] M  
INNER JOIN [Person] P ON P.[Id] = M.[PersonId]  
WHERE [GroupId] = @GroupId  
AND M.[GroupMemberStatus] = 1                                                                            -- Active

Yep, the same query from the post I linked to just above- why not? Just like on that page, you'll want to hide the Id column, set the Selection URL to ~/Person/{Id}, set Parameters to GroupId=0 and specify that this is a Person Report with all of the grid actions turned on.

At first glance, it looks like this page it done- I certainly thought so until I had someone test it out. It quickly became clear that there is no "Delete" or "Add" button for group members. Oops.

Here's the solution: create two sub-pages for this page: one called "Add to group" and one called "Remove from group". Take note of their IDs- in my case "Add" is page 511 and "Remove" is page 510 - replace these numbers below with your page numbers.

On the "Add" page, put a "Group Member Detail" block in the Main zone. Nothing more to do here.

On the "Remove" page, put a "Group Member RemoveFrom Url" block in the Main zone. Then go into the Block Settings for this block and in "Advanced Settings", add something like <a href="~/page/507?GroupId={{ PageParameter.GroupId }}">Return</a> to "Post-HTML" (unless you want to use JavaScript to automatically redirect you back. But since you'll see the page load briefly before that kicks in, I prefer showing the success message and letting the user return on their own rather than wondering, "What did that page say?")

OK, now your "Add" and "Remove" pages are ready to go, we just need to link to them.

Add the following text to your Dynamic Data block on your "Districts" page, with the leading comma going right after AS [Address]:

,
    '<a class="btn btn-danger btn-sm grid-delete-button" href="510?GroupId=' + CONVERT(varchar(10), @GroupId) + '&PersonGuid=' + CONVERT(varchar(50), P.[Guid]) + '"><i class="fa fa-times"></i></a>' AS [Delete]

(remember to replace 510 in the href above with the ID of your own Delete page).

Now your Dynamic Data block has a nice red X delete button that will pass the GUID of the person on that row and the current group ID to your "Group Member RemoveFrom Url" block.

Now we'll use a bit of JQuery trickery to link to the "Add" page. Edit the block settings for your Dynamic Data block and put the following in Post-HTML:

<script>$("td.grid-actions").prepend("<a href=\"~/page/511?GroupMemberId=0&GroupId={{ PageParameter.GroupId }}\" accesskey=\"n\" class=\"btn-add btn btn-default btn-sm\"><i class=\"fa fa-plus-circle\"></i></a>");</script>

(once again replacing 511 in the href with the ID of your own Add page).

You'll need to reload the page to get the script to fire, but as soon as you do, you'll see an "Add" button that works just like the normal Group Viewer page. Whew!

SmallGroupPortal-Districts.png

Create lists of groups and people by pastor

Remember how I said that our pastors each oversee one or more "Districts" within the city? We have created a custom group type that takes a geofence as the group location, and only has a "District Pastor" role for members. We identify which pastor is over each region by making them the only member of each "District". In this way, we can find which district anyone is in by looking for which geofence they're contained within, and finding their District Pastor by getting the member of the group. This works well for workflow automation and the like.

This organization also works well for being able to automatically list all groups, leaders and group members that each pastor oversees. Which the pastors love but the rest of staff loves as well so we surfaced it to everyone.

So on a new Full-Width layout sub-page of the "Viewing" header page, let's get a nice-looking list of all of our District pastors. Add a Dynamic Data block to the "Main" page region, with the following query (and where 53 is your version of the "District" group type:

SELECT DISTINCT
    p.[Id]
    ,p.[NickName] + ' ' + p.[LastName] AS "Name"
    , CASE WHEN @PastorId=p.[Id]
        THEN 1
        ELSE 0
      END AS "Current"
FROM [GroupMember] gm
    LEFT JOIN [Group] g ON gm.[GroupId]=g.[Id]
    LEFT JOIN [Person] p ON gm.[PersonId]=p.[Id]
WHERE
    g.[GroupTypeId]=53

Set Parameters to PastorId=0 and set the formatted output to the following:

<div class="panel panel-default list-as-blocks clearfix">
    <div class="panel-body">
        <ul>
            {% for row in rows %}
            <li{% if row.Current == 1 %} class="active"{% endif %}>
                <a title="" href="?PastorId={{ row.Id }}">
                    <img title="{{ row.Name }}" src="{% assign Person = row.Id | PersonById %}{{ Person.PhotoUrl }}" style="height:52px;width:52px;" />
                    <h3>{{ row.Name }}</h3>
                </a>
            </li>
            {% endfor %}
            <li{% unless PageParameter.PastorId > 0 %} class="active"{% endunless %}>
                <a title="" href="?PastorId=0">
                    <span style="font-size:52px;line-height:1em;">∞</span>
                    <h3>All</h3>
                </a>
            </li>
        </ul>
    </div>
</div>

Now you get a row of buttons with the pastor's photo and name displayed nicely (thank you list-as-blocks class) and whenever a button is clicked, the page will reload with ?PastorId set in the URL. It also adds an "All" button at the end of the pastor list that will act as kind of a superset. We'll use this in our other Dynamic Data blocks:

Before we go on, we need to get the Id of the "District Pastor" group role. Go to Admin Tools -> Power Tools -> SQL Command and run this command: SELECT Id,Name FROM [GroupTypeRole] WHERE [GroupTypeId]=53 (replacing 53 again with the group type of your districts). Make sure the name is correct, and make note of the Id it gives you. In the examples below you'll need to replace my GroupRoleId=45 with your own ID.

OK, let's get a list of groups for the currently-selected pastor. Add a Dynamic Data block to the Section B region of the page (the first of the three columns near the footer). Here's where we're going to get into a SQL feature called "Common Table Expressions" (CTE). Think of CTEs as a temporary table, and we're going to use a flavor called a "Recursive Common Table Expression" to loop through our group hierarchy, resulting in a list all of the groups and the ID of the leader no matter how many generations of groups are between the district and the group (in this case, the districts have children of our neighborhoods and grandchildren of our actual groups but it doesn't matter, our groups will be matched with the ultimate leader from the district despite being within a neighborhood)...very cool.

But don't worry, you don't need to understand what's happening (it took me a day to come up with this query and I couldn't tell you any more exactly how it works) - just copy and paste.

The query defines the CTE first, then runs a SELECT based on it. (This is the first place you'll need to replace GroupRoleId=45 with the District Pastor Role Id that we identified using the SQL Command tool above).

WITH GroupHeirarchy (Id, [Name], GroupTypeId, ParentGroupId, [Root], [DistrictPastorId]) AS
(
      SELECT
            g.Id,
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId,
            g.Id,
            gm.PersonId
      FROM
            [Group] g
            LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
        WHERE gm.[GroupRoleId]=45
      UNION ALL 
      SELECT
            g.Id, 
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId, 
            gh.[Root],
            gh.DistrictPastorId
      FROM
            [Group] g
      INNER JOIN GroupHeirarchy gh ON 
            g.ParentGroupId = gh.Id
)

SELECT DISTINCT g.[Id] ,g.[Name] "Group Name" FROM GroupHeirarchy gh LEFT JOIN [Group] g ON gh.[Id]=g.[Id] INNER JOIN [GroupMember] gm ON gm.[GroupId]=gh.[Id] JOIN [Person] p ON p.[Id]=gm.[PersonId] WHERE gm.[GroupRoleId]=24 AND (gh.[DistrictPastorId] = 0 OR gh.[DistrictPastorId]=@PastorId) ORDER BY g.[Name] ASC

Hide column Id, set Parameters to PastorId=0, and set the Selection URL to the "Districts" page we created in the last section with the GroupId given (for example, ~/Page/507?GroupId={Id} I also recommend turning off all of the grid actions as well as "Show Grid Filter". Now get into the block properties and set Pre-HTML to <h3>Groups</h3> to give it a nice title.

Now we'll use the same CTE for our second Dynamic Data block, in the Section C page zone, but with a different SELECT query:

WITH GroupHeirarchy (Id, [Name], GroupTypeId, ParentGroupId, [Root], [DistrictPastorId]) AS
(
      SELECT
            g.Id,
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId,
            g.Id,
            gm.PersonId
      FROM
            [Group] g
            LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
        WHERE gm.[GroupRoleId]=45
      UNION ALL 
      SELECT
            g.Id, 
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId, 
            gh.[Root],
            gh.DistrictPastorId
      FROM
            [Group] g
      INNER JOIN GroupHeirarchy gh ON 
            g.ParentGroupId = gh.Id
)

SELECT p.[Id] ,p.[NickName] + ' ' + p.[LastName] AS GroupLeaders ,'<a href="/page/507?GroupId=' + CONVERT(varchar(10), g.[Id]) + '">' + g.[Name] + '</a>' "Group" FROM GroupHeirarchy gh LEFT JOIN [Group] g ON gh.[Id]=g.[Id] INNER JOIN [GroupMember] gm ON gm.[GroupId]=gh.[Id] JOIN [Person] p ON p.[Id]=gm.[PersonId] WHERE gm.[GroupRoleId]=24 AND (gh.[DistrictPastorId] = 0 OR gh.[DistrictPastorId]=@PastorId) ORDER BY g.[Name],p.[LastName],p.[FirstName] ASC

Be sure to replace 507 in the href attribute with the Page ID of the "Districts" page we created first, and also replace 45 in the middle of the CTE with the GroupRoleId we found using SQL. As before, hide column Id, set Parameters to PastorId=0, but this time set the Selection Url to ~/Person/{Id}. Leave "Show Group Filter" selected, and this time check Person Report and activate all of the Grid Actions. Get into the Block Properties and set Pre-HTML to <h3>Leaders</h3>

Finally, add one more Dynamic Data block to the Section D page zone with the following query:

WITH GroupHeirarchy (Id, [Name], GroupTypeId, ParentGroupId, [Root], [DistrictPastorId]) AS
(
      SELECT
            g.Id,
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId,
            g.Id,
            gm.PersonId
      FROM
            [Group] g
            LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
        WHERE gm.[GroupRoleId]=45
      UNION ALL 
      SELECT
            g.Id, 
            g.[Name],
            g.GroupTypeId,
            g.ParentGroupId, 
            gh.[Root],
            gh.DistrictPastorId
      FROM
            [Group] g
      INNER JOIN GroupHeirarchy gh ON 
            g.ParentGroupId = gh.Id
)

SELECT p.[Id] ,p.[NickName] + ' ' + p.[LastName] AS GroupMembers ,'<a href="/page/507?GroupId=' + CONVERT(varchar(10), g.[Id]) + '">' + g.[Name] + '</a>' "Group" FROM GroupHeirarchy gh LEFT JOIN [Group] g ON gh.[Id]=g.[Id] INNER JOIN [GroupMember] gm ON gm.[GroupId]=gh.[Id] JOIN [Person] p ON p.[Id]=gm.[PersonId] WHERE gm.[GroupRoleId] <> 45 AND (gh.[DistrictPastorId] = 0 OR gh.[DistrictPastorId]=@PastorId) ORDER BY g.[Name],p.[LastName],p.[FirstName] ASC

Lest you accuse me of being unpredictable, I'm going to admonish you one more time to replace 507 in the href attribute with the Page ID of the "Districts" page we created first, hide column Id, set Parameters to PastorId=0, set the Selection Url to ~/Person/{Id}, leave "Show Group Filter" selected, check Person Report and activate all of the Grid Actions. This time, there are two places to replace 45 with the GroupRoleTypeId we looked up in SQL; once in the normal place halfway down the CTE, and once more near the end of the query. The only difference in the configuration (other than the query) is in Block Properties: set Pre-HTML this time to <h3>Group Members</h3>

Now, it's looking pretty good. There's one niggling problem- the layout associated with "Filter Options" on the "Leaders" or "Group Members" list doesn't quite work, with our Pre-HTML set...a line (the bottom border of the filter element) runs right through our title, and when we expand the filters, they wrap a little oddly around the title.

So let's tweak the style on this page a bit. Get into "Page Properties" (the cog icon on the Admin bar) and under "Advanced Settings", set the following "Header Content":

<style>
    div.grid-filter {border-bottom:0px;}
    div.grid-filter-entry {clear:left;}
</style>

Cool.

SmallGroupPortal-Pastors.png

Roster boxes

One Rock community member was looking for all of their groups in individual boxes, with the group name in bold at the top, leaders displayed first with photos, and all members below. Having run through the above solutions, I won't enumerate the entire process here, but all you need is a single DynamicData block which takes ParentGroupId=0 as a parameter with the following query:

SELECT [Name] FROM [Group] WHERE [Id]=@ParentGroupId;
SELECT
    g.[Id] "GroupId"
    ,g.[Name] "GroupName"
    ,gm.[PersonId] "PersonId"
    ,gm.[GroupRoleId] "RoleId"
FROM
    [Group] g
    LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
    LEFT JOIN [Person] p ON gm.[PersonId]=p.[Id]
WHERE
    g.[ParentGroupId]=@ParentGroupId
    AND g.[IsActive]=1
ORDER BY
    g.[Order]
    ,g.[Id]
    ,CASE gm.[GroupRoleId]
      WHEN 24 THEN 1
      ELSE 2
   END
   ,p.[LastName]
   ,p.[FirstName];
...and the following Formatted Output:
{% for row in table1.rows %}<h1 style="text-align:center;">{{ row.Name }}</h1>{% endfor %}
{% assign CurrentGroup = 0 %}
<div class="row">
{% for row in table2.rows %}
    {% if row.GroupId != CurrentGroup %}
        {% if CurrentGroup != 0 %}
            <p style="text-align:right;"><a class="btn-add btn btn-default btn-sm " href="/page/511?GroupMemberId=0&GroupId={{ row.GroupId }}"><i class="fa fa-plus-circle"></i></a><p></div></div>
        {% endif %}
        {% assign CurrentGroup = row.GroupId %}
        <div class="col-md-4"><div style="border:1px solid black;" /><h3 style="text-align:center;">{{ row.GroupName }}</h3>
    {% endif %}
    {% assign CurrentPerson = row.PersonId | PersonById %}
    {% if row.RoleId == '24' %}
        <img src="{{ CurrentPerson.PhotoUrl }}" alt="{{ CurrentPerson.FullName }}" style="height:3em;width:3em;" /><b>
    {% endif %}
    {{ CurrentPerson.FullName }}
    {% if row.RoleId == '24' %}
        </b>
    {% endif %}
    <br />
{% endfor %}
<p style="text-align:right;"><a class="btn-add btn btn-default btn-sm" href="/page/511?GroupMemberId=0&GroupId={{ CurrentGroup }}"><i class="fa fa-plus-circle"></i></a><p></div></div>
</div>

GroupRosters.png


Group Info (via Lava entities)

Another fun request to figure out was how to get a list of groups - something that may be public-facing - which showed names, leaders and group information. For a bit of variety, let's leave the SQL approach behind and do this one using Lava Entity commands in an HTML block. Once again, you'll need to link to (or hardcode) an appropriate ParentGroupId parameter in the URL


{% group dynamicparameters:'ParentGroupId' %}
    {% for group in groupItems %}
        <div class="col-md-4" style="padding:1em; text-align:center;">
            <div style="padding:0.5em; background-color:white; border:1px solid black; box-shadow:-5px 5px 5px #999;">
                <h3>{{ group.Name }}</h3>
                <div class="row">
                    {% groupmember where:'GroupId == {{ group.Id}} && GroupRoleId == 24' %}
                        {% for member in groupmemberItems %}
                            {% person where:'Id == {{ member.PersonId }}' %}
                                {% for person in personItems %}
                                    <div class="col-md-6" style="text-align:center;"><img src="{{ person.PhotoUrl }}" alt="{{ person.FullName }}" style="height:60px;width:60px;border-radius:30px;" /><h4>{{ person.FullName }}</h4></div>
                                {% endfor %}
                            {% endperson %}
                        {% endfor %}
                    {% endgroupmember %}
                </div>
                <p style="font-weight:bold;">{{ group.Description }}</p>
                Meeting Time: {{ group.Schedule.FriendlyScheduleText }}<br />
                Meeting Location:<br />
                {% assign theLocation = group.GroupLocations | First %}{{ theLocation.Location.Street1 }}<br />
                {% if theLocation.Location.Street2.Size > 0 %}
                    {{ theLocation.Location.Street2 }}<br />
                {% endif %}
                {{ theLocation.Location.City }}, {{ theLocation.Location.State }} {{ theLocation.Location.PostalCode }}
            </div>
        </div>
    {% endfor %}
{% endgroup %}

GroupEntities.png

Map

Rounding out the "typical" ways to display groups, let's touch on maps for a minute.

I did create a maps page which had a tree of districts and groups on the left, and their maps on the right. The nice thing was, when you clicked on a district, you could toggle sub-groups on and off, which included neighborhood outlines and group pins.

To accomplish this, use a Left Sidebar layout with a Group Tree View block in the sidebar, set to your Small Group parent group. That way, you're only seeing child groups of that parent (districts, neighborhoods and small groups, in my case). The Detail Page in the block settings should be set to this same page as the block is on. Then put a Group Map block in the main zone. Set the Group Page to the Districts page (or another page you've created which takes GroupId as a parameter to show your group's information), set the Person Profile Page to the normal person profile page, and the Map Page to this same page. That's about it!

It worked well, for what it was, but didn't really bring anything that the Districts page didn't, so it was rarely used and has been removed now.

Perhaps the biggest gotcha on this page was that the GroupTreeView block (in the left sidebar) will automatically load the page with the first group (sorted alphabetically) set as the default GroupId parameter, if you don't provide a different one. So there was no way to default the maps to all districts, short of re-creating the Group Tree ourselves in SQL.

But, if you're looking to put a map on one of your pages, perhaps this will help.

Others

There are, of course, any number of other ways to display your groups. When I come across novel or useful methods, I'll be sure to add them here. In the meantime, log into Slack and let me know if you come up with something you think I might be able to share, or if you want to tweak one of these methods and want some pointers.


@mikejed
Spark Development Network
Flagstaff, AZ

Michael Garrison recently left his job in Architecture to become one of the "new guys" at Spark (the "Core Team"), but he's still helping out Christ's Church of Flagstaff and other non-profits with tech needs in his off-hours, trying to make computers do what computers do best, so that people are freed to do what we do best: relate with people!