Skip to main content

Hi Airtable Community,

 

I have the following problem:

I have a Projects table with columns Assigned Budget 25, Assigned Budget 26 etc.

I also have a table “Orders” which is linked to the project table. This means that several purchase orders can be assigned to one project. Each purchase order is also linked to table 3 Financial budget.

So there are projects that can have several financial budgets. Each project can have different purchase orders which can also run on different financial budgets.

However, the projects are not directly linked to the financial budget.

Projects to Orders (1 to n)

Orders to financial budgets (1 to 1)

Now I would like to have the sum of all assigned budgets for each calendar year in the Financial budgets table. So I need to somehow get the values from the Projects table into the Assigned Budget table.

Any ideas?

Thanks & best

Mirko 

Hello Mirko!

If I understood your post correctly, I believe you could solve this issue by creating a rollup field in your project table. Use the financial budgets field from the orders table, and set the rollup function to SUM(values). This should give you the result you’re looking for!

Hope this helps!
Nina

Could you provide some screenshots of your tables and example output?

Hi @TheTimeSavingCo, 

in the projects table we have the projects an the assigned budget for each Financial Year.

In the Purchase order Table we have the informations regarding the POs and the link to the financial Budget for each PO. 

Test project 2 has budget assigned for YF25 and YF26. If a PO is linked to FY25, it is billed to this FY and the available budget for test project 2 in this financial year is reduced by the Order Value of this PO.

What I want now is to have the sum of the assigned budgets per FY in the third table (financial budget) to have an overview of all budget relevant figures. But the projects are not directly linked to it and in the PO table, the assigned budgets appear multiple times if a project has multiple POs.

 

 

Thank you & best

Mirko 

Ah I see!  Hmm, if you want to keep the data structure, then you'd need to link every Project record to a single record in the Financial Budgets table, and then create one rollup field per financial year to conditionally sum up the values:

---
I'm not too sure what the business logic is, but when using Airtable, the general rule of thumb is to try to add new records for dynamic data instead of new fields, and so you may also want to consider updating your data structure to use a table where each record represents a single project and financial year's budget instead:  




Reply


Terms & Conditions