Support lookups and references/links within formulas

[Alex_Gunnarson]

First off, Airtable is great! One thing though: I’d really like to not have to create additional columns in order to simply do something like the following:

Formula:
if(lookup(row_10,"column_1"),lookup(row_12,"column_4"),lookup(row_53, "column_2"))

Also, I have a lot of varied data and I’m trying to use one big table for everything with lots of different views rather than use lots of different tables with aggregate tables and views based on those smaller tables. I can link to a row within the same table (awesome!) but I can’t do a lookup to a row within the same table (upsetting). This is a separate problem of course, but it would be sidestepped by supporting lookups within formulas.

Thank you!

[Jeremy_Oglesby]

A lookup in a formula wouldn’t really work as you have it laid out there (in the parlance of a spreadsheet).

The “rows” in Airtable don’t actually have a static spatial relationship to each other like a the rows in a spreadsheet do. They are made to look like they do on the grid view, but in the background, they are really more like floating records related to each other only in their belonging to the same table. You’ll notice that if you filter or sort differently, the row numbers still order in ascending fashion starting at 1 on top, down through the last record. In other words, what was “row 1” in one sort schema, is no longer “row 1” when you sort by another schema.

This happens because a grid view is really the graphical display of a search query being made against the database. If you have no filters, no sorts, and no groupings applied, it’s like asking the database, “Show me all records and all fields in this table”. But if you apply a filter, you are saying, “Show me all records in this table where this condition is true” - the first record returned (ie, “row 1”) may not be the first record returned when you asked for all records (no conditions). There is no static, spatial relationship going on there. In excel, the data in row 12, column A is always located there. If you hide rows, you see a gap in the numbers - the numbers don’t change. If you re-sort a table, you see the row numbers rearrange - the numbers don’t change. That’s what allows a lookup against a row/column in Excel to work - but this just won’t work in a relational database like Airtable, unfortunately.

[Alex_Gunnarson]

I appreciate your response! I understand they don’t have a static spatial relationship, and that’s one of the reasons I really like Airtable — the extremely flexible nature of the views. I think my example was misleading and unclear. When I wrote row_10, "column_1", etc., I wasn’t intending to refer to them in any sort of spatially-related ordinal way. By row_10 I meant that you might have an immutable autoinc ID column that that identifies a particular row as having e.g. ID 10. And by "column_1" I should have removed the ordinal and said something like "column_whatever" to better denote arbitrariness.

I realize what you’re saying about how static spatial relationships don’t apply here and definitely agree, but as a full-stack programmer myself, I do think that it’s eminently possible to implement a lookup function that looks up a row within the same table by autoinc ID (or even just by some arbitrary guaranteed-unique column value) and use the result in a non-circular formula.

[Jeremy_Oglesby]

In that case, I’ve got a feature request going already for more formula functions in general, one of them being the ability to perform a lookup from within a formula (albeit with slightly different structure than you are proposing). I’d appreciate your support on that feature request, and I’m going to add a link to your post as supporting evidence of user need.

The post is here:

New Formula Field Functions Product Suggestions

There are several requests floating around the forums related to the need/desire for more formula field functions - mostly related to arrays, linked records, or javascript like functionality. I’m going to create this #feature-requests post with some of the initial ones that I have posted myself, or have seen in the forums, and I will endeavor to keep it updated with new ones that I find or see posted; and, I will even endeavor to cross them off the list as Airtable implements them :wink: To ke…

[Alex_Gunnarson]

Sounds great Jeremy — thanks!