What: We wanted to find historical info the contributions made on behalf of a specific team member's previous Short-Term Mission Trips.  We have the trips set up as Fundraising Opportunities. 

Why: When processing new records, knowing who gave to whom can help match previous donors with the same name but different addresses, or spouses, if one gave in the past and a different spouse gave this time. Many of the contributors are new and do not consider our church their church home, so will not already have existing records, but you never know! Side note: we mark them with a specific Connection Status so they are not pulled in to church communications outside of their specific short-term missions trip interest.

How: I created a tab on the person record called STM Giving, which is only visible to security profiles RSR-Rock Administration and to RSR-Finance Administration. The tab has two Dynamic Data blocks.  One queries any trips the person has given to, the other queries who has given to trips on the person's behalf.  I used Sections B1 and B2 so the blocks could be parallel to each other on the page without needing to format with divs.

Skills and permission needed:

• create or copy a page in CMS
• edit blocks on a page
• set security on a page.

Information needed:

• [FinancialTransactionDetail].[EntityTypeId] for short-term missions gifts (ours = 90)
• [Group].[GroupTypeId] for short-term trips.  We use Fundraising Opportunities for our short-term trips (ours = 31).

Create the Person Page:

I like to copy from an existing page in the person profile so I don't have to add all of the page parameters from scratch.  The Security page works well here.

1. Go to Admin Tools > CMS Configuration > Pages
2. Go to Internal Homepage > People > Person Pages > Security
3. Click the Copy icon. Do not include child pages.
4. Edit your new page and change the Internal Name, Page Title, Browser Title and Description as you wish.  I called all three titles STM giving.  All of the other page information should remain the same as the page you copied from.
5. Go to the Login Zone dropdown and choose Section C1, then delete all of the blocks there.  If copying from a different page, delete the blocks you won't need.
6. Set the page security to limit the viewers as appropriate.
7. You may need to go to the parent page, Person Pages, and Edit the child pages to drag the new page where you want it in your tab order.

Add the Giver data block:

1. Go to Section B1 and add a Dynamic Data 🎉 block.  I named it Dynamic Data Giver. 
2. Edit the block.
3. Paste this code into the SQL Query field.  NOTE your [FinancialTransactionDetail].[EntityTypeId] may not = 90 and  [Group].[GroupTypeId] may not = 31.   Use your own values for short-term trip transactions and group type there.

  SELECT DISTINCT

        P.[lastname] AS [Last Name],
        P.[Nickname] AS [Nick Name],
        TD.[Amount],
        G.[Name] as [Trip]
    
FROM [FinancialTransactionDetail] TD 
    INNER JOIN [GroupMember] M on M.[Id] = TD.[EntityId]
    INNER JOIN [FinancialTransaction] T ON T.[Id] = TD.[TransactionId]
    INNER JOIN [Person] P ON P.[Id] = M.[PersonId]
    INNER JOIN [PersonAlias] PA ON PA.[Id] = T.[AuthorizedPersonAliasId]
    INNER JOIN [Person] D ON D.[Id] = PA.[PersonId] 
    INNER JOIN [Group] G ON M.[GroupId] = G.[Id] AND G.[GroupTypeId] =31

    WHERE TD.[EntityTypeId] = 90 AND  D.Id = @PersonId

    ORDER BY P.[LastName], P.[NickName]
 

3. Add a Query Parameter of

   PersonId=

4. Scroll down and press [ Reload Columns from Query ]
5. Set the Amount format to Currency
6. [ Save ]

Add the Recipient data block:

1. Go to Section B2 and add a Dynamic Data 🎉 block.  I named it Dynamic Data Recipient.
2. Edit the block.
3. Paste this code into the SQL Query field. NOTE your [FinancialTransactionDetail].[EntityTypeId] may not = 90 and  [Group].[GroupTypeId] may not = 31.   Use your own values for short-term trip transactions and group type there.

  SELECT DISTINCT

	D.LastName, 
	D.NickName,
        TD.[Amount],
        G.[Name] AS [Trip]
    FROM [GroupMember] M 

    INNER JOIN [FinancialTransactionDetail] TD ON TD.[EntityTypeId] = 90 AND TD.[EntityId] = M.[Id]
    INNER JOIN [FinancialTransaction] T ON T.[Id] = TD.[TransactionId]
    INNER JOIN [Person] P ON P.[Id] = M.[PersonId]
    INNER JOIN [PersonAlias] PA ON PA.[Id] = T.[AuthorizedPersonAliasId]
    INNER JOIN [Person] D ON D.[Id] = PA.[PersonId]
    LEFT JOIN [Group] G ON M.[GroupId] = G.[Id] 

    WHERE  P.Id = @PersonId and G.[GroupTypeId] =31

    ORDER BY D.[LastName], D.[NickName]

3. Add a Query Parameter of

   PersonId=

4. Scroll down and press [ Reload Columns from Query ]
5. Set the Amount format to Currency
6. [ Save ]

Find someone who will have given or received toward a short-term trip and view their page. Get yourself a cup of coffee.