Description:

This short workflow and report help us to better manage assigning Connectors to Connection Requests. For the following process, our primary focus is on our “New Connections” connection type which we catch brand new people to Trinity Fellowship as well as people that have indicated that they are making a decision to follow Jesus.  We do utilize some automation around our other types and opportunities, but felt like we needed a better way to specifically manage our new connections.  

We spread out connection request assignments to most of our staff and some of our lay pastors and needed a better way to manage vacations and other leave as well as workload.  This process also helped us to match our connectors to a new person request to make a more meaningful match based on their ministry area and demographic data. 

---

What's Needed:

1. Connector Group(s) for our staff/lay pastors 
2. Workflow to self-report leave & unavailability
3. Report to aid in connection assignments 

---

1. Connector Groups

We have several groups that we utilize for different connection types and connection opportunities, but for this purpose, we primarily use our main one, "TFC Connectors", as well as a secondary group, "HR Health Check Connectors".  

2. Workflow

This is a simple workflow that allows our staff/lay pastors to self-report when their unavailability, so that they won't be assigned connection requests while they are out.  We utilize delays within the workflow to make our connector "Pending" or "Active" so that they won't be selectable as a Connector.

(^Activities)

(^Workflow Entry)

(^Attributes)

Workflow export (JSON) can be found at the bottom of this recipe. Import this into your instance and modify as follows:

For the first attribute, "Connector" (Single-Select type), we used the following sql to just include those in a connector group.  Remember to replace the Group ID with yours. 

Note, we are only including the connectors that are in the "Active" (gm.[GroupMemberStatus] = 1) or "Pending" (gm.[GroupMemberStatus] = 2).

SELECT
   p.[Id] AS [Value], p.[NickName] + ' ' + p.[LastName] AS [Text]
FROM
    [GroupMember] gm
INNER JOIN [Person] p ON (gm.[PersonId] = p.[Id])
WHERE gm.[GroupId] = (Place Group Id here) AND gm.[GroupMemberStatus] IN (1,2)
ORDER BY [Text] AS

(^SQL for Single-Select field type for "Connector")

It should look like the following in your workflow attribute:

Replace the default value in the Group(s) in the Group Type attribute(s) with your group(s).

We've also chosen to add the link to this workflow entry in the "Quick Links" section on our internal users' homepage, so that it is easy to access.

3. Report

Lastly, we've created a filterable report for our staff to use to make assignments.  Use whatever types of columns make sense for your organization.

1. First, create a page within your internal site.
2. Add two blocks, a.) Page Parameter block and b.) Dynamic Data block.
3. Add filters within the Page Parameter block.  We started with Campus, Gender, and Age Range.
4. Add relevant SQL.I'm not a SQL expert by any means, so there is likely a more efficient/better way to do this, but I'll share how we decided to create this with parameters and then some filtering happens within our Lava at the bottom of the block settings. (Select “Customize Results with Lava”)

DECLARE @Now date = GETDATE();
DECLARE @CampusId Int;
DECLARE @Gender Int;
DECLARE @AgeStart Int;
DECLARE @AgeEnd Int;
{% assign campus = 'Global' | PageParameter:'CampusId' | AsInteger %}
{% if campus %}
SET @CampusId = {{ campus }};
{% endif %}
{% assign gender = 'Global' | PageParameter:'Gender' | AsInteger %}
{% if gender %}
SET @Gender = {{ gender }};
{% endif %}
SELECT
 p.[NickName] + ' ' + p.[LastName] as Person, DATEDIFF(MM, p.[Birthdate], @Now)/12 AS Age,
    CASE 
        WHEN gm.[GroupMemberStatus] = 2 THEN 'Unavailable' 
    END AS Unavailable,
    -- Count of Active Connection Requests
    COUNT(CASE WHEN cr.[ConnectionState] = 0 THEN 1 END) AS ActiveCount,
    -- Count of Requests in Follow-Up
    COUNT(CASE WHEN cr.[ConnectionState] = 2 THEN 1 END) AS FollowUpCount,
    p.[PrimaryCampusId],
    CASE
     WHEN p.[MaritalStatusValueId] = 143 THEN 'Married'
     WHEN p.[MaritalStatusValueId] = 144 THEN 'Single'
     WHEN p.[MaritalStatusValueId] = 800 THEN 'Widowed'
    END AS MaritialStatus,
    CASE
     WHEN p.[Gender] = 1 THEN 'Male'
     WHEN p.[Gender] = 2 THEN 'Female'
    END AS Gender,
    CASE
     WHEN p.[PrimaryCampusId] = 2 THEN 'Hollywood'
    --  WHEN av.Value = 'Yes' THEN 'Español'
     WHEN p.[PrimaryCampusId] = 3 THEN 'Lubbock'
     WHEN p.[PrimaryCampusId] = 4 THEN 'Willow Creek'
     WHEN p.[PrimaryCampusId] = 5 THEN 'Online'
     WHEN p.[PrimaryCampusId] = 6 THEN 'Pampa'
     WHEN p.[PrimaryCampusId] = 7 THEN 'Wellington'
     WHEN p.[PrimaryCampusId] = 12 THEN 'Decatur'
    END AS Campus,
gm.[Note], p.[Id], av.[Value], avone.[Value] as Department
FROM
    ConnectionRequest cr
    INNER JOIN PersonAlias pa ON (cr.[ConnectorPersonAliasId] = pa.[Id])
    INNER JOIN Person p ON (pa.[PersonId] = p.[Id])
    INNER JOIN GroupMember gm ON (p.[Id] = gm.[PersonId])
    -- Espanol service attribute
    LEFT OUTER JOIN [AttributeValue] av on p.[Id] = av.[EntityId] AND av.[AttributeId] IN (6556)
    -- Department
    LEFT OUTER JOIN [AttributeValue] avone on gm.[Id] = avone.[EntityId] AND avone.[AttributeId] IN (25797)
WHERE gm.[GroupId] = 106317 AND gm.[GroupMemberStatus] IN (1,2) AND cr.[ConnectionOpportunityId] IN (10,4,16,21,17,18,19)
{% if campus %}AND PrimaryCampusId IN (@CampusId){% endif %}
{% if gender %}AND Gender IN (@Gender){% endif %}
GROUP BY
    p.[LastName], p.[NickName], p.[MaritalStatusValueId], p.[Gender], p.[PrimaryCampusId], p.[BirthDate], gm.[Note], p.[Id], av.[Value], 
    avone.[Value], gm.[GroupMemberStatus]
ORDER BY 
    p.[LastName] ASC,PrimaryCampusId ASC,Campus ASC,Age ASC, ActiveCount DESC;

   5. Select “Customize Results with Lava” and add and modify the following (Similar to the above SQL, there may be a better/more efficient way to handle this, but this has been working for us):

{% assign ages = 'Global' | PageParameter:'AgeRange' %}
{% if ages != '' %}
    {% assign agerange = ages | Split:',' %}
    {% assign agestart = agerange[0] | AsInteger %}
    {% assign ageend = agerange[1] | AsInteger %}
{% else %}
    {% assign agestart = '1' | AsInteger %}
    {% assign ageend = '100' | AsInteger %}
{% endif %}
<table class="table table-striped table-bordered">
  <thead class="thead-dark">
    <tr>
      <th scope="col">Connector</th>
      <th scope="col">Campus</th>
      <th scope="col">Active Requests</th>
      <th scope="col">Requests in Followup</th>
      <th scope="col">Age</th>
      <th scope="col">Gender</th>
      <th scope="col">Marital Status</th>
      <th scope="col">Parent of birth - 12th</th>
      <th scope="col">Notes</th>
      <th scope="col">Department/Title</th>
      <th scope="col">Status</th>
    </tr>
  </thead>
  <tbody>
{% for row in rows %}
    {% assign peepid = row.Id %}
    {% assign out = row.Unavailable %}
    {% assign person = row.Person %}
    {% assign active = row.ActiveCount %}
    {% assign followup = row.FollowUpCount %}
    {% assign age = row.Age | AsInteger %}
    {% assign gender = row.Gender %}
    {% assign campus = row.Campus %}
    {% assign maritalstatus = row.MaritialStatus %}
    {% assign notes = row.Note %}
    {% assign dept = row.Department %}
    {% assign supervisor = row.supervisor | PersonByAliasGuid %}
    {% assign peep = peepid | PersonById %}
    {% assign empstatus = peep | Attribute:'FLSAStatus' %}
    {% assign children = peep | Children | Sort:'Age' %}
    {% if age >= agestart and age <= ageend %}
    <tr {% if out %} style="background-color: #C8D7F3; opacity:0.2;"{% endif %}>
      <th>{{ peepid | PersonById | Property:'NickName' }}</th>
      <td>{{ campus }}</td>
      <td>{{ active }}</td>
      <td>{{ followup }}</td>
      <td>{{ age }}</td>
      <td>
          {% if gender == "Male" %}
            <i style="color:blue;" class="fas fa-male"></i>
          {% else %}
            <i style="color:#e75480;" class="fas fa-female"></i>
          {% endif %}
      </td>
      <td>{{ maritalstatus }}</td>
      <td>
        <div id="hoverText">
            {% for child in children %}
            {% assign age = child.Age %}
            {% assign childgender = child.Gender %}
    
                {% if childgender == "Male" %}
                    <span title="{{ child.NickName }} is {{ age }} years old."><i style="color:#89CFF0;" class="fas fa-child"> </i></span>
                {% else %}
                    <span title="{{ child.NickName }} is {{ age }} years old."><i style="color:pink;" class="fas fa-child"> </i></span>
                {% endif %}
            {% endfor %}
        </div>
      </td>
      <td>{{ notes }}</td>
      <td>{{ dept }}</td>
      <td>{% if empstatus != "Full-Time" %}<div style="color:#F8005F;"> {{ empstatus }}</div>{% else %}{{ empstatus }}{% endif %}</td>
      {% endif %}
    </tr>
{% endfor %}
  </tbody>
</table>

Helpful features of report

^Grayed out rows indicate the Connector is unavailable, and the Notes column populates from our workflow --> group member notes.

^Children's names and ages appear on hover.

That's it! Let me know if you have any questions or suggestions. Thank you!