TL;DR A solution for quick check-in to an event registration by clicking your name on the list of all registered parties. Jump to Implementation to find out how.

The Issue

VBS check-in is a challenge every year. Twice now we have attempted to use our standard Sunday morning check-in process, only to fall back to paper check-in within a couple of minutes. The issue is that we have 3 times a normal Sunday crowd, all trying to check in across a 30-minute window. Also, VBS features the highest number of kids being dropped off by grandparents, friends, and neighbors. A grandparent is 2 minutes into trying their phone number before we realize the child is registered under the parent's number. And don't get me started on kids typing in their own number... it's too slow!

The Goal

The nice thing about VBS is that everyone is pre-registered, and if you're not, don't worry: we'll hand you an iPad in the parking lot and you can pre-register there. That means we have a list of everyone who is going to be checking in. After thinking it over, the ideal situation seemed like a list of all pre-registered children where you only had to tap their name to check them in. The problem came with how to integrate that with attendance and with label/claim tag printing.

This year we finally figured it all out...

The Solution

If you heard my talk at RX2019, you would know I advocate for a three-step process for designing your own UI for tasks in Rock like this:

  1. Build a custom interface using pages, blocks, Lava, and SQL.
  2. Store all data in existing Rock structures.
  3. Use workflows to connect the two.

Only when we started looking at the problem through that framework did we come up with the working fast check-in solution we were looking for. Giving that, here's how our solution breaks down:

  1. We created a custom page with some SQL in an HTML block that lists all of the pre-registered kids in alphabetical order. We added some razzle-dazzle to improve the experience:
    • Checkmarks for those students already checked in each night
    • Larger text for easy touch screen usage
    • Quick scroll bar on the side to jump to any last name
    • Auto jump to the last name of the last person checked in (to speed up checking in siblings)
    • Running team stats at the bottom of the page (so I'd stop getting pestered for updates)
  2. All of our data was stored as follows:
    • The list of pre-registered kids came directly from the Event Registration instance they signed up for online.
    • Team assignments were stored by putting those kids in groups.
    • Attendance was recorded for each team group.
    • Name tag and claim labels were printed from a label design we added to Rock.
  3. We created a workflow that did the following when a name was clicked:
    1. Determine if that person had already been added to a team group (i.e. Red Team, Blue Team, etc.).
    2. If not, determine which team was least full.
    3. Present the user with that team selection on a User Entry Form, allowing them to override team selection if needed. ("... But my friend is on the blue team!")
    4. Add them to the team selected and remove them from any previous teams.
    5. Record attendance for the team selected.
    6. Redirect back to the pre-registered kids list and trigger a print of their name tag and claim labels. (More on how we did that later.)

Here's what that looks like:

When you approach, you are met with this:

Check-in List.png


Once you click your name above, the following appears:

Check-in Confirmation.png

When you click your name, your label prints, and you are on your way.

The Implementation

Step 1: Short Link

Every bit of configuration for our solution is passed through URL parameters. That means that one of our check-in links looked like the following:

https://rock.ourdomain.com/vbscheckin?Groups=44017,44018,44019,44020,44039&RegistrationInstanceId=53&ExcludeFromBalancing=44039

Here's the breakdown of those parameters:

  • Groups = all the possible teams a kid could be assigned to.
  • RegistrationInstanceId = the registration instance from which to get the list of pre-registered individuals
  • ExcludeFromBalancing = this held special teams that someone had to be manually placed in. (i.e. child-care or special needs)

To make those links easier, for each campus we used Rock's shortlink feature to generate a more user-friendly URL. Campus Kids Directors never had to see the messy underside of what we were doing.

https://rock.ourdomain.com/vbscheckin-london

Future Improvement: Store our configurations in defined types, rather than URL parameters.

Step 2: The List

In order to generate the list that people were presented with when they approached, we created a new page with 5 HTML blocks. I'll post the code for each below and, for the most part, leave you to decipher it.

Block 1: The Refresh Button

<div style="text-align:right;">
    <a style="font-size:2em" href="{{ 'Global' | Page:'Url' | Split:'?' | First }}?Groups={{ 'Global' | PageParameter:'Groups' | Default:'-1' }}&RegistrationInstanceId={{ 'Global' | PageParameter:'RegistrationInstanceId' | Default:'-1' }}&ExcludeFromBalancing={{ 'Global' | PageParameter:'ExcludeFromBalancing' | Default:'-1' }}">Refresh</a>
</div>

Block 2: This List Itself

{% sql %}
    SELECT
        p.[NickName], p.[LastName], p.[Id], pa.[Id] as PAI, (
            SELECT COUNT (a.[Id])
            FROM [Attendance] a
            JOIN
                [AttendanceOccurrence] ao on ao.[Id] = a.[OccurrenceId]
            JOIN
                [PersonAlias] ipa on ipa.[Id] = a.[PersonAliasId]
            WHERE
                ipa.[PersonId] = p.[Id]
                AND a.[DidAttend] = 1 
                AND ao.GroupId IN ( {{ 'Global' | PageParameter:'Groups' | Default:'-1' }} )
                AND a.StartDateTime = CAST (GETDATE() AS DATE)
            ) as Count
                
    FROM
        [Registration] r
    JOIN
        [RegistrationRegistrant] rr on rr.[RegistrationId] = r.[Id]
    JOIN
        [PersonAlias] pa on pa.[Id] = rr.[PersonAliasId]
    JOIN
        [Person] p on p.[Id] = pa.[PersonId]
        
    WHERE
        r.[RegistrationInstanceId] = {{ 'Global' | PageParameter:'RegistrationInstanceId' | Default:'-1'}}
    ORDER BY
        p.[LastName], p.[NickName]
    
{% endsql %}

{% capture ReturnUrlBase %}
    {{ 'Global' | Page:'Url' | Split:'?' | First }}?Groups={{ 'Global' | PageParameter:'Groups' | Default:'-1' }}&RegistrationInstanceId={{ 'Global' | PageParameter:'RegistrationInstanceId' | Default:'-1' }}&ExcludeFromBalancing={{ 'Global' | PageParameter:'ExcludeFromBalancing' | Default:'-1' }}
{% endcapture %} 

<table id="theList" class="table" style="font-size:2em;">
    {% for result in results %}
        <tr class="alphanav-header-{{ result.LastName | Slice:0,1 }}"><td><a style="display:block;" href="/page/3238?workflowTypeId=98&PersonId={{ result.Id }}&GroupString={{ 'Global' | PageParameter:'Groups' | Default:'-1'}}&RegistrationInstanceId={{ 'Global' | PageParameter:'RegistrationInstanceId' | Default:'-1'}}&ExcludeFromBalancing={{ 'Global' | PageParameter:'ExcludeFromBalancing' | Default:'-1'}}&ReturnUrlBase={{ ReturnUrlBase | Trim | EscapeDataString }}">{{ result.LastName }}, {{ result.NickName }}</a></td>
        <td>{% if result.Count > 0 %}<i class="fa fa-check" aria-hidden="true"></i>{% endif %}</td></tr>
    {% endfor %}
    
</table>

Block 3: The Javascript to Print Name Tag and Claim Labels

This is where some trickery starts happening. For years I tried to implement a solution like this but always stumbled on how to access Rock's built-in check-in system to print the needed labels. This year I discovered that I could get the Windows Check-in client to successfully print for me by spoofing the label printing process.

It turns out, the Windows Check-in client is simply a browser that is listening for one special javascript command in order to know what to print. The following javascript command will trigger the print action:

window.external.PrintLabels(jsonTags);

All I had to do was format some JSON with my person's info, call that function, and voilà, out came a label. 

After an individual completes the team assignment workflow (details to follow), they are redirected back to the registrants list, except this time with URL parameters for the PersonId, team assignment, and claim code for the person that was just checked in. The javascript in this block looks to see if any PersonId was returned, and if so, initiates the print.

Without further ado, here's the complete code for that 3rd block:

<script>
    function getUrlVars() {
        var vars = {};
        var parts = window.location.href.replace(/[?&]+([^=&]+)=([^&]*)/gi, function(m,key,value) {
            vars[key] = value;
        });
        return vars;
    }
    $(document).ready(function(){
        
        
            var pid = getUrlVars()["PersonId"];
            if(pid)
            {
                var jsonTags = '[{"LabelType":0,"Order":1,"PersonId":' + getUrlVars()["PersonId"] + ',"PrinterDeviceId":0,"PrinterAddress":"","PrintFrom":0,"PrintTo":1,"FileGuid":"4cfcbbc1-e642-4d88-b377-9ccce09f5c24","LabelFile":"https://rock.thecreekchurch.com/GetFile.ashx?id=1087","LabelKey":"4cfcbbc1-e642-4d88-b377-9ccce09f5c24","MergeFields":{"Nick":"{{ 'Global' | PageParameter:'PersonId' | PersonById | Property:"NickName" }}","Last":"{{ 'Global' | PageParameter:'PersonId' | PersonById | Property:"LastName" }}", "Code":"' + getUrlVars()["Code"] + '","Team":"' + getUrlVars()["Team"] + '"}},' + '{"LabelType":0,"Order":1,"PersonId":' + getUrlVars()["PersonId"] + ',"PrinterDeviceId":0,"PrinterAddress":"","PrintFrom":0,"PrintTo":1,"FileGuid":"4398ca61-6a6c-443a-b650-dd8148d15162","LabelFile":"https://rock.thecreekchurch.com/GetFile.ashx?id=2397","LabelKey":"4398ca61-6a6c-443a-b650-dd8148d15162","MergeFields":{"Nick":"{{ 'Global' | PageParameter:'PersonId' | PersonById | Property:"NickName" }} {{ 'Global' | PageParameter:'PersonId' | PersonById | Property:"LastName" }}", "Code":"' + getUrlVars()["Code"] + '","Team":"' + getUrlVars()["Team"] + '"}}]';
                console.log(jsonTags);
                window.external.PrintLabels(jsonTags);
              
            }
    });
</script>

Future Improvement: We cheated! I ran out of time to figure out how to successfully generate a real parent claim code, so I simply generated a random 4 digit number and hoped they were unique and acceptable. This means that a label re-print will generate a new claim code. Hopefully, I'll fix that next year.

Block 4: Nightly Stats

{ % sql % }
            SELECT COUNT(DISTINCT a.[PersonAliasId]) as Count
            FROM [Attendance] a
            JOIN
                [AttendanceOccurrence] ao on ao.[Id] = a.[OccurrenceId] 
            WHERE
                a.[DidAttend] = 1 
                AND ao.GroupId IN ({{ 'Global' | PageParameter:'Groups' | Default:'-1' }})
                AND a.StartDateTime = CAST (GETDATE() AS DATE)
            
{% endsql %}

<h2>Total Checked In: {{ results|First | Property:'Count' }}</h2>
{% assign groups = 'Global' | PageParameter:'Groups' | Default:'' | Split:',' %}

{% for aGroup in groups %}
    {% sql %}
            SELECT COUNT(DISTINCT a.[PersonAliasId]) as Count
            FROM [Attendance] a
            JOIN
                [AttendanceOccurrence] ao on ao.[Id] = a.[OccurrenceId]
            JOIN
                [PersonAlias] pa on pa.[Id] = a.[PersonAliasId]
            JOIN
                [GroupMember] gm on gm.[PersonId] = pa.[PersonId] AND gm.[GroupId] = {{ aGroup }}
            WHERE
                a.[DidAttend] = 1 
                AND ao.GroupId IN ({{ aGroup }})
                AND a.StartDateTime = CAST (GETDATE() AS DATE)
    {% endsql %}
    {{ aGroup | GroupById | Property:'Name' }} {{ results|First | Property:'Count' }}<br />
 
{% endfor %}

Block 5: Scroll Bar Alpha-nav

This javascript is just there to give you a quick-scroll bar on the side. It's totally optional but helped a lot.

<div class="letterHolder" style="font-size:1.5em; position:fixed; right:0; top:0; bottom:0; width:45px; background-color:#eee; padding:10px;">
</div>
<script>
    $(document).ready(function(){
    
        //$('.letterHolder span').click(function(){
        $(document).on('click', '.letterHolder span', function(){
            var letterToJumpTo = $(this).html();
            console.log('HEEE ' + letterToJumpTo);
            
            var elementToVisit = $('*[data-customerID="22"]');
            
            $([document.documentElement, document.body]).animate({
                scrollTop: $(".alphanav-header-" + letterToJumpTo).first().offset().top
            }, 100);
        });
        
        var letter = getUrlVars()["Letter"];
        if(letter)
        {
             $([document.documentElement, document.body]).animate({
                    scrollTop: $(".alphanav-header-" + letter).first().offset().top
                }, 100);
        }
        var height = $(".letterHolder").height();
        var lineHeight = height / 27;
        $('.letterHolder').css('line-height',lineHeight + 'px');
        
        var fullList = $( "[class^=alphanav-header-]" )
          .map(function() {
            return $(this).attr('class').replace('alphanav-header-','');
          })
          .get();
          console.log(fullList);
          var list = $.unique( fullList );
          console.log(list);
          $.each(list,function(index, value){ $('.letterHolder').append('<span>' + value + '</span><br>') });
    });
</script>


Step 3: The Workflow

Attached below is a screen capture of the entire workflow so you can see how everything is put together. Below, I'll give a brief overview and explain the less obvious parts.

Action 1: Calculate Claim Code

As I said above, this is a cheat, so all we do is generate a random 4 digit number:

SELECT CAST(RAND() * 10000 AS INT) AS [RandomNumber]

Action 2: Determine Previous Group

I loop through all of the possible groups to see if our person currently belongs to one of them.

{% capture output %}
    {% assign existingTeam = "" %}
    {% assign groups = Workflow | Attribute:'GroupString' | Default:'' | Split:',' %}
    {% for aGroup in groups %}
        {% assign memberCount = Workflow | Attribute:"PersonId" | PersonById | Group:aGroup | Size %}
        {% if memberCount > 0 %}{% assign existingTeam = aGroup %}{% endif %}
    {% endfor %}
    {{ existingTeam }}
{% endcapture %}{{ output | Trim }}

Action 3: Determine Team if New

If we didn't find a team membership with the previous step, we will determine which group currently has the least members.

{% capture smallestGroupResult %}
    {% assign groups = Workflow | Attribute:'GroupString' | Default:'' | Split:',' %}
    {% assign exclusion = Workflow | Attribute:'ExcludeFromBalancing' | Default:'' | Split:',' %}
    {% assign smallestGroup = '' %}
    {% assign smallestValue = 100000000 %}
    {% for aGroup in groups %}
        {% assign teamCount = aGroup | GroupById | Property:'Members' | Size %}
        {% if teamCount < smallestValue %}
            {% assign excludeGroup = exclusion | Contains:aGroup %}
            {% if excludeGroup == false %}
                {% assign smallestGroup = aGroup %}
                {% assign smallestValue = teamCount %}
            {% endif %}
        {% endif %}
    {% endfor %}
    {{ smallestGroup }}
{% endcapture %}{{ smallestGroupResult | Trim }}

Action 4: Turn Person ID into Person

Thus far we have been working with the PersonId passed as a parameter. Now we will turn that into an actual workflow attribute of type person.

{% assign person = Workflow.PersonId | PersonById  %}{{ person.PrimaryAlias.Guid }}

Action 5: User Entry Form

This is where we display our team suggestion to the user and let them decide to accept or change it.

We added a little extra flair partway through the week to make this screen more useful. In the header of the UEF, we include the following:

<h1>Team Selection</h1>
<p>
    {{ Workflow | Attribute:'Person','Object' | Property:'NickName' }} {{ Workflow | Attribute:'Person','Object' | Property:'LastName' }}{% assign bDay = Workflow | Attribute:'Person','Object' | Property:'BirthDate' | Default:'-1' %}
    {% if bDay > 0 %} - {{ bDay | DateDiff:'Now','Y' }} Years Old{% endif %}
</p>

Action 6: Create Group Attribute

Here we take the user's input from the previous step, and convert the results of a Single Select field into a workflow attribute of type Group.

Action 7: Delete from All Groups

Here we used some SQL to remove the user from all of the potential groups. We are getting ready to add them back into their chosen group, and we want to make sure they aren't part of any other groups. This is important when the person changes groups on night two. We don't want them to be in multiple groups.

DELETE FROM [GroupMember]
WHERE
    [PersonId] = {{ Workflow | Attribute:'PersonId' }}
    AND [GroupId] in ({{ Workflow | Attribute:'GroupString' | Default:'-1' }})

Warning: This code is a little dangerous and makes me slightly uncomfortable. If you're following along, you could exploit this by putting some other GroupId in the URL and cause the individual to be deleted from that group. For now, it was a risk I was willing to take.

Action 8: Record Attendance and Add to Group

We use the built-in workflow action to record an attendance for this person in the specified group. The action is also nice enough to go ahead and add them into the group for us. (Remember, we removed them from all groups last action.)

Action 9: Redirect Back to Form

Now we just need to redirect back to our list of registrants. Remember, that page is on the lookout for some URL parameters so it can print this person's labels. We provide those here by tacking on PersonId, Team, and Code (claim code).

We also tack on the first letter of their last name. Because our solution makes you check in a single individual at a time, some javascript on the list page automatically scrolls you to this letter in case you have a sibling you want to check in. 

Action 10: Complete

That's all, folks!


In Conclusion:

I know that's a lot, but it is a solution that finally answered one of our nagging issues. We were able to check-in 375 kids each night on six stations with no slow down. We did staff each of our stations and suggested the volunteer do all the work. All they had to do was ask for a last name when someone approached, and they were off.

Everything above comes as-is, but I'd be happy to help you track down issues if you find them. I'm hoping you will find it a viable solution as well. (Or at least parts of it will help you build a better solution!)