Looking up the latest value from a different table

[Luis_Novo]

Hi everyone, we are building a little CRM inside Airtable and I am trying to do something that seems to be almost impossible. Perhaps I am just not seeing it. So, would love some help to get this going:

Setup:
One table called Customers
Another table called “Health Score Reviews”

The first table is self-explanatory. The second one, has a series os “log entries” for every time someone from our Customer Success team interacts with a customer. In that interaction, they can add a SCORE to the interaction from 1 to 3.

Objective:
We want to show the LATEST Health Score for that customer in the Customers table.

The first part I was able to do, which was to retrieve the max(id) for the Customer Health entries and pul that number to the Customers table.

The problem is that I want to Lookup in another column what was the Score that was registered under that ID. I am trying to use a Lookup field.

The idea is to retrive just the latest Score. But it doesn’t work and it brings up all the Scores associated with that customer, not just the last one.

What am I doing wrong?

[Justin_Barrett · Accepted solution]

Welcome to the community, @Luis_Novo! :grinning_face_with_big_eyes: What you want is definitely doable, and you’re about halfway there. It just takes a little more back-and-forth between the tables.

Right now, you’ve got a rollup in your [Customers] table that pulls in the ID of the most recent review. Now go back to [Health Score Reviews] and add a lookup field that pulls that value back across using the customer link. That will take that most-recent ID for each customer and put it into every record for that customer. I’ll call that lookup field {Latest ID}

Add a formula field in [Health Score Reviews] that looks like this:

{Latest ID} = ID

That will put a 1 in each field where the latest ID matches that review’s actual ID (change the {ID} reference if that’s not the correct name of your ID field), and a 0 in the rest. I’ll call this field {Latest ID Match}.

Back in [Customers], add a lookup of the {Score} value, using a condition on the lookup to only show the score where {Latest ID Match} is 1. That will bring in the score value for that latest review. Every time a new review is done with a higher ID number, that field will update automatically with that newest score.

[Luis_Novo]

Thank you, works like a charm

[Justin_Barrett]

Glad to know that you got the answer you were seeking! If you would, please mark my comment (above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!