Thursday, May 1, 2008

Exporting calendar data for the website

I have been struggling with a programming problem for work this week. For our church management system we use Shelby Systems V5. This is a powerful program but it is not very user friendly or overly easy to use. Also it is a server based software system that can only be accessed at the church by people with a user ID / password, and enough training to know how to use it. We have been using Shelby since before I came on staff and we still do not really have a true handle on it, mostly because with a system like Shelby you need one person to be full time dedicated to the software, learning it, updating it, training others on how to use it, etc... and we do not have that person.

My preference would be for Chapel Hill to move to a web based system that would be easy and intuitive to use, and would require very little training for volunteers to use, but we have a lot of money invested in Shelby, and that makes it hard to justify a change. So we deal with what we have.

Right now I am trying to bridge the gap between our Shelby calendar system which we use to schedule church events (and make sure we do not over schedule the building) and our website, which really needs to have a calendar of events for the church on it.

Shelby stores event data in a SQL server database in several tables. The ones I am interested in are a table to store the names of the different locations that events take place, a table for one time events, and a table for re-occurring events. It is this last table that is causing me the big problems, as each re-occurring event is only listed in the table once, along with info on the date it starts happening, the date it stops happening, and how often in happens.

What I need is a composite table of the tables containing one time events, and the table containing re-occurring events, only with each event instance listed as its own record. Shelby creates this composite table on the fly whenever it is needed using an SQL server stored procedure. I have looked at this solution, but my T-SQL is not good enough to figure out how to copy and modify it for what I need, and I am not sure this would not be a licence violation anyway. So I am left with either doing a manual export of the calendar data each week and then importing this into my website database, or figuring out some programmatic way of combing these two tables to get the data I need.

No comments: