Linking one to many with rollup

[Brite_Admin]

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 #.

[andywingrave]

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]

Play Count Table:

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

Hope that helps!

[Brite_Admin]

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/

.

What am I missing? I appreciate your help!

[andywingrave]

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.

[Brite_Admin]

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.

[andywingrave]

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

[andywingrave]

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

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:

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