As the end of the year winds down and we move our focus to the reason for the season, giving to non-profits and churches increases. This makes ensuring that your person and giving records are in tip-top shape as you gear up towards sending out those yearly contribution statements in January.

Here is a list of things to look out for and potentially correct now before you uncover them in your EOY reporting process:

• Duplicate Person Records (you know you have them and it's even worse when there are gifts attributed to multiple person records that really need to be merged into one). How does this happen? Here is a short list of the most common reasons that duplicate records get created, many of them are created from Event Registrations and Workflow forms:
  • The parent's email is on the child's record
  • A person gives with a new name not on their person record (many times churches only have a person's short or nickname in the FirstName field instead of the NickName field and their legal first name is vastly different. Ex: Margaret | Mag or Charles | Chuck
  • A person gives with a new email not on a RockRMS record
• Children with the "Combine Giving With" field set to that of their parents in the Advanced Settings of their person profile. This field should be set to null for 99% of your child records in Rock unless there are some specific cases as some children do give to the church. This setting adds the child to the same GivingGroupId of their parents. This causes many challenges such as:
  • Adding the child's name to the FamilySalutation which then gets listed on the Contribution Statement. This is a calculated field which gets rendered and stored into the Analytics Giving Unit table as well as being an available lava field. 
  • Listing the child in the Giving Analytics block as having given because they are combined with their parents causing confusion for the finance team.
  • Any DVs created based on giving will often-times add in the children to the results. You may be using these DVs in order to send out your contribution statements via email using Jim Michael's epic recipe or physical mailings. This can cause a bunch of confusion because it may send an additional statement email to the parents if the child has their parents email on their record with the name of their child in the letter for the contribution statement.
• General Person Record field maintenance:
  • First name fields with an "&" or "and" combined with their spouses first name, ex. Bob & Judy
  • Family (Group Type) Name with "The" prepended, likely from a former ChMS import as Rock only has the surname on the Group name.

Now that we've identified some things to look out for and potentially needing to be corrected, let's go over how to address some of these issues.

Duplicate Person Records

I'm not going to spend too much time on this topic as you likely (hopefully) already have a process for dealing with duplicate records and actively monitor and deal with these. I'll simply offer up a few documentation areas regarding duplicates and a few simple queries and DVs to consider.
RockRMS Documentation on Duplicates

New Pending Person Data View and Report:

New Pending Person Record Alert:

At the bottom of this recipe I've included a zip file that includes a New Pending Person Record Workflow that you may download and import as well as a System Communication Email template. Now if you're a very large church and run regular event registrations you may not want this constant alert setup, but for mid to small sized churches with very few staff or administration team members, this could be helpful to them.

This workflow combined with a simple Workflow Trigger will alert any team member you want for new pending person records.

To use this:

1. Create a new System Communication Template (find the HTML template file in the attached zip)
2. Import and Edit the Workflow:
   1. Set the Group to Notify workflow attribute to the group of people that you want the email alerts to go to
   2. Set the System Communication in the Send Email activity to the one which you just created
3. Create a new Workflow Trigger as outlined below and set the Workflow Type to the one you imported and edited:

Children with Combine Giving With or Transactions on their Person Record

Some churches are very diligent with tracking the tithing of children with cash and sometimes checks. But rarely would a legitimate gift or event payment be from a child via credit card. It is possible, but usually unlikely and should be attributed to a parent. Below are some SQL queries that you can use to research your child records: 

• Find all person records that are marked as Children with an Age less than 18 that have "Combine Giving With" field set to their parents:

  Note: Know that there can be children older than 19 that have the Flag Lock as Child selected on their profile in the Advanced Options.

  SELECT
     p.Id,
     p.FirstName,
     p.LastName,
     p.GivingGroupId,
     dp.Age
  FROM
     [Person] p
     JOIN [AnalyticsDimPersonCurrent] dp
         ON p.Id = dp.PersonId
     WHERE p.[AgeClassification] = 2 -- Child
         AND (dp.Age < 18 OR dp.Age is null)
         AND p.GivingGroupId is not null -- Combine Giving With setting in Advanced Settings on Person Profile
     ORDER BY dp.Age asc  

  ! IMPORTANT ! Running the following in the SQL Command tool will wipe out the Combine Giving With field for all children and is possibly irreversible. You'll have to flip the select switch in the SQL Command tool prior to running. You should take an Excel export backup of the original Selection result from above prior to running this update. You may want to exclude certain children from this update. It's always good to test with one child record first before doing them all. Un-comment out the extra AND [Id] line to test a single record.
  Note: Updating records via a SQL Command UPDATE will not populate the Person History timeline.

  UPDATE [Person]
  SET [GivingGroupId] = null
          WHERE [AgeClassification] = 2
  AND [GivingGroupId] is not null
      -- Note: the extra filters below may be used as you see
      -- AND [Id] = 358 -- used to test a single record first, change to child person Id of your choosing
      -- AND [Id] not in (358,362) -- used to exclude a list of children records from having their GivingGroupId removed, adjust you children Ids of your choosing)
  

  
  

• Find all person records that are children that truly do have a Transaction tied to them:

  These you may have to fix and attribute to one of the parents. But some of these may be legit if it's a teen that is giving on their own or paying for their own registration. You may find that some of these children are really adults but their record doesn't reflect this. This is why pulling their Age is important, so that you can go through and check these:

  SELECT DISTINCT
      p.Id,
      p.FirstName,
      p.LastName,
      p.GivingGroupId,
      dp.Age,
      t.TransactionId,
      t.TransactionCode,
      t.TransactionDateTime,
      t.Amount,
      CASE WHEN t.TransactionTypeValueId = 53 THEN 'Contribution' ELSE 'Event Registration' END as [Type]
  FROM
      [Person] p
      JOIN [PersonAlias] pa
          ON pa.PersonId = p.Id
      JOIN [AnalyticsDimPersonCurrent] dp
          ON p.Id = dp.PersonId
      JOIN [AnalyticsSourceFinancialTransaction] t
          ON t.AuthorizedPersonAliasId = pa.Id
  WHERE p.[AgeClassification] = 2 -- Child
      AND (dp.Age < 18 OR dp.Age is null)
      AND t.TransactionId is not null -- A real transaction is attributed with this child, not the parent
      AND DATEPART(year, t.TransactionDateTime) >= '2021'
      -- Note: the extra filters below may be used as you see fit
      -- AND p.GivingGroupId is not null -- Combine Giving With setting in Advanced Settings on Person Profile
      -- AND t.CurrencyTypeValueId not in (6,9,667)  -- cash, check, non cash asset (note these valueIds may not match your system, adjust accordingly)
      -- AND t.Amount > 100 -- transaction amount threshold to ignore smaller gift amounts
  ORDER BY t.TransactionDateTime desc, dp.Age asc    

  Depending on your results you may decide the following:

  • Transactions are tied to children are good and no action is needed
  • Transactions should be attributed to one of the parents, these were matched or entered in error to the child
  • The child is actually an adult and the record updates have been stale (children without birthdays are hard to figure out that they've become adults and are now tithing)

Updating any of the transactions which are tied to child records in mass is not a likely scenario and therefore I won't be covering what to do with these records as you likely already have a process outlined in order to rectify any transactions attributed to someone incorrectly.

General Person Record Field Maintenance

This section is typically a manual process as more detailed information is learned, gathered, and entered about a person or family group. However there are ways to spot errors across many records and ways to clean them up in one quick SQL update.

In order to best explain this process see the other new recipe specific to this clean-up process.