Creating a submissions log with Airtable

Okay, so Twitter ads were useful for once. They pointed me to Airtable, which is a very slick site which is halfway between a spreadsheet and a database. You create tables, like you would in a database, but adding, updating, and deleting columns is as easy as doing the same for a column in a spreadsheet – plus you can link between tables easily.

I was asked to put together a very simple submissions tracking application. “Show me a list of markets with deadlines, and a list of what I’ve sent where, the date I sent it, and the date I heard back”. I was able, with the help of Airtable, to put this together very quickly. (I would note that the sample sales tracking database was instrumental in understanding how to set things up; I encourage anyone who wants to try their hand at a similar application take a look at the samples, which are really well done.)

One design note: you’ll make it easier on yourself if you go bottom-up in your design. It’s possible to go top-down, but it means creating columns that you have to backfill later with links; in my case, I created the submissions table first, which is dependent on the pieces and markets. Because I did it that way, I ran into the restriction that the first column can’t be a link, and I had to change it to a formula text column instead. I’ll show it last, because it’s easier to understand that way.

I created a table of “pieces” – things to be submitted. This was pretty simple: a title, the kind of piece, date written, and a notes field – “everything needs a notes field.”

The kind is a selection column:

Then a table of markets – places to submit things. That had a name, a deadline date (most markets have submission deadlines; even though those change, it’s easier to put in the current one and update it later than to try to have a table of them and fill in the right one – simple is the watchword), and of course notes.

Now we can properly fill out the Submissions table. The first column, because it can’t be a table link, was set to

Piece & “sent to ” & Market & “on ” & {Date submitted}

(This means empty records have a note that reads “sent to on”. Oh well.) Market is set to a link to the Markets table, and Piece to the Pieces table. Date submitted and response date are of course dates, and the Response is another selection column:

Plus as always, notes. Here’s the final table:

To use this, one adds pieces to the Pieces table and markets and deadlines to the Markets table. The Markets table can be sorted by date to see where pieces can usefully be submitted, and the submissions logged in the Submissions table by clicking on the Piece and Market cells to fill them in from the available options, followed by the submission date once they’re sent out. The table has to be updated manually once a response comes back.

The filters and sorting can be used to figure out what’s out to a given market, what’s been rejected or accepted  by a given market, what’s been sent to a market, and so on – and it provides a handy log, when sorted into submission order, of the work done; filtering even lets one see how much has been done over a period.

This was demoed and found to be very usable. The flexibility to add more data to better slice and dice how submissions are going should make this easy for to customize as needed.

There are some shortcomings:

• Sharing is only by email, so I can’t put up a public copy of this Airtable to show off.
• Lock-in: I can’t run this anywhere else; when it passes 1200 records, I’ll have to upgrade to a pay tier – and if Airtable goes poof, so does my database.
• I can’t do full-up relational queries, like “show me all the short stories rejected by market X or Y” easily. I can use filters to select only the short stories and items with status rejected and in market X, but “or” operations are harder.
• Automatically creating many-to-one, one-to-many, and many-to-many mappings may be possible but they’re not straightforward.
• It’s possible to do backups, but not easy. Snapshots are OK unless there’s a data loss at Airtable, in which case the data might be permanently gone. My best option is probably to write a program using the Airtable access APIs to pull the data and back it up locally, and write another to reload it in such a case. (The tables would have to be reconstructed; there’s no way to download a schema or upload one, either.)

Overall, because I needed to put together something simple, usable, flexible, and done  in a very short time, Airtable was the right call for this application.