Although being able to communicate to registrants or registrations is helpful, we wanted a way to view and communicate with an event's registrations and the corresponding registrants together. This would allow us to generate a communication once and send it to everyone that needs it. A perfect example is student ministry wanting to communicate with everyone that completed a registration for teen camp and the registrants.

To accomplish this, we used a Dynamic Data block along with pre and post HTML to run lava and JavaScript respectively.

The Query:

-- DECLARE @RegistrationInstanceId int;

SELECT 
    'Registree' [Type],
    p.[Id],
    ri.[Name] AS [Registration Instance],
    p.[FirstName], 
    p.[LastName], 
    p.[Email],
    ph.[NumberFormatted] AS [Mobile Phone]
FROM Registration r
JOIN RegistrationInstance ri ON r.RegistrationInstanceId = ri.Id
JOIN PersonAlias pa ON pa.Id = r.PersonAliasId
    INNER JOIN Person p ON p.Id = pa.PersonId
    INNER JOIN PhoneNumber ph ON ph.PersonId = pa.PersonId
WHERE r.RegistrationInstanceId = @RegistrationInstanceId AND ph.NumberTypeValueId = 12

UNION

SELECT
    'Registrant' [Type],
    p.[Id],
    ri.[Name] AS [Registration Instance],
    p.[FirstName],
    p.[LastName],
    p.[Email],
    ph.[NumberFormatted] AS [Phone]
FROM Registration r
JOIN RegistrationInstance ri ON r.RegistrationInstanceId = ri.Id
JOIN RegistrationRegistrant rr ON rr.RegistrationId = r.Id
JOIN PersonAlias pa ON pa.Id = rr.PersonAliasId
    INNER JOIN Person p ON p.Id = pa.PersonId
    INNER JOIN PhoneNumber ph ON ph.PersonId = pa.PersonId
WHERE r.RegistrationInstanceId = @RegistrationInstanceId AND ph.NumberTypeValueId = 12

The query uses a RegistrationInstanceId parameter to pull all registrations and UNION with all registrants for that specific instance. The query also joins the results with the Person and PhoneNumber tables. For the PhoneNumber, it only looks for numbers with a NumberValueTypeId of 12 (Mobile).

Block Pre-HTML:

This is pulling all RegistrationInstances that are active and populating them into a select input. This allows for the data to be specific to that instance.

Block Post-HTML:

This bit of JavaScript does two things. The first function gets the RegistrationInstanceId query parameter and sets it as the selected value in the dropdown input. The second function takes the Registration Instance selected by the user, generates a new URL, and replaces the current URL in the browser with the newly created URL. This allows the dropdown to act as a filter on the page and the resulting URL to be something like <rockinstance>/page/1188?RegistrationInstanceId=37.

Final Page: