Automatic check-in from Sunday School to Junior Church

Question

0

Automatic check-in from Sunday School to Junior Church

Robert Fuller III posted 6 Years Ago

We have one age/grade breakdown for Sunday School, and another for Junior Church. I was wondering if anyone has developed a way to automatically check-in to the Junior Church class all children that have not checked out from Sunday School. Can this be done with a simple sql block? I would need them to keep their auto-generated 4 character security code from the SS check-in so that it still matches the one that their parents received. Point me in the right direction folks!

BTW...some of you have been INCREDIBLY helpful, and I just wanted to say thank you to the community for helping this new guy figure stuff out.

Michael Garrison · Answer 1 · 2017-06-26T12:35-07:00

0

Michael Garrison replied 6 Years Ago

It's possible in a SQL block, sure, but depending on a few things, it may not be "just a simple" block =)

Disclaimer: I haven't tested this- it should work based on some of my other work with checkins, but PLEASE test this extensively on a test copy of your data, NOT on your production server!!

======================================

You're going to need to start the query off with something like

INSERT INTO [Attendance] (LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId)

Now, assuming that you can look up and hard-code your ScheduleId and the Junior Church GroupId (let's say 123 and 987 respectively), next in your query may be something like:

SELECT [LocationId], 123, 987, [DeviceId], '{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}', 1, 'Jr Church Rollover', NEWID(), [PersonAliasId]
FROM
    [Attendance]

That's going to use the same LocationId, DeviceID and Person as the initial checkin, set the time of their new checkin to the time you ran the query, and add a note to the checkin (which I don't think shows up anywhere in the GUI) so you can see in the database how the attendance was added

Now you need to limit the records which are rolled over to just records which were checked in today and aren't checked out yet:

WHERE
    [ScheduleId] IS NOT NULL
    AND [StartDateTime] > '{{ "Now" | Date:"yyyy-M-d" }}'
    AND [EndDateTime] IS NULL

You need to also limit to just people checked into your Sunday School group(s). You'll either add something like

    AND [GroupId] = 456

(presuming that you only have one Sunday School group, with ID of 456), or if you have multiple groups, something like

    AND [GroupId] IN (444,445)

Now, the above is all based on a routine I developed which records attendance, but not actual kiosk checkins. For kiosk checkin type records, you'll probably want to add these columns inside the parentheses in the first of the code blocks above:

, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId

and then the corresponding data in the second block:

, [SearchTypeValueId], [AttendanceCodeId], [QualifierValueId], [CampusId]

BUT!! I haven't ever tried that, and I don't know if multiple attendance instances pointing to a single AttendanceCodeId is going to cause issues. In theory, I wouldn't expect it to, but PLEASE backup your data and test, test, test to make sure there won't be issues with that.

(If it does cause issues, you'll have to similarly copy data from the AttendanceCode table into a new record, then point to the new record instead).

Finally, if you want to check out the records from Sunday School when they're rolled into Junior Church, add a semicolon to the end of the last line above, then add a second statement like the below:

UPDATE [Attendance]
SET [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] > '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] = 456;

(or replace the last line with something like AND [GroupID] IN (444,445) for multiple source groups, as above)

=====================================

So your final query may end up looking something like:

INSERT INTO [Attendance] (LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId)
SELECT
     [LocationId]
     ,123
     ,987
     ,[DeviceId]
     ,'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
     ,1
     ,'Jr Church Rollover'
     ,NEWID()
     ,[PersonAliasId]
     ,[SearchTypeValueId]
     ,[AttendanceCodeId]
     ,[QualifierValueId]
     ,[CampusId]
FROM
     [Attendance]
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] > '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] IN (444,445);
UPDATE
     [Attendance]
SET
     [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] > '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] IN (444,445);

If you want to get really fancy, you can add some HTML controls to the top of your page which would allow you to select the group to rollover at runtime, etc, use FormattedOutput to check the number of records rolled over, etc, but this should help point you along the path, since it seems you have some familiarity with SQL.

Edit Answer

It's possible in a SQL block, sure, but depending on a few things, it may not be "just a simple" block =)Disclaimer: I haven't tested this- it should work based on some of my other work with checkins, but PLEASE test this extensively on a test copy of your data, NOT on your production server!!======================================You're going to need to start the query off with something like&nbsp;<pre>INSERT INTO [Attendance]&nbsp;(LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId)</pre>Now, assuming that you can look up and hard-code your ScheduleId and the Junior Church GroupId (let's say 123 and 987 respectively), next in your query may be something like:&nbsp;<pre>SELECT [LocationId], 123, 987, [DeviceId],&nbsp;'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}', 1, 'Jr Church Rollover', NEWID(), [PersonAliasId] FROM &nbsp; &nbsp; [Attendance]</pre>That's going to use the same LocationId, DeviceID and Person as the initial checkin, set the time of their new checkin to the time you ran the query, and add a note to the checkin (which I don't think shows up anywhere in the GUI) so you can see in the database how the attendance was addedNow you need to limit the records which are rolled over to just records which were checked in today and aren't checked out yet:<pre>WHERE &nbsp; &nbsp; [ScheduleId] IS NOT NULL &nbsp; &nbsp; AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}' &nbsp; &nbsp; AND [EndDateTime] IS NULL</pre>You need to also limit to just people checked into your Sunday School group(s). You'll either add something like<pre>&nbsp; &nbsp; AND [GroupId] = 456</pre>(presuming that you only have one Sunday School group, with ID of 456), or if you have multiple groups, something like<pre>&nbsp; &nbsp; AND [GroupId] IN (444,445)</pre> Now, the above is all based on a routine I developed which records attendance, but not actual kiosk checkins. For kiosk checkin type records, you'll probably want to add these columns inside the parentheses in the first of the code blocks above:<pre>, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId</pre>and then the corresponding data in the second block:<pre>, [SearchTypeValueId], [AttendanceCodeId], [QualifierValueId], [CampusId]</pre>BUT!! I haven't ever tried that, and I don't know if multiple attendance instances pointing to a single AttendanceCodeId is going to cause issues. In theory, I wouldn't expect it to, but PLEASE backup your data and test, test, test to make sure there won't be issues with that.(If it does cause issues, you'll have to similarly copy data from the AttendanceCode table into a new record, then point to the new record instead). Finally, if you want to check out the records from Sunday School when they're rolled into Junior Church, add a semicolon to the end of the last line above, then add a second statement like the below:<pre>UPDATE [Attendance]
SET [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
 [ScheduleId] IS NOT NULL
 AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
 AND [EndDateTime] IS NULL
 AND [GroupId] = 456;</pre>(or replace the last line with something like AND [GroupID] IN (444,445) for multiple source groups, as above)=====================================So your final query may end up looking something like:<pre>INSERT INTO [Attendance] (LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId) SELECT
 [LocationId]
 ,123
 ,987
 ,[DeviceId]
 ,'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
 ,1
 ,'Jr Church Rollover'
 ,NEWID()
 ,[PersonAliasId]
 ,[SearchTypeValueId]
 ,[AttendanceCodeId]
 ,[QualifierValueId]
 ,[CampusId]
FROM
 [Attendance] WHERE
 [ScheduleId] IS NOT NULL
 AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
 AND [EndDateTime] IS NULL AND [GroupId] IN (444,445); UPDATE
 [Attendance]
SET
 [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
 [ScheduleId] IS NOT NULL
 AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
 AND [EndDateTime] IS NULL
 AND [GroupId] IN (444,445);</pre>If you want to get really fancy, you can add some HTML controls to the top of your page which would allow you to select the group to rollover at runtime, etc, use FormattedOutput to check the number of records rolled over, etc, but this should help point you along the path, since it seems you have some familiarity with SQL.

<p>It's possible in a SQL block, sure, but depending on a few things, it may not be "just a simple" block =)</p><p><b>Disclaimer: I haven't tested this- it should work based on some of my other work with checkins, but PLEASE test this extensively on a test copy of your data, NOT on your production server!!</b></p><p>======================================</p><p>You're going to need to start the query off with something like&nbsp;</p><pre>INSERT INTO [Attendance]&nbsp;(LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId)</pre><p>Now, assuming that you can look up and hard-code your ScheduleId and the Junior Church GroupId (let's say 123 and 987 respectively), next in your query may be something like:&nbsp;</p><pre>SELECT [LocationId], 123, 987, [DeviceId],&nbsp;'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}', 1, 'Jr Church Rollover', NEWID(), [PersonAliasId]<br>FROM<br>&nbsp; &nbsp; [Attendance]</pre><p>That's going to use the same LocationId, DeviceID and Person as the initial checkin, set the time of their new checkin to the time you ran the query, and add a note to the checkin (which I don't think shows up anywhere in the GUI) so you can see in the database how the attendance was added</p><p>Now you need to limit the records which are rolled over to just records which were checked in today and aren't checked out yet:</p><pre>WHERE<br>&nbsp; &nbsp; [ScheduleId] IS NOT NULL<br>&nbsp; &nbsp; AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'<br>&nbsp; &nbsp; AND [EndDateTime] IS NULL</pre><p>You need to also limit to just people checked into your Sunday School group(s). You'll either add something like</p><pre>&nbsp; &nbsp; AND [GroupId] = 456</pre><p>(presuming that you only have one Sunday School group, with ID of 456), or if you have multiple groups, something like</p><pre>&nbsp; &nbsp; AND [GroupId] IN (444,445)</pre><p><br></p><p>Now, the above is all based on a routine I developed which records attendance, but not actual kiosk checkins. For kiosk checkin type records, you'll probably want to add these columns inside the parentheses in the first of the code blocks above:</p><pre>, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId</pre><p>and then the corresponding data in the second block:</p><pre>, [SearchTypeValueId], [AttendanceCodeId], [QualifierValueId], [CampusId]</pre><p>BUT!! I haven't ever tried that, and I don't know if multiple attendance instances pointing to a single AttendanceCodeId is going to cause issues. In theory, I wouldn't expect it to, but PLEASE backup your data and test, test, test to make sure there won't be issues with that.</p><p>(If it does cause issues, you'll have to similarly copy data from the AttendanceCode table into a new record, then point to the new record instead).</p><p><br></p><p>Finally, if you want to check out the records from Sunday School when they're rolled into Junior Church, add a semicolon to the end of the last line above, then add a second statement like the below:</p><pre>UPDATE [Attendance]
SET [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] = 456;</pre><p>(or replace the last line with something like AND [GroupID] IN (444,445) for multiple source groups, as above)</p><p>=====================================</p><p>So your final query may end up looking something like:</p><pre>INSERT INTO [Attendance] (LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId)<br>SELECT
     [LocationId]
     ,123
     ,987
     ,[DeviceId]
     ,'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
     ,1
     ,'Jr Church Rollover'
     ,NEWID()
     ,[PersonAliasId]
     ,[SearchTypeValueId]
     ,[AttendanceCodeId]
     ,[QualifierValueId]
     ,[CampusId]
FROM
     [Attendance]<br>WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL<br>     AND [GroupId] IN (444,445);<br>UPDATE
     [Attendance]
SET
     [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] IN (444,445);</pre><p>If you want to get really fancy, you can add some HTML controls to the top of your page which would allow you to select the group to rollover at runtime, etc, use FormattedOutput to check the number of records rolled over, etc, but this should help point you along the path, since it seems you have some familiarity with SQL.</p>

<p>It's possible in a SQL block, sure, but depending on a few things, it may not be "just a simple" block =)</p><p><b>Disclaimer: I haven't tested this- it should work based on some of my other work with checkins, but PLEASE test this extensively on a test copy of your data, NOT on your production server!!</b></p><p>======================================</p><p>You're going to need to start the query off with something like&nbsp;</p><pre>INSERT INTO [Attendance]&nbsp;(LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId)</pre><p>Now, assuming that you can look up and hard-code your ScheduleId and the Junior Church GroupId (let's say 123 and 987 respectively), next in your query may be something like:&nbsp;</p><pre>SELECT [LocationId], 123, 987, [DeviceId],&nbsp;'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}', 1, 'Jr Church Rollover', NEWID(), [PersonAliasId]<br>FROM<br>&nbsp; &nbsp; [Attendance]</pre><p>That's going to use the same LocationId, DeviceID and Person as the initial checkin, set the time of their new checkin to the time you ran the query, and add a note to the checkin (which I don't think shows up anywhere in the GUI) so you can see in the database how the attendance was added</p><p>Now you need to limit the records which are rolled over to just records which were checked in today and aren't checked out yet:</p><pre>WHERE<br>&nbsp; &nbsp; [ScheduleId] IS NOT NULL<br>&nbsp; &nbsp; AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'<br>&nbsp; &nbsp; AND [EndDateTime] IS NULL</pre><p>You need to also limit to just people checked into your Sunday School group(s). You'll either add something like</p><pre>&nbsp; &nbsp; AND [GroupId] = 456</pre><p>(presuming that you only have one Sunday School group, with ID of 456), or if you have multiple groups, something like</p><pre>&nbsp; &nbsp; AND [GroupId] IN (444,445)</pre><p><br></p><p>Now, the above is all based on a routine I developed which records attendance, but not actual kiosk checkins. For kiosk checkin type records, you'll probably want to add these columns inside the parentheses in the first of the code blocks above:</p><pre>, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId</pre><p>and then the corresponding data in the second block:</p><pre>, [SearchTypeValueId], [AttendanceCodeId], [QualifierValueId], [CampusId]</pre><p>BUT!! I haven't ever tried that, and I don't know if multiple attendance instances pointing to a single AttendanceCodeId is going to cause issues. In theory, I wouldn't expect it to, but PLEASE backup your data and test, test, test to make sure there won't be issues with that.</p><p>(If it does cause issues, you'll have to similarly copy data from the AttendanceCode table into a new record, then point to the new record instead).</p><p><br></p><p>Finally, if you want to check out the records from Sunday School when they're rolled into Junior Church, add a semicolon to the end of the last line above, then add a second statement like the below:</p><pre>UPDATE [Attendance]
SET [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] = 456;</pre><p>(or replace the last line with something like AND [GroupID] IN (444,445) for multiple source groups, as above)</p><p>=====================================</p><p>So your final query may end up looking something like:</p><pre>INSERT INTO [Attendance] (LocationId, ScheduleId, GroupId, DeviceId, StartDateTime, DidAttend, Note, Guid, PersonAliasId, SearchTypeValueId, AttendanceCodeId, QualifierValueId, CampusId)<br>SELECT
     [LocationId]
     ,123
     ,987
     ,[DeviceId]
     ,'{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
     ,1
     ,'Jr Church Rollover'
     ,NEWID()
     ,[PersonAliasId]
     ,[SearchTypeValueId]
     ,[AttendanceCodeId]
     ,[QualifierValueId]
     ,[CampusId]
FROM
     [Attendance]<br>WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL<br>     AND [GroupId] IN (444,445);<br>UPDATE
     [Attendance]
SET
     [EndDateTime]='{{ 'Now' | Date:'yyyy-MM-dd HH:mm:ss' }}'
WHERE
     [ScheduleId] IS NOT NULL
     AND [StartDateTime] &gt; '{{ "Now" | Date:"yyyy-M-d" }}'
     AND [EndDateTime] IS NULL
     AND [GroupId] IN (444,445);</pre><p>If you want to get really fancy, you can add some HTML controls to the top of your page which would allow you to select the group to rollover at runtime, etc, use FormattedOutput to check the number of records rolled over, etc, but this should help point you along the path, since it seems you have some familiarity with SQL.</p>

Save Cancel

Robert Fuller III · Answer 2 · 2017-06-26T13:07-07:00

0

Robert Fuller III replied 6 Years Ago

Michael, this is another huge help to get me down the road. It will take me a while to implement and test this all out but I will try and make sure to let you know how it all worked out.

Wayne Johnson · Answer 3 · 2018-03-09T06:03-07:00

0

Wayne Johnson replied 6 Years Ago

This not an answer, but a question to the community about what must be a common practice. Out of the box, what would be the best practice of having children checked into Sunday school, then children’s church without automatic rollover? The children are dropped off by their parents before Sunday school and not picked up until the end of children’s church. It seems that two labels could be printed at Check-in, but is that the best way to accomplish this? I apologize for hijacking this thread.

Michael Garrison

6 years ago

My church doesn't have this practice, so just help me understand; why do you need two labels? If the kids are all taken from Sunday School to Children's Church, can't they just keep the single label throughout? Otherwise someone is going to have to keep the additional labels and help all the kids apply them... Sounds like an unnecessary hassle.

Wayne Johnson · Answer 4 · 2018-03-09T08:09-07:00

0

Wayne Johnson replied 6 Years Ago

Sorry, I didn't mean to suggest that we need 2 labels. In fact, I believe I just found my solution. I checked a child into SS (using my 'test' daily schedule). Because there were two schedules/areas/groups active for his age today, the check-in screen displayed 2 check boxes- one for SS, the other one for children church. Upon selecting both check-boxes, the system checked the child into both groups, and printed only one label! Way to go, Rock! It did just what I needed out of the box!

Question

0

Automatic check-in from Sunday School to Junior Church

0

0

0

0