Last week I told you how DVDpedia, the DVD cataloguing database, was being sunset by the developer at bruji.com. I explained how I exported all of my data in a CSV file plus the images, and then imported them into Under My Roof from Binary Formations, the app I use to catalog everything in my home.
When I finished the article, Sandy Foster in the live chatroom said that she was sure glad to learn about this because she uses another one of the “pedias”, Bookpedia. She has catalogued all of her quilting books, and would now need to look for a new solution. She’s not really interested in maintaining a database of everything in her home, so Under My Roof would be overkill for her.
When I posted my article about the process, Brian Blankenship on Mastodon @[email protected] thought Under My Roof sounded like a great tool, but he’s a Windows user and wondered if I knew of an equivalent for him.
In response to Brian, I explained that I haven’t personally used any tools like this on Windows. Whenever I’m asked about alternatives for Windows users, I usually start at a site called AlternativeTo. You enter the software for which you want an alternative, and then you can filter the results by things like what platforms you want the tool to run on, whether it’s open source, and the pricing model. The results are kind of hit or miss, but if there is a good alterative, it might just show up on this site.
I filtered Alternative To’s results for Brian down to just Windows inventory programs and wished him the best of luck.
But then I got to thinking. Here’s Brian and Sandy both looking for a way to catalog some stuff and for which Under My Roof might not be the right answer. What if Sandy and Brian could just use a normal database program to import their data?
I knew Sandy had catalogued books, and I went back to Brian on Mastodon and asked him what kinds of items he was hoping to track in a database and how he had his data. He answered:
I have two goals, one to catalog my CD and DVD collection, and two to have a household inventory. I’ve ripped my CD collection and most of my DVDs/Blu-rays, so I can create an import file if that’s possible with your solution. And I have a few different, yet incomplete, spreadsheets with lists of household goods.
This was going to be perfect – I could help both Sandy and Brian and maybe some of you.
My favorite freemium database program is Airtable from airtable.com. In 2018 I wrote an article entitled I Finally Understand Databases Because of the Free Airtable App and I’ve been using it ever since. Steve and I use it to manage what shows we’re watching on TV, and we also use it to manage all of our interviews from trade shows like CES and the CSUN Assistive Tech conference.
Bart always says to follow the money to make sure a free tool isn’t a creepy tool (or as he likes to call it a “freepi” tool). I’m glad to say that Airtable does have fee-based plans for teams and businesses at $20 and $45/month per seat respectively, but for free as an individual (or very small teams like me and Steve) you can get unlimited bases, 1000 records per base with up to 5 editors, 1GB of attachments per base, and a few more features.
I asked Sandy to export a CSV file from her Bookpedia database so I could test to see if Airtable might work for her. Sandy is always up for a good experiment and was even more enthusiastic since this might solve her immediate problem. It sounded like Brian could make his data available in the same way.
Airtable is available as a standalone app for macOS, Windows, iOS, and Android, or you can just log in with your free account at airtable.com.
My testing with iPadOS with VoiceOver was also informative because I discovered that there is no way to import data from the iOS version of Airtable. Luckily you can run Airtable via the web and the import functions work even on iOS.
So if you’re blessed with the gift of sight, and you have a desktop or web version of Airtable we can get started. I promise it’s easy and awesome from here on out!
When Airtable starts, you can create various workspaces on the left, and then using the button in the bottom left you can add a new base.
Once you create a new base, there may be two different ways to import the CSV file. I saw a sidebar on the right showing a bunch of nifty options, but you can also see in the default grid view a button that says “+ Add or import”. It kind of looks like a new tab, but when selected, you’ll see that with a free account, you can import a CSV file, Google Sheets, or Microsoft Excel. There’s a popover to see even more sources, including Apple Numbers, Calendar, Contacts, XML, and more.
This is good news for Brian because he already has a spreadsheet started on his home inventory so he could just start from there without needing to go through the intermediate step with a CSV file.
After selecting CSV, we get the overlay I mentioned earlier asking us to drag and drop, or copy and paste files. We can also select to import from several of the typical cloud storage providers. In this case, I’ll just drag Sandy’s Bookpedia CSV from my desktop and select Upload.
One important point about Airtable that I probably should have mentioned. Airtable syncs through their cloud so as soon as you hit upload, you’ve put your data onto their servers. In Sandy and my case with our DVD and book collections, that’s not a concern, but when I saw the option to upload my contacts, it gave me a little hitch in my giddyup if you know what I mean. I’ll leave it up to you to read, understand, and make your own decision of whether you trust Airtable’s privacy policy.
Ok, back to the fun part. The next question will be whether you want to create a new table or import it into the default table they have provided. I chose new table because that default table had fields I wasn’t interested in and didn’t want to clean up if they got all mushed together.
Airtable then pops up a little mockup of what the data will look like when it’s imported, with a note at the top that says, “check to make sure it’s right or let us know if something looks wrong.” Below the mockup is an option to adjust the import, and a button that says All Fields. This button pops up a list of the fields that will be imported and all can be unchecked except for that first sacred field I talked about last week.
Sandy’s Bookpedia export included the number of pages in each book and she didn’t give a hoot about that. On doing this import, she could simply uncheck that field and her new database would be cleaner right off the bat.
One of my favorite things about Airtable is how easy it is to create different field types. By that I mean you can define a field type as text, number, date, checkboxes, multiple selects, single selects, phone numbers, emails, and more.
In this import mockup, Airtable auto-selected field types for Sandy’s data. For example, since she has several books by the same author, it chose single select as the field type. Airtable delightfully color codes the different options in the select fields in nice pastels so it’s very easy to see the distinctive options in addition to reading the text.
I hit import, and in about 7 seconds, I had a beautiful grid view of all of Sandy’s quilting books. While this grid view is nice enough, the real fun in Airtable will come when we create new views. But first, we have to eat our vegetables. Remember we still need to get the book cover images into Sandy’s database. As you may recall, CSV files can’t contain images so we’ll have to drag them in one by one from the separate export of images made by Bookpedia.
The first thing I needed to do was create a new field to hold the book cover images. If you scroll to the right of your existing fields, there’s a plus button. From there you can name your field, and the field type we want is Attachment. If you hover over Attachment, you’ll see a tooltip telling you that this field is for images, documents, or other file types. You can add a description to the field but I think Book Covers as a field title will be sufficient.
The book covers will be the showy part of this grid view, so I think dragging this field as far as possible to the left would look good. Remember though, the title field is sacred so it will stay on the far left and the images can go into the second column over.
Attachment fields, when selected, show a little message that says “drop files here”, inviting Sandy to drag in her book cover images exported by Bookpedia. There’s to be no magic here, she still has to drag each book cover image in one by one, just like I did in Under My Roof. The other thing of note is that it takes a few seconds for the image to show up in Airtable after you drag it in. I assume it’s because Airtable is sending the image up to the cloud. The good news is that she doesn’t need to sit there and wait for it, she can start the next image while it’s still working.
Once the tedium of importing the images is complete, the fun can begin. The main reason to keep an inventory in a database instead of a simple spreadsheet is the ability to create completely different views of the data depending on what you want to see. The main view is called the grid view and it’s just as boring as any spreadsheet.
At the bottom left of the Airtable window, there’s a section that says “Create…” and it lists several views you can add with the free version of Airtable along with a few that require a team account.
One of the ones that might be fun for Sandy is the Kanban view. Kanban is a Japanese term for sign board and it’s often used in manufacturing or in coding to develop a visual way of looking at data. The key thing about Kanban is that it is really pretty and yet organizes the data in useful ways. In Airtable, the first thing to do is to choose a grouping field.
In Sandy’s case, a good grouping (yeah, I hear it) might be by author. The resulting Kanban view shows the books stacked vertically by author with their pretty book covers. I only dragged in some of the book covers for this example but it still looks really cool.
We don’t get much information beyond the title and the pretty cover in this default Kanban view. If Sandy wants to see more information on each card, she can go to Customize Cards and toggle on other fields such as the publisher, genre, and location.
But let’s say Sandy wants to see the books by their location. She could create a new Kanban view and stack it by location, or she can select Stacked by Author and just change it to location. I played around with that and very quickly I could see that Sandy has a little bit of data cleanup to do because of tiny typos in the locations. It will be a matter of seconds for her to use the single select in the grid view to choose the correct locations and her Kanban view will be beautiful.
I think the Gallery view in Airtable might be a great option as well. If Sandy is a very visual person, she might like the Gallery view which makes little cards for each record with the pretty book covers showing prominently. She can turn on and off info about the books like maybe she wants to show the location she’s entered so she can find the book on the right bookshelf. They look much like the Kanban view but they’re simply in alphabetical order. Of course, Airtable provides sorting on all of the fields in a simple dropdown at the top of the table.
With over 200 books catalogued, Sandy might like to take advantage of the Filter option at the top. Sandy has tagged her books with genre, so she could filter to find just the type of book she’s looking for.
I could go on and on about how much fun you can have with Airtable in setting up views for your data but I think you get the picture. I think for Sandy and Brian, Airtable might be a great free alternative to buying a dedicated application for doing inventories.
I would be remiss if I didn’t explain why I would still choose Under My Roof. It’s the difference between building your own structure and having a tool tailored to this specific task. Under My Roof already has separate areas expecting photos, receipts, and warranty information. It notifies me when my items go out of warranty. It has a special area for my insurance information. When I got my Apple Watch repaired, there was already a place for that information to be recorded. It knows my devices will have serial numbers.
But I also understand that not everyone will do the work to maintain this level of information so Airtable might be a much easier place for the Brians and Sandys of this world to get a bit better handle on the things they do want to inventory.
Check out Airtable at airtable.com