Creating a simple searchable database using Evolution

(Timed random post)

A couple of people have asked me if I knew of a free database package that could help them keep track of their book and CD collections. They want the database to be searchable. Just type in a title or part of a title and they would get a list of matches if any.

If you’re on Windows there’s probably hundreds of free cataloging applications out there but if you’re on Linux, specifically Ubuntu, tough.

Synaptic offers you nothing but the usual SQL tools. Whatever you can download off Sourceforge is not quite what it seems as I’ve personally found out. Running Windows apps from Ubuntu using Wine… yeah but not for me.

If you have the inclination to mess with table relationships, queries and forms, I recommend you build your own database using the OpenOffice Database package, the equivalent of Microsoft Access . If you’re not that type of person, then read on.

Lucky for you, there’s an application with a little-known searchable database that’s shipped with all recent releases of Ubuntu that I know of right up to Karmic Koala. Its called the Evolution Mail and Calendar client.

What you do is turn Evolution’s address book into a catalog that holds other stuff than email addresses, like your CD collection for example. You can accomplish this in about 20 minutes or less. Absolutely no programming is needed.

To recap, we’re going to create a simple database for a CD collection where I can enter

– media type (CD or mp3)
– band or artist name
– album title
– track language (since I do listen to Chinese)

And I can

– add/delete/modify album info
– list my entire collection on the screen
– search for specific titles and categories

Ready? Here goes.

SETTING UP THE CD CATALOG

In Ubuntu I fire up Evolution. It should take me to the main Mail window.

Click on Contacts (bottom left icon) and I’ll get this.

By default, if its your first time, Evolution loads a blank address book using your computer name (mine happens to be called Personal). I’ll recommend that you create a separate database for your CD collection so you don’t mix it up with your regular address book. So lets create one and call it CD Library.

To do that, on the top left, click on the little “V” next to the icon New.

Click on Address Book. A dialog box opens up.

At Name I type in CD Library. Click Type and a drop down menu appears. I choose On This Computer to keep my database on my machine. When done this is what I’ll see.

Click ok.

I’ve now created my new empty database as listed on the top left panel below.

Now I can get down to keying in my stuff.

ENTERING THE FIRST ALBUM INFO

Let’s enter the first album details.

First, a little mental realignment. The address book’s fields are purpose-designed for entering contact information like home addresses and fax numbers. Unless you know how to change the field names, you’ll have to live with them. I get by it by mentally associating “Full Name” with Band Name for instance. Since only you will be using this database, it won’t matter right?

So I click on the New button on the top left and I get a contact editor dialog box where I key in my data.

Let’s put in Linkin Park under Full Name. The File Under field below it will reflect the words I typed above it. I ignore it.

Now I give the album a category. I have 2 categories in mind – CD or mp3 (it can be genre or anything you want). Evolution provides a predefined drop-down Category list Category you can use.

I won’t find CD or mp3 in the standard list but fortunately Evolution allows me to define a new category so I’ll go ahead and define CD as a new category.

I click New at the bottom and enter “CD” in the dialog box that follows.

I click ok, the dialog box disappears and the new category will be added to the drop-down list.

If I want, I can associate an icon with this new category but I’ll leave you to discover how to do that. Hint – click the Category Icon button.

Next, I click the check box to select the CD option,

I click ok and I have this.

We still need to define the album title (one artist may produce many album titles) so I click on the “Personal Information” tab at the top of the dialog window to get this.

I look for a field called Title and type in the album title, “Songs From The Underground Ep” there.

I leave the other fields blank, click OK and I have this.

Yay, I’ve got my first album details in my database. 😀

DECIDE HOW YOU VIEW YOUR DATA

Now because I’ve got dozens of CDs, screen space will be at a premium so rather than the default display cards I prefer to deal with a simple list of albums like on an Excel worksheet. I do that by changing the view to a “list view”.

So at the main (topmost) menu I go to View > Current View > List View

This is what I get.

My album is now displayed as a single line, the way I want it. Fields like Email, Business Phone, etc. have no place in my CD catalog but I can hide those and see only the fields I want. To do that I click anywhere on the field headings to get this menu.

Click Customize Current View and I get this

Choose Fields Shown and I get this dialog window.

I Remove everything and Add only Full Name, Title and Categories. Click ok and I have this.

A row with only Full Name, Title and Category. The rest of the unused fields are hidden. That’s much better.

ENTERING MORE ALBUMS

I now want to add in another Linkin Park album in my collection using the steps above.

This time however, when I type Linkin Park under Full Name, Evolution gives me this warning.

Understandable. Under normal circumstances you wouldn’t know two Mariah Careys so flagging down potential duplicates is an awesome idea. For an address book.

So I ignore that, click Add and I now have this.

I can now go ahead and add as many Linkin Park album titles as I want, not that they have that many.

EDITING AN ENTRY

To edit any data, from the list view I double click on any album displayed to get the data entry form. I can make all the changes I want there.

To delete an entry, I select the target album from the same listing, right-click and choose Delete.

SEARCHING FOR AN ALBUM

Let’s say after keying in a hundred albums, I want to search for an artist whose first name is Jay. I go to the search bar (top right), type in “jay” and click on the magnifying glass icon.

I choose “Any field contains” to refine the search, hit the return key and I get this.

And that’s exactly what I want it to do.

Note: The search function appears to be non case sensitive and that’s a good thing for us humans. We assume that dog = Dog = dOg = doG so don’t worry about getting the uppercase or lowercase exactly right.

Another thing to note is that you can search on parts of a name. For example if I search for “ark” I will get a list of all Linkin Park albums in my list. Cool.

Other things you can do:

– Use more unused fields if you decide you need more. Just don’t forget to unhide the columns you use. See DECIDE HOW YOU VIEW YOUR DATA on how to do this
– Add cover art for your albums as you would attach photos to your contacts
– Add icons to your categories
– Edit field entries to your heart’s content
– Add as many new databases as you like. Perhaps one for your books, movies, even fixed assets.
– Save your entire database as a portable vcard
– Save common searches as you would a database query

About the only thing you can’t do is rename your field names but hey, we can’t have everything when its free can we.

That’s it. You now have a searchable database for your CD collection.

(I haven’t tried it but I assume you can use other common e-mail clients like Thunderbird to create your catalog. There’s an even simpler way than the above to build a searchable catalog using any e-mail application, Microsoft Outlook included. For each album, send yourself an e-mail containing its particulars like title, track info and whatever. Organize these e-mails into categories or folders. Then whenever you want to locate an album, track or group of albums, you simply perform a search for a particular word. Easy peazy.)