I covered the basics of using the EMS API to output events in a Web page. Today, let’s take a look at using the EMS API to generate a CSV file of all matching events.
Again, EMS (or Event Management Systems) is a room scheduling and facility management tool from Dean Evans and Associates. Many schools, churches and businesses use their software.
Incorporating events from EMS into digital signage is straightforward, but there is one fatal flaw in the EMS API. The results are not sorted by start date. I think (but I’m not 100% sure) that they are sorted by ID… which would be the order entered.
So, we needed an intermediate tool to sort the events and remove some of the chaff.
Our solution is pretty straightforward. We created a tool that creates a CSV file on the fly from the EMS API.
Itching to get your hands on it? I figured you might be. Grab the ems-csv-export from GitHub. You’ll be glad you did.
In order to use this tool, you’ll need to meet the following prerequisites:
- You obviously need to own EMS
- You need to have licensed the basic API from Dean Evans. Your account manager can assist with this.
- You need to install the API on your EMS Web Server. EMS Tech Support can assist with this.
- Finally, you need to add the ems-csv-export tool on a Web server running PHP that can access content on the EMS Web Server.
Once you’ve got the script installed on a Web server running PHP, you’ll need to set a few variables on the script itself.
The username and password variables are the credentials in EMS of a user that has access to the EMS API. I suggest creating a user specifically for API access, rather than using an existing human. This makes things easier if someone gets canned or forgets their password.
The URL is the full URL to the EMS API on your web server additional reading. It will look pretty much like the one entered by default.
For the allowed_event_status variable, you’ll need to look up your status IDs in the EMS Database server. On our database, status ID 1 is confirmed, and status ID 7 is academic confirmed. We want both of those to be returned in results. We don’t want to return events in a request or cancelled status, so we’re not even asking for those.
In your EMS Database server, just run this command:
SELECT TOP 1000 [ID],[Description] FROM [EMS].[dbo].[tblStatus]
That’ll tell you real quick what the ID is for each of your statuses. Shame on EMS for not allowing you to do this through the client!
For the Date and Time formats, head over to the documentation for the PHP Date function to get some more help. Basically, this string tells the script how to output your dates and times. By default, we are outputting the Start and End Date columns with the full month name and the day (ie: “November 24”). For the times, we have set a default of the hour with no leading zeros (in 12-hour format) followed by a colon, the minute, and the am/pm designator (ie: “4:45 pm”). You can change this in whichever way that works best for you.
Finally, set the time ahead variable. By default, we’ve set it to look for events that begin up to 2 days from the current time. If you want less or more events, just manage this variable.
Usage is pretty simple. You just call the script in this format csv.php?bldg=[BuildingID]. If you want to output all buildings, just call it with 0 (zero) for the building ID.
Again, you’ll need to look up IDs in the database. Here’s a quick SQL query to run to look up your buildings and their IDs.
SELECT TOP 1000 [ID],[Description] FROM [EMS].[dbo].[tblBuilding]
Once you’ve got your ID, put it into your URL. Then you can use that URL in signage players to build custom content using your event data.
We use Four Winds Interactive to power our digital signage.
First things first, you’ll want to create new Live Data content in the Content Manager.
Select a “Data Format” of “Separated text…” The defult values are good to go, so just click “OK” when the popup appears.
Then paste in your URL. Our Library has a building ID of 5 in EMS, so you can see that I’ve named the example as EMS: Library Events and I’m calling my script at csv.php?bldg=5.
All that’s left to do is to mess with the layout area. This is where you set how you want the different fields to be displayed in your data row. If it’s your first time doing this, head over to the Four Winds Wiki for a detailed play-by-play.
If you missed the link to the software, it’s not too late to grab it. Get ems-csv-export here.