What

This is a very simple report that shows you any Person records with a & or and in the First, Middle, or Last name properties.

Why

There are many ways people can create records in your system (Online Giving, Event Registration, New Family Preregistration, or just a new login account to name a few) and for who-knows-what reasons some people like to enter "Jack and Jill Hill" as the first name, thinking "we're a couple!" or somesuch and not realizing that this practice is seriously trashing your data. This report helps you find such Person records so you can fix them.

How

First, we need to create a page for the report to live on. I added ours as a button on the existing Tools | Data Integrity menu as that's where it naturally belongs.

1. Go to Tools | Data Integrity
2. In the Admin Toolbar (hover over the bottom of your screen) choose the Child Pages ( icon) and add a new page.
3. Give it an Internal Name of Bogus Name Finder and leave Layout at full width, then click Add.
4. You should now see your new page in the list of Child Pages. click the link to go to your new Bogus Name Finder page.

Now we need to add a Dynamic Data block to our new page.

1. In the Admin Toolbar, click Page Zones ( icon) to go into Zone Edit mode.
2. Click the flyout on the Main Zone and then click the Zone Blocks icon.
3. Add a new block to this zone and choose Dynamic Data from the list of blocks and click Save

Now we need to configure the new block.

1. On the Admin Toolbar, click Block Configuration ( icon)
2. Click the flyout on the Dynamic Data block we added and click Edit Criteria ( icon)
3. Paste the following SQL into the Query box:

   SELECT
       '<a href="/Person/' + CONVERT(varchar(50), Id) + '">' + CONVERT(varchar(50), NickName) + ' ' + CONVERT(varchar(50), LastName) + '</a>' as 'Person Link',
       ConnectionStatusValueId as 'Connection Status', FirstName as 'First Name', NickName as 'Nickname', MiddleName as 'Middle Name',  LastName as 'Last Name', Email as 'Email Address', CreatedDateTime as 'Created'
   FROM
       [Person]
   WHERE
       RecordTypeValueId = 1 and (
       FirstName like '%&%' or
       NickName like '%&%' or
   	MiddleName like '%&%' or
   	LastName like '%&%' or
       FirstName like '% and %' or  
       NickName like '% and %' or 
       MiddleName like '% and %' or
       LastName like '% and %')
           

4. Save the block

That's it! Assuming you copy/pasted the SQL correct, the block should run without any error. You may or may not get any records back... that all depends on if you have any people with & or and in their First, Middle, or Last Names.

Polishing up

Now that you have a working report, there are a few things you can do to spiff it up.

1. Add a HTML block above the Dynamic Data block with some explanatory text, so people know what it does. This is what we use, or you can roll your own:

   <div class="alert alert-danger">
   This report finds people that entered "bogus" First, Middle, Nickname, OR Last names like "Jack and Jill Hill" or "Jack & Jill Hill"
   <br /><br />
   This can occur when a user creates an account or gives on the website and enters an improper name for two people. All such records should be corrected by manually creating the second person in the family and fixing up the names.
   </div>
           

2. On the Bogus Name Finder page, give it a nice icon... edit the page properties ( icon in Admin Toolbar) and set the Icon CSS Class to fa fa-eye.
3. Now go back to Tools | Data Integrity and observe you have a nice Bogus Name Finder button with the icon.