Open data from TMS for all

How to get shareable collection data from TMS without tinkering under the hood.

This is a page from the original collections management system at the Williams College Museum of Art. It’s on view in the current exhibition “Accession Number.”

TMS stands for “The Museum System.” It’s the proprietary collections management system for thousands of museums. It was built back when databases were exotic and expensive, when collection information was entered by hand by a select few, when servers were local and accessed on-site, and when sharing collection information meant printing labels and reports. For many museums, TMS was their first technology system. Despite sweeping changes in technology (hello internet!), TMS remains the source-of-truth for collection data for many museums.

Truth be told, TMS is simply the front-end for a (rather baroque) relational database. (Similar to those used everyday by WordPress, Drupal, etc.) You could get the collection data out by pulling it straight from the database. For museums without technical staff, though, this would likely be a one-off project, and an expensive one. Another approach may be more sustainable: a workflow that allows registrars and collection managers to export data with the tools they use every day.

The goal of this little guide is to gather shareable collection data using only the client-side front end of TMS. We’re aiming for a CSV file (comma-separated-value)—it’s a simple text file that can be opened by almost any application. It’s bare-bones, but it’s the most useful type of data file for researchers, scholars, artists, and others. Once you have your CSV file, you can post it on GitHub just like the Met, Carnegie, Cooper-Hewitt, Tate, MoMA, and WCMA. The workflow should be simple enough that it’s repeatable and sustainable, so you can update the dataset as needed, without calling in extra help.

I’ll walk you through what we did to share collection data at WCMA.

Let’s get started.

Here’s the TMS home screen. Start by selecting the “Objects” tab on the far left.

…that will bring you to the “Objects Query” screen. This screen is asking how you want to select the objects for your dataset, and how you want to output those objects.

Which objects do you want to include in your dataset? In short, you want your whole collection. That’s because your collection is interesting as a whole. It says something about you as an institution. It reflects the personalities, fascinations, foibles, fortunes and biases of a human cultural organization through time. Does it matter if the metadata is incomplete, out of date, or wrong? NO. Your data will never be complete, up-to-date or perfect. This is a snapshot in time, not a catalogue publication. (…and those are always perfect, right? 😏)

Use an “Advanced Query” to gather objects; view as a “List.” Click “Next.”

Select “Advanced Query” to build your list. “Query Assistant” can also provide a step-by-step wizard for selecting objects. Select “List” to output your objects.

WCMA uses the “Classification” field to broadly organize objects. Because there were classifications that we thought were not appropriate for the dataset (temporary loans, archival objects, unlocated objects, non-WCMA objects), it was easiest to include all objects that didn’t have those classifications.

Here’s what we developed on the “Advanced Query” screen:

On this screen you can build your query using drop-downs. It can be cumbersome, but it can work. Trial and error is your friend. Note the And/Or logic field and the use of parentheses for nesting. Also note: you can name and save your query for next time. We named this one “GitHub CSV.”

Build your query, save it if you like, and click “Execute.”

Now that TMS knows what objects you want to include, it will load them into a “List” view.

The List view shows each object as a row, and metadata fields as columns. (We’re getting closer!) TMS ships with some standard list views, and they’re a fine start. You can select your list view using the drop-down at the top left.

Eventually you’ll want to add fields to your dataset, like image filename, earliest date, latest date, and object dimensions as numbers. You’ll also want to edit field order and field names. (Surprisingly, there is no shared standard for fields and fieldnames used across museum datasets.) To do all that, you’ll need the List View Designer application that ships alongside TMS. You’ll also need to know SQL to create a new view. Or know someone who knows. Or reverse-engineer or borrow some SQL. Or pay Gallery Systems to write a bit of SQL so you can output your own data. I know, I know. Don’t get me started. It shouldn’t be so hard. It should be easy, actually. Museums need systems that make it easy to put data in and get data out. That’s a topic for another day. (Let me know if List View Designer is something you’re interested in for a follow-up post. UPDATE! We released an XML profile on GitHub to make it easier!)

This List view is altogether lovely.

Even lovelier is the almost-hidden treasure: when you right-click on a list item, a contextual menu pops up with the option to Export > Excel > All Rows.

From the List view, right-click on an item, choose Export > Excel > All Rows. This will give you a nearly perfect file that Excel will massage just a bit. (The less persnickety can also select Export > CSV > All Rows, get a not-as-perfect file, and be done with it. Doing it that way creates a file in which every value is enclosed by quotes, regardless if they’re needed. It doesn’t look quite as nice.)

BOOM. Mic drop. Etc.

Chances are, the next thing you know, a file with all your collection data will open in Excel. This is good.

In Excel, go to File > Save As.

Where it says “Save as type,” select “CSV.”

Under “Tools” go to “Web Options” select the “Encoding” tab, select “Unicode (UTF-8).” This will ensure that accented characters are loved and cared for across platforms. Click “OK.”

Then click “Save” and you have your dataset, ready to share.

In Excel, select your file type, mark your preference for UTF-8, then save.

Done!

This project made possible by a generous grant from the Mellon Foundation. For more stories related to this project, check out WCMA Digital or follow Chad Weinard.