Notify group leaders of new members once a day

  • By Michael Garrison 11 Months Ago

The question was asked in Slack recently, how to notify group leaders of new members, without sending a separate email for every single person. The only solution we could come up with was to send a "daily digest" email each evening to leaders whose groups got a new member that day. That would mean that your single workflow would need to be able to examine any number of groups and deal with them intelligently.

But it's been an elusive quest in the community to be able to have a workflow iterate over groups, group members, etc, as Workflows tend to be rather linear.

Kevin Rutledge and I bounced some ideas back and forth though, and came up with the idea to get a list of such groups, and go through that list one row at a time using SQL's offset syntax. To do this, we just needed to store the current offset in a Workflow Attribute. We also needed to store an arbitrary list of people returned by SQL in a comma-separated list, rather than the typical "one row per result".

Kevin took it from there and with some additional back-and-forth chatting between us, developed this workflow that manages to do all of this and arrive at our "Daily Digest" email for any number of groups of a specified type. There are a number of novel things working together in here, so don't miss anything!

The approach:

We're going to set up a Workflow that gets triggered by a scheduled job. So let's make a new workflow! (In the screenshots, you'll see that we built this in the "Samples" section of the workflows on the demo server, but you can put it wherever you like.

Workflow details:

When we tested this, we set the workflow up to be Automatically Persisted. I think that's actually optional, so if you don't need a record of which groups were iterated over, try un-checking this option to keep things clean.

GMAdded-Details.JPG

Now we need to set up some Workflow attributes. There are only two of them, but they're where the magic happens.

Attribute Attribute Key Field Type Values Default
Iterator Iterator Integer   0
Default Group Type DefaultGroupType Multi-select
SELECT
    Id as Value,
    [Name] As Text
FROM
    [GroupType]
Where
    ShowInGroupList = 1
    and IsSystem = 0
(your choice)

Now, that "Default Group Type" row may look pretty unusual to you. It's really nice though - it's going to let you select which group types you'd like to enable this reporting on, without having to look up GroupTypeIds. Once you copy the Values query above into the attribute, you'll get a series of checkboxes to choose the default value(s).

GMAdded-GroupType.JPG

In this example, we selected "Serving Team" and "Small Group" group types. Feel free to select any default group types as you wish.

Now let's start on the "Start" activity (this is actually going to be the only activity in the workflow). This activity is going to run once for every group which has had people added in the last 24 hours, so we need to store the group-specific attributes in Activity Attributes

Attribute Attribute Key Field Type Default
Group Members GroupMembers Memo  
Group Leader Email GroupLeaderEmail Text  
Group Leader Names GroupLeaderNames Text  
Group Group Group  

Now we can start in on the actions.

Get Group

We're going to use a SQL Run action to get a list of groups which have new members. BUT we're going to use a fancy OFFSET parameter to get just one row at a time. (We track which row to get using the Iterator Workflow Attribute).

SELECT
    DISTINCT g.[Guid]
FROM
    [Group] g
Join [GroupMember] gm
    On gm.GroupId = g.Id
Join [GroupTypeRole] gr
    On gm.GroupRoleId = gr.Id
Join
    [Person] p
    On gm.PersonId = p.Id
WHERE
    g.[GroupTypeId] in ({{ Workflow | Attribute:'DefaultGroupType','RawValue'}})
    and gr.IsLeader = 1
    and p.Email != ''
    and p.Email IS NOT NULL
    and g.Id in(
        SELECT 
            DISTINCT gm.[GroupId]
        FROM
            [GroupMember] gm
        WHERE 
            gm.[CreatedDateTime] > '{{ 'Now' | DateAdd:-1450,'m' | Date:'yyyy-MM-dd HH:mm:ss' }}'
    )
ORDER BY
    g.[Guid]
OFFSET
    {{ Workflow | Attribute:'Iterator' }} ROWS FETCH NEXT 1 ROWS ONLY

Store the result of this query in Group.

This query actually does a lot - in addition to only including groups which have had new members added in the last 24 hours (ok, 24 hours and 10 minutes, just to be safe), it also excludes groups without a leader with an email address. So any group returned by this query is one where we know we can send an email to the leader.

Also, we're returning the Group GUID and storing it in a Group Attribute, so it's a really useful attribute - it stores the group object itself, not just the name. We'll use the name later on, but you can run any of your normal property and attribute filters on this attribute and get data back about the group.

GMAdded-GetGroup.JPG

Complete if no groups found

If that query doesn't find any groups, then congratulations, you've reached the end of the list. We'll check for that condition in the Run if filter, and if Group is blank, we'll run a Workflow Complete action so it stops processing.

GMAdded-Complete.JPG

Get Group Members

OK, so we know that the current group has some members we need to report on. Let's find them and store them in an attribute. Here's the query for another SQL Run action:

SELECT (
    REPLACE(
        REPLACE(
            (SELECT 
                [PersonId] 
            FROM [GroupMember]
            WHERE [GroupId] = {{ Activity | Attribute:'Group','Id' }}
            AND [CreatedDateTime] > '{{ 'Now' | DateAdd:-1450,'m' | Date:'yyyy-MM-dd HH:mm:ss' }}'
             FOR XML AUTO)
            ,'<GroupMember PersonId="',''
        )
        ,'"/>',','
    )
)

This is another tricky query: instead of returning rows of results, it's returning a single string of output like <GroupMember PersonId="1"/><GroupMember PersonId="2"/>, so we're having SQL find and replace some characters so that we end up with a nice comma-separated list of Person IDs.

So store that output in Group Members.

GMAdded-GetMembers.JPG

Get Group Leader Emails

Now we need to get a comma-separated list of email addresses for the leaders of the group. BE AWARE that this is going to e-mail any leaders of groups with new members, REGARDLESS of any e-mail preferences the leader has set. The idea is that your leaders should be getting this communication even if they've unsubscribed from your lists, which would be what most churches want, but you need to be aware of this behavior. Here's the query for yet another SQL Run action:

SELECT (
    REPLACE(
        REPLACE(
            (SELECT 
    p.Email

FROM [GroupMember] gm Join [Person] p on gm.PersonId = p.Id Join [Group] g On gm.GroupId = g.Id Join [GroupTypeRole] gr On gm.GroupRoleId = gr.Id

Where gr.IsLeader = 1 and p.[Email] != '' and p.[Email] IS NOT NULL and gm.[GroupId] = {{ Activity | Attribute:'Group','Id' }} For XML auto) ,'<p Email="','' ) ,'"/>',',' ) )

Again, we're using the FOR XML AUTO trick with some REPLACE trickery to format the comma-separated list.

GMAdded-GetLeaderEmails.JPG

Group Leader Names

One more SQL Run query here. This will allow us to send an addressed email like Ted and Cindy, the following members have been added to "Ted's small group". So it's not strictly necessary, but it's nice and doesn't cost many cycles.

SELECT (
    REPLACE(
        REPLACE(
            (SELECT 
    p.NickName

FROM [GroupMember] gm Join [Person] p on gm.PersonId = p.Id Join [Group] g On gm.GroupId = g.Id Join [GroupTypeRole] gr On gm.GroupRoleId = gr.Id

Where gr.IsLeader = 1 and gm.[GroupId] = {{ Activity | Attribute:'Group','Id' }} For XML auto) ,'<p NickName="','' ) ,'"/>',',' ) )

Set the result of that query to Group Leader Names.

GMAdded-GetLeaderNames.JPG

Email the leader

Finally, we've reached the meat of what we want to do. This should be an Email Send action. Type in the email address you want it to send from, and set the "To" address to your Group Leader Email attribute. Type in your subject, then provide the following body:

{% assign members = Activity | Attribute: 'GroupMembers' | Split: ","%}
{% assign leaders = Activity | Attribute: 'GroupLeaderNames' %}
{% if leaders != '' %}
{{ leaders | ReplaceLast: ',','' | ReplaceLast: ',',', and'}}, new members have been added to your group <b>{{ Activity | Attribute:'Group','Name' }}</b><br>
{% endif %}
Members added in the last 24 hours: 
<ul>
{% for item in members %}
    {% assign member = item | PersonById %}
    <li>{{ member.NickName }} {{ member.LastName }} {% if member.Email %}- Email: - {{ member.Email }}{% endif %}</li>
{% endfor %}
</ul>

See how we used the Group Attribute to display the name of the group in the Email? I told you it would be useful! On that note, it should be noted that {{ member }} is a full person record. We're using the NickName and LastName properties, as well as Email, but you can use any of your normal filters and person properties on this object to get more information about the person you're currently listing in the email to the group leader.

I like storing the communication history, but that's optional.

GMAdded-SendEmail.JPG

Increment the count

Now, we're done with this group. Since our Iterator attribute is the secret sauce to being able to iterate through groups, we need to increment that value so that we can look at the next group.

This is an easy Lava Run action with the following lava: {{ Workflow | Attribute:'Iterator' | Plus:1 }}

GMAdded-Increment.JPG

Loop

Now that the iterator is all set up to look at the next group, we need to tell the Workflow to go back and do everything again. Create an Activate Activity action that simply re-activates Start. Set this action to complete the activity when it's completed, so the workflow knows we're done with this group.

GMAdded-Loop.JPG

Triggering the workflow nightly

Now that your workflow exists, head on over to Admin Tools > System Settings > Jobs Administration. Scroll to the bottom of the grid and click on the (+) button to create a new job.

Give the job a name you'll be able to identify later, and make sure it's "Active". Turn notifications off, and select Rock.Jobs.LaunchWorkflow as the Job Type. Now you'll be able to pick your new workflow from the list. Finally, use CronMaker to generate your schedule - the example below shows the job configured to run every night at 8pm.

GMAdded-Job.JPG

Now your new workflow will be run by Rock automatically every day, and the correct leaders will begin getting their daily digest of any new members added to their group that day.


Thanks again to Kevin for all of his hard work in putting together our harebrained ideas into something that actually works ... really well!


@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!