Phone Import ID - Not part of query

Carrie Clark · February 2017

Hi all -

I've been having difficulty updating specific phone numbers because the import ID doesn't exist within the query.

We get list of phone numbers from our Call Center with phone numbers only - so I create a query = Phone Number - One of. Which spits out everyone who has that phone number somewhere in their record.

What I can't figure out how to do is get the import ID of that specific phone number for that individual because that doesn't exist on the query side of things.

If I take it to export, it exports all different phone import ID's and not necessarily the ones I want to update. Email for example. I don't want to update that to "Disconnected", but I can't figure out how to get import ID's specific to a phone NUMBER.

Any advice out there?

Denise Covington · February 2017

Carrie Dufault:

Hi all -

I've been having difficulty updating specific phone numbers because the import ID doesn't exist within the query.

We get list of phone numbers from our Call Center with phone numbers only - so I create a query = Phone Number - One of. Which spits out everyone who has that phone number somewhere in their record.

What I can't figure out how to do is get the import ID of that specific phone number for that individual because that doesn't exist on the query side of things.

If I take it to export, it exports all different phone import ID's and not necessarily the ones I want to update. Email for example. I don't want to update that to "Disconnected", but I can't figure out how to get import ID's specific to a phone NUMBER.

Any advice out there?

You'll have to run your query through and export and then you can export our the phone import ID that way. You can still export out your constituent ID and then the Phone Import ID is under Phones --> Import ID.

Ryan Hyde · February 2017

This is a tricky one. The solution I'm going to give you involves Excel and the use of VLOOKUP. If you're unfamiliar with VLOOKUP, stop and do some googling as I'm not going to explain it as well as others have.

So, you start in the same way you have been starting, by using "Phone Number is ONE OF X" in a query. You will then need to use that constituent query to create an export where you will export the Constituent ID, Phone Number, and Phone Number import ID. As you've noticed, you're going to get a lot more numbers than you want. In this export you'll need to specific how many phones per constituent you want to export. Pick a high number because you can just get rid of any empty columns after the export.

Now, you will have to tediously turn this export into a table with just three columns - Constituent ID; Phone Number; Phone Number Import ID. The easiest way to do this is to start with the first three columns of your export, which will contain all three of those elements, and copy and paste them into another tab. Then delete the first set of phone numbers and import IDs from the spreadsheet. Then, sort by the 2nd phone number column to group all non-empty fields together and again copy the first three columns present over to the new spreadsheet. You'll repeat these steps (sort, copy, delete) over and over until all phone numbers and import IDs and associated constituent IDs are togeter in three columns.

Now, open up your spreadsheet of phone numbers that need to be changed/updated and use VLOOKUP to match them to the phone numbers in your new organized spreadsheet. This will identify every phone number that needs to be updated. Sort by your VLOOKUP column and delete any records that are not matched.

Finally, use VLOOKUP to map whatever updates you're attempting to make to this number. Once this is done and all your information is in one place, you can import the updates using the Constituent Phone import.

Jen Claudy · February 2017

I *think* you can use Import > Create Import File to get the Phone Import ID.

Catherine Burns · February 2017

Hi. I would suggest a combination of Jen's and Ryan's methods.

Use Import to create an import file for the phone numbers of the constituents found by your query. This will export ALL the phone numbers for those records, not just the phone numbers in your query.
Export the phone numbers you want to change directly from your query.
Use VLOOKUP (or MATCH) in Excel to find the phone numbers from your query in the long list of phone numbers in the import file. Remember to look for EXACT matches only.
Sort by your VLOOKUP column and delete any records that are not matched.
Delete all the phone numbers that do not match/are not found
Make the required changes to the numbers and import the updates.

Cammi Derr · February 2017

Carrie Dufault:

Hi all -

I've been having difficulty updating specific phone numbers because the import ID doesn't exist within the query.

We get list of phone numbers from our Call Center with phone numbers only - so I create a query = Phone Number - One of. Which spits out everyone who has that phone number somewhere in their record.

What I can't figure out how to do is get the import ID of that specific phone number for that individual because that doesn't exist on the query side of things.

If I take it to export, it exports all different phone import ID's and not necessarily the ones I want to update. Email for example. I don't want to update that to "Disconnected", but I can't figure out how to get import ID's specific to a phone NUMBER.

Any advice out there?

I have been asking for Blackbaud to add the phoneimportID to query for 2 years now, and I guess it must be an impossible task to complete because it still hasn't happened. So we are stuck with a lot of extra work just to update large numbers of phone numbers.

Phone Import ID - Not part of query

Comments

Categories