Linking one to many with rollup

Topic Labels: Base design
1352 6
Showing results for 
Search instead for 
Did you mean: 
7 - App Architect
7 - App Architect

I am not sure why I can’t get my head around something that I think is easy but:

I have a table of Videos that we air on a cable channel. One of the fields in the table (not the primary field) is a code# the station gives us. Each month, they send me an Excel list of the Code and number of airs that month. It does not include other fields. I can add the Code number to the Videos table but am lost after that.

I need to copy and paste the Code# and # of airs into Airtable and then have a rollup showing the total number of airs per video for the life of the video. I know I need a linked table but for the life of me I can’t figure out how to do this because it is linking the Video Title rather than the Code #.

6 Replies 6

Hey Brite,

Here’s a quick guide:

Video Table:

  1. Name of Video [Short Text]
  2. Code [Link To Code on another Table]
  3. Lifetime airs [Rollup with sum aggregation]

Screenshot 2020-01-25 at 11.45.42

Play Count Table:

  1. Code [Short Text]
  2. Videos [Link to Videos table]
  3. Play Date [Date or Text]
  4. Plays [Number]

Screenshot 2020-01-25 at 11.47.16

Hope that helps!

7 - App Architect
7 - App Architect

Thanks for your help, Saastronomical. That is what I did but I’m obviously missing a step. See my screenshots. When I paste in the Code # and # of Airs, it is not pulling in the Name of the Youth project (the Video) so the rollup field in the other table is not including them in the Sum. If I manually add the name of the video to the Youth project field in the Video count Table, then it does include them in the Rollup sum but Since that video name does not come in the monthly data, it is cumbersome to have to look each one up and manually enter them. I’m confused wht it doesn’t come it automatically based on the Code/

Video table

What am I missing? I appreciate your help!

I think you might need to create an automation to get this to pull in automatically - A zap that waits for a new record and assigns a project based on the ID should do it.

Thanks. I’m surprised it doesn’t somehow look up the Code# and auto-populate the Video name so the rollup works. Maybe someone has a clever way to make it happen without zapier.

I appreciate your help.

So it would work as a lookup, but you first need the link to to be populated. If you have that, all your lookups will work

Having a think about this @Brite_Admin, I think the best way to do it without automation is as follows:

Screenshot 2020-01-25 at 22.49.32

Per the above, don’t make the first Field the code - make it a formula. This way you can paste in the code, and Videos being a lookup, will automatically populate, and you can see the results in another table, which is effectively the master table:

Screenshot 2020-01-25 at 22.49.39

I think this should solve your problem if all you want to do is paste a list of codes, dates and hours played.