Do I need multiple bases or tables to achieve this?

[David_Jones2]

Hi everyone, I’m new to Airtable and I’ve exported a CSV list of our projects/customer information from Hubspot to create an overall project tracker. This works really well and I’ve added a new field for a total cost to this base, which I can then break down into a few categories eg pre-production, production, post-production, expenses etc.

What I’d like to do is adapt the Airtable film budget template to create detailed budgets for each project, but I can’t work out how I link this to the project tracker for more than one project.

Do I need to set up a completely new base for each budget or should I have one base with the film budget template and then keep duplicating the table when I start a new project?

We often have multiple projects on the go at the same time, or are pitching for new work where we need to create a cost estimate, so I could quite quickly end up with a lot of spaces/tables. At the moment we use a google sheet for each project but it’s very slow to manage all the information.

Thanks in advance for any help!

I forgot to post the link to the template - Film Budget Template - Free to Use | Airtable

[kuovonne]

Welcome to the Airtable community!

You do not need multiple bases. You also do not need a new table per project. You can create a table like the one in the Film Budget, and add one additional field: a linked record field that links to the project record in your [Projects] table. When you create a new project, also create the associated records in the [Budget] table, linking them to the project record.

[David_Jones2]

Hi kuovonne, thanks for your reply.

I’ve tried to do what you’ve suggested but maybe I’m doing it wrong. I now have two tables in one base, one with the list of projects and then one with the film budget with an extra field where I can pick a project from the first table.

It seems I can allocate individual line items in the budget to a project but what I am trying to do is add up the subtotals (ie the categories) and the grand total of everything and then send those totals back to fields in the project table.

I also then want to be able to save that budget for one project and have a fresh budget for a subsequent project that could have completely different line items, but still be able to go back to previous ones and perhaps adjust them if a project scope changes.

I hope I’m explaining that properly! Thanks again for your help.

[kuovonne]

That two table setup is correct. You want to use rollup fields to get the totals back into the projects table. You can use conditional rollups to get subtotals.

To get a fresh budget for a later project, just create new records in the [budgets] table and link them to the new project. Each project can have different line items in the budgets table.

You can learn more about linked record fields in this support article.

[Chris_Williams1]

Hi @David_Jones2,

I believe what you are trying to set up can be done with only 2 tables, maybe 1 is the project table, where global project info lives like total project cost and the other table is project “events”. You could create each event and link it to a project so it could roll-up the cost of the event into the total project cost. There are many things you can do with this type of format. Let me know if you have any questions, or would like someone to work with you on this project. I’m happy to help.

Chris