March 2008 - Posts
How frustrating is it to track the total number of foursomes in a golf tournament using The Raiser's Edge? Several methods are used by the various organizations. The following points assume you have the RE:Event module for The Raiser's Edge. This works for registrants and sponsors within the Participant tab.
1. Enter a foursome as a price unit on the Prices tab in the event record. Then add this as a registration unit (under the Registration tab) in the participant's record with the appropriate number of units.
2. If it possible to have two companies split a foursome, add a half foursome as a price unit on the Prices tab in the event record. Then add this as a registration unit in the participant's record with the appropriate number of units. To join the two companies together using the "Seating Group" dropdown box on the participant's record.
3. If half foursomes isn't your cup o' tea, then consider using some sort of comments or attributes within the participant's record. This will lead to queries and possibly custom reports (depending on how nice you want the output to look like).
This is my short list. If you have any other methods you use to report on the total number of foursomes please post a comment.
Cheers,
Chris
Good afternoon - welcome to my first post. It's my goal to post here occasionally with tips, tricks, and insights into The Raiser's Edge. In this post I'd like to talk about a common Support question concerning numbers that appear on Reports. Often times we hear from organizations that see 'unusual' numbers on their reports. For example, a donation is listed as $375.09 on a report but you know the donor actually gave $50. This is often caused by the Currency Filter field.
We recommend setting the Currency Filter field to 'None (recommended)' unless you want to only view donations that were given in a specific currency.
To set the Currency Filter to 'None (recommended)' go to Tools, User Options, and select the Reports tab. Highlight Gift Preferences, in the Currency Filter table select 'None (recommended)'. Note this field is user specific so make sure other users know. For more information on this you can review knowledgebase solution BB88646.
Well, it wasn't a large bag, but I consistently used the tricks in it to help customers with puzzling import questions when I supported The Raiser's Edge. The "tricks" are pretty simple, but they may come in handy the next time you tackle an import.
-
Use the Fields tab or create an import file to find out what your options are. If I want to see what fields are available for a particular type of import, I select Create an import file on the General tab of the import, enter any characters in the Import file field, and go to the Fields tab. Here, you can browse through the available fields and select them to view the header. You can also click Click here on the bottom left to view the relevant page of the Import Guide for details on which fields are required and what kind of data go in those fields. If I'm updating existing records, I just go ahead and create a real import file, update the data in the fields, and import it back in, knowing it's already formatted correctly.
Note: You can only include one-to-many fields in the import file created through the Import facility. For example, if you're creating a constituent import file, you can't export phone numbers or anything that can occur more than once per record.
-
Go ahead and shove in the data. OK, so this isn't always practical in your live data, but give it a try in your sample data with a few of the records from your actual data file. Or if you are able to set up a standalone workstation with a copy of your live data attached, give it a try there to see how the import works with your real data. If you're not able to test in sample or on a standalone machine, back up your live data and test with a few "dummy records" or one or two records you know very well and don't mind playing with (maybe your own). I often find it easier to troubleshoot problems if I can see exactly what happens when I click Import Now.
-
Use the sample data files in Knowledgebase. I have no problem with plagiarizing when it comes to data files. I love copying the sample data files onto my machine and updating them with my own data. Hey, if someone else has tested the files for you, why not use them? (And for quite a few of them, that someone would be yours truly. 
) Search Knowledgebase with "How to import [whatever it is you're importing]." The solutions with "(includes sample data file)" in the title have a file available to view and download. You can import them into your sample database to see what they do, or you can replace the data with your own.
-
When in doubt, check extensions. Sometimes I just can't figure out why I'm getting an exception. My data file looks correct, and I've tried everything in Knowledgebase I can find on the exception error. More often than not, a messed up extension is the culprit. If you're importing certain fields (like phone numbers or addresses) more than once, make sure each group of fields has the same extension. Sometimes, just one field will have the wrong extension, and that will throw everything else off.
-
Don't import after 4:00 in the afternoon. A customer told me this one, and I think she got it from a Blackbaud consultant. We may not always have a choice, but whenever possible try not to import or do anything else "global" in nature when your mind's not fresh. I know the cells in my data file all start looking the same late in the afternoon, so I wouldn't want to take a risk if I were working in an organization's live data.
If you have any tips for making imports easier, feel free to share them here!
Ever been confused by solicit codes? Maybe you inherited your database from someone else? Or maybe too many users have been allowed to add to your code tables? Either way, keep it simple -- solicit codes should be in the negative (ie: "Do not solicit," "Do not call," etc.) BTW, "Do not email" is a completely unnecessary solicit code -- check the "Requests no email" box on the lower right-hand corner of the Bio 1 tab instead. If you're using solicit codes in the affirmative (ie: "Send spring mailing," "Call during fall phonathon," etc), consider using constituent attributes to pull constituents into queries and exports instead.
Use "Do not solicit" sparingly. Think about it -- should this constituent truly never, ever be solicited for anything? Annual appeals, event participation, legacy giving, etc. -- not just now but for ever and ever? Consider creating more specific Do Not Solicit codes instead. Personally, I don't appreciate phone calls, but am happy to receive a well-crafted case for support via email or snail mail. And (speaking from experience) might your constituent be highly offended if he/she didn't recieve an invite to your fantabulous annual gala? The generic Do Not Solicit code might inadvertantly strip him/her from that prestige mailing (been there, done that).
Am always happy to share real-life experiences and will continue to do so in coming blogs!
Taking a cue from Sarah McBride and her affection for alliteration, I intended to inject some personality into my first blog post through the application of repeated consonant sounds. I pondered and planned a plethora of possible posts - the top topics entitled, "Better Batches", "Glorious Global Changes", and "Find the Fun with Funds!"
Unfortunately, Sarah beat me to the punch, and I was reduced to posting a blog tragically bereft of alliteration.
To make amends, the blog that begins below commences with a cacophony of consecutive consonants.
Dialing Down Date Drama Concerning Constituency Codes
Last week at Nice Things Are Good, Debbie Data was catching up on some data entry. First, she added Connie Constituent's $1000 December donation on the gift tab. After that, she went to the Bio 2 tab - to update Connie's constituency codes. Connie had been elected to the Nice Things Are Good Board at the beginning of the year, so Debbie added the constituency code of Board Member and entered 1/1/08 in the Date From field.
Later that day, Reggie Report ran the Gift Detail report to present to the Board. He wanted to know how much money each Board Member had given in 2007, so he selected Last Year for the Gift Date and filtered on the Constituency Code of Board Member. Much to Reggie's chagrin, when he printed the report, Connie Constituent and her $1000 gift were nowhere to be found.
Reggie turned to Tommy Troubleshooter, his friend who sat one desk over, and explained his predicament.
"I don't understand," Reggie told Tommy, "I can see Connie's gift as plain as day, and when I look at her Bio 2 tab, she's a Board Member all right. Why doesn't she come up in my report?"
Tommy nodded thoughtfully and rubbed his chin. "Tell you what, Reggie," he said, "What happens if you Include one record - Connie's - on the General tab, and get rid of that Constituency Code filter. Does the gift show up then?"
There was a pause while Reggie carried out his instructions. After a few moments of waiting, the report filled Reggie's screen. The gift was there.
"She's there now," Reggie said, "but I don't understand what made the difference - is something wrong with the Constituency Code?"
"Let's take a look at that Bio 2 tab again," Tommy said.
Reggie opened Connie's record, and almost at once Tommy began jabbing his finger excitedly at the screen.
"There it is!" Tommy said. "It's the Date From field - there's your culprit. When you ran the report before, the Raiser's Edge was looking for constituents who had given gifts in 2007 and who were Board Members at the time of the gift! Since Connie didn't become a Board Member until January 1, 2008, the report didn't even consider her!"
Tommy leaned back triumphantly and folded his hands behind his head. This triumph proved to be short-lived.
"Well, that certainly explains what's happening in my report, but how do I fix it?" Reggie asked.
Tommy thought this over awhile and then called to Debbie.
"Hey Debbie," he said, "do all our current Board Members have a Date From of 1/1/2008 on their Constituency Code?"
"All the new ones do," Debbie said, "but the holdovers - well, it depends on when they started. Not all of our Board Members started at the same time."
"So querying on the Date From field is out," Reggie said.
"I'm afraid so," replied Debbie.
"What about the Date To Field?" Tommy asked. "Do you use that?"
"Sure," Debbie said. "Once somebody leaves the Board, I enter the date. If they're still active, that field's blank."
"That's it!" Reggie said. "Instead of using the report filters, I'll just create a Constituent query with the Criteria Constituency Code equals Board Member and Date To is blank."
Reggie created the query and re-ran the report. Connie, the rest of the Board, and all their gifts were accounted for. Now Reggie didn't have to worry about upsetting Connie by presenting a report to the Board that ignored her generous donation.
The elastic nature of the Raiser's Edge allows for numerous variables in the way data is entered and reported on. Because organizations have unique needs, it cannot be said that one process is always better than another. What is universal is a need for consistency in data entry and reporting, so that both aspects of the work complement each other.
This is a simple enough concept in theory, but it can become more slippery in practice. Had Debbie not used the Date From and Date To fields in her Data Entry, Roger would not have had to adjust his report. Now that Roger understands how the data he is working with is entered, he can be sure to request it in the correct manner.
Later on, Tommy would find troubleshooting steps for this situation in Knowledgebase, in solution BB119027.
When I'm time-pressed
When my brain hurts
When I'm feeling rushed
I simply select all my favorite fields
And then I don't feel so stressed!
I like to sing, and what better to sing about than Favorite Fields! If you're not already familiar with Favorite Fields, allow me to introduce you to one of the best features in Query.
For those of you who create a lot of queries, I suspect you often utilize many of the same fields to filter on, include in your output, and/or sort by. It can get rather tedious when you're constantly having to scroll through all of the available fields to locate the ones you need. So why waste time and effort when you don't need to?
By taking the following steps, you can add your most frequently used query fields to your Favorites so they'll be easy to access going forward:
- Open a query.
- On the Criteria, Output, or Sort tab, select the appropriate field in the Available Fields section.
- Right-click the field and select "Add to Favorite Fields."
For the uber-efficient among us, you can also quickly select multiple favorites at the same time:
- Open a query.
- On the Criteria, Output, or Sort tab, select any field in the Available Fields section.
- Right-click and select "Organize Favorite Fields." The Favorite Fields screen appears.
- Select the appropriate query type. For example, if you want to select favorite fields for constituent queries, select Constituent.
- In the Available Fields frame, locate the appropriate field and click the right arrow to move it to the Fields to Include frame. Repeat for each field that you would like to make a favorite.
- Click OK.
These steps are also in Knowledgebase: How to add query fields to Favorites (20990).
As a final tip, I think you'll find it helpful to sing like Julie Andrews while you select your favorite fields, and I'm sure your coworkers will be appreciative. 
After working for days to set up an import file, you may be tempted to take your chances with the Raiser's Edge gods and import the entire file at once. But wait! Don't get too trigger-happy with that Import button, my friends. If there happens to be a problem with the way the data was entered in your spreadsheet, you could very well face a lot of manual cleanup.
You'll commonly hear us advise you to have a recent backup of your database (should you need to restore to pre-import orderliness), and we often recommend validating the import so you can check for any potential exceptions before starting the actual import process. (See BB58904 for more info.)
Kudos to you if you've made these regular habits in your import process! But have you also developed a healthy respect for guinea pigs?
Though I do like those furry rodents, I'm actually referring to a test import file that just includes the first one or two records from your original spreadsheet. Using an import guinea pig will give you a chance to confirm the import produces the right results and can make it easier for you to identify any changes that need to be made within your original file. Just remember to remove the guinea pig records from the larger file once they've imported successfully in your test run.
This may take away some of the adrenaline rush from importing all of the records on the first go, but it will also help you avoid that horrible lump in your stomach when you realize something went askew.
A Raiser's Edge query is a great tool for comparing specific values. For instance, if you want to find every record that has two $50 gifts, you can do so by creating a constituent query with the criteria Summary Information, Total Number of Gifts equals two, filtering on Gift Amount equals $50.
But what happens if you want to find records that have two gifts with the same amount, regardless of which amount you’re using?
If you have a great deal of patience and nothing else of any consequence to achieve in the next several months, you could set about using endless OR operators, or merging queries to find these records, but I wouldn’t recommend it.
There’s also the higher math approach, which is to use RODBA to write a SQL query, but if you’re a Plain Jane end user like I am, that isn’t an option either.
There is a third way to find these records, to compare one field against another without the tedium of entering every permutation into a query or using a more advanced tool like RODBA.
Sometimes you just have to be smarter than the software.
I have used the knowledgebase solution BB16868 many times as a skeleton key to get the information I need. The solution walks you through the process of finding constituent/spouse pairs that have the same (or different) last names and popping the records into a query for you.
The process involves query and export, which are familiar to most, as well as the great boogeyman of the Raiser’s Edge – Import. Don’t let the involvement of Import discourage you from trying this solution – there is only one field to import and you don’t even change anything on the records.
Depending on what fields you need to compare, you can either create a query of your records or export every record in your database. When you select your fields to export, you will only need to include three – the record’s Import ID, and the two fields you wish to compare. The KB solution compares constituent and spouse last names, but you could compare two different phone numbers on a constituent, or even Gift Amount and Soft Credit Amount on a gift.
Extensive knowledge of Excel is not required to use this solution. Assuming that you have a header row, and that the Import ID is in Column A, the solution you would enter in Column D would look like this:
=IF(B2=C2,"Yes","No")
Once you have entered this in Cell D2, copy it all the way down the rest of the column. For every record where the values in columns B and C match, you will have a “Yes” in Column D. Contra wise, if the records don’t match, the formula will return a “No” value.
Once Column D has been populated, your goal will be to winnow down the spreadsheet until only the records you want remain. Your first step will be to sort the spreadsheet by Column D, so that you have all the “Yes” records in one group, and all the “No” records in another.
Using the example from the KB solution, if you want all the constituents whose last names do not match their spouses’, you would delete all the rows containing the “Yes” records. When only the “No” records are left, delete columns B, C, and D – you won’t need them where this process is going.
When the Import ID column is the only one left standing, save the spreadsheet as a .csv file and go to Admin, Import in the Raiser’s Edge. Create an Import and select the .csv file you’ve edited and saved as the file to Import.
At this point, the process pretty much comes down to marking two options. The first of these is on the General tab, where you will mark Update existing records. Remember, since the Import ID is the only field you are importing, you won’t actually be making any changes to the record – you’ll just be tricking the software into thinking you are.
The other option to mark is on the Summary tab – Create an output query of records updated. When you click Update Now, you will be prompted to save that Query – once the Import is completed, you will have the query of the records you need.
This process does take you a few paces off the beaten path, but it’s a handy way to put together a query when a more straightforward approach isn’t an option.
Cheers! Am thrilled to be part of Blackbaud's new blogging forums...
As introductions are in order, I'm Jennifer Paquette, a Fundraising Solutions Consultant and long-time RE user (since 2000!). Am happy to share thoughts, advice, etc. from the perspective of both a Blackbaud Consultant and a Fundraising Professional / long-time client. Feel free to check out my background on my profile -- am betting there are many of us with quite a bit in common.
Suggestion for the day.... When creating RE queries, check the "Others may run" box, but NOT "Others may modify" box when saving your query. Why let others mistakenly ruin your hard work? Instead force them to save it as their own and then make changes. And why do I feel so strongly about this? Because I was that sorry soul who was constantly (and inadvertently) modifying my colleagues' queries -- I'd see a cool query and think "great -- I'll save myself some time and just tweak it." And then forget to save it as my own. Sort of peeved my colleagues who had invested the time in creating those complex queries.... Believe me -- protecting your queries saves everyone from grief! 
You’ve just finished printing letters and envelopes for all of the records in your database. There’s only one problem: The letters and envelopes are not in the same order! 
This presents quite a dilemma when you’re trying to manually match each letter with its corresponding envelope. Unless you have an excessive amount of time on your hands, I’d like to suggest an easier method before you find yourself in a similar situation:
1. When setting up your letters, mark the Create output query checkbox at the bottom left of the General tab. This will prompt you to save a static query once the letters are merged.
2. On the General tab of your envelopes, include the output query from step one. The envelopes will automatically sort in the same order as the output query from your letters.
Note: The same concept applies for labels as well. Check out BB15665 for the instructions in Knowledgebase.
It's so tempting to use the Supervisor login because it can do anything and everything in RE. However, as a customer once told me after she accidentally deleted a large number of records, that much “power” is too much for one person to wield all day long. We ended up giving every user a named login (e.g. Bob, Sue, Katie) and setting security for every person that gave him or her just enough access to perform their specific job functions. The Supervisor login is now used only for occasional administrative tasks or troubleshooting problems that might be related to security rights.