Combine select records from multiple tables into one list?

I’m starting to use more features in Airtable and I’ve run into something I can’t figure out. Can I have a table that lists records from multiple other tables if the record has a certain classification?

I’ve been reading about links and relationships but maybe I’m missing something.

Example: I have 3 tables in a base that each list songs by artists I like. Each table has a column with a single-select dropdown list if I want to buy a specific track. Can I make a 4th table that will pull all the records into one list from these three artist tables if the record has a “want to buy” label?

From reading other posts it sounds like I might have to have all the artists and songs in one table and then filter by “want to buy.” (Not my preferred solution but would consider it)

Easiest solution is to use a linked-record field rather than a single-select field for your ‘Want to buy’ classification. That is, define a second table called, oh, [Status]. [Status] might have only a single record, “Want to buy” — or it might have other records if you have other statuses: “Never listen to this again”, “OK for parties”, whatever. When you want to buy a track, link that track to the “Want to buy” record in the [Status]. table. Once that’s done,.you can go to [Status] and display all records linked to the “Want to buy” record, regardless of which table they come from.

But…

You don’t explain why you have three tables containing songs by artists you like. To me, saying you have three functionally similar tables suggests you have two tables too many. Now, I can understand a number of scenarios where this would be a valid arrangement, so it could be a valid structure. But if you’re creating a new table for each artist or album, I’d recommend reformatting the base.

For instance, you could create tables for [Artist], [Album], and [Track]. Each [Artist] table might link to one or more [Album] records; each [Album] record would link to one or more [Tracks]. In that case, using a “Want to Buy” drop down makes sense – and makes creating a list of tracks to go out and buy far simpler than in my original approach. However, since I don’t know the structure of the rest of your base, I can’t tell you which design would be best…

1 Like

Thanks for the input! I have three similar tables because that’s just what I started with. I thought one table for tracks by Artist 1, another for Artist 2, etc. because over time I’d end up with a massive list. I know I can filter that list for specific features, like if I have artist and album names for each track, but I was concerned it would get unwieldy.

Initially I just wanted a list of artists and the date I last looked for their releases. Then it grew into a larger idea and I wanted a way to list future purchases. So I started reading about linking so I could have one table with artist info and additional tables with track information. This hasn’t panned out as expected.

Here’s a quick implementation of the base structure I mentioned. It may be completely inappropriate for your use, but it follows the Artist>Album>Track hierarchy I described. Note the <Buy These Tracks> view in the [Track] table. :wink:

(That’s a read-only share with copying allowed; duplicate it to your workspace to be able to examine and modify the fields.)

(Music from the always-wonderful Philip B. Price can be found here. :wink: )

Wow, you’ve gone above and beyond my friend! Thank you so much for the demo. I’ll definitely be exploring this more and thinking over the best way to proceed.