2 Duplicate Dashboard Shared by Stan Yoder, The Crossing 2 months ago 12.0 Reporting Intermediate Overview We designed this tool to help us more quickly identify potential duplicate profiles and profiles which shared an email or phone number but shouldn't. The user first selects which person they want to find potential dups for. Then they chose up to 3 of those potential dups to see a comparison of their data. Any properties/attributes which match between the first person and the potential dups are highlighted. Many of the report fields are clickable links that go the person search page with the appropriate search term. The Setup Webpage on Internal Site Page Parameter Filter block HTML block 1. Webpage on Internal Site I'm going to presume you can handle creating a new webpage with all authentication and access. 2. Page Parameter Filter block Add Page Parameter Filter block to new webpage Adjust settings as follows: Add a new filter: Name: Person 1 Key: Person1 Field Type: Person Add 3 more filters (Person 2, 3, & 4): Name: Person 2 Key: Person2 Field Type: Single-Select Values: {% assign person1Guid = 'Global' | PageParameter:'Person1' %} {% if person1Guid != null and person1Guid != empty %} {% assign prsn1 = person1Guid | PersonByAliasGuid %} {% assign prsn1Id = prsn1.Id %} {% assign lastName = prsn1.LastName %} {% assign firstInitial = prsn1.FirstName | Slice:0,1 | Append:'%' %} {% assign nickInitial = prsn1.NickName | Slice:0,1 | Append:'%' %} {% assign email = prsn1.Email %} {% assign mobilePhone = prsn1 | PhoneNumber:'Mobile' | Default:'0' | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign homePhone = prsn1 | PhoneNumber:'Home' | Default:'0' | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign workPhone = prsn1 | PhoneNumber:'Work' | Default:'0' | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign phones = '' | AddToArray:mobilePhone | AddToArray:homePhone | AddToArray:workPhone | RemoveFromArray:'0' %} {% assign phoneNumbers = phones | Join:"','" | Prepend:"('" | Append:"')" %} SELECT CONCAT( '{{ person1Guid }}' , '|' , p.[Guid] ) AS 'Value' , CONCAT( p.[FirstName] , ' ' , p.[LastName] , ' (Id: ' , p.[Id] , ')' ) AS 'Text' FROM [Person] p WHERE p.[Id] != {{ prsn1Id }} AND ( (p.[LastName] = '{{ lastName | Escape }}' AND ( p.[FirstName] LIKE '{{ firstInitial }}' OR p.[NickName] LIKE '{{ firstInitial }}' OR p.[FirstName] LIKE '{{ nickInitial }}' OR p.[NickName] LIKE '{{ nickInitial }}' ) ) OR ( p.[Email] = '{{ email }}' and p.[Email] != '')) UNION SELECT CONCAT( '{{ person1Guid }}' , '|' , p.[Guid] ) AS 'Value' , CONCAT( p.[FirstName] , ' ' , p.[LastName] , ' (Id: ' , p.[Id] , ')' ) AS 'Text' FROM [PhoneNumber] pn INNER JOIN [Person] p ON p.[ID] = pn.[PersonId] AND p.RecordTypeValueId != 2 AND p.RecordTypeValueId != 1619 WHERE p.[Id] != {{ prsn1Id }} AND Number in {{ phoneNumbers }} AND Number != '' ORDER BY 'Text' {% endif %} {{ results }} 3. HTML block Add HTML block to new webpage Enable "Rock Entity" for Lava Commands Content: (NOTE: Just for an example, I've left in several person attributes which we use. You will want to remove the code related to these.): <style> .dupdash { .panel-group { margin-bottom: 2px; } .panel-heading { padding: 12px; } .panel-title { font-size: 16px; font-weight: 500; } .table-dupdash { padding: 3px 5px; font-size: 14px; width: 100%; .col-labels { width: 16%; } .col-profile-1 { width: 84%; } .col-profile-2 { width: 42%; } .col-profile-3 { width: 28%; } .col-profile-4 { width: 21%; } th { font-weight: 500; background-color: #e2ddd5; } .attr-label { font-weight: 600; } span { padding: 2px 0px; border-radius: 5px; overflow-wrap: anywhere; } .hilite-yes { background-color: #ffbaba; } } } </style> {% comment %} Get page parameters {% endcomment %} {% assign person1AliasGuid = 'Global' | PageParameter:'Person1' | Default:'0' %} {% assign person2Param = 'Global' | PageParameter:'Person2' | Default:'0' %} {% assign person3Param = 'Global' | PageParameter:'Person3' | Default:'0' %} {% assign person4Param = 'Global' | PageParameter:'Person4' | Default:'0' %} {% assign personList = '' %} {% if person1AliasGuid != 0 %} {% assign person = person1AliasGuid | PersonByAliasGuid %} {% assign personList = personList | AddToArray:person %} {% if person2Param != 0 %} {% assign person2Parts = person2Param | Split:'|' %} {% assign person2Check = person2Parts | First %} {% assign person2Guid = person2Parts | Last %} {% if person2Check == person1AliasGuid %} {% assign person = person2Guid | PersonByGuid %} {% assign personList = personList | AddToArray:person %} {% endif %} {% endif %} {% if person3Param != 0 %} {% assign person3Parts = person3Param | Split:'|' %} {% assign person3Check = person3Parts | First %} {% assign person3Guid = person3Parts | Last %} {% if person3Check == person1AliasGuid %} {% assign person = person3Guid | PersonByGuid %} {% assign personList = personList | AddToArray:person %} {% endif %} {% endif %} {% if person4Param != 0 %} {% assign person4Parts = person4Param | Split:'|' %} {% assign person4Check = person4Parts | First %} {% assign person4Guid = person4Parts | Last %} {% if person4Check == person1AliasGuid %} {% assign person = person4Guid | PersonByGuid %} {% assign personList = personList | AddToArray:person %} {% endif %} {% endif %} {% assign profileCount = personList | Size %} <div class="dupdash"> <div class="panel-group"> <div class="panel panel-block"> <div class="panel-body"> <table class="table table-striped table-dupdash"> <colgroup> <col class="col-labels"> <col span="4" class="col-profile-{{ profileCount }}"> </colgroup> <thead> <tr> <th></th> {% for person in personList %} <th> <a target="_blank" href="/person/{{ person.Id }}"> {{ person.FirstName }} {% if person.NickName != person.FirstName %} ({{ person.NickName }}) {% endif %} {{ person.LastName }} [Id: {{ person.Id }}] </a> </th> {% endfor %} {% if profileCount == 0 %} <th colspan="4"><em>Select someone for Person 1 above - then choose from possible matching profiles</em></th> {% endif %} </tr> </thead> <tbody> <tr> <td class="attr-label">Connection Status</td> {% for person in personList %} {% assign val = person.ConnectionStatusValue.Value %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">How Created</td> {% for person in personList %} {% assign val = person | Attribute:'HowCreated' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">When Created</td> {% for person in personList %} {% assign val = person.CreatedDateTime | Date:'M/d/yyyy' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Record Status</td> {% for person in personList %} {% assign val = person.RecordStatusValue.Value %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Has a Login?</td> {% for person in personList %} {% assign loginCnt = person.Users | Size %} {% if loginCnt > 0 %} {% assign val = 'Yes' %} {% else %} {% assign val = 'No' %} {% endif %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Age</td> {% for person in personList %} {% assign val = person.Age %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Birthdate</td> {% for person in personList %} {% assign val = person.BirthDate | Date:'M/d/yyyy' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/birthdate?SearchTerm={{ val | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Email</td> {% for person in personList %} {% assign val = person.Email | Downcase | Trim %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/email?SearchTerm={{ person.Email | EscapeDataString }}"> <span class="{{ td-class }}">{{ person.Email }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Search Keys</td> {% for person in personList %} {% assign pid = person.Id %} {% if forloop.first %} {% assign firstPerson = true %} {% assign p1Emails = null %} {% assign val = person.Email | Downcase | Trim %} {% if val != null and val != empty %} {% assign p1Emails = p1Emails | AddToArray:val %} {% endif %} {% else %} {% assign firstPerson = false %} {% endif %} {% assign emailList = null %} {% personsearchkey expression:'PersonAlias.Person.Id == "{{ pid }}"' where:'SearchTypeValueId=="1198"' securityenabled:'false' %} {% assign searchKeys = personsearchkeyItems %} {% endpersonsearchkey%} {% for searchKey in searchKeys %} {% assign searchEmail = searchKey.SearchValue | Downcase | Trim %} {% assign emailList = emailList | AddToArray:searchEmail %} {% if firstPerson == true %} {% assign p1Emails = p1Emails | AddToArray:searchEmail %} {% endif %} {% endfor %} <td class="attr-value"> {% for email in emailList %} {% if firstPerson == true %} {% assign td-class = 'hilite-no' %} {% else %} {% assign foundEmail = p1Emails | Contains:email %} {% if foundEmail == true %} {% assign td-class = 'hilite-yes'%} {% else %} {% assign td-class = 'hilite-no' %} {% endif %} {% endif %} <a target="_blank" href="/Person/Search/email?SearchTerm={{ email | EscapeDataString }}"> <span class="{{ td-class }}">{{ email }}</span> </a> {% unless forloop.last %}<br>{% endunless %} {% endfor %} </td> {% endfor %} </tr> <tr> <td class="attr-label">Mobile Phone</td> {% for person in personList %} {% assign val = person | PhoneNumber:'Mobile' %} {% assign compVal = val | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% if compVal != null and compVal != empty %} {% assign phoneList = null | AddToArray:compVal %} {% endif %} {% assign homephone = person | PhoneNumber:'Home' | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% if homephone != null and homephone != empty %} {% assign phoneList = phoneList | AddToArray:homephone %} {% endif %} {% assign workphone = person | PhoneNumber:'Work' | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% if workphone != null and workphone != empty %} {% assign phoneList = phoneList | AddToArray:workphone %} {% endif %} {% elseif compVal != empty and compVal != null %} {% assign foundPhone = phoneList | Contains:compVal %} {% if foundPhone == true %} {% assign td-class = 'hilite-yes'%} {% endif %} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/phone?SearchTerm={{ val | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Home Phone</td> {% for person in personList %} {% assign val = person | PhoneNumber:'Home' %} {% assign compVal = val | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign td-class = 'hilite-no'%} {% if forloop.index > 1 and compVal != empty and compVal != null %} {% assign foundPhone = phoneList | Contains:compVal %} {% if foundPhone == true %} {% assign td-class = 'hilite-yes'%} {% endif %} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/phone?SearchTerm={{ val | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Work Phone</td> {% for person in personList %} {% assign val = person | PhoneNumber:'Work' %} {% assign compVal = val | Replace:'(','' | Replace:') ','' | Replace:'-' %} {% assign td-class = 'hilite-no'%} {% if forloop.index > 1 and compVal != empty and compVal != null %} {% assign foundPhone = phoneList | Contains:compVal %} {% if foundPhone == true %} {% assign td-class = 'hilite-yes'%} {% endif %} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/phone?SearchTerm={{ val | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Home Address</td> {% for person in personList %} {% assign val = person | Address:'Home','[[FormattedHtmlAddress]]' %} {% assign searchVal = person | Address:'Home','[[Street1]]' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/address?SearchTerm={{ searchVal | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> <tr> <td class="attr-label">Campus</td> {% for person in personList %} {% assign val = person | Campus %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Spouse</td> {% for person in personList %} {% assign val = person | Spouse | Property:'FullName' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Anniversary</td> {% for person in personList %} {% assign val = person.AnniversaryDate | Date:'M/d/yyyy' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Children</td> {% for person in personList %} {% assign children = person | Children %} {% assign val = '' %} {% for child in children %} {% capture val %} {{ val }} {% unless forloop.first %}, {% endunless %} {{ child.FullName }} {% endcapture %} {% endfor %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> {% comment %} BEGIN Crossing specific attributes {% endcomment %} <tr> <td class="attr-label">Baptized?</td> {% for person in personList %} {% assign val = person | Attribute:'Baptized' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Baptism Date</td> {% for person in personList %} {% assign val = person | Attribute:'BaptismDate' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Vision Dinner</td> {% for person in personList %} {% assign val = person | Attribute:'VisionDinner' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">G1 Course</td> {% for person in personList %} {% assign val = person | Attribute:'G1Course' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Explorations</td> {% for person in personList %} {% assign val = person | Attribute:'Explorations' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> <tr> <td class="attr-label">Leadership U</td> {% for person in personList %} {% assign val = person | Attribute:'LeadershipU' %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"><span class="{{ td-class }}">{{ val }}</span></td> {% endfor %} </tr> {% comment %} END Crossing specific attributes {% endcomment %} <tr> <td class="attr-label">Name Search</td> {% for person in personList %} {% capture val %} {{ person.LastName }}, {{ person.FirstName | Slice:0,1 }} {% endcapture %} {% assign td-class = 'hilite-no'%} {% if forloop.first %} {% assign firstVal = val %} {% elseif val == firstVal and val != empty and val != null %} {% assign td-class = 'hilite-yes'%} {% endif %} <td class="attr-value"> <a target="_blank" href="/Person/Search/name?SearchTerm={{ val | EscapeDataString }}"> <span class="{{ td-class }}">{{ val }}</span> </a> </td> {% endfor %} </tr> </tbody> </table> </div> </div> </div> </div> {% else %} <div class="alert alert-info">Please select someone for Person 1 to see results.</div> {% endif %} BONUS To make this tool even more accessible/usable - we added links to the webpage from 2 places under Data Integrity: the Duplicate Finder page/block & the Pending Individuals report. Duplicate Finder page Add a Custom Column under Custom Grid Options for the Duplicate Finder block Lava Template: Be sure to change the href to match your webpage. {% assign p1Guid = Row.PersonId | PersonById | Property:'PrimaryAlias.Guid' %} <a class="btn btn-default" target="_blank" href="/dupdash?Person1={{ p1Guid }}" title="Duplicates Dashboard"> <i class="far fa-user-friends"></i> </a> Pending Individuals report Add an additional column to the report Template: Be sure to change the href to match your webpage. {% assign p1Guid = Row.PersonId | PersonById | Property:'PrimaryAlias.Guid' %} <a class="btn btn-default" target="_blank" href="/dupdash?Person1={{ p1Guid }}" title="Duplicates Dashboard"> <i class="far fa-user-friends"></i> </a>