A process to facilitate staff for following up with members who stop serving or leave their small group. Starts with a pastoral call to see how the staff can help and keep touch points at 30,60 and 90 days before triggering a change in membership status.
A look at a small snippet of the code behind the scenes for this project:
{% assign Campus = 'Global' | PageParameter:'Campus' %}
{% if Campus == '' %}
<br/>
<h2>Please Select a Campus</h2>
<br/>
{% else %}
{% sql %}
SELECT top 100 p.Id as 'PersonId',concat(p.firstname,' ',p.lastname) as 'PersonName',CASE WHEN av1.value is not null THEN av1.value ELSE '01/01/2018' END as 'DirIntDate','Serving' as 'Type',concat(cgdirp.firstname,' ',cgdirp.lastname) as 'assigned',av6.value as 'NoteId'
FROM Person p
INNER JOIN [Campus] c on p.PrimaryCampusId = c.Id and c.id = {{Campus}}
LEFT OUTER JOIN [AttributeValue] av1 on av1.entityid=p.id AND av1.AttributeId = 25206
LEFT OUTER JOIN [AttributeValue] av4 on av4.entityid=p.id AND av4.AttributeId = 24801
LEFT OUTER JOIN [PersonAlias] cgdirpa on cast(cgdirpa.guid as varchar(100)) = cast(av4.value as varchar(100))
LEFT OUTER JOIN [Person] cgdirp on cgdirp.id = cgdirpa.personid
--Master Serving Note Id
LEFT OUTER JOIN [AttributeValue] av6 on av6.entityid=p.id AND av6.AttributeId = 24802
-- People who are Members
WHERE p.ConnectionStatusValueId = 65
--Has Stopped Serving Date
AND p.Id IN (SELECT av2.EntityId FROM [AttributeValue] av2 WHERE av2.AttributeId = 8264 AND av2.Value IS NOT NULL AND av2.Value != '')
--Has Assigned Director Serving
AND p.Id IN (SELECT av3.EntityId FROM [AttributeValue] av3 WHERE av3.AttributeId = 24801 AND av3.Value IS NOT NULL AND av3.Value != '')
--No 30 Day Serving Phone Call Date
AND p.Id NOT IN (SELECT av5.EntityId FROM [AttributeValue] av5 WHERE av5.AttributeId = 24814 AND av5.Value IS NOT NULL AND av5.Value != '')
--Has Director Interaction Serving Date
AND p.Id IN (SELECT av7.EntityId FROM [AttributeValue] av7 WHERE av7.AttributeId = 25206 AND av7.Value IS NOT NULL AND av7.Value != '')
--Director Interaction Date greater than 25 days
AND cast(av1.value as DATE) <= GETDATE()-25
UNION
SELECT top 100 p.Id as 'PersonId',concat(p.firstname,' ',p.lastname) as 'PersonName',CASE WHEN av1.value is not null THEN av1.value ELSE '01/01/2018' END as 'DirIntDate','Community' as 'Type',concat(cgdirp.firstname,' ',cgdirp.lastname) as 'assigned',av6.value as 'NoteId'
FROM Person p
INNER JOIN [Campus] c on p.PrimaryCampusId = c.Id and c.id = {{Campus}}
LEFT OUTER JOIN [AttributeValue] av1 on av1.entityid=p.id AND av1.AttributeId = 24990
LEFT OUTER JOIN [AttributeValue] av4 on av4.entityid=p.id AND av4.AttributeId = 24805
LEFT OUTER JOIN [PersonAlias] cgdirpa on cast(cgdirpa.guid as varchar(100)) = cast(av4.value as varchar(100))
LEFT OUTER JOIN [Person] cgdirp on cgdirp.id = cgdirpa.personid
--Master Community Note Id
LEFT OUTER JOIN [AttributeValue] av6 on av6.entityid=p.id AND av6.AttributeId = 24803
-- People who are Members
WHERE p.ConnectionStatusValueId = 65
--Has No Longer in CG Date
AND p.Id IN (SELECT av2.EntityId FROM [AttributeValue] av2 WHERE av2.AttributeId = 24800 AND av2.Value IS NOT NULL AND av2.Value != '')
--Has Assigned Director CG
AND p.Id IN (SELECT av3.EntityId FROM [AttributeValue] av3 WHERE av3.AttributeId = 24805 AND av3.Value IS NOT NULL AND av3.Value != '')
--No 30 Day Community Phone Call Date
AND p.Id NOT IN (SELECT av5.EntityId FROM [AttributeValue] av5 WHERE av5.AttributeId = 24818 AND av5.Value IS NOT NULL AND av5.Value != '')
--Has Director Interaction CG Date
AND p.Id IN (SELECT av7.EntityId FROM [AttributeValue] av7 WHERE av7.AttributeId = 24990 AND av7.Value IS NOT NULL AND av7.Value != '')
--Director Interaction Date greater than 25 days
AND cast(av1.value as DATE) <= GETDATE()-25
{% endsql %}
<table id="table" class="stripe cell-border">
<thead>
<tr>
<th>Interaction Date</th>
<th>Person</th>
<th>Reason</th>
<th>Assigned Director</th>
<th>Call Only</th>
<th>Voicemail</th>
<th>Emailed</th>
<th>Called and Emailed</th>
<th>No Action</th>
<th>Delay</th>
<th>Status Change</th>
</tr>
</thead>
<tbody>
{% for row in results %}
{% assign person = row.PersonId | PersonById %}
<tr>
<td>{{row.DirIntDate | Date:'MM/dd/yyyy'}} ({{row.DirIntDate | Date:'MM/dd/yyyy' | DaysSince}})</td>
<td><a href="{{'Global' | Attribute:'InternalApplicationRoot'}}Person/{{row.PersonId}}">{{row.PersonName}}</a></td>
<td>{{row.Type}}</td>
<td>{{row.assigned}}</td>
<td><a href='{{'Global' | Attribute:'InternalApplicationRoot'}}WorkflowEntry/XXX?Type={{row.Type}}&PersonID={{row.PersonId}}&NoteID={{row.NoteId}}&Interaction=Called and Talked' class="btn btn-success">Called and Talked</a></td>
<td><a href='{{'Global' | Attribute:'InternalApplicationRoot'}}WorkflowEntry/XXX?Type={{row.Type}}&PersonID={{row.PersonId}}&NoteID={{row.NoteId}}&Interaction=Voicemail' class="btn btn-success">Voicemail</a></td>
<td><a href='{{'Global' | Attribute:'InternalApplicationRoot'}}WorkflowEntry/XXX?Type={{row.Type}}&PersonID={{row.PersonId}}&NoteID={{row.NoteId}}&Interaction=Emailed' class="btn btn-success">Emailed</a></td>
<td><a href='{{'Global' | Attribute:'InternalApplicationRoot'}}WorkflowEntry/XXX?Type={{row.Type}}&PersonID={{row.PersonId}}&NoteID={{row.NoteId}}&Interaction=Called and Emailed' class="btn btn-success">Called and Emailed</a></td>
<td><button onclick="$(this).closest('tr').hide();NoAction('{{row.Type}}','{{row.PersonId}}','{{row.NoteId}}','No Action')" class="btn btn-primary">No Action</button></td>
<td><button onclick="$(this).closest('tr').hide();Delay('{{row.Type}}','{{row.PersonId}}','{{row.NoteId}}')" class="btn btn-warning">30 day Delay</button></td>
<td><a href='{{'Global' | Attribute:'InternalApplicationRoot'}}WorkflowEntry/YYY?Type={{row.Type}}&PersonID={{row.PersonId}}&MasterNoteID={{row.NoteId}}&IsDirector=False' class="btn btn-danger">Status Change</a></td>
</tr>
{% endfor %}
</tbody>
</table>
{%endif%}