Program or platform for making complex custom lists

May 14, 2018 7:36 AM

I like to make lists: lists of books I want to read, my ratings of movies, lists of things I own. For simple lists, I put them on Evernote. I put more complicated lists in Excel, but when a list/spreadsheet gets too long or too wide (too many columns), I would like to be able to view the data as something like a sortable table or even a results page showing just the relevant information.

For instance, let's say I have a spreasheet with the following columns: author name, author country of origin, book title, year published, read (Y/N), owned (Y/N). I'd like to be able to click on the author's name and just see the books by that author, maybe even sorted by "read" status. If I click on Canada, I would like to just see the books by authors who were born in Canada. I see websites that do this all the time. Do I need to build my own database(s)? I know there are specialized database programs and websites for certain types of collections. But if I put my books/book wishlist into librarything or goodreads and my cds into discogs and my list of concerts I've seen into songkick and my movie ratings into netflix, everything gets really fragmented and inconsistent and I lose control of the presentation. And what happens when one of those sites goes under or decides to use my data in a way I don't like? And where do I put my yarn data organized by color, # of skeins, material, source, etc.? Or my household appliances and their service history? Or a database of historical events I could view in different ways. This is just for my use. I wouldn't mind it being on the web though I prefer it to be in a private/semi-private cloud, like Google Drive. Would prefer free to pay and one-time pay to subscription.

*In preparing this question, I came across recommendations for Airtable. It seems like a little bit of everything, but maybe it can do what I want? It certainly seems like it can contain the data I have, but can it present it back to me in the way I described above?

posted by nequalsone to Computers & Internet (15 answers total)

Airtable is very powerful and allows you to link different kinds of data, and also supports creating forms that you could use to easily do the data entry part, and so on. I think it *would* fit your needs, but I don't know that I would want to use it for this project myself because once you've got all your data in airtable and have everything working the way you want, you're locked in. And what if airtable disappears/is acquired and discontinued?

I think you've got a perfect use-case for MS Access or LibreOffice "Base", which would ensure you had ownership over everything. Although this would complicate things if you wanted access to the data from, say, your smartphone, which Airtable would permit.
posted by dis_integration at 8:20 AM on May 14, 2018

Would Excel pivot tables do what you need? They have extremely powerful sorting and filtering capabilities and you should be able to easily add one that uses your existing Excel data as a source table.
posted by crocomancer at 8:27 AM on May 14, 2018

What you are describing is very literally a database. MS Access would probably work for you although there's going to be a learning bump.
posted by parm at 8:37 AM on May 14, 2018 [1 favorite]

Yeah, if this is just for personal use and you're not looking to build a professional skill or anything, I would use pivot tables in Excel. At least let them take you as far as they can go before you start futzing with database software. Here is a quick video of someone answering similar questions for a list of films.
posted by rollick at 9:03 AM on May 14, 2018

Nthing Excel and possibly pivot tables, although the sorting and filtering functions might be sufficient. Also, with Excel you can limit certain fields to certain values e.g. you could have the options for "country" be US, Canada, China (or whatever set you want) or for yarn you could have a list of yarn weight options (aran, worsted, etc).
posted by 2 cats in the yard at 9:59 AM on May 14, 2018

Response by poster: Thanks for mentioning the pivot tables. I had no idea they were so versatile. It does seem like they can do what I want to do. But it seems like there would be quite a few steps each time I wanted to look at the data a different way. I did see something about using pivot tables to build dashboards, so I will look into that.

I probably should have emphasized this more, but I am interested in having broader access to my data, which is why I mentioned having it on the web or Google Drive. That's part of the reason I am thinking about moving away from Excel (and by extension Access or Base), unless I can perhaps build a web front end for it? I am OK doing some work to get things set up if I can then have easy, dynamic access to the data. Is there a way you can publish an Excel pivot table, or a dashboard with multiple tables, online so it queries the underlying xls file when you click on it to drill down? Or would Office 365 give me a web-based interface to do that? Does Google Sheets have the equivalent of pivot tables?

How crazy is it for someone who has built an Access database for a class but never for "real life" and done a little HTML coding to try to build a web-based database and interface using SQL or something for this type of data? That's probably not sensible.

As long as I can get my data out again in CSV or similar, it might be worth trying Airtable even if I would have to revert to no fancy interface after.
posted by nequalsone at 10:08 AM on May 14, 2018

Google sheets has pivot tables although I haven't tried to use them. Office 365 allows you to view pivot tables via a web interface - ie you could host a "full" Excel sheet in OneDrive and you wouldn't necessarily have to have Excel on your computer to view a pivot within it, although you would need Excel to create one.
posted by crocomancer at 10:35 AM on May 14, 2018

In terms of publication, Excel online can do everything you want, and if you want to make it more beautiful and useful, then Microsoft PowerBI is pretty easy to use and gives you access to some excellent visualisations which you can share with anyone.
posted by ambrosen at 10:43 AM on May 14, 2018 [1 favorite]

I use Airtable at work to keep our various databses of things. Just as an example, we keep a database of technologies we've heard of and maybe looked at, but don't officially support, and we tag them all so that when someone calls and says "I need something that does X" we can search the database for everything with the X tag. This is done by using a filter and it looks like this. There are also different views you can use to display your database. There's a standard "looks like a spreadsheet" grid view, but you can also create a gallery view that is prettier, shows any images you upload, and displays each record as it's own "card". You can filter just as easily in that view as the grid view. You can also create forms for ease of input.

So, I vote Airtable. I've been super happy with its capabilities. It takes a bit of learning just because it's a relational database and that requires a different way of thinking about your data than a spreadsheet but it's very user-friendly.
posted by soren_lorensen at 11:00 AM on May 14, 2018 [1 favorite]

I too use Airtable and find it handy for looking at data different ways. Airtable can export data as CSV so that checks another of your requirements. I use it on both a PC and an Android phone, fwiw.
posted by anadem at 12:37 PM on May 14, 2018 [1 favorite]

You don't even need a pivot table. Just make a regular Excel table, and use the column headers to sort or filter. Add total rows for your desired aggregate data, and you're done.

If you really want to get fancy, record some macros for common tasks, and put icons for them in your Quick Access Toolbar.

Excel table tutorial on YouTube
posted by SuperSquirrel at 6:22 PM on May 14, 2018

How crazy is it for someone who has built an Access database for a class but never for "real life" and done a little HTML coding to try to build a web-based database and interface using SQL or something for this type of data? That's probably not sensible.

This is pretty much the Introduction/Tutorial step of like 90% of the CRUD libraries for programming languages. Create, Read, Edit, Delete. The birthplace of Web 2.0 startups is taking something like Ruby on Rails (insert Python / Perl / Java / etc. same), typing in a list of Table names and Fields and some Relations between them, then you press a button and it's a web app all ready to go.

So, not crazy really. Pretty much the first step of trying the self-programming plus ORM/CRUD thing will easily get you a local web server with a GUI interface to your data. A good one will let you use SQLite so your database is in a single file that you could share about. You can take that first attempt as far into the web scale realm as you desire.
posted by zengargoyle at 7:05 PM on May 14, 2018

I'll add. With the ORM stuff I've used... You could save your spreadsheets as CSV/TAB/whatever, import them yourself into a SQLite database (one table per sheet or whatnot), the point the ORM creation tool at that SQLite database, press RETURN, and have a web app running on http://localhost:3000/ No muss, no fuss. It's really easy to start if you want to go down the path of some SQL/HTML/JS/etc. programming.
posted by zengargoyle at 7:11 PM on May 14, 2018

Yes to pivot tables in Sheets. Lots of helpful videos out there. I’ve done it exactly once for a test and it seemed fairly intuitive. Not sure but I think you could have pivot tables in multiple tabs for different views of your data.
posted by AnOrigamiLife at 12:09 AM on May 15, 2018 [1 favorite]

Response by poster: I am probably going to give Airtable and Google Sheets both a try since I can migrate a few things over without a big commitment—and I remembered that I don't have a subscription to Office 365!

If I were to go the web database route, is there a good package, tutorial, etc. that would get a novice started and that might be geared toward the type of thing I'm trying to do, rather than any database-driven website/web-app? Is Ruby on Rails what I want? Or LAMP/WAMP?

Zengargoyle, when you say "ORM creation tool" what exactly do you mean? Can you point me toward one? Or would such a tool be embedded in the other software I would need to get?

Thanks everyone!
posted by nequalsone at 9:03 AM on May 15, 2018