I was working with a customer who had a Dean Evens  EMS database, but they wanted to use a custom field as the event start time. We didn’t want to replicate the original database because there would be a delay in the updates, and it seemed like a lot of work just to use a different field. We also didn’t want to add this custom field as a feature to Carousel because it is only for one customer…. what to do?

I made them a proxy database that pulls the data from the original database, transforms the results and feeds it to Carousel with views! I had never used views to do this before, but it worked great. Here’s what I did…

First I created an empty database on the same server as original database.

Then I made views for all of the tables that Carousel requires. See the ‘Integrating Carousel with 3rd Party Databases’ document for a list of these tables and fields.

Most of these views just select the data from the original database and return it, unaltered.

However, for the tblBooking view, I wanted to re-map the tblBooking.TimeEventStart field to another custom field in the database. This field actually mapped through the tblServiceOrder table… So I created a view with a bit more intelligence.

The tblBooking view returns all of the required data for Carousel, and instead of returning the tblBooking.TimeEventStart data, it returns data from tblServiceOrder.TimeStart… Pretty cool.

As you can see the proxy database is doing just as it is named. This technique works really well. You could use it to link to pretty much any database.