License files for Airtable

December 4, 2018 at 10:03 AM by Dr. Drang

Shortly after my software license post went up, Jezper Söderlund tweeted me a very good question: How do you handle software that uses a license file instead of a license key? Turns out there’s a simple answer, and I needed it.

After exporting the licenses from 1Password and importing them into Airtable, I had noticed that the entry for Witch, the better-than-stock app switcher from Many Tricks, had no license key. Weird, I thought, but I didn’t follow up on it. When I saw Jezper’s tweet, I realized why.

Witch does, in fact, use a license file instead of a license key to authorize its use. The license file is buried in the Application Support subfolder of your Library folder. 1Password has a system for adding attachments to software license entries, and I had done that when I bought Witch. Unsurprisingly, the file attachment didn’t get exported to the CSV file. I was a little surprised, though, that the CSV didn’t have a field to indicate there was an attachment for that entry.

In any event, now that I knew I needed to handle file attachments, the solution was simple. I added a new field to my Airtable database and chose the Attachment field type.

Airtable field type

With this field added to the database, it was easy to drag Witch’s license file into Airtable. When you click on an empty attachment field in Safari on the Mac, the field tells you it’s ready to have a file dragged and dropped onto it.

Airtable attachment field in Safari

I scrolled through all the other software licenses in 1Password to see if any others had license files. They didn’t, but now I’m ready to handle any new apps that do.

Reorganizing software licenses

December 1, 2018 at 7:35 PM by Dr. Drang

As part of the slow process of moving my myself away from 1Password to iCloud Keychain, last week I exported the list of software licenses and put them in a database. This took more time than I expected, as I tried out different ways to organize the list.

I have, by the way, nothing against 1Password. It’s a great program and has been a big help to me for many years. But as I try to simplify my computing setup, it seemed best to avoid having a separate program that, as I use it, largely replicates a function of the operating system. Especially when that separate program has moved to a subscription service—I’d rather use that money to pay for software that’s substantially different from what Apple provides.

One of 1Password’s functions that isn’t in iCloud Keychain is the list of software licenses.

1Password software licenses

There are a couple of ways to get the license data out of 1Password:

I started out doing it the iOS way and quickly decided the Mac way would take less time for the several dozen licenses I needed to export. Whichever method you choose, you won’t be pleased with how much fiddling you have to do.

With the data in a Numbers spreadsheet, I figured there were three options:

  1. Just keep it in a spreadsheet. That’s not a bad format for this kind of data, and I could make it available on all my devices by saving it to iCloud Drive.1
  2. Export the spreadsheet out to a CSV file and import that into a database. Airtable would be my choice, as it looks good and works across platforms. I’m not entirely sold on its security2, but software licenses don’t strike me as especially damaging if they get hacked.

  3. Export the spreadsheet to CSV and then turn it into formatted text that can be kept in Notes. This is also available on all my devices and notes have the additional advantage of being lockable. This little Python script reads the CSV and formats it for a note:

    python:
 1:  #!/usr/bin/env python
 2:  
 3:  import csv
 4:  
 5:  print("Software licenses\n")
 6:  with open('1p.csv') as csvfile:
 7:    reader = csv.DictReader(csvfile)
 8:    for row in reader:
 9:      print(row['app'])
10:      for f in reader.fieldnames[1:]:
11:        if row[f]:
12:          print("{}: {}".format(f, row[f]))
13:      print()
14:

    It opens the CSV file named 1p.csv, and writes out every record in its own paragraph. The first line is the name of the app, and the subsequent lines are all the other fields in

    key: value

    format. After importing to Notes, it looks like this:

    Software licenses in Notes

    The disadvantage of using Notes is that I have to type everything in myself when adding a new entry. Also, I can’t just add it to the bottom if I want the list to stay alphabetized.

Although I’ve done all three of these to see how they work, I think I’ll stick with the Airtable solution. It’s easier to scroll through than Notes and has a single-entry view that Numbers doesn’t provide, which is especially convenient when using an iPhone.

Single entry in Airtable

  1. And password protect it, too. Thanks to Teddy Svoronos for reminding me. ↩

  2. No particular reason for this; I’m just generally skeptical of cloud security, especially with things I’m not paying for. ↩

Airtable

November 24, 2018 at 4:37 PM by Dr. Drang

I first heard of the Airtable database about a week ago from this Twitter thread involving Christina Warren, Ed Cormany, T.J. Luoma, and Rosemary Orchard. Already it’s my primary way of accessing my main projects database from work.

Airtable is a cloud-hosted database1 that can be accessed through a web interface, an iOS app, or programmatically through an API. I haven’t used the web interface much, but the iOS app is very well designed and the API is a wonder of clarity.

The database I moved into Airtable is a table of projects at work that I’ve been using in one form or another for over 20 years. It’s basically a list of jobs, storing the following information for each job:

For many years this was just a plain text file, one record per line and the fields separated by pipe (|) characters. A small set of Perl scripts were used to add and extract information. Then I imported it into a SQLite database and rewrote the scripts in Python using the sqlite3 library.

As I found myself wanting to search the database on my phone and iPad, I moved the database file to iCloud Drive and wrote some scripts in Pythonista to do the searching. This worked, but it was a little clumsy and the results, while correct, were ugly. I didn’t really want to put in the effort to make the output prettier, but I was resigning myself to get down to it when Airtable appeared in my Twitter feed.

It took basically no time to export the data from SQLite and import it into Airtable. Now I have a nice looking view of the project information (well, it’s nice looking when I haven’t scrambled the fields for anonymity).

Airtable database on iPhone

It’s easy to search by tapping the magnifying glass, but I often find the information I want (who’s the client on Project X? what project did I just open for Client Y?) sitting right in front of me when I open the app. The other fields can be seen by scrolling to the right.

When I first imported the data, all the fields were treated as either text or numbers. Airtable has a variety of other field types that can specialize these two generic types:

Airtable field types

Within the Number type, you can choose between integer and floating point, which can be quite handy.

To reduce the clutter, I have a filter that shows only the latest projects.

Airtable filter

And I sort them by number in inverse order because the projects I’m most likely to be looking for are the most recent.

Airtable sort

The API is easy to understand and has possibly the best documentation I’ve ever seen. The docs are formed with examples that are specific to your database; they include your fields and your records, not some made-up example that means nothing to you.

Airtable API example

Airtable doesn’t provide a Python library, but it’s easy to use the API through the Requests and JSON libraries:

python:
 1:  # Add record to Airtable database.
 2:  airURL = 'https://api.airtable.com/v0/ABC987654321/projects'
 3:  airHeaders = {
 4:    'Authorization': 'Bearer 12345ABCDE',
 5:    'Content-Type': 'application/json'}
 6:  payload = {'fields': {
 7:    'name': 'New Project,
 8:    'number': 9999,
 9:    'client': 'Jane Q. Client',
10:    'clientref': 'JQC1234',
11:    'directory': 'new.project',
12:    'box': 0,
13:    'email': 'jane@company.com'}}
14:  r = requests.post(airURL, headers=airHeaders, data=json.dumps(payload))

The identification code in the URL is specific to the database, and the authorization code that goes in the header is specific to the user. The JSON library’s dumps function takes a Python structure—in this case a nested dictionary—and turns it into a JSON string for POSTing. The response is stored in the r variable, from which we can extract the JSON data that tells us whether the request was a success.

For the small data sets I typically work with, the free version of Airtable, which limits you to 1200 records per database, is sufficient. I’m a little leery of using free services for job-critical data—I don’t want something I rely no to end up with the dreaded sunsetting post on Medium—but Airtable seems to be well funded for now. Also, I’m still keeping and updating the old SQLite database for safety.

With my first Airtable database under my belt, I’m eager to add more. Airtable markets itself for teams (because everything markets itself for teams nowadays), but don’t let that stop you from using it for personal databases. I have a few inventories for both work and home that could really use the structure of a database behind them.

  1. They say it’s “part spreadsheet, part database,” but aren’t all databases part spreadsheet (and all spreadsheets part database)? ↩

Tweeting App Store links via Shortcuts

November 22, 2018 at 7:34 AM by Dr. Drang

The extra long framed screenshots in yesterday’s post drew some puzzled tweets. They were created with PicSew, which stitches together overlapping screenshots into a single long image and can, if you want, frame the image with the outline of an iPhone. I don’t know if I’ll continue to add the frame, but it’s kind of fun.

After answering the questions about PicSew, I thought it would be nice to have a shortcut that would automatically assemble a tweet with an app’s name, icon, and App Store link. So I combined the general outline of this shortcut for tweeting Kindle book links with the parts of yesterday’s shortcut that handle app names and icons and ended up with this:

Tweet App shortcut

You can build it from scratch or just download it.

To use the shortcut, you start on the App Store page of the app you want to tweet. Bring up the share sheet, choose Shortcuts, and select the Tweet App shortcut. You’ll then be presented with a tweet ready for sending.

App tweet ready for editing or sending

By default, the text portion of the tweet contains the name of the app and the developer, followed by the App Store link. You can edit the tweet before sending.

With these last two shortcuts ready to go, I guess I should start blogging more about apps.

Older posts