Have you ever found yourself building an ad-hoc query in Blackbaud CRM™, and frustrated with the “duplicate" results? This is a common issue that users encounter when building queries, and there are multiple ways to tackle this problem.
One reason this occurs is because of a lack of understanding about how the queries function. When you add output fields to a query, you need to be aware of whether the output field is a one-to-one field, or a one-to-many field. For example, the “First Name” field is a one-to-one field, because a constituent can only have one assigned first name in the system. However, something like “Phone Number” is a one-to-many field, because a constituent can have multiple phone numbers recorded in their record.
When you put a field like phone number in the output of your query, it will return the results to you with multiple rows for the same constituent, one row for each phone number on their record.
There are several ways that you can prevent getting duplicate results in query:
Use “Primary” Fields
Several nodes offer the ability to pull the information that has been marked as primary - for example, Address(Primary), Business (Primary), Contact (Primary), and Education (Primary). For fields like email address and phone number, you will need to do a little extra work to get to the primary record. When trying to get a list of Board Member phone numbers, you may build the query below:
Unfortunately, your output is going to show multiple rows for each board member, as it will output any and all phone numbers on their constituent record. To make things a little cleaner, add the filter “Phones\Primary phone number is equal to Yes”. This will give you output that will show one like per board member, with their primary phone number listed in the second column.
Use the Summarize Query Output Function
The summarize query output feature enables you to view a calculation of field results rather than the actual contents of that field. This could be a helpful tool in removing excessive rows in your query. For example, say you were trying to look at Board Members who made donations to your organization in a certain period of time. When you build the query shown to the left, you will potentially have multiple rows for each board member, showing each donation that they made during that time period. To simplify your results, apply the SUM function to the Revenue\Application Details\Amount field. This will result in one row per board member, with a total giving amount for that time period.
The COUNT function can also be helpful when trying to remove duplicate rows in your query. Add a system record ID field like “Revenue Record” or “Constituent Record” to your output (depending on what kind of information you are querying on), and then apply the COUNT function to that field. This will clean up your query output and show a count of each record in the query.
Minimize Your Output Fields
Overall, it is best to make your query output as simple as possible. Don’t overload it by throwing the kitchen sink into your output fields, especially when it comes to those one-to-many fields. If you find that you need more output fields and your query is becoming cumbersome, it might be time to...
Run Your Query Through an Export
The export tool in Blackbaud CRM™ has way more power to get you the output you need. It allows you to place filters on top of each output field so that you get exactly what you are looking for.
So before you tear your hair out while building your next query, try one of these four solutions and see if they can give you the clean and duplicate-free results you are seeking.