Need to make a query for a list of e-mails in excel

Thomas -

I think you could do it this way:

• Do a query of records in RE that have email addresses (if you can further narrow this down that's probably helpful). Include the constituent name, email address and constituent ID.  Export out to Excel.
• Then copy this Excel info into your other Excel file (in additional sheet) and use VLOOKUP to find the matches.
• Get rid of the rest of the data in your Excel file, and then using the list of constituent IDs you can create a query from RE (from this listing of IDs). 
• You should then be able to use this query to globally change the email type?

 

Just as you can copy and paste constituent IDs into a query, you can do the same for email.  This trick only works for numeric fields, but fortunately, Raiser's Edge treats email addresses like numbers.  You are limited to pasting 500 records at a time, so you'll have to keep repeating steps in the same query until you reach the bottom of your list.


1.  In query select All Addresses, then Phones, then use Phone Number as your field criteria.

2.  Change the Operator to One Of.

3.  Critical Step!!!  -  Left click on the Down Arrow of the scroll bar beside the values field.  To be clear, I mean the scroll bar of the Field Criteria pop up window.  This is kind of tricking the field to make space for your list.  

4.  Open your excel document.  Copy the first 500 emails.

5. In your Query, left click into the Values field.  (do not click the Up Arrow on the field criteria scroll bar.  It's tempting, but it ruins the trick).  Now hit Control + V to paste your email list.

6.  Select OK on the field criteria window and now you should see in the Filters window a running list of many emails.

7.  Select Phone Number as a field criteria again and repeat steps 2-6 until you have your whole list in the query.

8.  Under Output, select Phone Number, Phone Type and Constituent ID.  This will return the exact  email address ("phone Number") it's phone type and related ID.  


Keep in mind the number of records in output might be more than what you queried on because an identical email address might exist on more than one constituent (hopefully not) or it might be on one person's record under multiple types (to be expected).

Never worked with Altru before...this thread is in RE:7 > Query, so this may not be applicable/transferrable.


What Rachel means is that you need to select the One Of operator and then, before entering any data, scroll down to the very last row in the One Of box and paste your data there.  It may appear that nothing has happened until you click [OK].  Once you click [OK] that line of criteria should appear with your list of pasted data.  (You don't have to go all the way to the last row, but it's easier to explain, I think.)