Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formulas to pull values from a different table

Topic Labels: Formulas
8384 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

3 Replies 3

Welcome to the community, @Emma_Sousa! :grinning_face_with_big_eyes: 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:

Screen Shot 2019-12-19 at 4.53.12 PM

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

Screen Shot 2019-12-19 at 4.54.31 PM

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

Screen Shot 2019-12-19 at 4.57.33 PM

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:

Screen Shot 2019-12-19 at 4.59.11 PM

Instead of a formula, you can use both the Linked Records and Lookup Table fields. This will link a table to another table, and the lookup field can reference specific data from the linked table. From there you can start referencing the column data via formulas.

If someone else is looking for a way to do this here is the solution I found.

You should create another view of your data. Airtable creates a default "Grid view" for your new table. If you add another view it can act like another table with filtered or grouped parameters. The views all share the same records.

Let me know if that helps!