Adding more data and sorting variables to a linked records tables

[Francesca_Bensi]

Hello,
I’ve been working on an extensive reading log on Airtable and I’ve gotten quite a bit of it figured out. I different “tables” in the same base for different years. I have certain line items as linked records (such as author name, genre, rating) on each of those linked records tables I am basically just doing yearly tallies right now. So basically how many books read for each author per year and then a total count field that adds up all the years to give most read authors, for example.

What I’d like to do is have more information added to the sorting capability and I can’t figure out how to make that happen. So for example instead of just counting total books read per year for that author (or genre etc) I’d like to add another field of information. Like say how many books per author and then sort per genre or rating. Basically to have the information of what do I generally rate books by a certain author or in a certain genre for example.

Any ideas on how to accomplish this or what fields I need to add would be greatly appreciated!

[Jeremy_Oglesby]

All of that should be possible using the “Grouping” feature.

My first piece of advice would be to combine all of your “Books Read” into a single table, rather than splitting them up into different tables by year. Instead, have a field in your single “Books Read” table where you mark the date you finished the book, and then another formula field that pulls out the year from that == YEAR({Date Finished}).

Now, with all your books in the same table, create different views that Group your records in different ways – and you can stack those groupings. So, for example, Group first by Year, then by Author, then by Genre – in the summary bars that get created, you’ll be able to see the average rating you gave to a genre by an author in a year, the average rating you gave to an author across all genres in a year, and the average rating you gave to all books in that year.

Group first by Genre, then by Year, then by Author, and you will be able to see the average rating you gave for an author in a specific genre for the year, the average rating you gave for a genre across all authors in a year, and the average rating you gave to the genre across all years.

The key thing that enables doing this kind of analysis, though, is the combining of all “Books Read” records into a single table.

[Francesca_Bensi]

Thanks for the idea Jeremy, I’m working on this. When I group for example first by year then by author it sorts them by Alphabetical order. So it’s a little harder to see which one was the most read of the year. Is there a way to make that sort by the count instead?

[Jeremy_Oglesby]

Ah, I see what you were after now.

No, you can’t sort by that “Count” per section. However, another way you could do this is to set up a Table called “Years”. For each book read, link it to a record in the “Years” table for the year (this could replace your formula field for “Year” in the “Books Read” table). So in your “Years” table, you’d have a record called “2018”, another record called “2019”, etc. And you link a Book to the Year in which you read it.

Then, by using this extra table, you can use Rollups, Formulas, and Lookups to get an author’s “Read per Year” count in a separate field in your Books Read table, and sort by that field.

[Francesca_Bensi]

This is where I’m stuck since I get those 3 functions confused and maybe not use them properly.

What I had setup which I thought would work was a table “Authors” and another for “Genre” (which has all the authors/genres on the linked record side) and brings data from each year into separate columns for the totals per year. I can easily tally up and sort by year, and I have a formula field that adds up those count totals to give me most read author/genre by year, or overall.

So an example of what I was trying to do and can’t get to work. I added a Lookup field for the rating and it pulls from the specific year table. I did that for every year, so it shows me the linked record for the rating under each author. What I’d like to do is have a column that using a formula gives me the average of those. But when I use the AVERAGE() formula and enter the columns that have the linked rating record, it gives me a NaN code on that instead of doing the average of the ratings.

[Francesca_Bensi]

So I think I got the average rating working if I create an additional field in my original table of type Number for the rating. My original Rating field is a linked record not a number type. If I use a number it’ll give me the average. Does that mean even if my linked record contains only numbers it won’t treat it as numbers?