Where can I find a listing of tables and fields that I can query in the "Run Query" feature?

Troy Wolbrink — Wed, 06 Jan 2021 21:49:37 GMT

Current revision posted to Frequently Asked Questions by Troy Wolbrink on 1/6/2021 9:49:37 PM

Most of DonorWise is well documented, but the "Run Query" feature (under the "Tools" menu) is a power-user feature that's more of an "explore on your own" feature. To use this feature, the power-user needs to learn some basic SQL (structured query language). More specifically it's T-SQL, the SQL Server variant.

The quickest way to start exploring this feature is by clicking the "Fields" button. This cuases a list of tables you can query appear to the left.

You can click on a table name to see that table's fields.

The most common table (technically a "view" not a "table") you'd query is GiftDonationView. This contains every approved donation, including reversals and corrections.

For example, to query the 100 most recent donations, you'd run a query like this:

SELECT TOP 100 * FROM GiftDonationView ORDER BY ReceivedDate DESC

The GiftDonationActualView might be a better view to run queries on as it only contains the final state of a donation/gift ... after any reversals and corrections. Let's say a gift was posted, then reversed and corrected, and then reversed and corrected again. The "actual" view will only show the final state of the gift and not the entire correction history. So unless you want the entire correction history, this might be the best view to use.

The GiftDonationRawView is the same as GiftDonationView, except that it includes donations that are not yet approved.