Adding more data and sorting variables to a linked records tables

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.

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?

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.

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.

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?