Problem to Solve

Viewing the serving schedule isn't very accessible on the external page. You have to go to the internal site, have access to the schedule status board, navigate to the team you're trying to view, and then look at it. To do that, every volunteer needs the proper access to the internal site. So we wanted to create an easy-to-use, less-clicks way to view the serving schedule.

The Solution

Add a tab to the Group Detail page titled "Schedule". Use a Dynamic Data block and Page Parameter Filter in order to display the schedule for a given date.

How-To

1. Create a new set of pages for the serving teams.

• Reason: So other groups that use these pages don't also get edited.
• For us, we used the same pages for our small grouops. So when we edited the block for serving teams, it did the same for small groups which we did not want.
• Go to "Pages" in "CMS Configuration" and following this path to find the pages to copy: External Homepage >> Support Pages >> My Account >> Group Toolbox. Copy these (and all child pages) and rename to "Serving Team Toolbox".

2. Add a page called "Serving Team Schedule"

• Reason: We need a new page to put the schedule on.
• I copied the "Group Attendance" page (not the child pages with it) and just edited the blocks from there.

3. Create a new lava file that copies the "GroupDetail.lava" file so you can edit it to specifically serve your serving teams. We'll call the file "ServingTeamDetail.lava".

• Reason: So you can edit the tabs that show up.
• Use the attached lava file and edit to fit your situation. For us, we commented out the "Attendance" tab as that does not apply for us. But if it applies for you, you can "un-comment" it.
• If you want to find the original GroupDetail.lava file, follow the following path from the internal site home page to find the file: Admin Tools >> CMS Configuration >> File Manager >> Themes >> [YOUR CHURCH NAME]External >> Assets >> Lava >> Find file titled "GroupDetail.lava"

4. In the "Group Detail Lava" block, change the file page to "~~/Assets/Lava/ServingTeamDetail.lava"

5. Add a Dynamic Data block

• Add the following SQL code to the query:

{% assign groupId = PageParameter.GroupId | SanitizeSql %}
{% assign scheduleDate = PageParameter.ScheduleDate | SanitizeSql | SundayDate %}
{% assign serviceTime = PageParameter.ServiceTime | SanitizeSql %}
{% assign position = PageParameter.PositionLocation | SanitizeSql %}
DECLARE @nextSunday DATE = CONVERT(date, DATEADD(day, 1, GETDATE() - DATEPART(dw, GETDATE()) + CASE WHEN DATEPART(dw, GETDATE()) < 1 THEN 0 ELSE 7 END)) -- Used so that if a non-Sunday date is selected, the upcoming Sunday will be used
    
SELECT p.Id AS 'PersonId',
    p.FirstName +' ' + p.LastName AS 'FullName',
    p.Email,
    sch.Name AS 'ServiceTime',
    loc.Name AS 'Position/Location',
    (CASE 
        WHEN a.RSVP = 0 THEN 'Declined'
        WHEN a.RSVP = 1 THEN 'Accepted'
        WHEN a.RSVP = 3 THEN 'Pending'
    END) AS 'InviteStatus',
        ao.OccurrenceDate AS 'ScheduleDate'
FROM Attendance a
JOIN AttendanceOccurrence ao ON ao.Id = a.OccurrenceId
JOIN Schedule sch ON sch.Id = ao.ScheduleId
JOIN [Location] loc ON loc.Id = ao.LocationId
JOIN PersonAlias pa ON pa.Id = a.PersonAliasId
JOIN Person p ON p.Id = pa.PersonId
WHERE ao.GroupId = '{{ groupId }}' AND 
    ao.OccurrenceDate =
    {% if scheduleDate == null %} -- If scheduleDate filter isn't being used, default to upcoming Sunday
    @nextSunday 
    {% else %} -- If scheduleDate filter IS being used, use it (if it's not a Sunday, will automatically fill in upcoming Sunday from date selected)
    '{{ scheduleDate }}'
    {% endif %}
    AND
    a.RequestedToAttend = 1 --Means they were scheduled
    {% if serviceTime != null %} -- If the ServiceTime filter is being used, then add to "WHERE" statement
    AND sch.Name = '{{ serviceTime }}'
    {% endif %}
    {% if position != null %} -- If the Position filter is being used, then add to "WHERE" statement
    AND loc.Guid = '{{ position }}'
    {% endif %}
ORDER BY
    (CASE 
        WHEN sch.Name = 'Sunday 8:30am' THEN 1
        WHEN sch.Name = 'Sunday 10:00am' THEN 2
        WHEN sch.Name = 'Sunday 11:30am' THEN 3
    END), -- Orders so that services times are in chronological order
    (CASE
        WHEN loc.Name LIKE '%Coordinator%' THEN 1
        WHEN loc.Name LIKE '%Team Leader%' THEN 2
        ELSE 3
    END), -- Orders so that Coordinators and Team Leaders are listed first (after service time ordering)
    loc.Name, -- Order by Position/Location
    p.FirstName ASC -- Order by FirstName

6. Add a Page Parameter Filter block

Here are the three filters I used:

• Date: 
• Service Time: 
• Position/Location: 

Here's the code from the last screenshot:

{% assign groupId = PageParameter.GroupId | SanitizeSql %}
{% assign scheduleDate = PageParameter.ScheduleDate | SanitizeSql | SundayDate %}
{% assign serviceTime = PageParameter.ServiceTime | SanitizeSql %}
DECLARE @nextSunday DATE = CONVERT(date, DATEADD(day, 1, GETDATE() - DATEPART(dw, GETDATE()) + CASE WHEN DATEPART(dw, GETDATE()) < 1 THEN 0 ELSE 7 END))
    
SELECT loc.Guid AS 'Value',
    loc.Name AS 'Text'
FROM Attendance a
JOIN AttendanceOccurrence ao ON ao.Id = a.OccurrenceId
JOIN Schedule sch ON sch.Id = ao.ScheduleId
JOIN [Location] loc ON loc.Id = ao.LocationId
JOIN PersonAlias pa ON pa.Id = a.PersonAliasId
JOIN Person p ON p.Id = pa.PersonId
WHERE ao.GroupId = '{{ groupId }}' AND 
    a.RequestedToAttend = 1 --Means they were scheduled
GROUP BY loc.Guid, loc.Name
ORDER BY loc.Name

Obviously, edit it to meet your specific needs. But hopefully this is a helpful start. The biggest downside in my opinion is the visual side, it just looks like a spreadsheet which I don't love. So, if you find a way to do this better, please let me know!