More Phone Fun: Can you find records with an email address... but none marked as primary?

Tom Klimchak

I am both delighted and horrified by what 7.95 did with Constituent phones.  Yes, it's great to have them separated from physical household addresses, but it's proving to be difficult to actually select and exclude or find people based on the myriad of phone types. 


Here's a puzzle:


Can you find constituents in your database that have one or more active email addresses... but don't have any of them marked as "Primary?"


Why wouldn't RE make a primary mark a mandatory field?


Thanks!

Jen Claudy

I'm not sure what problem you're encountering.  We are not using the Primary checkbox on any of our Phone Numbers (unless they've been edited or added via NXT, in which case it's required that you choose a Primary).  I just searched for one of my own email address, out of 3 on my record, and the result popped up immediately.  I marked one as Inactive and one as Primary and now I can find my record by searching for the Primary or the Active, but not the Inactive.  (We also do not use the Inactive checkbox...there were existing Phone Types that indicate Inactive, unsubscribed or bounced, and we haven't found any reason to change that...in fact, searching is one reason to keep it.)


If the Primary checkbox is to be a required field, I'd rather that be a Config>BusinessRules setting that each org can choose how to handle.

Tom Klimchak

Okay, so that's what you're doing different: not using the Primary checkbox and using specific phone types to indicate inactive or bounced.  We were hoping to simply use the primary and inactive checkboxes as they were designed.  But I don't think that's going to work...


We might end up using that same method due to the inability to search using just the primary checkbox and finding null values.  If we use phone types AND the checkbox to indicate preferred/primary email address then we're really just double-coding and that's a waste of effort. 


We have people with 4 email addresses and we love the idea of clicking the "primary" box and not worrying about anything else.

shani traum

Thomas Klimchak:

Okay, so that's what you're doing different: not using the Primary checkbox and using specific phone types to indicate inactive or bounced.  We were hoping to simply use the primary and inactive checkboxes as they were designed.  But I don't think that's going to work...


We might end up using that same method due to the inability to search using just the primary checkbox and finding null values.  If we use phone types AND the checkbox to indicate preferred/primary email address then we're really just double-coding and that's a waste of effort. 


We have people with 4 email addresses and we love the idea of clicking the "primary" box and not worrying about anything else.

We do a hybrid, I guess.  We use former email when the email is old, and click inactive so it doesn's show in the list (unless we want to see it), and for active emails, we code them as email, email-1. email-2.... With email being primary, email-1 being secondary, etc...  No clue why 2nd choice email is email-1, but that happened before me!

Jen Claudy

Sorry...I think I misunderstood the question.  I thought you were talking about the Search Window, not a Query.  I think what you're looking to do could be done if the Primary & Inactive checkboxes were nested below with the Number & Comment fields under Specific Phones.  However, that not being the case, there is a workaround.


You can use Export, but I think Query might be your better bet (and I know, Query is a grouping tool...usually not to export from).


Set the Criteria as: Phone Type one of ___ (use whichever Phone Types you're looking at) AND Phone Inactive equals No

Set the Output as: Constituent ID, Phone Type, Phone Number, Phone Is Primary?

Export the Query to MS Excel, or MS Access (Access is probably simpler, if you have it & are familiar with it).


Depending on which Microsoft product you're using, sort and manipulate your data and find the Constituent IDs that do not have the Primary checkbox checked (if they don't have an Active Phone, they won't be in the Query).  Get your results to a list of Constituent IDs (duplicates are okay).  Select the IDs and paste into Query as a new line of Criteria.  (Constituent ID one of ___ ...in this window, scroll down at least one "click" in the window, select a blank row and use Ctrl+V to paste in up to 500 IDs...for more than 500, use multiple lines of Constituent ID one of with an OR operator and put them all within parenthesis.)


If you're wanting to change and mark one of them as Primary, you can change the data outside of RE and use Import to update the records within RE.  Include the Phone Import ID in your Query (before you export the data from RE).  Admin > Import > Constituent > Constituent Phone and Import your changes with the Primary checkbox checked for the numbers you want.


Hope this is of more help. 

Gina Gerhard

Also wondering about NetCommunity - you need to designate a specific email type that will then be used by BBNC when sending email.

Does anyone know if BBNC has the ability to select the email now with the 'Primary' checkbox or do you still need to specify the email by type alone?

 

Thomas Klimchak:

Okay, so that's what you're doing different: not using the Primary checkbox and using specific phone types to indicate inactive or bounced.  We were hoping to simply use the primary and inactive checkboxes as they were designed.  But I don't think that's going to work...


We might end up using that same method due to the inability to search using just the primary checkbox and finding null values.  If we use phone types AND the checkbox to indicate preferred/primary email address then we're really just double-coding and that's a waste of effort. 


We have people with 4 email addresses and we love the idea of clicking the "primary" box and not worrying about anything else.

 

Tom Klimchak

Jen,


Yeah, that's great for a one-time process, but we have 45,000+ people with email addresses.  I need to be able to sweep through this list pretty regularly.  I could probably do something with Crystal but... yeesh.  This is a lot of craziness to find a simple data error.


My ultimate goal is to create a quick way to exclude people with an active email address in a query with every mailing list we put together.


I think the solution might be to essentially "double-code" inactive email addresses with both an inactive checkmark AND an "Inactive Email" type.  I can query on the Type and Comment of each specific phone, but I can't query on the "active/inactive" status of specific phone numbers.


Creating an "Inactive Email" type kind of defeats the purpose of using the Inactive checkmark, though...

Our problem actually comes from our conversion to 7.95.  It gave us thousands of "Email 1" addresses from the relationship fields and it marked them all inactive. 


I realize this is pretty "in the weeds" and probably not helpful to anyone else. 


Bottom line: we have thousands of people with active and inactive "Email 1" phone types.  It's pretty difficult to deal with those records in queries.

Alan French

We've not upgraded to 7.95 yet, but could you create a query of anyone who has an active email address, and a second query of everyone who has a primary email address, and then either merge the queries or use a query list to subtract one from the other? That should leave you with a query containing only the people you're interested in.

Tom Klimchak

Alan French:

We've not upgraded to 7.95 yet, but could you create a query of anyone who has an active email address, and a second query of everyone who has a primary email address, and then either merge the queries or use a query list to subtract one from the other? That should leave you with a query containing only the people you're interested in.

Alan,  Ahh!  Yeah, good idea!  That worked... Looks like our latest alumni import didn't mark email addresses as primary.  Makes sense.  Thanks!

Gina Gerhard

We just upgraded to 7.95 and as part of the preparation, I actually decided to create new phone/email types with the 'Inactive' as part of the name.  

• That way, I'm hoping to get around any lack of functionality with the 'Inactive' checkbox.  
• I also like the fact that it's more obvious right in the name, and someone may inadvertently just click the Inactive checkmark while poking around (we've already run into that issue) -- so by making the phone type also show 'Inactive' it's a bit safer.
• We also ended up keeping our 'Pref Email' email type (for primary/preferred email) since I wasn't going to count on BBNC being able to recognize the primary email based on the 'Primary' checkbox -- I think it still pulls on a specific Email 'type' so the checkbox is inoperative there.

Tom Klimchak

We just found another problem an hour ago:  When we converted to 7.95 it took our "Email 1" from our shared addresses (relationships!) and made them inactive on one of the spouses.  So we have thousands of "inactive" email 1 addresses.


I think moving forward email will be easier to work with (though there are some odd limitations to what you can query on).... but it may take us the next 9 months to clean up our existing records.

Gina Gerhard

Thomas -


Not sure if this would help, but using Import you can create an export file of your phone numbers, then fix things like the Phone Type, Inactive=TRUE or FALSE, then reimport them to fix these.


I've been using this to fix some of the weird/unaccountable things that happened during the upgrade.


Another great tip (I got from someone else) was for any phones you actually want to delete, you can create the export file and rename the phone type to DELETE (or some unique name) and then use the Delete Phones plug-in to remove them. This gets around the issue that BB didn't built any kind of Global Delete/Change for phones yet... 

Tom Klimchak

Gina,


Hmm, yeah... those are two very good options.. thanks!  I need to really look through these people and try to figure out what 7.95 did.  I'll have to fiddle with Excel a bit, but that's not a problem.  I like the idea of giving them a "DELETE" phone type...

Gina Gerhard

Also, I just discovered this -- that the 'Mark Phones Primary' plug-in will allow you to mark certain phone types as 'Primary'

AND

it also allows you to mark certain phone types as 'Inactive'

(even though that's not in the title of the Plug-in!!)


So I could have saved myself some importing work (to mark phones as Inactive) had I known .....

Gina Gerhard

Thomas -


That's what I ended up doing, trying to figure out what happened to the phones/emails during the conversion and then running numerous routines using different tactics/methods to fix things.  I ended up doing about 7-8 processes to finally get things under control !!


 

Molly Witherell

Gina Gerhard:

Thomas -


That's what I ended up doing, trying to figure out what happened to the phones/emails during the conversion and then running numerous routines using different tactics/methods to fix things.  I ended up doing about 7-8 processes to finally get things under control !!


 

Hi Gina,

We have created seperate email types for active and inactive email addresses.  Thank you for your posts!  They have been priceless!    

We are looking to upgrade to 7.95.  

Just wondering if you wouldn't mind sharing the list of the 7-8 processes that you still had to do after the upgrade?  Or a partial list of the items you recall.    

Thank you very much!  

Gina Gerhard

Molly -


I'll try to recall some of the things:

• For some reason we ended up with phones that came through with no number, so I searched for those (and probably just manually deleted them).
• I might have done this pre-conversion, but exported out all the phone numbers and looked for duplicate values, then removed dupes.
  • For instance, we had the same phone number as both Home and Business. Had to decide which to keep, then removed the other. 

Here are some other things I now actively audit for, so these were probably involved in post-cleanup as well:

• Check active phone types marked inactive
  • Since we include 'Inactive' as part of the phone type, I can find all active phone types by type (ex: Home vs. Home-Inactive) and make sure they are NOT checked inactive.
• Find Inactive phones that are NOT checked inactive.
• Check Pref-Email and make sure it's checked  'Primary'
  • We duplicate the email we want to use as preferred and create a Pref-Email -- because we don't want to have to rely on the Primary checkbox (and since BBNC doesn't recognize the Primary checkbox for its email functions)
• Deceased records with active phones (we make sure we inactivate any phones left on deceased records)

I found that by just scanning through records I could find these 'situations' that I had to check for -- 

Molly Witherell

Hi Gina,

I cannot thank you enough for your guidance!  Your posts
have helped tremendously with upgrade planning.  We have been
doing pre-work similar to what you described.  Feeling better
prepared for 7.95 now.    

If you ever get out to California please give a shout. 
Would love to repay you with a meal!  

Best regards,

Molly Witherell

310.506.6893

Systems Analyst 

Advancement
Services 

Pepperdine
University

Gina Gerhard

Molly - So glad the information was helpful!  I was at a loss when faced with this upgrade as far as the cleanup 'homework', even though I had Blackbaud's guide.  

• I found a lot of things I needed/wanted to check that weren't really clearly stated in the info provided, so I somewhat tried to think through things so I'd have less 'post' cleanup to do.  
• I also didn't want staff to be actively working in data that still needed to be cleaned up, already faced with things being new they had to deal with. So that's where the pre-work is also helpful, to get as much ready before the major change occurred.  
• And knowing about the Plug-ins was extremely helpful (I got that info from the community forum posts).

I actually lived in Los Angeles for almost 10 years (back in the 1980's) -- worked in downtown LA for the real estate developer that built the tallest building downtown. I was working on data systems BEFORE there were relational databases -- so actually had to build a lease abstraction data system on a flat file database that was limited to 10 pages of screens -- really crude compared to what we have today!!  Perhaps that's why I lean towards 'simplicity' and 'lean' when working on systems ----

Molly Witherell

Hi Gina,

Did you have any issues with Reports after the upgrade? 
We have read that others have had problems with crystal reports and
we have over 200 custom reports so this is  a troublespot!
   

Gina Gerhard

Molly -


We have very few custom reports so I made sure to test and they were all OK.   So not able to shed much light on this issue.


What we did have were a few VBA customizations that moved address and phone data from RE to other systems and amazingly, they did not break but do no longer keep the phone data completely in sync (we really only needed the mailing addresses synced and not the phone data so it was a non-issue).