Lookup last linked entry

[Helmi]

Hey everyone,

I’m fighting with a thing that I wanna do with airtable:

Table ‘customers’ holds all my customers information
Table ‘orders’ holds all orders with all the order information
Table ‘catalogues’ holds all catalogue orders (as they are vastly different from orders) with all catalogue infos.

What I want is to have a column on the customers table that shows the last catalogue ordered by it’s catalogue name. This is for example “Winter 2018” and is kept in a select field in the catalogues table for each catalogue order entry.

I already have an auto number on the catalogues table and are showing the highest ID on the customers data row but I can’t seem to map a catalogue name to that. So what I need is to lookup that catalogue name from the catalogues table.

I am already showing things like last order but it’s way simpler there as I can use the data and use a max() rollup on that. That’s not possible with the catalogue names as they’re not numbers.

Any idea on how to do that?

Thanks.

[AlliAlosa]

Hi there! Let’s assume your field on the [Customers] table that contains the highest ID is named {Most Recent ID}. I’m also assuming you have a lookup field on your [Orders] table that pulls the {Catalogue Name} from the [Catalogues] table.

I would add a rollup field to your [Orders] table, pointed at {Most Recent ID} on the [Customers] table, and use the following formula:

IF(MAX(values) = {ID}, {Catalogue Name})

This should give you the name of the most recent catalogue. Let’s say you call it {Most Recent Catalogue}.

Finally, add a lookup field to your [Customers] table and point it at the {Most Recent Catalogue} field on the [Orders] table.

The following reply offers another approach to this, using dates instead of auto numbers.

Display only the Last Updated Modified Value Formulas

Are the guests (F1, F2, etc.) listed on another table? If so, you can use a conditional rollup to accomplish this. I would make sure the entries submitted by form are timestamped with a CREATED_TIME() field. Let’s say it’s called {Date Submitted}. Next, I would add a rollup field to your guests table, pointed at the {Date Submitted} field on the form entries table, call it something like {Most Recent Submission}, and use the formula function: MAX(values) Next, add a lookup field that pulls {M…

I hope this helps!

[Helmi]

Hey @AlliAlosa ,

sorry for my late reaction - I was busy with some other stuff and only now came back to this. I’m not sure if I can follow your advise. Let’s cut out the orders table for the moment as this isn’t really involved in the example.

So I do have the customers table and the catalogue orders table. Each row of the catalogue orders has an ID (auto increment so easy in terms of the max values) and (besides others) a field called “Katalogpaket” which is used to hold the catalogue name - a select field with all the existing catalogue names in there. Also the catalogue order table rows are linked to customers in the customer table.

What I do already have is a rollup field on the customers table that pulls the highest ID using max(values) from the catalogue order table. So the only thing I’m missing is actually pulling the catalogue name too and show that.

So what I tried is using

IF(MAX(values) = {ID}, {Katalogpaket})

similar to your suggestion but that doesn’t seem to work.All I get is

Sorry, there was a problem saving this field. Unknown field names: id, katalogpaket

I’m not sure about the syntax and where to use the formula given though so I guess I just didn’t get behind your advise yet. I think I probably confused you with orders and catalogues table. So it’s really only one table and each row contains the select field with the catalogue selection. That’s probably why the formula fails.

I hope that makes sense…

[AlliAlosa]

Hi there @Helmi! Apologies for the confusion.

In order for this to work, you have to take the other tables into consideration. You’re getting the error:

Helmi:

Sorry, there was a problem saving this field. Unknown field names: id, katalogpaket

Because {ID} and {Katalogpaket} are on the [Orders] table, and you’re trying to reference them on the [Customers] table.

What I was trying to describe, is a way to dive across your three tables to get the values you want to appear in the [Customers] table. It is hard to wrap your head around and hard to describe as well (I’m certainly still learning the best ways to explain things :winking_face: )

I put together this sample base - maybe it will help clear things up. Click the link and then click the button in the top right that says “Copy Base” - this will allow you to see the formulas and linking relationships.

Airtable

Catalogues (Sample Base) - Airtable

Explore the "Catalogues (Sample Base)" base on Airtable.

1. The [Catalogues] table contains the auto-number field {ID}, and the single select field {Katalogpaket}.

2. The [Orders] table looks up {ID} and {Katalogpaket} from the [Catalogues] table.

3. The [Customers] table contains a roll-up to yield the highest ID in the field {MAX ID}

4. The [Orders] table contains the rollup that you were struggling with in the field {Most Recent Catalogue}. The formula is…

   IF(MAX(values) = {ID}, {Katalogpaket})

5. Finally, the [Customers] table looks up {Most Recent Catalogue} to show the corresponding {Katalogpaket} on the [Customers] table.

I hope this helps to clear things up! Don’t hesitate to reach out if you have any questions.

[Helmi]

Thanks for helping again. I‘m only on my phone now so only can check your example later but I think our man confusion really is the order table that does not exist. It was my error to put it in the initial question as it is not used for this problem.

It‘s really just the catalogue select field on the catalogue orders table and this select is not filled from another table. It‘s just a select field.

So my basic problem is that I need to fetch the selected value from that field.

My customers table already has the highest catalogue order ID from the catalogue order table but I need to fetch this other field to get the selected name.

Again thank you for your patience.

[Shaun_Hornsby]

@AlliAlosa , I am trying to follow your sample table and directions here but i am seeing an issue. If you look at your “orders” table, You will see that the Most Recent Purchase for Customer Number 1 is actually “1 - Winter 2018” but because your using the “Max Value” for the catalog number it shows on the “Customers” tab that the Most Recent Purchase is “3 - Summer 2018”. This is because it is using the “Max Value” of the Catalog numbers instead of the Mx Value of the Orders themselves.

I am trying to find a way to do this for my issue but i have not come across something that actually works. Can you take another look at this and let me know if you find a way to get this working?

TIA

[Shaun_Hornsby]

@AlliAlosa I am trying to follow your sample table and directions here but i am seeing an issue. If you look at your Orders table, You will see that the Most Recent Purchase for Customer Number 1 is actually 1 - Winter 2018 but because your using the Max Value for the catalog number it shows on the Customers tab that the Most Recent Purchase is 3 - Summer 2018. This is because it is using the Max Value of the Catalog numbers instead of the Max Value of the Orders themselves.

I am trying to find a way to do this for my issue but i have not come across something that actually works. Can you take another look at this and let me know if you find a way to get this working?

TIA