How to query on duplicate relationship phones

Gina Gerhard

After conversion to 7.95 I ended up with a lot of individual relationships where the phones are coming over from the linked record but they're also showing on the individual relationship as well.




Does anyone know how to query on this condition (so I can remove the duplicate phones?)

 

John Heizer

Gina Gerhard:

After conversion to 7.95 I ended up with a lot of individual relationships where the phones are coming over from the linked record but they're also showing on the individual relationship as well.




Does anyone know how to query on this condition (so I can remove the duplicate phones?)

 

Within RE the only thing I can think of is a Pivot Table where you'd look for a Count > 1.

Denise Covington

We have a staged upgraded Environment and our older live environement right now.  I'm curious if your individual relationship record is between two constituent records or with a non-constituent individual?


 

Alicia Barevich

I'm guessing this is happening on relationships to non-constituent individuals. If it's happening on relationships between two constituent individuals, I'm rather confused how it got like that.


What I would recommend, is building a Relationship query. Restrict this how you want, or not at all. Include these fields in the output:

  --Constituent ID

  --Constituent Import ID

  --Relationship Import ID

  --Phone Import ID

  --Phone Type

  --Phone Number

  --Any other human-readable fields you might want for context


Export the output of this query to Excel. Sort by Relationship ImportID, then by Phone Type, then by Phone Number.


Concatenate these three columns into a single column.


Add a column next to that one, starting in row 3 that is something like =IF(E3=E2,"Dup","Keep"). Once that formula has been dragged down the whole column, and spot-checked for accuracy, copy the column, paste as values, sort by that column, and delete everything that is "Keep."


At this point, you could take that list and manually fix all of the records you found. However, I'd think that fixing them in bulk would be much easier.


Replace all of the "Phone Numbers" with the carrot symbol, "^", and import the file. (You may want to test this with a small number of rows, just to make sure it works as intended.) This should delete the phone number, but leave the phone type. If the test worked well, import the remainder, and then run the Delete Phones plugin based on blank phone number fields.


If you want me to elaborate on any of those steps, please let me know.

Gina Gerhard

Alicia - Thanks so much for your thoughtful response!  

This is actually happening on individual relationships to organizations, where the individual has their own constituent record.


So here's what I'm getting in my query results (sorry the picture is teeny and I can't seem to make it bigger):

• So there are 4 lines for 9304-0 with repeats of the Pref Email and Work Email.
• This is the situation where the relationship has 2 emails coming from the constituent record and 2 'repeats' on the relationship.

 

But I don't see any distinguishing information on those 4 lines that would allow me to distinguish between the 2 phones on the individual record and the 2 phones on the relationship?


 

Alicia Barevich

Ahhhh, ok. That makes more sense. We're currently preparing for upgrade, and we migrated all business phone numbers off of the business relationship record, and onto the Bio 1 of the individual.


What is the bulk of your data cleanup? Would you want to do it manually or automate it? In either case, I believe the instructions I gave would still work. You'd definitely want to test it with just a couple rows to make sure it works as intended though.

Gina Gerhard

Alicia Barevich:

Ahhhh, ok. That makes more sense. We're currently preparing for upgrade, and we migrated all business phone numbers off of the business relationship record, and onto the Bio 1 of the individual.


What is the bulk of your data cleanup? Would you want to do it manually or automate it? In either case, I believe the instructions I gave would still work. You'd definitely want to test it with just a couple rows to make sure it works as intended though.

So in the example above, how would I know which 2 of the 4 phones to mark for removal?

Alicia Barevich

Sorry, but I'm not entirely sure, as I don't have an example of this in our system. You could try exporting out every field available for phones and see if there's any way to figure it out. Also, if you try to import over the "yellow" numbers, it may error out on you, and only let you import over the "blue" ones. Take a test record and play with it to see if you can figure it out.

Gina Gerhard

That's what the last screenshot was showing, I exported out the relationship phones and there's nothing to distinguish the 'yellow' ones from the 'blue' ones -- nothing that I could find. So there's no what to actually know which phone I'm looking at.  Very weird ....  maybe Blackbaud will chime in

Alicia Barevich

Can you get at anything useful via Export? I'm wondering if the Import ID or System Record ID might be able to shed some light. Compare these IDs to the parallel IDs on the constituent's record. Can you find a match? Discern a pattern?

Gina Gerhard

Nope -- all I see are the 4 phones attached to the relationship and the phone IDs and the phone type - so how do I distinguish the two Pref Emails and the two Work Emails?  No way to tell them apart ...  

Alicia Barevich

If you look at the Relationship record and make the column "Source Record" visible, you can tell which phones come from the Organization's Constituent record. Unfortunately, this isn't a queryable or exportable field.


If you want to delete all of the duplicate "blue" numbers, I have a theory of what would work, but it would involve a lot of Excel formula work:


Export all phone numbers on relationships suspected of having duplicate phone numbers, including Const ID, Phone Type, Phone Number, and Phone Import ID.

Export all phone numbers on organization records (you could restrict this down to only include organizations with individual relationships...or something like that). Include the same fields.


On the relationship phones list, concatenate Const ID, Phont Type and Phone Number, then use "Highlight cells rules" to highlight duplicate values. Delete any that are not duplicates.

V-lookup based on Phone Import ID, a field into the relationships phone list to indicate which phones are also present on the Organization's record. Any that don't have a match from the V-Lookup are ones you should delete.


Realize, this is theoretical and untested, but it seems to me like it would make sense for the Import IDs to match, and thus, be able to format an import file this way.