Our kids summer camp team needed a way to set capacities for individual grades within the same registration instance rather than creating separate instances with unique capacities. This recipe demonstrates one way to accomplish that.

Technically this could be used to select quantities of just about anything without needing to use fees.

Entity Attribute

Setup an entity attribute at /admin/system/entity-attributes. The settings are shown in the example screenshots.

  • Use a qualifier field to limit this to a specific registration template. 
  • Field Type: Key Value List
  • Custom Values: Use the lava command provided, it selects the defined values for grades.
  • Display values first
  • Set default values for each grade.
{% definedvalue where:'DefinedTypeId == 51' securityenabled:'false' sort:'Id desc' %}
    {% for definedvalue in definedvalueItems %}
        {% capture valuePair %}{{ definedvalue.Value }}^{{ definedvalue.Description }}{% endcapture %}
        {% if forloop.last %}{{ valuePair }}{% else %}{{ valuePair }},{% endif %}
    {% endfor %}
{% enddefinedvalue %}

Lava File

Create a lava file with the content below, and store it somewhere that you can reference with the lava include tag..  On the line that has {% assign registrationInstanceAttributeId = 48882 %}, replace 48882 with the Id of the attribute you created above, which can be found at the top of the block when editing the attribute. In my case, I saved this file as eventGradeCapacities.lava. Review the sql query and referenced attributes below to make sure they correctly reference values in your own database.

{% comment %}
This file calculates and displays the available spots for different school grades in a registration process, based on set capacities on the instance and the number of registrants already registered for those grades. It can be adjusted to either include or exclude grades that are fully booked, based on a user-defined setting.
{% endcomment %}

{% assign host = 'Global' | Page:'Host' %}
{% assign publicAppRoot = 'Global' | Attribute:'PublicApplicationRoot' %}
{% comment %}We only want to hide the at/over capacity grades on the external site during registration. Otherwise we'll see blank grade values on the registrants page.{% endcomment %}
{% if publicAppRoot contains host %}
    {% comment %}Hide grades that are at capacity if we're on the external site.{% endcomment %}
    {% assign hideAtCapacity = 1 %}
{% else %}
    {% comment %}Don't hide if we're not on the external site.{% endcomment %}
    {% assign hideAtCapacity = 0 %}
{% endif %}

{% comment %}Assign variables used in the query.{% endcomment %}
{% comment %}They need to work for any page the attribtue is viewed on for the registrant, not just the external registration page.{% endcomment %}

{% assign registrationInstanceId = 'Global' | PageParameter:'RegistrationInstanceId' %}

{% comment %}If not provided, get instance ID from registrantId - likely on internal site registrant page.{% endcomment %}
{% assign registrantId = 'Global' | PageParameter:'RegistrantId' %}
{% if registrationInstanceId =='' and registrantId !='' %}
    {% registrationregistrant Id:'{{ registrantId }}' securityenabled:'false' %}
        {% assign registrationInstanceId = registrationregistrant.Registration.RegistrationInstanceId %}
    {% endregistrationregistrant %}
{% endif %}

{% comment %}If still not provided, get instance ID from slug - targetting external site where a url slug linkage has been set and page parameter for instance id is not available.{% endcomment %}
{% assign slug = 'Global' | PageParameter:'Slug' %}
{% if registrationInstanceId =='' and slug !='' %}
    {% sql return:'linkages' %}
        SELECT TOP 1
            [UrlSlug] = '{{ slug }}'
    {% endsql %}
    {% for linkage in linkages %}
        {% assign registrationInstanceId = linkage.RegistrationInstanceId %}
    {% endfor %}
{% endif %}

{% registrationinstance Id:'{{ registrationInstanceId }}' securityenabled:'false %}
    {% assign registrationTemplateId = registrationinstance.RegistrationTemplateId %}
{% endregistrationinstance %}

{% assign registrationInstanceAttributeId = 48882 %}

{% comment %}The registrationRegistrantAttributeKey is set on the template form field question. It's the actual drop down select field.{% endcomment %}
{% assign registrationRegistrantAttributeKey = 'GradeCapacity' %}

{% sql %}
-- CTE to parse and aggregate capacities by grade
WITH GradeCapacities AS (
        -- Parsing the grade value from the key-value pair
            WHEN CHARINDEX('^', kv.value) > 0 THEN RIGHT(kv.value, LEN(kv.value) - CHARINDEX('^', kv.value))
            ELSE NULL 
        END AS GradeValue,
        -- Parsing and casting the capacity value from the key-value pair and summing up
                WHEN CHARINDEX('^', kv.value) > 0 THEN LEFT(kv.value, CHARINDEX('^', kv.value) - 1) 
                ELSE '0' 
             END AS INT)) AS TotalCapacity
        RegistrationInstance ri
        AttributeValue av ON ri.Id = av.EntityId AND av.AttributeId = {{ registrationInstanceAttributeId }}
        STRING_SPLIT(av.Value, '|') kv
        ri.Id = {{ registrationInstanceId }}
            WHEN CHARINDEX('^', kv.value) > 0 THEN RIGHT(kv.value, LEN(kv.value) - CHARINDEX('^', kv.value))
            ELSE NULL 
-- CTE to count registrants for each grade
RegistrantCount AS (
        av.Value AS GradeValue,
        COUNT(*) AS RegistrantCount
        RegistrationRegistrant rr
       AttributeValue av ON rr.Id = av.EntityId
       Attribute a ON av.AttributeId = a.Id AND a.[Key] = '{{ registrationRegistrantAttributeKey }}'
        Registration r ON rr.RegistrationId = r.Id
        RegistrationInstance ri ON r.RegistrationInstanceId = ri.Id
        ri.RegistrationTemplateId = {{ registrationTemplateId }} AND ri.Id = {{ registrationInstanceId }}
-- Main query to select grade capacities and calculate remaining capacity
    dv.Value AS DefinedValue,
    dv.Description AS GradeDescription,
    ISNULL(rc.RegistrantCount, 0) AS RegistrantCount,
    gc.TotalCapacity - ISNULL(rc.RegistrantCount, 0) AS RemainingCapacity
    GradeCapacities gc
    RegistrantCount rc ON gc.GradeValue = rc.GradeValue
    DefinedValue dv ON gc.GradeValue = dv.Value
    DefinedType dt ON dv.DefinedTypeId = dt.Id AND dt.Id = 51
    ({{ hideAtCapacity }} = 0 OR (gc.TotalCapacity - ISNULL(rc.RegistrantCount, 0) > 0))
    dv.Id desc
{% endsql %}

{% comment %}Below is what gets rendered in the single select attribute.{% endcomment %}
{% for result in results %}
    {% assign remaining = result.RemainingCapacity %}
    {% capture resultPair %}{{ result.GradeValue }}^{{ result.GradeDescription }} ({{ remaining }} {{ 'Spots' | PluralizeForQuantity:remaining }} Remaining){% endcapture %}
    {% if forloop.last %}{{ resultPair }}{% else %}{{ resultPair }},{% endif %}
{% endfor %}

Add the field to the template

On the registration template with the Id that you specified in the qualifier field of the instance attribute, create a new registrant form field that is a single select. The Key needs to be GradeCapacity to be referenced properly by the lava file above. The values come from the output of the lava file, so use the include tag to reference that file. The default value selector will give you an error, but you can ignore that. The error occurs because it's trying to reference a registration instance that it can't know yet, because you're not on a registration instance page.

{% comment %}Including the file{% endcomment %}
{% include "~/Content/Lava/eventGradeCapacities.lava" %}

Set Capacities on the Instance

If your instance attribute was set up properly, you should get a key value attribute when editing the registration instance attributes. You may need to set up a page to edit instance attributes if you don't have one already. For us, we have a tab set up on the instance page that links to a page with an entity attribute values block.

From this block you can select a grade and then input the capacity for that grade. If you put a grade in more than once, the capacities will be added together.

Final Result

When it all comes together you should get a form field that checks how many registrants are already registered with a specific grade value. On the external site this should only show grades that have capacity. On the internal site it will show you grades that have 0 or negative capacity.