Recipe - Registrations Tab on Person Profile

Skill level: Beginner

Organization: The Well Community Church

Requires Rock: 1.12.0

{# strip images & classes from the HTML but otherwise leave structure #}

Description

I came across this idea for adding a Registrations tab on person profiles and agreed it could be really helpful and actually shouldn't be too difficult to implement, so here's what I came up with…

Security Caution
This recipe does not take security into account. All registrations will be visible on the registrations tab, even if the registration template is restricted to only certain staff.

If registration visibility is a concern for you, please see Tony Visconti's comment below for an alternate version that takes security into account.

How-To

Create a new page under Internal Homepage > People > Person Pages and name it Registrations.

Basic Settings
- Layout: PersonDetail
Advanced Settings
- Page Routes: Person/{PersonId}/Registrations
- Person Parameter Name: PersonId

Add a new Dynamic Data block to the SectionC1 zone.

Block Properties
- Update Page: No

Block Criteria

Parameters: PersonId=
Hide Columns: TemplateID,InstanceID,RegistrationID
Selection URL: /page/405?RegistrationId={RegistrationID}&RegistrationInstanceId={InstanceID}
Wrap in Panel: Yes
Panel Title: Registrations
Panel Icon CSS Class: fa fa-clipboard-list

Query:

SELECT TemplateID, InstanceID, RegistrationID, 
    RT.Name AS Template, RI.Name AS Instance, CAST(R.CreatedDateTime AS Date) AS Date, 
    RC.RegistrantCount AS Registrants,
    CAST(MAX(Registrar) AS bit) AS Registrar, 
    CAST(MAX(Registrant) AS bit) AS Registrant
FROM (
        SELECT RT.ID AS TemplateID, RI.ID AS InstanceID, R.ID AS RegistrationID, 1 AS Registrar, 0 AS Registrant
        FROM PersonAlias PA INNER JOIN
            Registration R ON R.PersonAliasID = PA.ID INNER JOIN
            RegistrationInstance RI ON RI.ID = R.RegistrationInstanceID INNER JOIN
            RegistrationTemplate RT ON RT.ID = RI.RegistrationTemplateID
        WHERE PA.PersonID = @PersonID
        
        UNION ALL
        
        SELECT RT.ID AS TemplateID, RI.ID AS InstanceID, R.ID AS RegistrationID, 0 AS Registrar, 1 AS Registrant
        FROM PersonAlias PA INNER JOIN
            RegistrationRegistrant RR ON RR.PersonAliasID = PA.ID INNER JOIN
            Registration R ON R.ID = RR.RegistrationID INNER JOIN
            RegistrationInstance RI ON RI.ID = R.RegistrationInstanceID INNER JOIN
            RegistrationTemplate RT ON RT.ID = RI.RegistrationTemplateID
        WHERE PA.PersonID = @PersonID
        
    ) X INNER JOIN
    RegistrationTemplate RT ON RT.ID = X.TemplateID INNER JOIN
    RegistrationInstance RI ON RI.ID = X.InstanceID INNER JOIN
    Registration R ON R.ID = X.RegistrationID CROSS APPLY
    (SELECT COUNT(*) AS RegistrantCount FROM RegistrationRegistrant WHERE RegistrationID = R.ID) RC 
GROUP BY TemplateID, InstanceID, RegistrationID, RT.Name, RI.Name, R.CreatedDateTime, RC.RegistrantCount
ORDER BY Date DESC, Registrant DESC, Registrar DESC

That's it! Every person profile in Rock will now have a Registrations tab that shows all of the Event Registrations they're associated with. Clicking on any of the registrations will take you directly to the details for that registration.

Follow Up

Please don't hesitate to leave a comment below or hit me up on Rock Chat (@JeffRichmond) if you have questions or find any issues with this recipe.

If you come up with better or more efficient ways of doing anything in this recipe, please let me know. Thanks!

Change Log

2022-10-13 - Initial Version
2023-11-07 - Added note about Tony's alternate version

Screenshots

/GetImage.ashx?guid=f7c0b190-bbb1-4489-b433-5895465c3fb2
/GetImage.ashx?guid=8709884b-96af-47f7-bcfa-3d72f62db0e0