Add Primary Flag for Constituents with No Primary Phones

What Does It Do?

The OUD-DS3 best practice is for constituents to have a piece of primary contact information if any contact information is available.

This integrity check identifies constituents without a primary phone number but with valid phone records in DART so that one of those phone numbers can be made the constituent’s primary phone.

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[IDS.V-INTEGRITY CHECK-PHONE-Create Primary from Valid Phone(DW)], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries active individuals without any primary phone numbers on their record in DART but who have a valid phone number on their record. If the constituent has multiple valid phone numbers, the following ranking system is used to select the address to be marked as primary:

1. Phone information source: Self-Reported > Web-Self Reported > Bio Acquisition Project uAG > Gift > Conveyed through UM Rep. > Constituent > (all others)

2. Phone type: Cell Phone > Home > (all others)

3. Phone start date (if null, fill in with the date that the phone was added to DART) in ascending order

4. Phone GUID as a tiebreaker

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\PHONE\

Snapshot GUID: BBDW.FACT_ConstituentPhone.ConstituentSystemID