Formulas to pull values from a different table

Hi All

I’m creating a budget database and need some help trying to figure out how to create a formula in one table that pulls values from another (not even sure I can do this).

I have 3 tables:

  1. Income table where horizontally the months are listed with a value and two wage types are listed vertically and it is auto-summing at the bottom of the table. I need the combined monthly income to pull into

  2. Bill table where each month is listed horizontally and each bill vertically…and the amount of each bill for a given month is listed.

  3. Budget to Income table…here’s where it gets messy for me.

I need the combined income from the Income table for each month to drop to the appropriate month field…

I need the combined value of each month from the Bill table to drop to an expense line by month

Is there a way to do this?

Emma

Welcome to the community, @Emma_Sousa! :smiley: I just replied to another post of yours, and I’m guessing this one came first. Based on the suggestions I provide here, the advice I gave in the other thread might not apply.

First off, you asked about creating a formula in one table that pulls values from another. Sadly, this isn’t possible. An Airtable formula field can only reference data in the table where it lives, and even more precisely, only the data in the same record. In other words, you can’t arbitrarily reference any other field in any other record, even within the same table. Database records, unlike spreadsheet rows, have no inherent knowledge of other records. Yeah, there are some hacky ways that can sometimes work to get around this, but even they have limitations.

Anyway, that aside, the main thing I recommend is redesigning your base. First, make a [Summary] table where the primary field contains a combo of the month and year. I used separate {Month} and {Year} fields for the initial data entry, then used this formula in the {Name} field:

Month & " " & Year

Screen Shot 2019-12-19 at 4.44.40 PM

Next you’ll have your [Income] table. Instead of separate columns (fields) for each month, you’ll use a link field pointing to the [Summary] table to pick the relevant month for each income item:

Screen Shot 2019-12-19 at 4.51.32 PM

The [Bills] table would be set up the same way:

Screen Shot 2019-12-19 at 4.52.15 PM

Now the Nov 2019 and Dec 2019 records in [Summary] have incoming links:

In that table, add a {Total Income} rollup field that sums the values from those linked items, and set its formatting to Currency:

This gives you a summary of all income, split by month:

Make another rollup named {Total Bills} that does the same with the links in the {Bills} field, then add a formula field that subtracts the difference between the two rollup fields, which will give you this: