One to many question

[J_W]

I could use some help with a problem that’s harder for me than it should be, but I’m new to Airtable.
First table is a list of unique books. There is also a multi-select column of genres that apply to each of the books.
Second table is a unique list of students. Each student has a multi-select column of interests that are the same items as the genres of the books.
My goal is to create a view link that I can send to the student that matches all the available books with any of the ‘interests’ that he/she has selected. Ideally, the student would be able to read other information about the books that I have in that book table.
Thank you for your ideas.

[JonathanBowen]

Hi @J_W - here’s how I would approach this problem:

• Have 3 tables - Books, Genres and Students. (The genre’s table replaces your genre multi select).

A book has one or more genres (a link field) and a student is interested in one or more genres (also a link field). Then, on the student table, you can do a lookup on the genres field to display the books associated with the student’s genres. You can then click on a book in the lookup field to show additional information about the book:

Have a look at my example base here:

Airtable

Books and Genres - Airtable

Explore the "Books and Genres" base on Airtable.

JB

[J_W]

Thank you, that’s very helpful. I see how they work together too.

Now, the follow-up:
My goal is to share the matches with each student. I want to share the book name(s) and also some other information in the Books table, say it’s your book description field, and a publisher field, and a price field.
Does that overly complicate things?
And would it just be easier to manually use the student’s “genres interested in” to do a filtered search and send those results manually to the student?
Thank you.

[Justin_Barrett]

J_W:

My goal is to share the matches with each student. I want to share the book name(s) and also some other information in the Books table, say it’s your book description field, and a publisher field, and a price field.
Does that overly complicate things?

That depends on how you envision sharing these details with the students. Do you plan on sharing details on all of the books that match a students interest(s), or just selected books from that match list? With a massive book list, a single student could have dozens or hundreds of matched books. I can think of some ways to aggregate certain details in a semi-friendly fashion for email/print, but it would pull in such details from all matched books, not just a few.

[J_W]

Thanks Justin. I was thinking of sharing all the books, yes. What I have done after playing around is to add a lookup field to the books table called student_lookup based on the genres. Then I created a filtered view of the books table with the student_lookup contains “the students name” and I get a narrowed down matching list that I can share the link to. Does that make sense?

I also realized that while I called everything Genres, I really have different “descriptors” mixed in that field that aren’t really genres. So I’ve created other tables, just like the Genre one and linked that to the student’s record. That gives me a second student_lookup2 lookup field in the books database. I can then do a view based on that field, or combine it with the first one if that looks better.

The reason I’m separating these descriptors, by the way, is that I’ve got a form (Wufoo not airtable) that the student completes about him/herself and it groups these descriptors in logical groupings, where genres is one, and that just makes it easier. I might want to then send them genre matches at one time, then in a later email send them some other matches, etc.

I’d love to hear more of your thoughts on structure and semi-friendly email/print, because I do think that will happen. I probably would try to limit what I sent at any one time to no more than 20-25 items and most would be less.

Thank you, Jonathan

[Justin_Barrett]

J_W:

What I have done after playing around is to add a lookup field to the books table called student_lookup based on the genres. Then I created a filtered view of the books table with the student_lookup contains “the students name” and I get a narrowed down matching list that I can share the link to. Does that make sense?

Absolutely, and it’s a great solution that allows each student to peruse their personal list at their leisure. Depending on the number of students, though, making filtered views for each student can become cumbersome.

J_W:

I’d love to hear more of your thoughts on structure and semi-friendly email/print, because I do think that will happen.

There are a couple options. For a printed version, you could use Page Designer to collect and format details from those linked book records. The only issue there is the volume of info you’d collect depending on how many linked books appear for a given student, and how to cleanly format that using Page Designer’s available tools.

The other option involves creating a custom field on the [Books] table that aggregates specific info that you want to share about each book. This could be formatted very basically by inserting line breaks ("\n") between pieces, or if you envision sharing this via email or web, you could wrap HTML tags around things. Once you have things formatted at the book level, you’d then add a rollup field in the [Students] table to aggregate all this info from the linked books, using other separators, tags, etc to indicate divisions between books. The text/HTML in that rollup field could then be emailed to each student, saved to a file, or whatever you want to do with it.

[J_W]

Thank you for those suggestions. I didn’t know all that formatting could go on inside the rollup field. I’ll have to give some thought to the options and see what looks the most promising. One of these ideas should certainly fit my needs, and I’ll keep working on it.