Skype Chat: Data Integrity - March 19th

Here is the link to the IdeaBank suggestion that came up during our chat today, about the problem of spelling differences in last names causing constituent records to be updated.

Caroline Barnes:

Quick update on handling address abbreviations ("Ave." is being switched to "Ave" by the NCOA scrub):

 

In Administration > Configuration > Global data entry settings, we have an Address Standardization table.  It is a list of address fields that we can use to replace and standardize address fields such as city and state.  So for example, if you have all three of these in your database:

 

Street

St

St.

 

You can choose which one you want to be standard across your addresses, and enter that as the replacement value for the other two original values. 

 

Once you’ve configured the Address Standardization table you’re not done though – we still have to clean up existing addresses.  There is a global change process in place that we can use to do this.  If you go to Administration > Global Changes and choose the one called “Standardize constituent names/addresses.”  You’ll have to choose a selection of constituents (likely the whole database).  I’d suggest running this process on a regularly scheduled basis (depending on how long it takes to run, either overnight, once weekly, etc.)

 

This will allow you to choose how you want your addresses to look across the board, with or without abbreviation.

Phone formatting:

Currently, we can specify phone formats for phone numbers based on the country selected on the phone record.  Once this is configured and enabled, all phone numbers entered going forward with that country will have the defined format.  However, this doesn't correct phone numbers already in the database.  So, we can have any of the following (and probably more):

1112223333

(111) 222-3333

111-222-3333

The problem here is that when we import a new number (and it's formatted correctly now), unless it's an exact match (number AND formatting), it will not be recognized as a match.  Depending on your configuration options, the new phone number may either be ignored, marked for review, or imported as a new number.  This can result in duplicate phone numbers (with different formatting) being added to your database.  

For now, the best way to handle this may be to do some backend cleanup of your PHONES table to ensure that all phone numbers have standard formatting.  However, in the long term we've created an IdeaBank suggestion to add a global change to standardize phone formatting in the front end.  Vote for that suggestion here.