Help

Looking up the latest value from a different table

Topic Labels: Formulas
Solved
Jump to Solution
1873 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Luis_Novo
4 - Data Explorer
4 - Data Explorer

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.

Screen Shot 2020-07-23 at 07.39.54

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.

Screen Shot 2020-07-23 at 07.41.00

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.

Screen Shot 2020-07-23 at 07.42.20

What am I doing wrong?

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

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.

See Solution in Thread

3 Replies 3
Justin_Barrett
18 - Pluto
18 - Pluto

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.

Thank you, works like a charm

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!