Tracking budgets across multiple tables

Are you linking each record from the original table (events or whatever it is) to a single record in the master budget table? That’s what you’d need to do, and then you can perform a rollup within the master budget table to total everything from the other table.

Thank you for your response! I might be in a little over my head as I think I know how to link fields but not records. Are you able to explain more or link to a tutorial? I couldn’t find specific information on this when I searched.

After your suggestion and subsequent searching for tutorials I decided we can have all of our event information in one table if I sort and view by event. With this expenses can just be entered and updated in one table. However, I still think Rollup could be helpful for combining event expenses across offices, if you’re willing to help walk me through linking rows.

Link to how my table is set now:

Combining multiple tables into one table with multiple views

In general, you can think of a table as a list containing all the people, ideas, or items of a specific type. For example, a table could be a list of blog posts or recording artists on your record ...

I appreciate your help!
Whitney

Whitney_Mahoney wrote:

Thank you for your response! I might be in a little over my head as I think I know how to link fields but not records. Are you able to explain more or link to a tutorial? I couldn’t find specific information on this when I searched.

After your suggestion and subsequent searching for tutorials I decided we can have all of our event information in one table if I sort and view by event. With this expenses can just be entered and updated in one table. However, I still think Rollup could be helpful for combining event expenses across offices, if you’re willing to help walk me through linking rows.

Link to how my table is set now:

Combining multiple tables into one table with multiple views

In general, you can think of a table as a list containing all the people, ideas, or items of a specific type. For example, a table could be a list of blog posts or recording artists on your record ...

I appreciate your help!
Whitney

Yes, you definitely want to keep all event expense records in one table, and just have a field to record which office they are for.

I would have another table called “Offices”, where each record represents 1 office - and you can create any fields you want to to add information about an Office, like address, city, state, number of employees, etc.

Back in your Event Expenses table, the “Office” field will be of the type “Link to another recored”:

linking to the “Offices” table:

I’d suggest unchecking “Allow linking to multiple records”, because you always want an expense to belong to only one Office:

Now, when you record an expense, you select the office it belongs to by linking to an Office record:

Now create a rollup in the Offices table that SUM’s all expenses linked to that Office:

Jeremy!! You are the best! This worked, my table and expenses are now in order, and I now understand it so I can recreate it with other data. I’m so excited!

Thank you for all your help! I really appreciate it.

Whitney

May I jump into this thread with a similar question? I’ve set up my family’s annual budget in Airtable but am also having trouble linking information between tables. I’ve set up tables for each month and, within that, a single-select column to indicate budget category.

Then I have a yearly-expenses table in which I’ve manually totalled each category for each month. (I’ll add the screen shot to a second post since this forum doesn’t allow new users to post two images in one post.)

Is there a way to automate this process? I’d love for records (via linked/rollup/formula) to sum up the total expenses in a given category in a given month. And then I’d make a column at the end that averages the sums across the year.

Any help is greatly appreciated! I’ve been reading the forums and watching tutorials for days to no avail.

Thanks,
Brian

That second screenshot:

Hmmm…

@Brian_Sholis,

I think I’d make the same suggestion to you as I made to @Whitney_Mahoney – you’d do much better to keep all of your expenses in a single table. You are already using a date field, which will help to separate them by month, and you are marking them with categories.

So step 1 is to consolidate all of your expenses into a single table by copy-pasting all of them from the table they are in into the new, unified table (if this makes you nervous, do it on a copy of your base first).

Then, create a field called “Year” that has this formula in it:

IF(Date, YEAR(Date))

Create another field called “Month” that has this formula in it:

IF(Date, DATETIME_FORMAT(Date, 'M') & " " & DATETIME_FORMAT(Date, 'MMMM'))

Next, create a new Grid View - this view will serve as your analysis quick view to see spending by month and by category, so name it accordingly. Use the “Group” menu at the top, and Group first by the “Year” field 9->1, then by the “Month” field 1-9, then by the “Category” field.

What you will see happen is your records will fall under nested groups, like I have mine here:

Your top level group will be the Year, so all your 2018 expenses will be grouped together, and under the year, they will be grouped by Month, and then under that, grouped by category. You will be able to see summaries in the summary bar for each grouping, in nested fashion. In my screenshot, I’m using the Standard Deviation, Average, and Histogram summary functions, but there is a Sum function you can use which will sum up all of your expense Costs first per category within a month (total spending in “Household Items” for “November” of 2018), then per Month within a year (total spending in all “Categories” for “November” of 2018), and then per Year (total spending in all “Categories” for “All Months” of 2018).

You could create another view in the same table that groups by Year and then by Category (skipping Month in-between), which would allow you to see total spending for “Household Items” for all of 2018.

The next step is to relate Income and Expenses in some way, and I think I’d suggest doing this with a join table. So, again, the first step will be to consolidate all Income into a single table, and use a Date field to distinguish them – create the same “Year” and “Month” formula fields there if you’d like to see them Grouped by those periods in the Income table.

I’m going to describe the simplest join table setup, and if you want to expand on it later, you can. The simplest setup is to create an all-to-one relationship between both your Income records and your Expenses records to the join table. Let’s call this third table “Summary”. It will have, literally, only 1 record in it. You can name that record whatever you like – I usually just use a dash “-” or some emoji like “ :moneybag: ” – and then you will create a “Link to record” field in both your Expenses table and your Income table that links to the “Summary” table. Back in your “Summary” table, you’ll see corresponding link fields named “Expenses” and “Income”. In your Expenses and Income tables, link every single record to the 1 record you have in your Summary table (this can be done quickly by linking the top record and then dragging the handle to auto-fill the rest).

Back in your Summary table, create a Rollup field that points at the “Income” table, the field that has the income dollar amount, and uses the SUM(values) rollup function.

Then create another Rollup field that points at the “Expenses” table, the “Cost” field, and uses the SUM(values) rollup function.

Now you’ve got your total Income summed up, and your total Expenses summed up – you can create a formula field that uses {Income} - {Expenses} to get your current balance.

IF(Date, YEAR(Date))

IF(Date, DATETIME_FORMAT(Date, 'M') & " " & DATETIME_FORMAT(Date, 'MMMM'))

Thank you. I will set this up for 2019 and see how it works!

Best,
Brian

@Jeremy_Oglesby
wow thanks! that “Month” formula was something I was looking for and it worked great!

Now I’m curious about your Z-Score and Rating columns, how did you get that Hist bar graph?

Those are options in the summary bar that Airtable provides:

If you are using “Grouping” to group your records, there will be a summary bar at the top of each Group, and you can choose a summary formula for each field.

If you are not using “Grouping” the summary bar is at the very bottom:

:joy: now I feel dumb! haha
thanks so much @Jeremy_Oglesby , I don’t know why I hadn’t noticed that before!

Wow I haven’t thought about Z scores in years. Now I want to add it to everything.

I had to create my own Standard Deviation formula for use in Airtable since Airtable doesn’t implement a native STDEV function:

Might come in handy if you plan to implement Z-Scores

Thank you so much for sharing this!