Formula with fields from different tables

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.

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

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?

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?

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")))

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

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

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.

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.

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.

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:

“Likes” are good too :slightly_smiling_face:

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.

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.

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

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!