Recipe - Find & Filter for Volunteers by Schedule Preference

Skill level: Intermediate

Organization: Flatirons Community Church

Requires Rock: 1.12.0

---

{# strip images & classes from the HTML but otherwise leave structure #}

We use this to filter to find volunteers based on their Scheduling Preferences. They do not have to be scheduled. Volunteers must however be apart of a scheduling group and have scheduling preferences set on their profile.

I have attached screenshots of these instructions as well for a general idea of what is happening.

You will need these blocks: Page Parameter Filter &  Dynamic Data

Of course, adjust this code to meet your instance. 

Page Parameter Filter Block Configuration:

    Add the Groups, Schedules, and Location filters. There is SQL inserted into the Values of the Groups and Locations. 

    Groups: Field Type = Multi-Select, Key = Groups;  Insert this code for the Values of the Multi-Select.

Select 
	Concat(G.Name,' - ',C.Name) As [Text]
	, G.Id As [Value]
From [Group] G
Left Join Campus C On C.Id = G.CampusId
Where G.GroupTypeId = *Your Scheduling Group Type Here*
And G.IsActive = 1
And G.IsArchived = 0
Order By G.Name

    

    Schedules: Field Type = Schedules, Key = Schedules; No code to add to this.

    Locations: Field Type = Multi-Select, Key = Locations;  Insert this code for the Values of the Multi-Select. 

Select 
	L.Id As [Value]
	, Case When PL.Id is not null then Concat(L.[Name],' - ',PL.[Name]) Else L.[Name] End As [Text] 
From [Location] L
Left Join [Location] PL On PL.Id = L.ParentLocationId
Where L.[Name] is not null
And L.[Name] != ''
Order By L.Id

Dynamic Data Block Configuration:

    Insert this code into the block. It is a Person report and there are no additional settings set on the block. 

{% assign schedule = PageParameter.Schedules | Split:',' %}
{% assign scheduleFirst = schedule | First %}
{% assign location = PageParameter.Location %}
{% assign groups = PageParameter.Groups  %}
{% assign locations = PageParameter.Locations %}

{% if groups != null and groups != empty %}
    Select 
    	P.Id
    	, Concat(P.FirstName,' ',P.LastName) As [Name]
    	, G.Name As [Group Name]
    	, STRING_AGG(S.Name, ', ') As [Schedule]
    	, STRING_AGG(L.Name, ', ') As [Location]
    From GroupMember GM
    Inner Join [Group] G On G.Id = GM.GroupId
    Inner Join Person P On P.Id = GM.PersonId
    Inner Join GroupMemberAssignment GMA On GMA.GroupMemberId = GM.Id
    Inner Join Schedule S On GMA.ScheduleId = S.Id
    Inner Join [Location] L On GMA.LocationId = L.Id
    Where G.Id In ({{groups}})
    {% if scheduleFirst != null %}
        And(
        {% for item in schedule %}
            {% if forloop.first %}
            (Cast(S.Guid As nvarchar(max)) = '{{item | SanitizeSql}}')
            {% else %}
            Or (Cast(S.Guid As nvarchar(max)) = '{{item | SanitizeSql}}')
            {% endif %}
        {% endfor %}
        )
    {% endif %}
    {% if location != null and location != empty %}
    And L.Guid = '{{location}}'
    {% endif %}
    {% if locations != null and locations != empty %}
    And L.Id In ({{locations}})
    {% endif %}
    
    Group By P.Id, P.FirstName, P.LastName, G.Name
    
    Order By P.LastName
    
{% endif %}


    

Credit to those who helped make this. I authored this recipe on behalf of those who helped make it.

Screenshots

• /GetImage.ashx?guid=118c9752-0ea3-40d2-b5c2-73e07a460c48
• /GetImage.ashx?guid=05ec15c1-b761-42be-8685-9450894842aa
• /GetImage.ashx?guid=315b8540-f0e8-4547-a41a-80e8316b429b