Formula with fields from different tables

[Daniel_Saunders]

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?

[papparaziz]

Awesome, I think with what @Jeremy_Oglesby and @Justin_Barrett have said this will help solve my issue. Thank you very much both of you for your help!! Wish this was like Reddit where I could give you gold coins :slightly_smiling_face:

[Jeremy_Oglesby]

“Likes” are good too :slightly_smiling_face:

[Jeremy_Oglesby]

Also, if you ever decide you need to preserve historical level labels and apply new thresholds only to future test takers, I have a solution for that as well that requires only a slight modification to the setup.

But even with your current setup, since you have to link every record from Table A to all 3 levels in Table B in order for this to work, I’d suggest flipping your Table B to run horizontally rather than vertically…

Here’s what I mean -

Instead of having 3 rows in Table B, one for each “Level” you want to define, I’d have 1 row in Table B, called “Levels”. Then, I’d have an integer COLUMN for each Level - “Intermediate Threshold Score”, “Advanced Threshold Score”, and “Expert Threshold Score”.

Then you only have to link each record in Table A to the one “Levels” record from Table B. That will work much better with what I described above.

… actually🤔… what I described above REQUIRES the setup I just described – a single record (row) in table B that ALL records in Table A are linked to. The link has to be established in order for the Lookup fields to be able to pull in your threshold values for each level.

[papparaziz]

Yes that is what I inadvertently wound up doing, but thank you for validating that approach, much appreciated! I over simplified my task here for the sake of simplicity in the discussion but I have 6 sections and 5 levels for each section for a total of 5^6 possible combinations which I can now do with the ideas you set forth.

[adelle_lin]

Hi, I’m looking to do something similar but I don’t quite understand how you pull values from table B to table A. I made a table B with some values called weights. But when I make a similar field in table A and try to make it a lookup, I don’t see table B as an option. I’m more specifically asking how to link the tables, since table B has some standalone columns that don’t relate to table A

Can you describe or show screenshots of how to link and lookup between these 2 tables? thanks!