Dabble DB

The Dabble Blog

Archives: Clips

Make your data behave

Your data application is only as good as your data. Sometimes you may be faced with data that isn’t as precise as you need it to be. Dabble now lets you merge and split fields — basically equivalent to combining and dividing columns in a spreadsheet, but instead of having to format your data before you import it into Dabble, now you can let Dabble do the dirty work for you.

One nifty thing you can do now is merge a date and time with a duration (a length of time) to produce a value with a start date and an end date. To illustrate, here’s a movie showing a schedule from a recent Web 2.0 conference. I start out with the start time and the duration of each event, and then merge the fields. Once the merged field contains a start and end date, Dabble’s calendar view becomes more useful since you can see at-a-glance which blocks of time are filled and how long each session lasts.

Watch the video for a demonstration.

This video requires the Flash Player version 8 or higher, and also requires JavaScript to be enabled on your browser.

Another useful thing you can do is improve the precision of your data by splitting a text field in two. For instance, the data you’ve imported might have people’s names in one field, but you want to separate the first and last name. What’s the point of that? Once the names are independent, you can sort people alphabetically by last name. Or perhaps you want to group together everyone whose first name is Steve. Here’s another video to demonstrate.

This video requires the Flash Player version 8 or higher, and also requires JavaScript to be enabled on your browser.

Digg into Dabble

It’s been a while since we’ve released a cool Dabble video, so I’ve thrown one together to show off some new work we’ve done with interesting data import and integration (you Unix types out there can think of it as “pipes and filters for the web”). We’ve just started down this path, and there is no end to the work we can do to enable more of this sort of thing, but the results so far are encouraging. Check it out:


[Update: feel free to Digg this story]

Compact and Pivot

One of the things we like to talk about is how Dabble DB lets you view your data in different ways without having to manually re-arrange things as in a spreadsheet. Move your columns around, add and remove subtotals, filter results, and do it all without affecting how your colleagues see the data. The data itself may be someone else’s business, but how you look at it is yours.

At the moment we have two primary views in Dabble: a table view which is similar to a traditional spreadsheet, and a calendar view, which lets you work with date-based information. People using Dabble with larger amounts of data have been asking us, however, for a variant on the table view which would allow them to see aggregate data.

For instance, if you have 5000 entries in a financial report and you’ve grouped them by a particular category, it could be useful to see just the subtotals for those groups. Well we’ve recently introduced exactly this feature. We call it compact view — it takes your normal table view, and squashes it to give you just the highlights. It also allows you to pivot data from being rows to being columns (from horizontal to vertical).

This can be a powerful way to look at data, so we’re very happy that it’s part of Dabble now. As usual with these sorts of things though, it’s often easier to show than tell, so without further ado, here’s a 60-second demo showing off the compact view, using a fictitious invoice application.

The demo starts with a list of invoices and then shows how you can use different types of grouping to get different summaries of the data.


If you have 40 minutes or so to kill, you might be interested in watching the video of the Dabble demo we gave to the Vancouver Lisp Users Group last Thursday. (via Bill Clementson) Sorry, this video is no longer available.

Dabble Clip #1: Migration

We’re planning a series of posts on this blog entitled “Dabble Clips”. These will be short, silent movies illustrating some of Dabble’s more interesting and unique features, and accompanied by text describing how the features fit into some of the goals we’ve discussed. The first goal we’re going to tackle is this: Dabble should allow the user to incrementally build up an application. (For those who just want the eye candy, the link to the first clip is about halfway down this post.)

Andrew has posted before about why incrementality is so important, but it’s worth repeating some of his points. It is, first of all, unrealistic to think that you will get the structure of your application right the first time: every interesting system is rethought and refined with use. For many people, however, having to come up with a structure up front can be such a high barrier that they simply never start. I think that one of the reasons that spreadsheets get used so much more, in practice, than traditional database systems do is that you can simply start typing into a grid without worrying about defining a schema or providing types or even labels for fields. Only later, after entering some real data and getting a feel for the problem space, might you start thinking about feeding the spreadsheet more information (this column contains dates, this one currency), so that it can validate your data and provide more display or calculation options.

Although Dabble is very much a database and not a spreadsheet, that sense of just being able to sit down and start typing was something that we were very interested in capturing. For example, adding a new field in Dabble is trivial: there’s a link at the bottom of every form that prompts you for a name and adds your field instantly. All fields start out as text fields, so you can start entering data right away without thinking too far ahead. Only when you decide you want more out of the system do you need to migrate the field to a more specific type. Because Dabble aims to end up with a more structured application than a spreadsheet, however, the migrations (a developer might call them “refactorings”) need to be a fair bit more sophisticated than just reformatting your data.

Dabble Clip #1 (sorry, no longer available) shows me performing two of these migrations on a simple Todo List application. The first is fairly simple, the second more involved.

At the beginning of the clip, I configure what started out as a free-form Status field to use a restricted set of choices. Dabble performs the migration by collecting all of the current values in that field (in this case, just “Open” and “Completed”) and using them as the initial set of choices. From then on, the UI shows Status as a drop down list rather than a text input, either when editing an individual object or when filtering the whole category. This kind of migration is also useful when you’re importing data from a consistent external source, like a data dump from another system. For instance, when we first brought in the OSCON conference data to use as an example, we noticed that the Session Type field was regular enough to migrate from its text form to the menu you can see in the linked screenshot.

Before doing another migration, I add an “Assigned To” field and start entering data. At first I’m just typing in single names, but some tasks really need to be assigned to multiple people (Jason Fried might disagree, but hey, he can build his own version of this Dabble app). With a text field, the best I can do is type in “Avi & Andrew”, but that’s not going to scale. That becomes especially obvious when I try to group the todo items by responsible person: with that ampersand in there, there’s no way to properly see a list of just the items that are assigned to me.

So at that point in this somewhat accelerated evolution of the application, I do the second migration. I change the type of the Assigned To field from text to a List of Entries. An entry is a full row or object in my database; so far, I’ve been dealing exclusively with entries in the Todo category, but here I choose to introduce a new Person category. I also tell it to migrate by splitting on “&”, since that’s how I’ve been entering lists of items so far.

This migration does quite a bit at once:

  • It creates a new type of entry in the database, “Person”, and adds a “Name” field to it
  • It goes through all of the Assigned To values and splits them into multiple values, so that “Avi & Andrew” becomes two values, “Avi” and “Andrew”
  • It converts each of those values into a new Person entry, and puts the value into that Person’s Name
  • It links up all the Todo entries to the new Person entries. If two todo items’ Assigned To fields used to reference the same person by name, they will now link to the same Person object.

After the migration, what was once a flat table of todo items is now a network of interlinked entries. Each Person knows which todo items it’s linked to. We can even add a field to its own entry form listing them, the mirror image of the Assigned To field on Todo. And of course we can add more fields, fleshing out Person further, and then migrate them, and the iterations continue…

As a side note: since we plan to do more of them, we’re curious what you think of this form of entry: part screencast, part tutorial, part blog post. Useful, or trying too hard? Leave comments.