Showing just the primary phone and email in a query (database view)

Ken Willey

Hello!


What has everyone's experience been with viewing data on phones and email addresses in database view? We want the contact info listed out for each constituent without having to click into the record. However, once phone or email fields are brought into the output of the query, the results show all sorts of duplicate rows, one for each phone number or email address (or every combination of the two, it seems). Adding the filter to check "Phone is Primary?" doesn't seem to remove the duplicate rows or narrow them down at all. Are we forced to go via the route of an export here, and just look at the resulting CSV/Excel file? That presents its own unique challenges.


Thanks!

JoAnn Strommen

Yes, output of phone/email in query will create duplicate rows as they are considered a one-to-many field. To have one row per constituent with primary/valid you would use Export. (Not export on the Query function).

You can create a basic data export and run whenever needed.


What challenges do you feel Export presents? Maybe there are some suggestions to overcome those.

Ken Willey

Wow, thanks for such a quick response!


Here's the problem--the original constituent query looked for constituents connected to a scholarship fund without an end date (i.e., the relationship is still ongoing):


Fund Relationship equals Scholarship

AND Fund Relationship Date To blank


So far, so good! The query puts out multiple/duplicate rows in the event that the constituent has more than one such relationship. And that's great. We want to know that.


So then I create a constituent export and use the above constituent query in the "General" tab where it says "Include." That becomes that basis of my export. In the output tab, I'd like to show the constituent's relationship records that have a "Fund Relationship" value of "Scholarship" and a "Fund Relationship Date To" value of blank (like the query above).


So I click on the relationship fields to move them over into the output, and it asks me what criteria to use. Great! However, the criteria window only asks me two things: how many fund relationships I want to export, and which Fund IDs I would like to include. But I can't filter by "scholarship" type or "Date To." Some criteria windows (like the ones for gifts) actually allow you to plug in a query, but that doesn't seem to be an option here.


Therefore, in the end result, I'm exporting relationships that are inactive or irrelevant to scholarships, if that makes sense! Then the user has to go through the process of deleting them out of the Excel file. I'm hoping to avoid that.

Mark Guncheon 2

We don't use fund relationships so I'm going to guess here:


Would a fund export work better for you? I think you can be more specific with the funds using those.

David Tang

Piggybacking on JoAnn Stromm's comment, I only use [Query] to check if I pull the right people or not. Once it is confirmed, I then use [Export] for its output to eliminate potential duplicates.


That being said, I have something similar to export both <Primary Email> and <Primary Phone> in the same file (see screenshot below). As you know, both data are stored under the same <phone> section so it is tricky. Hope this helps.

Larry Wheeler

In your export, try going to Gifts>Funds>Relationship>Individual. Once you click there, it opens up a Gift Criteria window where you can select (tab 2 Filters) a Fund Query using your needed criteria. Be aware that coming from the fund perspective you may need to use Reciprocal rather than Relationship. It would depend on your setup.


For the phone/email Primary situation - I have always used Phone Is Primary? but use Descending rather than Ascending.


Export




Fund Query

Karen Diener 2

I just want to highlight Larry Wheeler‍'s reference to the Filters tab of an export. I feel like this is often overlooked or not understood - especially the query aspect of it - and it can solve a lot of problems.


Good luck to you Ken. There is a lot of good advice in this thread, and you've done an excellent job of following up with the details of your issue.


Karen

Dennis Ladnier

Ken, we did some simple VBA code and used the 'vba user field' - the code simple gets the phone or email address which is marked as primary. Has saved us a lot of excel time in sorting/dups/etc.


Thanks, Dennis

Isaac Comer

Hi,


Back in the past, I gathered that if you wanted to code VBA you had to purchase the module from Blackbaud.


Do you know if that's still the case?

Dennis Ladnier

Isaac, no idea but knowing BB probably so - with that said, having volunteered my time to help many other orgs I have not seen an on-prem instance of RE that did not have it. With that said, you totally lose VBA capabilities within the database view when you move to RE-NXT.

Madeleine Holdsworth

Yes, you have to take your query into Export. Remember the Blackbaud mantra...."Query is a grouping tool, not a filtering tool". One of the big weaknesses of the application, in my opinion. The good news is that once you have your export set up properly, you have it saved and then can modify it when necessary.