What

This is a very simple report that shows you any mobile numbers that are duplicated on profiles

Why

As we began working on issues related to searching on mobile numbers we realized that mobile numbers have been put in all over the place, especially on children during the check-in process.  It became important that we tried to begin the process of cleaning up mobile numbers and making sure they were only on one profile.

Special Thanks

I got this idea from Jim Michaels original Recipe on Bogus Name Finder.  Go check it out!

Also special thanks to Michael Allen (@michaelallen) & Luke Potter (luke.potter) on RocketChat for helping get the SQL figured out.

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 Duplicate Mobile Numbers 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 Duplicate Mobile Numbers page.

Now we need to add a Dynamic Data block to our new page (the report section).

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
       COUNT( [NumberFormatted] ) AS [Duplicates]
       ,[NumberFormatted] AS [Number]
       ,STRING_AGG( CONCAT('',CONCAT(p.[NickName],' ',p.[LastName]),''), ', ' ) AS [People]
   FROM [PhoneNumber] pn
       INNER JOIN [Person] p ON p.[Id] = pn.[PersonId]
   WHERE [NumberTypeValueId] = 12
   GROUP BY [NumberFormatted]
   HAVING COUNT( [NumberFormatted] ) > 1
   ORDER BY [Duplicates] DESC
   

4. Make sure you have the correct NumberTypeValueId
5. Save the block
6. You should see the report formatted and if you would like to have the Total Count Box on top, you can continue below

To add the Total Count Box to the top, here are the steps

1. In the Admin Toolbar, click Page Zones ( icon) to go into Zone Edit mode.
2. Click the flyout on the Feature Zone and then click the Zone Blocks icon. (NOTE: This will put the block on top of the main zone, you could place the total block in the Main Zone and just rearrange it wherever you would like on that page)
3. Add a new block to this zone and choose Dynamic Data from the list of blocks 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
       COUNT(*) Over () AS TotalRecords
   FROM [PhoneNumber] pn
       INNER JOIN [Person] p ON p.[Id] = pn.[PersonId]
   WHERE 
       [NumberTypeValueId] = 12
   GROUP BY [NumberFormatted]
   HAVING COUNT( [NumberFormatted] ) > 1
   

4. Make sure you have the correct NumberTypeValueId

Output the report on the page to make it easy to see the duplicates

1. Click the toggle switch to Customize Results with Lava.  This will open up the formatted output input box.
2. Under Show Grid Actions, make sure nothing is selected.
3. Under Formatting you need to hide Column TotalRecords
4. Copy the code below into the formatted output box.

   
   {% for row in rows %}
      {% assign dupmobilenumbers = row.TotalRecords %}
   {% endfor %}
   <div class="card text-white bg-danger mb-3" style="max-width: 18rem;">
      <div class="card-header">Total Duplicate Mobile Numbers</div>
         <div class="card-body">
            <h1 class="card-title">{{ dupmobilenumbers }}</h5>
            </div>
         </div>
   

5. Save the block

Finishing Up

1. On the Duplicate Mobile Numbers page, give it a nice icon... edit the page properties ( icon in Admin Toolbar) and set the Icon CSS Class to fa fa-phone.
2. Now go back to Tools | Data Integrity and observe you have a nice Duplicate Mobile Numbers button with the icon.

   

3. Start working with your duplicates, hopefully you don't have 10,000+
4. Let me know if something doesn't work when you try it, this is my first recipe and getting the code to display was harder than I thought it would be.
5. Comment below if you implement this and let us know how many you see on your first run so we can begin to pray with you