What is the best base setup?

#1

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

#2

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

#3

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

#4

Which fields? It depends on the type of data.

#5

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

#6

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.

#7

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:

I would appreciate any help.

Thank you,
Mary

#8

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.

#9

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

#10

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.

#11

Hi Jeremy_Oglesby

Am I on the right track to create records, one after the other, (started this with “MURDOCH MYSTERIES”), in the episodes table, for all the TV shows. I wasn’t sure, unless I am missing something or do I include the episode names in the seasons table? Should it be listed one record after the other or in its own field across?

Thank you for your patience, as I make my way through this.

Mary

#12

Here’s how I would set it up, Mary:

#13

Hi Jeremy_Oglesby

Thank you very much for the base setup. I realize you are busy, so thank you for doing this. I now have a better idea of how the base looks like, since I am a visual person, it’s easier to see how the base should be set up.

Thank you,
Mary

#14

Hi Jeremy_Oglesby

I have had a chance to take a look at the base that you were kind enough to do. It’s great. I actually typed up a guide to show me, in text format, how the base is layed out, so I can see visually how it looks on paper.

I need to create another field: “AUTO #”, since I will be creating an integration with Integromat and I need a key field/unique I.D. number. I just wanted to get an idea how to do this. I am guessing that I would create it in the first table and link it to the other two tables, in sequence, but with what fields in each table do I link it to? Or do I create the same field in each table, linking all the same fields in all three tables? Or do I just set the field up in all three tables, as a stand alone field? I would like to chose the easiest way, that will accomplish the same result–a key field, in all tables.

Thank you for your help,
Mary

#15

Hi

Can someone take a look at reply #14, just above? I would be interested in how to do this.

Thank you,
Mary