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!