dozen stock tables for everything from student, teacher, and relation bio
and address information to classes, courses, and grades. I populate these
tables automatically on a schedule from the core Blackbaud FE7/EE7 database.
The initial SQL queries I use to populate these tables were a little tedious
to build, but now access to the data I need is very easy and I never have to
give anyone access directly to the core Blackbaud tables. The Blackbaud data
schema (../Help/FEDatabaseSchema.chm) is very helpful and the SQL view for
Blackbaud queries can also be used to save some time tracing down foreign
keys. I'll be glad to share my SQL without anyone who wants it. While I
could make the data real-time or almost real-time, I've found that a daily
rebuild works fine for all of my current applications and the daily refresh
approach makes it easier to deal with permissions and potential performance
issues.
The main thing I use my stock tables for is as a data source for ASP
applications. However, I also use the stock tables and SQL views based on
the tables as data sources for pivot tables in Excel as well as a data
source for other software such as a very simple but powerful email merge
program called WorldMerge. Below are a few examples of ASP apps I've built
using the stock tables:
*Warning List*: Blackbaud doesn't offer a solution that works well for our
warning lists which occurs 6 times a year for us. In the past we would have
to export all student/class data and then import this data to another
database used by faculty to submit their warning list. While not a big deal,
this took several hours 6 times a year. I've build a simple ASP application
for handling the warning list, and since I can access all student, teacher,
and class info via my tables, I was able to make loading the data as easy as
selecting the year and term and clicking a single button. Now I can load a
warning list in literally a minute or two.
*GradeYard:* Teachers often have a need to look at student grades and
progress reports in a number of different ways, but Faculty Access offers
very little flexibility in this. For example, suppose a dorm supe wants to
look at all the grades and comments for student in his dorm? The only way to
do this in Faculty Access is to go student by student looking at grade
reports (note: this method is also flawed in that all you can do in FA is
see another teacher's gradebook comments, not the actual comment stored in
the students permanent record). While this kind of information is fairly
easy to pull out of the Blackbaud client, teachers don't have access to the
client. To solve this problem I created web application called GradeYard
(catchy, huh?) to view grades and I have built a dozen or so filters. For
example, now it is very simple for a dorm supe (or dept heads, or coaches,
or advisors, or ...) to look at say all freshmen and sophomores in his dorm
getting a c+ or lower by setting a couple of filters and clicking a "View
Reports" button.
*Summer Mailing / Enrollment:* I've also built a summer mailing application
which delivers all of our summer mailing information via the web. When I
load an item (e.g. new student health form) I also specify a SQL filter for
the item which determines which students should get the item. After I load
the system in May, the items pretty much maintain themselves based on their
SQL filter. When a new student is enrolled the student automatically gets
all the items based on the SQL filter associated with each item. I've also
made it very easy to email and/or contact parents who have items that are
overdue. All the info comes from Blackbaud tables so no extra data entry is
needed to maintain the system such as when a parent changes an email
address, a student enrolls or withdraws, etc.
I've built a few other apps and have several more waiting for me to find
time to build. My next general purpose app is an emailer program which would
allow emailing students, parents, and/or faculty based on a simple web page
with assorted easy to use filters. While I know NetMail can do this, sending
something out via NetMail can be a chore and require Blackbaud client
access. I want something that would, for example, enable the College Office
to shoot off an email to the current Blackbaud email address of parents of
all female day student juniors (pick your bizarre filter) in the time it
takes to write the email itself and with no instructions required.
Lastly, of course the one huge gotcha with all this is that you can never
and YOU MUST NEVER EVEN THINK ABOUT writing data back directly to the
FE7/EE7 tables. You can automate the creation of import files, but any data
your application creates must come back into the FE7/EE7 tables via the
Blackbaud client. This is a definite handicap and it prevents me from doing
a lot of cool time saving stuff. This is also what's so exciting about the
8.x platform with a web services layer. Since all interaction with the
database will be through the web services layer, I could create applications
that connect to the web services layer which could do pretty much anything
the client could do from a data perspective as long as Blackbaud adequately
documents the web services layer which they've promised to do. This would
give us enormous flexibility to create (or hire a 3rd party to create)
custom applications whenever the stock Blackbaud application doesn't do
exactly what we need. Furthermore, the ability of niche companies (e.g.
Naviance, Infosnap, etc.) to build truly seamless applications would be
pretty much unlimited. This is why I'm SO frustrated that 8.x for Education
Edge looks literally years off from what I can tell. You may have noticed
that I have somewhat of a love-hate relationship with Blackbaud. I plan to
get counseling any day now ... :)
If you would like more info please contact me directly. I think I have
reached my quota of ISED-L posts for the week.
--
Tom Phelan
Director of Technology
Peddie School
tphelan@peddie.org
Office: 609.944.7625
http://www.peddie.org
On Wed, Oct 7, 2009 at 3:42 PM, Vern Ceder <vceder@canterburyschool.org>wrote:
> Hi,
>
> Forgive me for jumping on one snippet of a larger conversation, but I would
> be very interested in any wisdom you have on accessing Blackbaud DB's. I
> priced the API and we quickly decided that was not for us. Still, being able
> to grab data from various the various database would come in handy. A few
> years ago I made some initial successful experiments, but it wasn't easy
> enough to convince me to continue.
>
> Cheers,
> Vern
>
> Thomas Phelan wrote:
>
>> I've built a number of custom ASP apps (e.g. warning list, grade/progress
>> report look up tool, ...) that pull data from Blackbaud tables. If you
>> want
>> some insight into the possibilities regarding building solutions that
>> seamlessly use Blackbaud data (no exporting required) but don't rely on
>> their clunky and expensive API I might be able to help.
>>
> --
> This time for sure!
> -Bullwinkle J. Moose
> -----------------------------
> Vern Ceder, Director of Technology
> Canterbury School, 3210 Smith Road, Ft Wayne, IN 46804
> vceder@canterburyschool.org; 260-436-0746; FAX: 260-436-5137
[ For info on ISED-L see http://www.gds.org/ISED-L ]
Submissions to ISED-L are released under a creative commons, attribution, non-commercial, share-alike license.
RSS Feed, http://listserv.syr.edu/scripts/wa.exe?RSS&L=ISED-L
