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?

[Emmett_Nicholas]

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.

[Daniel_Saunders]

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

[Jeff_Solomon]

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?

[papparaziz]

Can somebody please help me understand how this works? This is my situation. Table A has a list of candidates and their scores on a test, Table B has the different categories a candidate falls under based on their score therefore table B is simply a 3 row table with two columns Status, Score Range. Under status I have Expert, Advanced, Intermediate and across each i have their ranges.
I want to assign the Expert value in Table A for a candidate if their score in Table A lies within the range set in Table B. How do i do this? Is there a hack?

[Justin_Barrett]

Based on your description, I don’t see the benefit of having Table B. I would recommend using a formula in Table A to look at the individual’s score and assign the appropriate status. Here’s a quick mockup with dummy ranges and field names:

IF(AND({Score}>=50, {Score}<70), "Intermediate",
    IF(AND({Score}>=70, {Score}<90), "Advanced",
    IF({Score}>=90, "Expert")))

[papparaziz]

The thing is though that we constantly change the ranges which is why I wanted to embed it as a formula instead of hard coding the values like you mentioned

[Justin_Barrett]

Are you saying that you want to set the ranges, have them apply to a certain set of records, then change the ranges later, but leave the original range applied to the original records, and only use the new range for new records? That won’t work. Whatever range values you use, whether they’re stored in another table or are hard-coded in a formula, they will apply to all records. Airtable doesn’t bake in formula calculations once they’re done. If some piece of data referenced by the formula changes (like your ranges), then all of the results will update, not just those in newer records.

If you do want your range tweaking to only affect newer records, you might be able to modify your Table B configuration to (long story short) time-stamp your modification of the ranges, apply similar time-stamping to each person’s record, then compare the time stamps to figure out which set of ranges to apply, but that would be a massive headache IMO.

[papparaziz]

My apologies I think I wasn’t clear the first time. We have one table where we are constantly redefining and changing the scores required to be at a certain level (Expert, Advanced etc.) for each section in a test, let’s say we have 6 sections. In Table A where I have the raw scores for each section by the candidate who took the test I want to assign these tags (Expert, Advanced etc.) for each section for that particular candidate. One way is as you mentioned hard-coding the thresholds as a formula field in Table A itself, but then the issue with this approach is that every time we change the thresholds, for all new records in Table A I will have to change the hard-coded IF statement. The historical assignment of the tags is not something I care about preserving.

[Jeremy_Oglesby]

In table B where you have your Level’s defined, you will want your “Score Range” value to be a number field, and set it to the LOW value for that range – the threshold that, when met, puts the test taker into that category (ie, it should be a single integer, not a range of values). To make that more clear, I would rename it to “Score Threshold”.

Pull each of those values into Table A with lookups – presumably you will have 3 fields each containing an integer value… for example:
{Intermediate Threshold} = 70
{Advanced Threshold} = 85
{Expert Threshold} = 100

Now, your formula that outputs the test taker’s level would look like this:

IF(
   {Score} >= {Expert Threshold},
   "Expert Label",
   IF(
      {Score} >= {Advanced Threshold},
      "Advanced Label",
      IF(
         {Score} >= {Intermediate Threshold}
         "Intermediate Label",
         "Default Label"
      )
   )
)

Now you can change your threshold values in Table B and the formula in Table A will already take those new values into account. BE AWARE that this setup will not preserve historical labels - when you change the thresholds in Table B, ALL records in Table A will update to re-label test takers with levels based on the new thresholds – but you said you didn’t care about that.

The formula will never accidentally under-label anyone because it always checks to see if they meet the highest threshold first, and if they do, it stops processing and gives them the label for that threshold; if they do not meet that threshold, it moves down to the next threshold, and so on, until it finds one the test taker meets, or else assigns the default label, if you have one defined.