The User's Edge : Query

11/11 Forums Roundup

Douglas Clinton — Wed, 11 Nov 2009 15:29:00 GMT

Here are last week's most popular Forums posts as well as unanswered posts:

Most Popular Topics:

Unanswered Posts:

Query Qualities: Halloween Edition

Douglas Clinton — Fri, 30 Oct 2009 17:57:00 GMT

Happy Halloween, everybody! Well, almost Halloween, anyway - there's still another day before it's time to light Jack O'Lanterns, put on costumes, and go trick-or-treating - but there's nothing wrong with celebrating a day early .

Halloween is a day for ghosts and goblins and other really scary things. One thing that scares people in The Raiser's Edge is duplicates in query. Like a movie monster, query duplicates leap out and scare you when you least expect them. You don't have to be afraid of query duplicates; they appear in your results because the record either meets the query criteria multiple times or the query output includes one or more one-to-many fields. When you save the query and use it in an export, report, or mailing, each record is included only once. In other words, your database isn't haunted with mysterious duplicates, it just looks that way.

Query is one of the most popular topic in The Raiser's Edge. Besides Why are there duplicates in the query results (which includes a demo video), here are the five most poular query solutions in Knowledgebase from last quarter:

Of course, those solutions can be pretty confusing if you don't understand the basics of query. If you're a new user, or are just rusty on the basics, you can take a free query class with Blackbaud Learn. Blackbaud Learn is an annual subscription that provides ongoing skill development and job training for software users. Course offerings range from basic training for beginners to advanced training for experienced users, and new classes are added every month.

The complimentary online class is called Query Essentials for The Raiser’s Edge. Enter the promo code “FreeQuery” on the registration form when you sign up and you'll be on your way!

Enjoy your Halloween weekend and remember to set your clocks back on hour!

10/21 Forums Roundup

Douglas Clinton — Wed, 21 Oct 2009 13:32:00 GMT

We want to know how you use the Forums - please take our poll in Forums Feedback.

Here are last week's most popular Forums posts as well as unanswered posts:

Most Popular Topics:

Unanswered Posts:

10/14 Forums Roundup

Douglas Clinton — Wed, 14 Oct 2009 12:30:00 GMT

We want to know how you use the Forums - please take our poll in Forums Feedback.

Here are last week's most popular Forums posts as well as unanswered posts:

Most Popular Topics:

Unanswered Posts:

9/30 Forums Roundup

Douglas Clinton — Wed, 30 Sep 2009 16:11:00 GMT

Here are last week's most popular Forums posts as well as unanswered posts:

Most Popular Topics:

Unanswered Posts:

9/23 Forums Roundup

Douglas Clinton — Wed, 23 Sep 2009 14:56:00 GMT

Here are last week's most popular Forums posts as well as unanswered posts:

Most Popular Topics:

Unanswered Posts:

News You Can Use: Blackbaud Learn

Douglas Clinton — Tue, 22 Sep 2009 16:54:00 GMT

Blackbaud Learn is an annual subscription that provides ongoing skill development and job training for software users. Course offerings range from basic training for beginners to advanced training for experienced users, and new classes are added every month.

Most Blackbaud Learn coursework is online, which eliminates travel. The classes are led by live instructors, and class sizes are limited to make your experience as interactive as possible. In addition to online coursework, we offer hundreds of pre-recorded courses you can access 24/7 and take at your own pace, plus regional training at locations across the United States and Canada.

Right now, we are offering a complimentary online class, Query Essentials for The Raiser’s Edge. Enter the promo code “FreeQuery” on the registration form when you sign up.

Query Qualities: Birthdays!

Douglas Clinton — Tue, 15 Sep 2009 16:07:00 GMT

Birthdays are a good excuse to get in touch. If you have friends or family members that you haven't talked to in awhile, sending them a card or giving them a call is a great way to let them know you're still thinking about them. The same thing is true of your constituents; an acknowledgement of a constituent's birthday lets them know they are important to you.

It's easy to find everyone who has a birthday in a specific month in query; you can query on constituents who were born in the same month without specifying a year. To do so, create a new constituent query, and select Constituent Information, Birth Date. Select Equals from the operator menu, and in the Date field, click the drop-down arrow and choose the desired month. Once you've saved the query, you can use it in an export or a mailing to wish your constituents a happy birthday!

These steps are also in Knowledgebase solution BB15157. The answer to the Brain Buster in this month's edition of The User's Edge is that Robert and Wendy can stand behind each other at the same time by standing back-to-back!

Query Qualities: Static and Dynamic Queries

Lauren Fiume — Tue, 04 Aug 2009 18:38:00 GMT

Query is a grouping tool, not a reporting tool. It can be used for mailings, reports, dashboards, exports, constituent searches, and global changes.

In each query, you need define the format. You have two options: static or dynamic.

Lets’ say you have two interns working at your company. One is named Static and the other is named Dynamic.

You first asked Static to go out into your warehouse and find everyone who lives in Charleston, South Carolina. You have an event coming up that is specifically for locals. Static searches through the warehouse and places a green paperclip on each file that meets your criteria. She finds 50 records. You place the files with the green paperclips into your cabinet.

You ask Dynamic to do the same thing, just to make sure you’re getting the same results. Dynamic also goes out into the warehouse and finds the same 50 people.

You are pleased with their work, but you get extremely busy and your event gets delayed. It has now been two months and you are finally able to work on this event.

You ask Static to go back into the field and find everyone who lives in Charleston, SC again. She returns the same 50 people with the green paperclips. Dynamic does the same thing, but she returns 60 people. You look through the paperwork and see that within the two months that you were delayed 10 new people have moved to Charleston. The major difference between static and dynamic queries is that dynamic queries are constantly changing and picking up new records.

What is a static query?

A static query will contain the same records and not update unless you specifically tell it to refresh. Static queries are a snapshot of the database at a given moment in time. When you create static queries they create static keys for each record that meets the criteria. This is analogous to the green paperclips that the Static intern placed on each record that met the criterion of living in Charleston, SC.

When do you use a static query?

Use a static query when you do not want the results to change. For example, you might use static queries when you are looking at historical preservation, comparisons from previous years, and global changes. Static queries can be refreshed to show updated results. To learn more about this, check out Knowledgebase solution BB4449.

What is a dynamic query?

Dynamic queries refresh automatically each time they are run. This is why the Dynamic intern displayed the ten new people who met the criterion when you asked for the results two month later.

When do you use a dynamic query?

Any time you want the results to constantly be updating, you would want to choose the format to be dynamic.

For more information on the differences between static and dynamic queries, check out Knowledgebase solution BB520383.

Query Qualities: Soft Credit Options

Douglas Clinton — Tue, 21 Jul 2009 20:10:00 GMT

Soft credits allow more than one constituent to share credit for a single gift without adding duplicate gift records to the database. Because soft credits are not the same as a standalone gifts, they are treated differently in Query.

In a query, soft credits can be credited to the original donor, the soft credit recipient (or recipients), or both. The default option is to soft credit the donor. Because you can give partial soft credit for a gift, if you select Recipients or Both, you can further define how to handle the gifts. Select Use amount in grid to credit each constituent with only the part of the gift for which they received soft credit. Select Full amount to all to give each constituent full credit for the gift—potentially skewing your totals. Or select Split Evenly to split the gift evenly among all recipients.

To choose how to credit soft credits, select Tools, Query Options from your query's menu bar and click the Gift Processing tab. Under the Credit Soft Credits to frame, select Donor, Recipient, or Both and click OK. How you use this option can drastically change your query results, so checking the Gift Processing tab is a good first troubleshooting step for incorrect or unexpected query results.

The answer to the Brain Buster in this month's edition of The User's Edge is the match!

Query Qualities: Output Limits

Paul Howiler — Thu, 11 Jun 2009 14:12:00 GMT

Did you know it’s possible to apply output limits in query? This feature can come in handy when you need a random sample of records, or a set number of records that meet your criteria.

The option to apply output limits is only available in a Static query. After establishing your criteria, you can narrow down your results by going to Tools, Query Options, Record Processing and marking Apply output limits. There are two types of output limits: Random sampling and Limit to top rows. The following examples demonstrate how each can be used.

Random sampling:

Your boss asked for a list of 25 random people who gave a gift of $100 or more last year:

Create a Constituent Static Query
Filter on Gift Amount is greater than or equal to $100 and Gift Date equals last calendar year
Go to Tools , Query Options , Record Processing and mark Apply output limits: Random Sampling and type 25 for the number of records to include
Run the query

Note: Your output will show all records that meet your criteria. In order to view the list of 25 constituents, open a new constituent Export and include this query on the General tab.

Limit to top rows:

If something more specific is requested, such as the top 5 constituents who donated $100 or more last year, then you can limit to the top 5 rows using this function.

Create a Gift Static Query
Filter on Gift Amount is greater than or equal to $100 and Gift Date equals last calendar year
Go to Tools, Query Options, Record Processing and mark Apply output limits: Limit to top rows, Limit to 5
Sort the query by Gift Amount (Descending)
Run the query

Note: Your output will show all records that meet your criteria. In order to view the list of 5 records, open a new Gift Export and include this query on the General tab.

These are just two of the secret talents of queries...

4/20 Forums Roundup

Douglas Clinton — Mon, 20 Apr 2009 17:36:00 GMT

Here are the discussions that generated the most chatter in The Raiser's Edge forums last week:

If you have advice or insight to share, join the discussion today!

Query Qualities: Duplicates in Query

Douglas Clinton — Mon, 13 Apr 2009 18:57:00 GMT

Unless Jamie Madrox is one of your constituents, duplicates in your query results are never a welcome sight. A record can appear multiple times in your query results for two reasons: either the record meets the query criteria multiple times, or the query output includes one or more one-to-many fields.

The important thing to keep in mind is that while it may look like a record is included multiple times in your query, it is not the case. When you save the query and use it in an export, report, or mailing, each record is included only once.

Even though these duplicate records aren't actually inflating the number of records in your query, they're still pretty ugly to look at. If you have records that meet the query criteria multiple times, you can usually remove them by selecting Tools, Query Options from the menu bar, then going to the Record Processing tab, and marking the Suppress duplicate rows checkbox.

If this doesn't remove all duplicates, it means that you have one or more one-to-many fields in your query output. In The Raiser's Edge, fields are either one-to-one or one-to-many. First Name is a one-to-one field, because a constituent can only have one first name. Constituency Code and Phone Number are examples of one-to-many fields because each constituent can have multiple values for those fields.

To get rid of duplicate records altogether, remove all one-to-many fields from your query output. For more information on duplicates in Query, check out Knowledgebase solution BB61118.

4/8 Forums Roundup

Douglas Clinton — Wed, 08 Apr 2009 17:11:00 GMT

Every day people post questions on our Forums. Sometimes people are looking for help with a specific issue, and other times they might have more general questions about best practices. Here's a list of unanswered posts from the past two weeks:

If you have advice or insight to share, join the discussion today!

Bringing Up Batch/Query Qualities Crossover: Cleaning Up the House

Douglas Clinton — Thu, 05 Mar 2009 17:27:00 GMT

You may find yourself hunting through a long list of queries. And you may find yourself scrolling through batches that have lost their relevance. And you may ask yourself, "Well, how did I get here?"

As the days go by, your database will likely accumulate a number of queries and batches. Your organization may have reasons for saving every query and each batch, or they may have been created, committed, forgotten, and turned to clutter.

Deleting loads of queries and batches one at a time is prohibitively time consuming. Fortunately, you can streamline the process by deleting multiple batches or queries at the same time.

To delete multiple batches:

In Batch, select Tools, Delete Multiple Batches from the menu bar.
Select any applicable filters.
Select batches to delete by marking the Delete? checkbox.
Click Delete Now to delete the batches.

To delete multiple queries:

In Query, select Tools, Delete Multiple Queries from the menu bar (or right-click within the query list and select Delete Multiple Queries - The Raiser's Edge only). The Delete Multiple Queries screen appears.
Mark the Only show my queries checkbox as needed.
Indicate the query type and format to delete.
Mark the Delete column checkboxes for the queries to delete.
Click Delete Now in the action bar to begin the process.

Cleaning up your obsolete batches and queries will keep your database off the road to nowhere. You can also find this information in Knowledgebase. The solutions are BB520727 (How to globally delete multiple batches) and BB520787 (How to delete a single query or multiple queries at once).