Overview
Here you'll find several resources related to my RX 2024 breakout session.
Slidedeck
https://thecrossing.church/content/stansworkshop/LegoMasterSlidedeck.pdf
Links
Sample Code
Explorations BSQ
SQL for the Dynamic Data View on the teacher's dashboard.
{% assign dateRange = 'Global' | PageParameter:'When' | Default:'Last|3|Month||' | DateRangeFromSlidingFormat %}
{% assign startDate = dateRange.StartDate %}
{% assign endDate = dateRange.EndDate %}
DECLARE @PersonAttrId INT = 20549
,@GroupAttrId INT = 31144
,@QuestionAttrId INT = 31145
,@BSQWorkflowTypeId INT = 181
,@BSQActivityTypeId INT = 390;
SELECT personAV.[ValueAsPersonId] AS 'PersonId'
,wfa.[ActivatedDateTime] AS 'Submitted'
,personAV.[PersistedTextValue] AS 'Person'
,groupAV.[PersistedTextValue] AS 'Group'
,questionAV.[PersistedTextValue] AS 'Question'
FROM [Workflow] wf
INNER JOIN [WorkflowActivity] wfa
ON wfa.[WorkflowId] = wf.[Id]
AND wfa.[ActivityTypeId] = @BSQActivityTypeId
INNER JOIN [AttributeValue] personAV
ON personAV.[AttributeId] = @PersonAttrId
AND personAV.[EntityId] = wf.[Id]
INNER JOIN [AttributeValue] groupAV
ON groupAV.[AttributeId] = @GroupAttrId
AND groupAV.[EntityId] = wf.[Id]
INNER JOIN [AttributeValue] questionAV
ON questionAV.[AttributeId] = @QuestionAttrId
AND questionAV.[EntityId] = wfa.[Id]
WHERE wf.[WorkflowTypeId] = @BSQWorkflowTypeId
AND wf.[ActivatedDateTime] BETWEEN '{{ startDate }}' AND '{{ endDate }}'
ORDER BY wfa.[ActivatedDateTime] DESC
Youth Camp - Dashboard
SQL for the Dynamic Data View on the dashboard.
{% assign groupId = 'Global' | PageParameter:'GroupId' | Default:'-1' %}
{% assign regInstances = 'Global' | PageParameter:'RegInstances' | Default:'' %}
DECLARE @GroupId INT
, @GradeAttrId INT
, @ShirtSizeAttrId INT
, @AllergiesAttrId INT
, @MedicationAttrId INT
, @OTCPermissionAttrId INT
, @OTCAllowedAttrId INT
, @SpecialNeedsAttrId INT
, @BusNumberAttrId INT
, @RoomNumberAttrId INT
, @ColorTeamAttrId INT;
SET @GroupId = {{ groupId }};
SET @GradeAttrId = 31020;
SET @ShirtSizeAttrId = 30679;
SET @AllergiesAttrId = 30680;
SET @MedicationAttrId = 30978;
SET @OTCPermissionAttrId = 30980;
SET @OTCAllowedAttrId = 30979;
SET @SpecialNeedsAttrId = 30981;
SET @BusNumberAttrId = 32008;
SET @RoomNumberAttrId = 32009;
SET @ColorTeamAttrId = 32010;
SELECT registrant.[Id] AS 'Id'
, gm.[Id] AS 'GroupMemberId'
, g.[Name] AS 'GroupName'
, registrant.[LastName] AS 'LastName'
, registrant.[NickName] AS 'NickName'
, gr.[Name] AS 'Group Role'
, registrar.[Id] AS 'RegistrarId'
, registrar.[LastName] AS 'RegistrarLastName'
, registrar.[FirstName] AS 'RegistrarFirstName'
, gradeAV.[PersistedTextValue] AS 'Grade'
, shirtAV.[Value] AS 'ShirtSizeIndex'
, shirtAV.[PersistedTextValue] AS 'ShirtSize'
, allergiesAV.[PersistedTextValue] AS 'Allergies'
, medsAV.[PersistedTextValue] AS 'Medications'
, otcPermissionAV.[PersistedTextValue] AS 'OTCPermission'
, otcAllowedAV.[PersistedTextValue] AS 'OTCAllowed'
, specialNeedsAV.[PersistedTextValue] AS 'SpecialNeeds'
, busAV.[PersistedTextValue] AS 'BusNumber'
, roomAV.[PersistedTextValue] AS 'RoomNumber'
, colorAV.[PersistedTextValue] AS 'ColorTeam'
FROM [GroupMember] gm
INNER JOIN [Group] g ON g.Id = gm.[GroupId]
INNER JOIN [Person] registrant ON registrant.[Id] = gm.[PersonId]
INNER JOIN [RegistrationRegistrant] rr ON rr.[GroupMemberId] = gm.[Id]
INNER JOIN [Registration] r ON r.[Id] = rr.[RegistrationId]
{% if regInstances != empty %}
AND r.[RegistrationInstanceId] in ({{ regInstances }})
{% endif %}
INNER JOIN [PersonAlias] pa ON pa.[Id] = r.[PersonAliasId]
INNER JOIN [Person] registrar ON registrar.[Id] = pa.[PersonId]
LEFT JOIN [AttributeValue] gradeAV
ON gradeAV.[AttributeId] = @GradeAttrId
AND gradeAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] shirtAV
ON shirtAV.[AttributeId] = @ShirtSizeAttrId
AND shirtAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] allergiesAV
ON allergiesAV.[AttributeId] = @AllergiesAttrId
AND allergiesAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] medsAV
ON medsAV.[AttributeId] = @MedicationAttrId
AND medsAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] otcPermissionAV
ON otcPermissionAV.[AttributeId] = @OTCPermissionAttrId
AND otcPermissionAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] otcAllowedAV
ON otcAllowedAV.[AttributeId] = @OTCAllowedAttrId
AND otcAllowedAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] specialNeedsAV
ON specialNeedsAV.[AttributeId] = @SpecialNeedsAttrId
AND specialNeedsAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] busAV
ON busAV.[AttributeId] = @BusNumberAttrId
AND busAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] roomAV
ON roomAV.[AttributeId] = @RoomNumberAttrId
AND roomAV.[EntityId] = gm.[Id]
LEFT JOIN [AttributeValue] colorAV
ON colorAV.[AttributeId] = @ColorTeamAttrId
AND colorAV.[EntityId] = gm.[Id]
LEFT JOIN [GroupTypeRole] gr
ON gr.[Id] = gm.[GroupRoleId]
WHERE gm.[GroupId] = @GroupId
ORDER BY registrant.[LastName], registrant.[FirstName]
Youth Camp - Room Roster
Lava used for the merge template
<html
document-width="8.5"
document-height="11"
margin-bottom=".1"
document-name="RoomRosters-ShirtSizes.pdf"
autogenerate-bookmarks="true"
reset-page-numbers-per-row="true" >
<style>
.title {
font-size: 60px;
font-weight: 700;
text-align: center;
}
.subtitle {
font-size: 24px;
font-weight: 300;
font-style: italic;
text-align: center;
margin-bottom: 20px;
}
.colhead {
font-size: 24px;
font-weight: 600;
}
.text-left {
text-align: left;
}
.text-center {
text-align: center;
}
.text-right {
text-align: right;
}
.tcolw-1 { width: 8.333333333% }
.tcolw-2 { width: 16.66666667% }
.tcolw-3 { width: 25% }
.tcolw-4 { width: 33.33333333% }
.tcolw-5 { width: 41.66666667% }
.tcolw-6 { width: 50% }
.tcolw-7 { width: 58.33333333% }
.tcolw-8 { width: 66.66666667% }
.tcolw-9 { width: 75% }
.tcolw-10 { width: 83.33333333% }
.tcolw-11 { width: 91.66666667% }
.tcolw-12 { width: 100% }
.pgbreak {
page-break-after: always;
}
table {
margin: auto;
width: 85%;
border-spacing: 5px;
}
th,
td {
padding: 5px 10px;
}
td {
font-size: 20px;
font-weight: 400;
}
tbody.roster tr:nth-child(even) {
background-color: #eaeaea;
}
table.counts {
margin-top: 20px;
border: 3px solid black;
background-color: #f4f3ff;
}
table.counts th,
table.counts td {
padding: 0px;
}
tr.counts {
background-color: #fff !important;
}
</style>
<body>
{% assign lastRoom = 'none' %}
{% assign totalRooms = 0 %}
{% assign totalKids = Rows | Size %}
{% assign roomKidCount = 0 %}
{% assign smCount = 0 %}
{% assign mdCount = 0 %}
{% assign lgCount = 0 %}
{% assign xlCount = 0 %}
{% assign xxCount = 0 %}
{% for row in Rows %}
{% assign thisRoom = row.RoomNumber | Default:'Roomless' %}
{% if thisRoom != lastRoom %}
{% unless forloop.first %}
<tr class="counts">
<td colspan="4">
<table class="counts">
<thead>
<tr>
<th colspan="5" class="colhead">Shirt Counts</th>
</tr>
<tr>
<th>Small</th>
<th>Medium</th>
<th>Large</th>
<th>X-Large</th>
<th>XX-Large</th>
</tr>
</thead>
<tbody>
<tr>
<td class="text-center">{{ smRmCount }}</td>
<td class="text-center">{{ mdRmCount }}</td>
<td class="text-center">{{ lgRmCount }}</td>
<td class="text-center">{{ xlRmCount }}</td>
<td class="text-center">{{ xxRmCount }}</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="4" class="text-left"><em>{{ lastRoom }} Student Count = {{ roomKidCount }}</em></td>
</tr>
</tfoot>
</table>
{% endunless %}
<table class="pgbreak">
<thead>
<tr>
<th colspan="4"><span class="title">{{ thisRoom }}</span></th>
</tr>
<tr>
<th colspan="4"><span class="subtitle">Team Color: {{ row.ColorTeam }}</span></th>
</tr>
<tr>
<th class="colhead tcolw-3 text-left">Student</th>
<th class="colhead tcolw-1 text-center">Grade</th>
<th class="colhead tcolw-4 text-center">Shirt Size</th>
<th class="colhead tcolw-4 text-center"></th>
</tr>
</thead>
<tbody class="roster">
{% assign totalRooms = totalRooms | Plus:1 %}
{% assign roomKidCount = 0 %}
{% assign smRmCount = 0 %}
{% assign mdRmCount = 0 %}
{% assign lgRmCount = 0 %}
{% assign xlRmCount = 0 %}
{% assign xxRmCount = 0 %}
{% endif %}
{% assign lastRoom = thisRoom %}
{% assign roomKidCount = roomKidCount | Plus:1 %}
{% case row.ShirtSize %}
{% when 'Small' %}
{% assign smCount = smCount | Plus:1 %}
{% assign smRmCount = smRmCount | Plus:1 %}
{% when 'Medium' %}
{% assign mdCount = mdCount | Plus:1 %}
{% assign mdRmCount = mdRmCount | Plus:1 %}
{% when 'Large' %}
{% assign lgCount = lgCount | Plus:1 %}
{% assign lgRmCount = lgRmCount | Plus:1 %}
{% when 'X-Large' %}
{% assign xlCount = xlCount | Plus:1 %}
{% assign xlRmCount = xlRmCount | Plus:1 %}
{% when 'XX-Large' %}
{% assign xxCount = xxCount | Plus:1 %}
{% assign xxRmCount = xxRmCount | Plus:1 %}
{% endcase %}
<tr>
<td class="text-left">{{ row.LastName }}, {{ row.NickName }} </td>
<td class="text-center">{{ row.Grade }}</td>
<td class="text-center">{{ row.ShirtSize }}</td>
</tr>
{% if forloop.last %}
<tr class="counts">
<td colspan="4">
<table class="counts">
<thead>
<tr>
<th colspan="5" class="colhead">Shirt Counts</th>
</tr>
<tr>
<th>Small</th>
<th>Medium</th>
<th>Large</th>
<th>X-Large</th>
<th>XX-Large</th>
</tr>
</thead>
<tbody>
<tr>
<td class="text-center">{{ smRmCount }}</td>
<td class="text-center">{{ mdRmCount }}</td>
<td class="text-center">{{ lgRmCount }}</td>
<td class="text-center">{{ xlRmCount }}</td>
<td class="text-center">{{ xxRmCount }}</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
<tfoot>
<tr>
<td colspan="4" class="text-left"><em>{{ lastRoom }} Student Count = {{ roomKidCount }}</em></td>
</tr>
</tfoot>
</table>
<table>
<thead>
<tr>
<th class="title">Total Counts</th>
</tr>
</thead>
<tr>
<td class="colhead">Rooms: {{ totalRooms }}</td>
</tr>
<tr>
<td class="colhead">Students: {{ totalKids }}</td>
</tr>
<tr class="counts">
<td colspan="4">
<table class="counts">
<thead>
<tr>
<th colspan="5" class="colhead">Shirt Counts</th>
</tr>
<tr>
<th>Small</th>
<th>Medium</th>
<th>Large</th>
<th>X-Large</th>
<th>XX-Large</th>
</tr>
</thead>
<tbody>
<tr>
<td class="text-center">{{ smCount }}</td>
<td class="text-center">{{ mdCount }}</td>
<td class="text-center">{{ lgCount }}</td>
<td class="text-center">{{ xlCount }}</td>
<td class="text-center">{{ xxCount }}</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
{% endif %}
{% endfor %}
<footer height=".25" spacing=".1">
<table style="margin:auto; width:90%;">
<tr>
<td style="width:50%;text-align:;left;">
{% assign row = Rows | First %}
<strong>{{ row.GroupName }} - Room Rosters (Shirt Sizes)</strong>
</td>
<td style="width:50%;text-align:right;">As of: {{ 'Now' | Date:'dd-MMM-yyyy hh:mm tt' }}</td>
</tr>
</table>
</footer>
</body>
</html>
Youth Camp - Bus Roster
Lava used for the merge template
<html
document-width="8.5"
document-height="11"
margin-bottom=".1"
document-name="BusRosters.pdf"
autogenerate-bookmarks="true"
reset-page-numbers-per-row="true" >
<style>
.title {
font-size: 60px;
font-weight: 700;
text-align: center;
}
.colhead {
font-size: 24px;
font-weight: 600;
}
.text-left {
text-align: left;
}
.text-center {
text-align: center;
}
.text-right {
text-align: right;
}
.tcolw-1 { width: 8.333333333% }
.tcolw-2 { width: 16.66666667% }
.tcolw-3 { width: 25% }
.tcolw-4 { width: 33.33333333% }
.tcolw-5 { width: 41.66666667% }
.tcolw-6 { width: 50% }
.tcolw-7 { width: 58.33333333% }
.tcolw-8 { width: 66.66666667% }
.tcolw-9 { width: 75% }
.tcolw-10 { width: 83.33333333% }
.tcolw-11 { width: 91.66666667% }
.tcolw-12 { width: 100% }
.pgbreak {
page-break-after: always;
}
table {
margin: auto;
width: 75%;
border-spacing: 5px;
}
tbody tr {
height: 34px;
}
td {
font-size: 20px;
font-weight: 400;
}
</style>
<body>
{% assign lastBus = 'none' %}
{% assign totalBuses = 0 %}
{% assign totalKids = Rows | Size %}
{% assign busKidCount = 0 %}
{% for row in Rows %}
{% assign thisBus = row.BusNumber | Default:'Unassigned' %}
{% if thisBus != lastBus %}
{% unless forloop.first %}
<tr>
<td class="text-left"><em>{{ lastBus }} Student Count = {{ busKidCount }}</em></td>
</tr>
</tbody>
</table>
{% endunless %}
<table class="pgbreak">
<thead>
<tr>
<th colspan="4"><span class="title">{{ thisBus }}</span></th>
</tr>
<tr>
<th class="colhead tcolw-6 text-left">Name</th>
<th class="colhead tcolw-2 text-center">Grade</th>
<th class="colhead tcolw-2 text-center">To Camp</th>
<th class="colhead tcolw-2 text-center">From Camp</th>
</tr>
</thead>
<tbody>
{% assign totalBuses = totalBuses | Plus:1 %}
{% assign busKidCount = 0 %}
{% endif %}
{% assign lastBus = thisBus %}
{% assign busKidCount = busKidCount | Plus:1 %}
<tr>
<td class="text-left">{{ row.LastName }}, {{ row.NickName }} </td>
<td class="text-center">{{ row.Grade }}</td>
<td class="text-center">__________</td>
<td class="text-center">__________</td>
</tr>
{% if forloop.last %}
<tr>
<td class="text-left"><em>{{ lastBus }} Student Count = {{ busKidCount }}</em></td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th class="title">Total Counts</th>
</tr>
</thead>
<tr>
<td class="colhead">Buses: {{ totalBuses }}</td>
</tr>
<tr>
<td class="colhead">Students: {{ totalKids }}</td>
</tr>
</table>
{% endif %}
{% endfor %}
<footer height=".25" spacing=".1">
<table style="margin:auto; width:90%;">
<tr>
<td style="width:50%;text-align:;left;">
{% assign row = Rows | First %}
<strong>{{ row.GroupName }} - Bus Rosters</strong>
</td>
<td style="width:50%;text-align:right;">As of: {{ 'Now' | Date:'dd-MMM-yyyy hh:mm tt' }}</td>
</tr>
</table>
</footer>
</body>
</html>
Youth Camp - Medical Roster
Lava used for the merge template
<html
document-width="8.5"
document-height="11"
margin-bottom=".1"
document-name="BusRosters.pdf"
autogenerate-bookmarks="true"
reset-page-numbers-per-row="true" >
<style>
.title {
font-size: 60px;
font-weight: 700;
text-align: center;
}
.colhead {
font-size: 24px;
font-weight: 600;
}
.text-left {
text-align: left;
}
.text-center {
text-align: center;
}
.text-right {
text-align: right;
}
.tcolw-1 { width: 8.333333333% }
.tcolw-2 { width: 16.66666667% }
.tcolw-3 { width: 25% }
.tcolw-4 { width: 33.33333333% }
.tcolw-5 { width: 41.66666667% }
.tcolw-6 { width: 50% }
.tcolw-7 { width: 58.33333333% }
.tcolw-8 { width: 66.66666667% }
.tcolw-9 { width: 75% }
.tcolw-10 { width: 83.33333333% }
.tcolw-11 { width: 91.66666667% }
.tcolw-12 { width: 100% }
.pgbreak {
page-break-after: always;
}
table {
margin: auto;
width: 75%;
border-spacing: 5px;
}
tbody tr {
height: 34px;
}
td {
font-size: 20px;
font-weight: 400;
}
</style>
<body>
{% assign lastBus = 'none' %}
{% assign totalBuses = 0 %}
{% assign totalKids = Rows | Size %}
{% assign busKidCount = 0 %}
{% for row in Rows %}
{% assign thisBus = row.BusNumber | Default:'Unassigned' %}
{% if thisBus != lastBus %}
{% unless forloop.first %}
<tr>
<td class="text-left"><em>{{ lastBus }} Student Count = {{ busKidCount }}</em></td>
</tr>
</tbody>
</table>
{% endunless %}
<table class="pgbreak">
<thead>
<tr>
<th colspan="4"><span class="title">{{ thisBus }}</span></th>
</tr>
<tr>
<th class="colhead tcolw-6 text-left">Name</th>
<th class="colhead tcolw-2 text-center">Grade</th>
<th class="colhead tcolw-2 text-center">To Camp</th>
<th class="colhead tcolw-2 text-center">From Camp</th>
</tr>
</thead>
<tbody>
{% assign totalBuses = totalBuses | Plus:1 %}
{% assign busKidCount = 0 %}
{% endif %}
{% assign lastBus = thisBus %}
{% assign busKidCount = busKidCount | Plus:1 %}
<tr>
<td class="text-left">{{ row.LastName }}, {{ row.NickName }} </td>
<td class="text-center">{{ row.Grade }}</td>
<td class="text-center">__________</td>
<td class="text-center">__________</td>
</tr>
{% if forloop.last %}
<tr>
<td class="text-left"><em>{{ lastBus }} Student Count = {{ busKidCount }}</em></td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th class="title">Total Counts</th>
</tr>
</thead>
<tr>
<td class="colhead">Buses: {{ totalBuses }}</td>
</tr>
<tr>
<td class="colhead">Students: {{ totalKids }}</td>
</tr>
</table>
{% endif %}
{% endfor %}
<footer height=".25" spacing=".1">
<table style="margin:auto; width:90%;">
<tr>
<td style="width:50%;text-align:;left;">
{% assign row = Rows | First %}
<strong>{{ row.GroupName }} - Bus Rosters</strong>
</td>
<td style="width:50%;text-align:right;">As of: {{ 'Now' | Date:'dd-MMM-yyyy hh:mm tt' }}</td>
</tr>
</table>
</footer>
</body>
</html>
QR Code Custom Column
Lava for a custom column on the short-links grid to generate a QR Code
{% capture qrUrl %}https://api.qrserver.com/v1/create-qr-code/?size=1000x1000&data=https://onefuturestl.com/giveaway?Booth={{ Row.Guid }}{% endcapture %}
<a href="{{ qrUrl }}" download target="_blank"><i class="far fa-qrcode"></i></a>
Booth Tracker
Flowchart for the workflow
