Rock Lobster Consulting

Kids Weekend Registration

by

I worked with our Kids team to build a weekend registration system to allow for a limited number of kids spaces to be claimed by our church members.


If a parent visits the registration form they will check to see if it is an appropriate time to register for the following sunday.  It will then check to see if all of the spots are already taken so as to not waste their time if they are.


If there are spots available it presents them with a multi-select list of their kids in the system.  This registration is built with the idea that we only want established members signing up and we will leave space for first time guests to walk up and get their kids registered.


Once they choose their kids it looks in each room based on the kids ages to see if they have space for the kids.  If there is space for all kids it gives a confirmation page to the parent and emails them a confirmation email.
If there is only partial room for their kids it asks them if they want to reserve the spots for the kids who have space, or release all spots.  If they reserve partial spots they are asked to only bring the kids who made it in and leave the others at home with the other parent.


If there is no spaces it informs them to check back on saturday at noon to see if any additional spaces have opened up.


On saturday morning people who registered will receive an email that asks if their plans have changed and if so to click a button to “release” their saved spots so that others can come.


If they release their spots it removes their kids from the groups/rooms they were in so that other parents can register for them at noon later that day.


On Sunday only parents/kids can check in that were registered to prevent walk ups from checking in without staff knowledge and approval.


I built a report for staff that allows them to see Capacity in rooms for each age range as well as the number of spots that were not registered, and the number of registered spots that have not yet checked in. This report allows staff to make on the spot decisions about walk-up families as to whether or not there is room for the families children that week.

Wedding Officiant

by

I worked with our marriage team to develop a plan for automating our couples who need a wedding officiant.


I built a process with them that allows them to input up to 3 preferred people by the couple who receive an email informing them that the Groom and Bride (with links to their Rock Profile).  Inside the email there is a link for them to look at more information about the wedding. The link takes them to a workflow where they can Claim or Pass on the opportunity.  If the possible wedding officiant passes the workflow then looks to see if there is another person in line up to 3 people. 


If no one is left who was preferred by the couple, or if the couple has no preferred staff members it puts the couple into a “General Pool”.  On Monday of every week an email goes out to all Male Staff members that has information about the available couples for wedding officiating in the “General Pool”.  There is a link to Claim the couple which is a workflow form.  There is also a n opt out link in the email so if a Male Staff Member has no wish to officiate any weddings they can ask to not be on these emails again.


When a staff member “Claims” a couple it sends emails and informs the marriage team who claimed who so they can send on more details and make sure the staff member knows what is expected of them in their officiating duties.

Community Group Assessment

by

I worked with our Community team to determine the needs and requirements for a yearly assessment that would be administered to the members of the group to help them grow in their walk with the Lord by sharpening them in the context of their small group.


The assessment works through custom tables to allow for quick querying when displaying results and navigating through pages.  A simple Data Diagram is show below:


A staff member can initiate a new assessment by clicking a button on their dashboard for that group.  The button uses a workflow to email all the members links to take the assessment which are custom workflow links for that group.  The workflow also inserts into one of the custom tables  an assessment request that will be referenced by all future steps.


As members take the assessment (A Workflow behind the scenes) they will insert into the Custom Answers table.  This allows staff members to see answers as they come in, and send out reminders to those who have not yet taken it if needed.


There are also some custom buttons to allow for different functions needed such as to follow up within the tool for those who have “lost” their link to send them a reminder email to take the assessment.  Also to manually mark the assessment as “Complete” in case not all members take it before the assessment is scheduled.


I worked with the community team to build out a “One Page” report so that staff can get all the data they need in a summary page which helps them drive conversations with their group.  The one page report has custom code to allow it to print in the correct format without any special instruction to the staff member hitting print.  An example of the one page document is below.  Each director receives training on how to read this document as there is no legend to explain it per the staff teams desire.

2 on 2 Mentoring Dashboard

by

I worked with our pre-marriage team to streamline their process for connecting a couple who would like to receive 2 on 2 mentoring prior to marriage.

We have the engaged couple fill out an application. That generates an email for the Administrative Assistant to either connect them with a mentor couple the couple requested or put them into a “General Pool” for the mentors to claim them as they feel they have opportunity.

We have a group of people who are available to serve and they receive an email each Monday morning if there are couples that need to be claimed. They can see generic information about the couples that will help them determine if they would be able to be their mentors such as Name, Location and Wedding Date.

Once a mentor couple “Claim’s” the engaged couple through a button on their dashboard (Workflow). It updates the engaged couples record to be claimed and moves the mentor couple into an occupied state so they no longer receive emails weekly as they are actively engaged with a couple.

The mentor couple can then “Complete” the 2 on 2 mentoring on their dashboard and write any notes the staff needs to know about the couple. This will update various fields internally automatically on each couples profiles. It also asks the mentor couple if they would like to be placed on “Rest” so that they are given a reprieve from getting weekly emails for 0,1,2,4,8 weeks based on their choice. Once the time elapses the mentor couple starts receiving the weekly emails again.

Community Shepherd Toolkit (Small Groups)

by

I built a system that allows high trust volunteers to interact with the community groups (small groups) that they oversee, typically 3-5 groups. The dashboard above is for our test group with various actions that the volunteer can take to update the group and notify staff for accountability purposes. This dashboard allows them to record actions that get reported to the staff member assigned as their director over them. They can also see all historical notes and groupmember leaving and joining as they are recorded in the history.

This was done with Lava, HTML, SQL and Workflows.

90 Day Dashboard

by

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%}