Linking Setup Question

[M_k]

Hi

I am thinking of setting up a base, but I am not sure what’s the best way. I want to create a base that will have about 25 different TV shows (having the same number of tables will be too many tables in one base). Then I want to have fields for: TV Show Name, Season, Episode Number, Air Date and Name Of Episode. These Field Names will remain the same, just the data entered will be different and I will be entering data on a weekly basis, that’s why I was thinking of using Integromat.

Views might be a little tricky with Integromat, so I don’t want to use them.

Any help would really help.

Thank you,
Mary

[Elias_Gomez_Sai]

You need 2 tables, one for Tv Shows and another for Episodes, both related by a Linked Record field.

[M_k]

Hi Elias_Gomez_Sainz

Thank you. This makes perfect sense.

Mary

PS I just realized that there will be five fields for each TV show, will this still work?

I am trying to visualize how this will look.

Thank you,
Mary

[Elias_Gomez_Sai]

M_k:

there will be five fields for each TV show

Which fields? It depends on the type of data.

[M_k]

Hi Elias_Gomez_Sainz

The fields will be:

Number of Records (Auto #)
TV Show Name (Long Text)
Season (Multiple select)
Episode Number (Multiple select)
Air Date (Date Field and Formula)
Name Of Episode (Long Text)
Notes (Long Text)
Time Created (Time Stamp)

I am no sure what records/fields to link.

Thank you,
Mary

[Elias_Gomez_Sai]

You have 2 entities: TV Shows and Episodes, each with properties, and a relation between them (an Episode belong to a TVshow, or a TVshow has Episodes, as you want), so you create a Linked Record field in one of the tables (it will be reflected in the other one). You have a relation between entities, so you link Tables, not Fields.

[M_k]

Hi

I wondered if someone can take a look at the base that I have created, so far, for TV Shows.

I am not sure if I mapped out the base correctly. I will also be linking each episode with a single TV show.

Each TV show will have 10 to 25 episodes, I was not sure if I just enter each of the episodes, one after the other (started this with “MURDOCH MYSTERIES”).

I am having a hard time visualizing how the base should look and I don’t want 25 tables (that’s how many shows there will be), I am trying to think of the best way to set this up.

Here is the link to a copy of the base, so far:

Airtable

PRIMARY FIELD TV SHOWS copy - Airtable

Explore the "PRIMARY FIELD TV SHOWS copy" base on Airtable.

I would appreciate any help.

Thank you,
Mary

[Jeremy_Oglesby]

Hi @M_k,

I took a quick look, and I’d suggest adding just one more table - SEASONS.

So each TV SHOW will have 1 or more SEASONS.
Each SEASON will have 1 or more EPISODES.

An EPISODE will belong to a TV SHOW through a SEASON.

You workflow will be:

1. Create a TV SHOW
2. In the TV SHOW table, create a new SEASON in the Linked record field linking to SEASONS
3. Ideally, have a “Season #” field in your SEASONS table, and then use a formula in the Primary field so that the name of the SEASON record is “TV SHOW Name - S#”, ex. - “Magnum P.I. - S1”, “Magnum P.I. - S2”
4. Whenever you create a new EPISODE in the EPISODES table, link it to a SEASON, and when you link it to a SEASON it will already be linked to its appropriate TV SHOW via the SEASON (this link to SEASON field will replace your Single Select field for Season)

The key thing you are missing, though, is LINKED RECORDS. Create “Link to another record” fields that link a TV SHOW to a SEASON and an EPISODE to a TV SHOW-SEASON.

It doesn’t look like you are using linked records at all in your current set up.

[M_k]

Hi Jeremy_Oglesby

Thanks for your reply.

Yes, l got stuck with how to link the records, so that will be the next step.

I will read through your post. Although, I noticed that you mentioned a formula in the primary field. How would this be written?

Thank you so much for your help.

Mary

[Jeremy_Oglesby]

Once you create your link between the TV SHOWS table and the SEASONS table, there will be a field called “TV SHOWS” in your SEASONS table.

I assume you will create a number field where you will enter the Season number, so if you called this field “Season #”, your formula would look like this:

{TV SHOWS} & " - " & {Season #}

You’ll have to adjust that to match your field names exactly.
It’s just a text concatenation formula. It takes the text in the {TV SHOWS} field, uses the & operator to join it with space, dash, space (the " - " you see in the formula), and then uses the & operator again to join that with the number you put in the {Season #} field.