Read Only Database Assistance

Decoding marital status code, country code etc

Ajit Dhaliwal — Fri, 20 Nov 2009 17:59:02 GMT

Hi,

We are trying to extract data from our Raiser's Edge system so that we can use it on our website. We have extracted the data in an Access document but we are having trouble decoding values such as the marital status code and country code among others. For example we have a marital status code of 3288 but we cannot find a table that will tell us what this code means in words. We are having the same issue with country codes and many others. Can any one tell me where I can find the table that will let us decode these?

Thanks a lot

Getting Gift & Donor Counts

Ken Kogler — Mon, 04 May 2009 17:13:53 GMT

I'm having a little trouble with a query in RODBA, and I'm hoping someone here can steer me in the right direction.

I'm trying to pull total gift sum, gift count, and donor count per fiscal year for the last five fiscal years. (This is for only certain funds, only certain appeals, and only certain gift types). I've got the gift sum working down to the penny, but the donor and gift counts are off by about 1% or less, and I can't figure out where it's coming from. My hunch is that it has to do with split gifts or adjustments, but I can't prove that yet.

I've created a report in Raiser's Edge, and I'm trying to write a query in RODBA that returns the same numbers so that I can create an external report for this data set. I'm trying to balance the numbers between the RE report and my external report, and that's where I'm seeing the mis-matched data.

Here's what I've done in RODBA so far :

SELECT FY,
COUNT(DISTINCT CONSTIT_ID) AS NUMDONORS,
COUNT(DISTINCT GIFT_ID) AS NUMGIFTS,
SUM(AMOUNT) AS SUMGIFTS

FROM (

SELECT CG.CONSTIT_ID AS CONSTIT_ID, CG.GIFT_ID AS GIFT_ID, GS.AMOUNT, DBO.CSL_GETFY(G.DTE) AS FY
     FROM GIFTSPLIT AS GS
     INNER JOIN GIFT AS G ON GS.GIFTID = G.ID --needed for gift date
     INNER JOIN CONSTIT_GIFTS AS CG ON G.ID = CG.GIFT_ID --needed for gift & donor counts
     INNER JOIN APPEAL AS A ON GS.APPEALID = A.ID --needed for appeals in WHERE clause
WHERE G.DTE BETWEEN '7/1/2002 00:00:00.000' AND '6/30/2008 23:59:59.997'
     AND A.APPEALCATEGORYID IN (SELECT TABLEENTRIESID FROM TABLEENTRIES
                                                        WHERE CODETABLESID = 5042) --all direct appeals
     AND GS.FUNDID NOT IN (SELECT ID FROM FUND
                                            WHERE FUND_CATEGORY = 3537) --exclude "misdirect" fund types
     AND G.TYPE IN (1,2,3,9,10,11,12,13,14,15,16,17,18,19,20,31) --only direct/cash gifts
     AND CG.FLAGS IN (0,1,4) --credit Soft Credits to donor; Matching Gift to company
     AND GS.AMOUNT > 0 --exclude zero dollar gifts

) AS giftsByFY

GROUP BY FY
ORDER BY FY DESC

I should explain that the csl_fy() function is a homemade scalar-value function that takes any valid date and returns "FYxxxx" where "xxxx" is the corresponding year, e.g. "FY2009" or "FY2008". That and the particular code table IDs will make it hard to copy/paste this into RODBA, but you're welcome to try!

Anyway, the output (in CSV) looks like this:

FY, NUMDONORS, NUMGIFTS, SUMGIFTS
FY2008, 16235, 35874, 20546214
FY2007, 18547, 42687, 26857963
FY2006, 14563, 38541, 19874563
FY2005, 12354, 32569, 18759632
FY2004, 10001, 22587, 12345678

(My Development department would want me to point out that these are made-up numbers!)

The donor and gifts counts in my RODBA results above, when compared to the RE report, are all LOW by between 50 and 200 each. However, the dollar amount (the "sumGifts" column) is exactly right.

As you can see from the first SELECT statement, I'm counting the DISTINCT constituent and gift IDs from the constit_gifts table, and my numbers are under by just a tiny bit. If I remove the DISTINCT clause, the numbers almost double, and that's obviously incorrect, too.

So here's really the question: how should I go about counting up the donor and gift counts? Should I be looking somewhere besides dbo.constit_gifts? Is there a table I forgot to join that factors into this? Do I have an INNER join somewhere that should be an OUTER instead?

I've been looking at this long enough that my brain is Jello. Can someone out there lend a hand? Thanks!

Org Sort Name

Sandy Fiddis — Mon, 23 Feb 2009 17:11:29 GMT

I am having trouble sorting Org names. For Org names wher I know that I have put a slash (\) in the company name I can't find the field that contains the name after the slash - this is what I want to sory by. Instead Org's that begin with "The" appear in the T's.

Queries and Temporary Views

Brian Dudley — Tue, 03 Mar 2009 20:01:13 GMT

I'm getting started on extracting data from Raiser's Edge with RODBA. The approach is to take the queries defined through the front-end and examine the SQL.

Some of the queries included views with names like tmp14565_286378713_1, that are also refereneced in the TempObjectLog table. These views are created and destroyed when the queries are open. The definition of these views is not available, from the database -- it seems like the source is encrypted or the proper permissions don't exist.

I'm looking for any information on the views. They do not seem to be needed for all queries. Are they certain constructions or approaches that will prevent them? Is there some way to resolve them to their underlying definition? Any pointers for working with them?

We are developing automated extraction scripts, so having a view generated by the front-end would be a problem.

Thanks!

Brian Dudley

Finding Student Homeroom

Candace Chesler — Sat, 17 May 2008 23:36:31 GMT

I'm having trouble locating the link between the student record and their Homeroom. I can find each faculty homeroom with no problem. I can see the field SBHomeroom - but because we are running Registrar's Office - that's null. I definitely have homerooms assigned to all students - and need this link so that I can create my customized report card.

Any suggestions would be greatly appreciated.