4 Improving Rock's Group Coaching Shared by Thomas Stephens, Northview Church 3 years ago 7.0 Small Groups Intermediate Intro My hope isn't that you would just copy and paste everything here (that wouldn't work anyway). My hope is that this would spark ideas and conversations for you to have with ministries at your church. Then take this recipe and mold it to your needs. Group Coaching Setup - Basic Setup Rock has some basic group coaching tools built in. They revolve around the idea of "Parent" groups. I attached a screen shot below of three groups. 'Small Groups', 'COACH: Thomas Stephens', and 'Jon Edmiston'. 'Small Groups' is the parent group to 'COACH: Thomas Stephens' and 'COACH: Thomas Stephens' is the parent group of 'Jon Edmiston'. In this example 'Jon Edmiston' is a small group and Jon Edmiston is the leader of that group. 'COACH: Thomas Stephens' is the parent group and Thomas Stephens is the coach. Thomas Stephens will be in that group marked as a leader. * Important tip: Create a new group type for your coaches. This helps with reporting and is required to complete the advanced portion below. After your group structure is setup correctly with small groups having parent groups where the coach is the leader. Your next step is to navigate the the page where attendance is taken and edit the block settings. Near the bottom of the block settings you will see 'Send Summary Email To'. This is where you can check the 'Parent Group Leader' box. So, at this point we have our groups structure nicely in Rock's group viewer. We have it set so that coaches will get attendance summary reports when a group leader submits attendance. Group Coaching Setup - Advanced Setup - External Website Overview We wanted to set this up so that group coaches did not need internal Rock access. They would have a dashboard that they could visit and see usefull information about the groups they were coaching. What your church decides is important information to display on the dashboard will most likely be different. Lava will be used to get and format any information you want to display. SQL will be used to get the groups. We also created a group health score. I will describe what we did, but again, what your church wants to use to calculate a group health score will probably be different. I have attached an image of our coach dashboard. Dashboard SQL is used to get the groups that the current person is the leader of. They also have to be the coach group type. I am just getting the group's id and then using lava to get the group object. {% sql return:'rows' %} SELECT g.Id,g.[Name] FROM [Group] g JOIN [Group] g2 ON g2.Id=g.ParentGroupId JOIN GroupMember gm ON gm.GroupId=g2.Id WHERE gm.GroupRoleId = 581 AND gm.PersonId = {{ CurrentPerson | Property:'Id' }}; {% endsql %} <h3>Group List</h3> <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Group Name</th> <th>Schedule</th> <th>Willing To Add</th> <th>Last Coach Check-in</th> <th>Group Health</th> <th>Coach Check-in</th> </tr> {% for row in rows %} {% assign group = row.Id | GroupById %} <tr> <!-- Group Name --> <td> <a href="https://northviewchurch.us/page/2400?GroupId={{ row.Id }}"> {{ group | Property:'Name' }} </a> </td> <!-- Schedule --> <td> {{ group.Schedule.FriendlyScheduleText }} </td> <td> {{ group | Attribute:'Howmanyareyouwillingtoadd' }} </td> <!-- Last Coach Note --> <td> {% note where:'NoteTypeId == 58 && EntityId == {{ row.Id }}' sort:'ModifiedDateTime desc' limit:'1' %} {% for note in noteItems %} {{note.ModifiedDateTime}} - {{ group | Attribute:'CoachAssessment' }} - {{note.Text}} {% endfor %} {% endnote %} </td> {% assign score = 0 %} {% assign score = group | Attribute:'GroupHealthScore' | AsInteger %} {% if score >= 2 %} {% assign color = 'Green' %} {% elseif score >= 0 %} {% assign color = 'Yellow' %} {% else %} {% assign color = 'Red' %} {% endif %} <td bgcolor="{{color}}"> </td> <!-- Check in --> <td> <a href="https://northviewchurch.us/groupcheckin?Group={{ group.Guid }}&Coach={{ Person | Property:'PrimaryAlias.Guid' }}" target="_blank">Click Here</a> </td> </tr> {% endfor %} </table> Group Name Link Page On the dash board you will notice that the group name is a link to a different page. This page is a group detail page. One thing our groups team wanted was to only show contact info for group leaders. {% sql return:'rows' %} DECLARE @groupId int = {{ 'Global' | PageParameter:'GroupId' }}; SELECT p.FirstName ,p.LastName ,p.Email ,ph.Number ,gtr.Name 'Role' ,gtr.Id 'RoleId' ,FORMAT(gm.DateTimeAdded,'MM/dd/yy') 'DateAdded' ,( SELECT TOP(1) FORMAT(a.StartDateTime,'MM/dd/yy') FROM Attendance a JOIN AttendanceOccurrence ao ON a.OccurrenceId = ao.Id JOIN PersonAlias pa ON a.PersonAliasId = pa.Id WHERE ao.GroupId = @groupId AND a.DidAttend = 1 AND pa.PersonId = p.Id ORDER BY StartDateTime DESC ) 'LastAttended' ,( SELECT TOP(1) FORMAT(a.StartDateTime,'MM/dd/yy') FROM Attendance a JOIN AttendanceOccurrence ao ON a.OccurrenceId = ao.Id JOIN PersonAlias pa ON a.PersonAliasId = pa.Id WHERE ao.GroupId = @groupId AND a.DidAttend = 1 AND pa.PersonId = p.Id ORDER BY StartDateTime ASC ) 'FirstAttended' FROM Person p JOIN GroupMember gm ON p.Id = gm.PersonId LEFT JOIN PhoneNumber ph ON p.Id = ph.PersonId AND ph.NumberTypeValueId = 12 JOIN GroupTypeRole gtr ON gm.GroupRoleId = gtr.Id WHERE gm.GroupId = @groupId AND gm.IsArchived = 0 AND gm.GroupMemberStatus = 1; {% endsql %} {% sql return:'mylist' %} DECLARE @groupId int = {{ 'Global' | PageParameter:'GroupId' }}; SELECT TOP(3) n.[Text] as Text,n.[ModifiedDateTime] as Date FROM [Note] n WHERE n.[NoteTypeId] = 58 AND n.[EntityId] = @groupId ORDER BY Date DESC {% endsql %} {% assign group = 'Global' | PageParameter:'GroupId' | GroupById %} {% assign emailListAll = '' %} {% assign emailListLeader = '' %} {% assign emailListMember = '' %} <h3>{{group.Name}}</h3> {% assign color = group | Attribute:'GroupHealth' %} <b>Overall Group Health:</b> {{color}}<br> <br> <b>Spiritual Growth:</b> {{ group | Attribute:'SpiritualGrowth' }}<br> <b>Building Community:</b> {{ group | Attribute:'BuildingCommunity' }}<br> <b>Serving Together:</b> {{ group | Attribute:'ServingTogether' }}<br> <b>Life Group Leader Pulse:</b> {{ group | Attribute:'LifeGroupLeaderPulse' }}<br> <br> <b><a href="https://northviewchurch.us/page/2401?GroupId={{group.Id}}" target="_blanl">View Attendance History</a></b><br> <b><a href="https://northviewchurch.us/groupcheckin?Group={{ group.Guid }}&Coach={{ Person | Property:'PrimaryAlias.Guid' }}" target="_blanl">Complete a Check-in</a></b><br><br> <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Name</th> <th>Role</th> <th>Email</th> <th>Mobile Phone</th> <th>Date Added</th> <th>First Attended</th> <th>Last Attended</th> </tr> {% for row in rows %} {% assign isLeader = 'False' %} {% if row.RoleId == 272 or row.RoleId == 479 or row.RoleId == 521 or row.RoleId == 523 %} {% assign isLeader = 'True' %} {% endif %} {% assign group = row.Id | GroupById %} <tr> <td> {{ row.FirstName }} {{ row.LastName }} </td> <td> {{ row.Role }} </td> <td> {% if isLeader == 'True' %} <a href="mailto:{{ row.Email }}">{{ row.Email }}</a> {% endif %} </td> <td> {% if isLeader == 'True' %} {{ row.Number }} {% endif %} </td> <td> {{ row.DateAdded }} </td> <td> {{ row.FirstAttended }} </td> <td> {{ row.LastAttended }} </td> </tr> {% if row.Email != '' %} {% assign emailListAll = emailListAll | Append:row.Email | Append:';' %} {% if isLeader == 'True' %} {% assign emailListLeader = emailListLeader | Append:row.Email | Append:';' %} {% endif %} {% endif %} {% endfor %} </table> <br> <b><a href="mailto:{{emailListLeader}}">Email Group Leaders</a></b><br> <br> <b>Previous Notes</b><br> {% for item in mylist %} {{ item.Date }} - {{ item.Text }}<br> {% endfor %} View Attendance History Page This is a link to a page that has the 'Group Attendance List' block on it. No custom work here, just using a stock Rock block. You will also need to add the pages for editing and entering attendance. These are the same pages that exist in your group leader tool box, all Rock blocks. Complete A Check-in Page This page is used in several places. It is the page where a coach will submit a group checkin and complete the health score. I will attach the exported workflow. Just remember that you will need to create the group attributes that some of this information is stored in. Also, I created a note type for coach notes. Group Leaders Button (insert any role you would like) This button takes the coach to a page that will list all of the group leaders that they coach. The idea behind this page is so that the coach can email all their leaders at once. You can alter this to be any group role you would like and duplicate and have as many buttons as you would need. {% sql return:'rows' %} SELECT g.Id, g.[Name], gtr.Name 'Role', gm2.PersonId, FORMAT(gm2.DateTimeAdded,'MM/dd/yy') 'DateAdded', ( SELECT TOP(1) FORMAT(a.StartDateTime,'MM/dd/yy') FROM Attendance a JOIN AttendanceOccurrence ao ON a.OccurrenceId = ao.Id JOIN PersonAlias pa ON a.PersonAliasId = pa.Id WHERE ao.GroupId = g.Id AND a.DidAttend = 1 AND pa.PersonId = gm2.PersonId ORDER BY StartDateTime DESC ) 'LastAttended', ( SELECT TOP(1) FORMAT(a.StartDateTime,'MM/dd/yy') FROM Attendance a JOIN AttendanceOccurrence ao ON a.OccurrenceId = ao.Id JOIN PersonAlias pa ON a.PersonAliasId = pa.Id WHERE ao.GroupId = g.Id AND a.DidAttend = 1 AND pa.PersonId = gm2.PersonId ORDER BY StartDateTime ASC ) 'FirstAttended' FROM [Group] g JOIN [Group] g2 ON g2.Id=g.ParentGroupId JOIN GroupMember gm ON gm.GroupId=g2.Id JOIN GroupMember gm2 ON gm2.GroupId=g.Id JOIN GroupTypeRole gtr ON gtr.Id=gm2.GroupRoleId WHERE gm.GroupRoleId = 581 AND gm.PersonId = {{ CurrentPerson | Property:'Id' }} AND gm.IsArchived = 0 AND gm.GroupMemberStatus = 1 AND gm2.IsArchived = 0 AND gm2.GroupMemberStatus = 1 AND gm2.GroupRoleId IN (272,479,521,523); {% endsql %} {% assign group = 'Global' | PageParameter:'GroupId' | GroupById %} {% assign emailListAll = '' %} <h3>Leaders</h3> <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Name</th> <th>Group</th> <th>Role</th> <th>Email</th> <th>Mobil Phone</th> <th>Date Added</th> <th>First Attended</th> <th>Last Attended</th> </tr> {% for row in rows %} {% assign group = row.Id | GroupById %} {% assign person = row.PersonId | PersonById %} {% assign email = person | Property:'Email' %} <tr> <td> {{ person | Property:'FullName' }} </td> <td> <a href="https://northviewchurch.us/page/2400?GroupId={{row.Id}}">{{ row.Name }}</a> </td> <td> {{ row.Role }} </td> <td> <a href="mailto:{{ email }}">{{ email }}</a> </td> <td> {{ person | PhoneNumber:'Mobile' }} </td> <td> {{ row.DateAdded }} </td> <td> {{ row.FirstAttended }} </td> <td> {{ row.LastAttended }} </td> </tr> {% if email != '' %} {% assign emailListAll = emailListAll | Append:email | Append:';' %} {% endif %} {% endfor %} </table> <br> <b><a href="mailto:{{emailListAll}}">Email All Group Leaders</a></b><br> <br> Group Coaching Setup - Advanced Setup - Internal Website Internal Coach List In most cases our staff team would be coaching the coaches. So they would also need a way to make sure that the coaches were doing their check-ins and also keep an eye over everything. So we built two pages to accomplish this. One was a coach list which could be filtered by campus. The other was a group health check page. This page would visible show all of the group health scores for each coach by campus. First the coach list. {% assign campus = 'Global' | PageParameter:'CampusId' %} {% if campus == '' %} {% assign campus = '(1,2,3,4,5,6,7,25,26,27)' %} {% endif %} {% sql return:'rows' %} SELECT gm.PersonId, gm.GroupId,g.CampusId FROM GroupMember gm JOIN [Group] g ON g.Id=gm.GroupId WHERE gm.GroupRoleId = 581 AND g.CampusId IN {{campus}}; {% endsql %} {% assign emailListAll = '' %} <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Coach</th> <th>Campus</th> <th>Number of Groups Coaching</th> <th>Contact Coach</th> </tr> {% for row in rows %} {% assign person = row.PersonId | PersonById %} {% assign email = person | Property:'Email' %} <tr> <td> <a href="/GroupCoaches/Detail?CoachGroup={{ row.GroupId }}"> {{person | Property:'FullName'}} </a> </td> <td> {{person | Campus | Property:'Name'}} </td> <td> {% group where:'ParentGroupId == {{ row.GroupId }} && IsActive == true && IsArchived == false' count:'true' %} <a href="https://rock.northviewchurch.us/page/501?GroupId={{ row.GroupId }}&ExpandedIds=571"> {% assign groupName = row.GroupId | GroupById | Property:'Name' %} {{ count }} </a> {% endgroup %} </td> <td> <a href="mailto:{{email}}">Email Coach</a> </td> </tr> {% if email != '' %} {% assign emailListAll = emailListAll | Append:email | Append:';' %} {% endif %} {% endfor %} </table> <br> <h4><b><button class="btn btn-primary btn-size" href="mailto:{{emailListAll}}">Email List</button></b><br></h4> <br> Then if you click on a coach I am using a dynamic data block. Not for any particular reason. It was on of the first pages I made in this project and if I were to do it again, I would just do an HTML block. Query {% assign gpId = 'Global' | PageParameter:'CoachGroup' %} SELECT gp.Id, gp.[Name] FROM [Group] gp WHERE gp.ParentGroupId = {{gpId}} AND gp.IsActive = 1 AND gp.IsArchived =0; Formatted Output <table class="table table-bordered table-striped table-hover table-sort"> <tr> <th>Group Name</th> <th>Schedule</th> <th>Last Coach Check-in</th> <th>Group Health</th> <th>Coach Check-in</th> </tr> {% for row in rows %} {% assign group = row.Id | GroupById %} <tr> <!-- Group Name --> <td> <a href="https://rock.northviewchurch.us/page/501?GroupId={{ row.Id }}&ExpandedIds=571"> {{ group | Property:'Name' }} </a> </td> <!-- Schedule --> <td> {{ group.Schedule.FriendlyScheduleText }} </td> <!-- Last Coach Note --> <td> {% note where:'NoteTypeId == 58 && EntityId == {{ row.Id }}' sort:'ModifiedDateTime desc' limit:'1' %} {% for note in noteItems %} {{note.ModifiedDateTime}} - {{ group | Attribute:'CoachAssessment' }} - {{note.Text}} {% endfor %} {% endnote %} </td> {% assign score = 0 %} {% assign score = group | Attribute:'GroupHealthScore' | AsInteger %} {% if score >= 2 %} {% assign color = 'Green' %} {% elseif score >= 0 %} {% assign color = 'Yellow' %} {% else %} {% assign color = 'Red' %} {% endif %} <td bgcolor="{{color}}"> </td> <!-- Check in --> <td> <a href="https://northviewchurch.us/groupcheckin?Group={{ group.Guid }}&Coach={{ Person | Property:'PrimaryAlias.Guid' }}" target="_blank">Click Here</a> </td> </tr> {% endfor %} </table> Internal Group Health Dashboard This page is where a campus staff person could check on all of their coaches. Under each coach, the groups are sorted by the date they last completed a check-in. With the oldest one first (or a group without a check-in) <script> function sortTable(tableId) { var table, rows, switching, i, x, y, shouldSwitch,value1,value2; table = document.getElementById(tableId); switching = true; /* Make a loop that will continue until no switching has been done: */ while (switching) { // Start by saying: no switching is done: switching = false; rows = table.rows; /* Loop through all table rows (except the first, which contains table headers): */ for (i = 1; i < (rows.length - 1); i++) { // Start by saying there should be no switching: shouldSwitch = false; /* Get the two elements you want to compare, one from current row and one from the next: */ x = rows[i].getElementsByTagName("TD")[2]; y = rows[i + 1].getElementsByTagName("TD")[2]; value1 = x.innerHTML.toLowerCase(); value2 = y.innerHTML.toLowerCase(); value1 = value1.replace(/(\r\n|\n|\r)/gm,"").trim(); value2 = value2.replace(/(\r\n|\n|\r)/gm,"").trim(); if (value1 == '') { value1 = '1/1/0001 00:00:00';} if (value2 == '') { value2 = '1/1/0001 00:00:00';} // Check if the two rows should switch place: if ((new Date(value1).getTime()) > (new Date(value2).getTime())) { // If so, mark as a switch and break the loop: shouldSwitch = true; break; } } if (shouldSwitch) { /* If a switch has been marked, make the switch and mark that a switch has been done: */ rows[i].parentNode.insertBefore(rows[i + 1], rows[i]); switching = true; } } } </script> {% assign campus = 'Global' | PageParameter:'CampusId' %} {% if campus != '' %} {% sql return:'rows' %} SELECT g.Id FROM [Group] g WHERE g.GroupTypeId = 198 AND g.CampusId IN {{campus}} AND g.IsActive = 1 AND g.IsArchived = 0; {% endsql %} {% endif %} {% assign emailListAll = '' %} {% for row in rows %} {% assign group = row.Id | GroupById %} {% sql return:'rows2' %} SELECT g.Name,g.Id FROM [Group] g WHERE g.ParentGroupId = {{group.Id}} AND g.IsActive = 1 AND g.IsArchived = 0; {% endsql %} <h3><b>{{group.Name}}</b></h3> <table id="{{group.Id}}" class="table table-bordered table-striped table-hover table-sort"> <tr> <th style="width: 15%;">Group</th> <th style="width: 15%;">Schedule</th> <th style="width: 15%;">Last Check-in Date</th> <th style="width: 50%;">Last Check-in Notes</th> <th style="width: 5%;">Group Health</th> </tr> {% for row2 in rows2 %} {% assign group2 = row2.Id | GroupById %} <tr> <td> <a href="https://rock.northviewchurch.us/page/501?GroupId={{ group2.Id }}&ExpandedIds=571"> {{ group2 | Property:'Name' }} </a> </td> <td> {{ group2.Schedule.FriendlyScheduleText }} </td> {% assign lastDate = '' %} {% assign lastNote = '' %} {% note where:'NoteTypeId == 58 && EntityId == {{ group2.Id }}' sort:'ModifiedDateTime desc' limit:'1' %} {% for note in noteItems %} {% assign lastDate = note.ModifiedDateTime %} {% assign lastNote = note.Text %} {% assign lastNote = lastNote | EscapeDataString %} {% assign lastNote = lastNote | Replace:'%20',' ' %} {% assign lastNote = lastNote | Replace:'%0A','<br>' %} {% assign lastNote = lastNote | Replace:'%28','(' %} {% assign lastNote = lastNote | Replace:'%29',')' %} {% endfor %} {% endnote %} <td> {{lastDate}} </td> <td> {{lastNote}} </td> {% assign score = group2 | Attribute:'GroupHealthScore' | AsInteger %} {% if score >= 2 %} <td bgcolor="Green"> {% elseif score >= 0 %} <td bgcolor="Yellow"> {% elseif score < 0 %} <td bgcolor="Red"> {% else %} <td > {% endif %} </td> </tr> {% endfor %} </table> <script> sortTable({{group.Id}}); </script> {% endfor %} And that's it! I know that is a lot. This project is actually still on going at Northview. With little tweaks here and there as the groups team roles this out. Please reach out with any questions thomas.stephens@northviewchurch.us. Again, I hope that this can spark some ideas for you! Download File