Busy, busy weekend

andybartlett.com draft

Crazy busy weekend
Friday date night… Saturday garage sales and upstairs-cleaning duties… Kid Day today. It’s been a long weekend. Fun, and mostly unproductive, but mostly fun.

To show support for Helen cleaning the total disaster that was her bedroom, I started straightening up my office upstairs. It has become so full of stuff that it’s barely useable; mostly furniture, really. The room is tiny; maybe 8×10 with no windows, but as the room has a sloped ceiling due to the house’s roof line only about half of the floor space is useable for much. As of yesterday, there were four full-height bookshelves, two two-shelf closet organizers that are on the floor under the roof slope, the desk that my parents got me as a high school graduation present and a drafting table. There also was a television on the floor which left me barely enough room to maneuver my chair between my computer desk and the drawing area.

Part of the problem is the absolutely enormous desk that is holding my equally enormous PowerMac G5. In November, that behemoth will be 10 years old and it’s to the point that I use it for a web browser and not much else. It’s time for it to go, and that’s going to free up a ton of space.

The fun thing about making progress with a major room overhaul is that you have to blow it up and make it an even bigger mess before it all comes back together. I made quite a bit of progress, but it looks worse than ever. Fun.

Helen’s Toy Show
Helen got hooked on YouTube videos of a couple doing box opening descriptions of My Little Pony toys this weekend; she got to the point that she decided she wanted to do her own. So, tonight, we started Helen’s Toy Show. We did a two-minute video of her opening a fat-pack of My Little Pony trading cards this evening and posted it on my Facebook wall. She got three likes and a comment from one of Mel’s friends before bed, and she went to sleep thinking she’s going to be famous. It was one of the most adorable things I’ve ever seen.


Tonight I started playing around with If This then That; I’ve just started playing with it, but I have started an action to send Facebook updates to a notebook in Evernote and a second action to automatically post Instagram photos here. I’m a little upset with myself that it’s taken me this long to start using IFTTT; I’ve wondered about a glue to pull together all the different fragments of my social media presence, and this looks like it may be it.

One thing that seems missing right off the bat is an option to send tweets that I favorite to Evernote; there are no Twitter triggers for IFTTT, which is probably due to Twitter’s “we really hate developers” stance toward its API.

I’m going to have fun

Building a news clip database

Tracking the News

Since I moved to my current role in Bemidji State’s Office of Communications and Marketing in 2007, I’ve used a static list in InDesign to keep track of the news releases I produce each fiscal year. It looked a lot like this one, from FY2013, and it was basically the same for six straight years with some cosmetic changes every now and again. It had a simple purpose: keep an ordered list of the releases I distributed each year and track the code number assigned to each release depending on the range of distribution for each story. I moved it around, from InDesign to Pages and back to InDesign, but it was basically just a text list.

For this year I wanted to do something different; for a variety of reasons, I decided to move the list to a spreadsheet in Google Docs. It looks like this. Just a few releases into the fiscal year and it’s working perfectly fine.

In the past, I had been using Filemaker Bento to keep an archive of stories mentioning Bemidji State found in various places online. It started out as a way to keep track of stories sourced from Google Alerts, Yahoo! News alerts, Bing news alerts, etc. Eventually I added the stories we were pulling in from our clip service at BurellesLuce. I kept track of a variety of information about a particular story — where I found it, how I found it, whether I could identify it as coming from a story I had released, a number of things. Each story in the database had an entry that looked like this. This database fell by the wayside during the year that I served as interim director of the office, but the idea of this as a tool to help keep track of news mentions was solid.

This year our office is putting an emphasis on measurement; measurement of our traditional media efforts and of our social media efforts. I’m using tools like Sprout Social and Crowdbooster to track social; for traditional media, I’m using tracking functionality in Burrelles, and this year we’ve subscribed to Meltwater, which has some media tracking tools as well.

However, there are two questions that were going to be extremely difficult to answer related to print media, even with all of the tracking tools we have access to right now. Those questions:
1) Of the news releases distributed in X period of time, which one generated the highest number of stories in the media?
2) How many stories did a particular news release generate last year?

The basic data to answer those questions exists in the relationships between two existing tools – the news release inventory lists I’ve been maintaining by hand for years, and the measurement services that track our media mentions. However, nothing that I’ve done connects those two tools in any way, leaving answers to questions like these two, and others like them, just out of reach.

However, moving the release inventory from a static text list to Google Docs sparked a thought — this new spreadsheet could be moved to a database, the Bento database to track news clips could be resurrected, and I would have the data sources I would need to find the answer to those two key traditional media measurement questions.

None of the tools I was using would do the trick. Bento is a single-table database with no relationship-building capabilities, so rebuilding the clip database there was pointless. Moving the clip database to Google Docs would give me parallel spreadsheets and allow me to save all the relevant data, but I wouldn’t be able to get the information I wanted about the relationships between data in those tables. I needed an actual database tool, and I chose Filemaker Pro.

I picked Filemaker over something like PHP because of Filemaker’s ability to easily deploy to iOS devices; I figured that might come in handy if this became A Thing.

So I had a problem to solve; I had picked a tool to try and use to solve it. Off I went.

Building the database
My first task was to try and recreate the clip archive I had constructed in Bento in Filemaker. This was relatively straight-forward and not terribly difficult; the final result looked like this, which looks basically the same as the Bento version I built a few years ago. All of the information is there, save for the “related art” data box to save PDF versions of the stories; that no longer seemed necessary. Once this was built, I populated the database with about a dozen records as a starting point.

I then toyed around with deploying that database table to my iPad, just to see how that worked. It required that I set up permission levels to grant access to my iPad, and in the process of setting up this guest access I apparently inadvertently assigned an overall administrative password to the database. Which, of course, I had no chance of remembering later, which means I locked myself out of my first draft.

So I rebuilt it from scratch, tweaking a few things along the way, until I got it up and running again and repopulated with about two dozen records this time.

Once that was done, I built a second database table to hold the information I’m currently storing in Google Docs as a release inventory; that screen looks like this.

The release code that has been part of the inventories I’ve been keeping plays an important role here. It quickly occurred to me that this unique code, which had been used to segregate and sort stories by category, also could function as a unique database key. The structure of the key is straight-forward – 2014-B-X-015 is the 15th story released for Bemidji State in Fiscal Year 2014 as an extended local story (a geographic category that carves Minnesota up into zones that stories can be sent to; “extended local” draws a line through St. Cloud and includes basically everything north of that line). That structure also creates a unique identifier; there can only ever be one 2014-B-X-015; thus its suitability as a database key.

I created a connection between the media clip table and the story inventory table within the database, using that release code as a key identifier. Then, I used the portal functionality in Filemaker to pull that release code information into an individual story in the clip database; this would allow me to identify a clip as having come from a BSU news story, as I had always done in Bento, but then also indicate exactly which release in the news release inventory had been the clip’s original source. That connection looks like this in the story database; it shows whether the story was sourced from a release that came from BSU, then has a field where I can display the release code of the story it came from.This field is connected back to the story inventory table, which also dynamically displays a Bitly link to the release’s home BSU News.

The magic
The fun part came when I built a second tab for the news release inventory data table and connected that back to the clip database; it scans the connections that were made in the previous step and returns a list of every clip that has been tagged as having been sourced from that news release. So for 2014-B-X-002, the second tab lists every story in the clip database that says it came from a BSU release and also came from story 2014-B-X-002. That display looks like this.

Honestly, this is about the most-basic multi-table database you can build in Filemaker; the two individual tables are just straight-up data tables that could easily be reconstructed in Excel or some other spreadsheet, and there’s only a single point of connection between the two tables, identifying the release code for the source stories in any news clip identified to have originated from one of our news releases. Even the “magic” piece that returned the list of every story that had been tagged with a certain release code used a function that is built into Filemaker. The most challenging aspect of this was simply learning Filemaker; the documentation could be better, and the software is not at all what you would call user friendly. Still, what I built today could end up being a powerful tool for us, and I feel that to have built it start to finish in one day while learning the software on the fly as I built it was a solid achievement.

For the first time in a long time, I can say I built software. That’s a pretty excellent feeling.

Summer of Software: Filemaker Pro 12

Today was my first real attempt to build something useful in Filemaker Pro, and I made solid progress.

My vision is to build a database that will allow me to maintain an archive of media mentions of Bemidji State coming into the office through our various measurement channels — Google Alerts; Yahoo! News alerts; Bing alerts; daily hit digests from Meltwater; random things we find on social media; things we dig up by hand; whatever is out there that we can identify. I then would like to have an ability to cross-reference this with the categorized listing of the news releases I distribute from the office over the course of an academic year. This would help me answer questions that are basically impossible for me to answer right now, like “what were your five most successful press releases last year in terms of the number of stories they generated” or “how many media hits did we get off of X story you wrote last November?”

I worked through two key steps of this development project this afternoon. The first was to build a table in Filemaker to enter the contents of a news clip from an online source. Having used Filemaker Bento to build a similar archive of news clips several years ago (the maintenance of which fell by the wayside during my interim director appointment), setting this up wasn’t especially difficult; I already had a basic template in place, it just needed to be built for Filemaker. I have this built, but honestly it’s easier in Bento; Filemaker could stand to adapt some tricks from its younger sibling in this regard. But, it’s done, and save for the functionality I included to store a PDF image of the story, which I dropped in Filemaker, it has all of the features I had built into the Bento version.

The second part wasn’t particularly challenging, but I also successfully deployed this database to my iPad. It was nonsensically difficult to find instructions on how to get this set up in Filemaker’s documentation (mostly because there are items in Filemaker’s menus that do not have entries in the documentation, which is just silly), but the actual process was quite simple once I found the right menus.

The next step in the process will be to build a second database table to keep my news release list, which is now being stored in a Google Docs spreadsheet, which will be straightforward as building the clip database was this afternoon.

From there, things get more complex, as I need to learn Filemaker’s functionality for building what will likely be a third table that will act as an array attached to that news release list to maintain pointers to stories in the news clip data table.

A future step would be to figure out a way to automatically dump stories into the database using Filemaker’s Open Database Connectivity (ODBC) functionality. That seems like pretty Serious Business™ where software development is concerned; it might be fun to take a run at that if/when I can get the base version up and rolling.

As is typically the case with software, I see in my head how this should work. The trick now is to learn how to build it.

That Summer of Software thing is something I came up with just now. Fun. Considering I’m putting effort into learning Filemaker and I’ve started playing around with Final Cut Pro, it does seem somewhat appropriate. Even though summer is more than half over.


Now that I’m done with graduate school save for my final paper and a comprehensive final exam, I’ve started giving some thought to what to tackle next. Grad school hasn’t been challenging, really, but having it hanging over my head constantly for the last two years, including summer school, has been incredibly taxing. It’s been a distraction, pulling my attention away from just about everything else with the mere fact that it’s been something that’s been constantly on my mind since August of 2011.

Right now, my “what’s next?” list has three projects. Those are:

• Write some software. This is a project I have already started; initially I want to work on doing some development for Filemaker Pro 12 as part of a project for work, and I think it’s finally time to stop making excuses and begin learning how to do iOS development. It’s something that has fascinated me since the very first moment I held my first iOS device, a third-generation iPod Touch.

For Filemaker, I want to start building a project for work. I maintain a series of spreadsheets in Google Docs to keep track of my news and social media calendars for work; right now there are three. One with an inventory of news releases I distribute over the course of a given year; one is a calendar of upcoming events on campus that I use as a coverage planner; the third is a similar calendar for scheduling social media posts, which is a new initiative for this year. With this system, I don’t have an easy way to answer questions like “what media outlets picked up X story?” or “what stories were picked up by the most number of media outlets?” I think I can use Filemaker to build something that can answer these questions for me.

For iOS, I have all kinds of ideas. There are things I could build for work; I have ideas for things I could build in order to support Brad’s new initiatives in athletic media relations; there are things I could build to help deliver publications and presentation materials. I also have an idea for a game, to digitally resurrect something from the late 1990s that has been gone for awhile and never got the virtual version it deserved.

• Write a book. Taking inspiration from this, I have an idea for a similar encyclopedic look at something that I’m pretty sure has not yet received this sort of formal treatment (although there are several web versions, but none that do exactly what I have in mind). This will take a ton of work, but I think the end result could be cool.

I also have had an idea for a work-related book in my head for about 10 years; it’s something that is right in my wheelhouse, and I have the ability to gather the assets I would need to write this book at my easy disposal through work. And, it’s a subject that deserves to be written.

So, ultimately “write a book” might become “write two books.” We shall see.

• Finish my postcard project for my friends. Just over a year ago, I put out a call to my friends on Facebook to request postcards – “Ask me to draw you something, and I will send it to you on a postcard.” I expected a few responses, and I ended up with a list of about 36 of these to draw. I had this idea that I’d do one of them each week. The first one was done for Megan in early June of 2012. By October, I had done eight. I finished a ninth in November, and have barely touched pencil to paper since then save for the Megan Fox piece I started in May. I need to get this off the ground again, and get my friends the cards they asked for. Maybe not a card a week, but at least a card every other week.

I also need to start drawing Lego Gagas again; I’ve scrounged up some great reference material on Pinterest that needs to be Legoized.