Import Insight: Excelsior!

Sometimes you have to be smarter than the software.

Knowledgebase solution BB16868 is a skeleton key to group records by comparing two different fields.  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 the quiet and unthreatening duo query and export, as well as the great boogeyman of The Raiser’s Edge – Import.  Don’t let that 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 to compare.  The KB solution compares constituent and spouse last names, but you could compare constituent phone number, or Gift Amount and Soft Credit Amount on a gift or anything else you can capture in export.

Excel expertise 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.

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

Query Qualities: Output Limits

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:

1. Create a Constituent Static Query
2. Filter on Gift Amount is greater than or equal to $100 and Gift Date equals last calendar year
3. Go to Tools , Query Options , Record Processing and mark Apply output limits: Random Sampling and type 25 for the number of records to include
4. 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.

1. Create a Gift Static Query
2. Filter on Gift Amount is greater than or equal to $100 and Gift Date equals last calendar year
3. Go to Tools, Query Options, Record Processing and mark Apply output limits: Limit to top rows, Limit to 5
4. Sort the query by Gift Amount (Descending)
5. 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...