Create Connections for Event Registration Cancellations

In light of our current season, our church has decided to cancel a few of our summer events. Each of our registrations has a few hundred registrants. It was important to us that we confirmed a few details before refunding the registrations:

1. Notify the family that we cancelled or postponed the event.
2. Ask the family if they would like to donate their cost/fees; receive a refund or pre-register for our event next year.
3. IF receiving a credit card refund - is the previously used card still valid?
4. IF receiving a cash/check refund - is the address on file correct?

We knew our central team was going to be overwhelmed on time trying to confirm these details.

The idea was to separate the registrations according ministry and campus, then ask the campus directors to contact those people from their campus regarding the above questions.

To make this process as easy as possible, we developed two workflows.
Note: I have screenshots around the expected output, at the bottom of this recipe. The first one shows what the connection request will look like. The second one shows what the workflow, after launched form the connection, would look like.

1. A workflow that would create a connection for each [Registered By] registration and assign it to the campus director.
2. A workflow that would be launched from the connection by the campus director.

I can export either workflow for you to import if you would like. Send me a DM on Rocket.Chat @jjones37

Things you will need / assumptions:

1. Free plugin from Southeast Christian Church called Workflow Launcher Plugin
2. Connection Opportunity that you will use (we will not go over how to set up a connection opportunity here)

First Workflow

The first workflow is launched using the Workflow Launcher.

• Select your Workflow
• Select Entity Type: Registration Instance
• Select your Registration Instance
• Select [All Registrants] under Registration [optional]

This first workflow is designed to gather all necessary information from each registration and create a connection assigned to your staff.

This first workflow has a few actions:

• Gets 'Registered By' - This is who the connection will be created for
• Gets 'Campus' - This is the campus listed for the 'Registered By'
• Gets 'Student Director' - We use the Org Chart in Rock and have a group of campus directors by ministry (in this example "Student Directors") sorted by campus. I will use SQL in the workflow to look at the 'Registered By' persons 'Campus'. Then look for that same campus in the Org Chart Group 'Student Directors'. The SQL will then grab the person listed for that campus and make them the workflow attribute 'Student Director'
• Get 'Registration Information' - Here we will grab any information the Campus Director will need when they make their phone calls from the connections. Things like: how much did they pay, how many people did they register and how did they pay.

First Workflow Attributes:

First Workflow Activity 1:

First Workflow Activity 1 Lava:

Get Registered By: [Set Attribute from Entity]

{% assign Registration = Entity %}
{{ Registration.PersonAlias.Guid }}

Get Campus: [Attribute Set Value]

{{ Workflow | Attribute:'registeredby','Object' | Campus | Property:'Guid' }}

Get Student Director: [SQL Run] - We did a SQL run to grab people from the Org Chart; you can also do this by having a workflow attribute for each campus director (based on campus) and use a filtered workflow action to assign the correct person based campus (as received above). Below is an example of the SQL I am using to pull in the correct Student Director.

{% assign person = Workflow | Attribute:'registeredby','Object' %}
{% assign campus = Workflow | Attribute:'Campus','Object' %}

-- Running SQL to get Person Alias ID of Student Directors
SELECT
	PA.Guid AS [Guid]

FROM [Group] G
INNER JOIN GroupMember GM ON GM.GroupId = G.id
INNER JOIN Person P ON P.Id = GM.PersonId
INNER JOIN PersonAlias PA ON PA.PersonId = P.Id
LEFT OUTER JOIN AttributeValue AV ON AV.[EntityId] = GM.id and AV.[AttributeId] = 
    (
		SELECT A.[Id] FROM Attribute A WHERE A.[Key] = 'PersonCampus'
	)
WHERE G.GroupTypeId = 28 -- Organizational Unit
AND g.[Name] = 'Student Directors' -- Filtering to only "Student Directors"
AND AV.[Value] = '{{ campus.Guid }}'

In the second to last line, be sure to change the 'Student Directors' after the [=]. Change this to be the name of the group in your Org Chart.

Get Registration Information [Set Attribute from Entity]

{% assign Registration = Entity %}
{% assign registrant = Registration.PersonAlias.Person %}
{% assign numberRegistered = Registration.Registrants | Size %}

### Registration Info
**Registered By**: {{ Registration.FirstName }} {{ Registration.LastName }} 

**Cost**: {{ Registration.TotalCost | Format:'c' }} 

**Financial Aid**: {{ Registration.DiscountAmount | Format:'c'}} 

**Payments**: {% for payment in Registration.Payments %}
{% assign detail = payment.Transaction.FinancialPaymentDetail %}
{% assign currencyType = detail.CurrencyTypeValueId | FromCache:'DefinedValue' %}
{% assign ccType = detail.CreditCardTypeValueId | FromCache:'DefinedValue' %}
- {{ payment.Amount }} paid with: {{ currencyType.Value }}{% if ccType %} - {{ ccType.Value }}{% endif %}{% if detail.AccountNumberMasked %} ({{ detail.AccountNumberMasked }}){% endif %}
{% endfor %}
**Balance Due**: {{ Registration.BalanceDue | Format:'c' }} 

> **Total Paid**: {{ Registration.TotalPaid | Format:'c'}} (available for refund)

### Students Registered:
{% for reg in Registration.Registrants %}
- **{{ reg.Person.FullName }}**

**Text**: {{ reg | Attribute:'AttributeKey' }} 
{% endfor %}

Note: In the last line I have 'text' followed by lava, that will pull in an attribute from the registration. Replace 'AttributeKey' with the key for the attribute you would like to pull in for each registrant.

You can also pull in a person attribute by using:

{{ reg.Person | Attribute:'ExamplePersonAttribute' }}

{{ reg.GroupMember | Attribute:'ExampleGroupMemberAttribute' }}

First Workflow Activity 2

From here, the workflow will create a connection for each registration.

My second workflow is used in the connection.

This second workflow is designed to help guide the campus director with some of the questions we want them to ask. This workflow will also re-assign the connection to another staff member based on a [single-select] attribute in the workflow. (Donation, Refund or Pre-Registration)

Be sure to go into your Connection Opportunity and add this workflow. ;)

• Sets the 'Connection Request' - This will be used to update the same connection when the workflow completes.
• Sets the 'Registration Information' - Brings in all registration information gathered in the previous workflow.
• User Entry Form - Used to gather information and/or guide the campus director through their call
• Reassign Connection - We will re-assign the connection with a new status depending on a specific option selected in the User Entry Form (Donation Requested, Refund Requested, Pre-Register)
• Sets Connection Status - In this example, I am setting the status of the connection to a few unique statuses. You can use these if you would like. If you use them, be sure to create the connection statuses for the connection opportunity you are using.

Second Workflow Attributes:

For my 'Sinlge-Select' options, I mostly used the only value of 'Yes'. I did this as a basic checklist for the staff.

Second Workflow Activity:

I can also export either workflow for you to import if you would like. Send me a DM on Rocket.Chat @jjones37

Of course we can't forget to thank Michael Allen for his expertise with lava!