Formula with fields from different tables


#1

Is it possible to use information from a linked table in a formula?

I’ve got an Airtable recording books that I read. In one table I have start and end dates for a book, and the details of the Book (author, number of pages) are in a second table. For some nerdy statistics, I wouldn’t mind being able to create a formula like (End Data - Start Date)/Number of Pages in Book…where the dates are in one table, and the number of pages in another.

Is this possible currently in Airtable?


#2

Yes! (With the help of Lookup fields.)

In the table that links to Books, you can create Lookup fields corresponding to Start Date, End Date, etc. Then, you can reference the Lookup field values in your formula.


#3

Thanks, that works perfectly. Especially after I found the DATETIME_DIFF() function.


#4

This was helpful; but in my table I’m looking up records to add to my primary view table, it’s a 1 to many relationship. So when I do the look field first, It’s possible to get 2 or more values. Which is displayed fine. But then when I go to create a formula to combine those looked up fields (as your comments suggest above) it only works in cases where there is 1 record in the lookup table. If 2 or more, then it throws an #ERROR! result.

To better understand my use case:

I have a table of PROPERTY RENTS, that feeds data to my PROPERTIES TABLE. So there is an entry in the first table for each year: {id1}, {year} = 2017, {rent} = $250,000; {id2}, {year} = 2018, {rent} = $300,000; and so on.

I want to display in a single field in the PROPERTIES table all years and corresponding amounts. So it would look like this:

2017 - $250,000, 2018 - $300,000 and so on.

Ideas?