Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Get last/newest linked record

7957 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Brian_Hendel
6 - Interface Innovator
6 - Interface Innovator

Let’s say I have two tables, orders and products with a link between each. On my products table, I can easily see -all- orders involving that product, but what if I have a requirement to show only the latest order, clickable just like the order entries in the “Orders” column of the products table. I know I can use a lookup field (in the products table) to pull the output of one of the fields from the linked order, and filter that by date, but I need this clickable for easy reference. and editing?

Is there any solution? Thank you!

5 Replies 5

For the item to be clickable and editable in-place, it would need to either be a linked record, or a lookup of a linked record, but unfortunately it’s not possible to take a collection of linked records—direct or from a lookup field—whittle them down to one, and keep the link behavior intact in the process.

What is doable, though, is to make a button field that will directly open the most recent order record for a given product. The only thing to be aware of is that this method will switch the active table and view to the one containing the target record; e.g. the [Orders] table and whatever view you choose during setup (more on that later). It won’t just pop the record open on top of the [Products] table. That particular behavior is restricted to actual linked records or lookups of linked records.

Unfortunately it’s late where I am and I have to teach a class in the morning, but I’ll return sometime tomorrow and outline the details of how to pull this off.

Brian_Hendel
6 - Interface Innovator
6 - Interface Innovator

I figured, thank you for digging in. Makes sense. Creating a button to do this requires use of AT scripting? Or just at the function level?

@Brian_Hendel It’s all done at the function level. No scripting required.

Step 1: Find the time of each product’s latest order

Finding the latest order will require some precision. If you have a field to capture the order date, that won’t be sufficient unless it also captures the time. Change that date field’s properties to add the time if it’s not already set; if you don’t yet have such a field, then a “Created time” field will suffice. Whichever option you use, I’ll call this field {Created} going forward.

In the [Products] table, add a rollup field named {Latest}, using the incoming record links from the [Orders] table, pulling the value of the {Created} field, and using MAX(values) as the aggregation formula. That will bring in the time of the latest order.

Step 2: Mark that latest order using its record ID

In the [Orders] table, add a rollup field named {Latest Record ID}, using the links to the [Products] table, pulling in the value of the {Latest} field, and using this aggregation formula:

IF(FIND(Created & "", ARRAYJOIN(values)), RECORD_ID())

That will turn the order’s created time into a string, look for it in an array of similar date strings from all linked product records (the ARRAYJOIN() function will turn each retrieved {Latest} datetime value into a string), and output the order record’s ID if found. All order records that are not the latest for any linked product will remain empty.

Step 3: Bring the latest order’s ID into the product record

In the [Products] table, add a rollup field also named {Latest Record ID}, using the order links, pulling the value of the {Latest Record ID} field from those orders, and using values & "" as the aggregation formula. This should give you a single record ID from the latest order of each product.

Step 4: Make a button to open that latest record

For this final step, you’ll need to get the URL of the table and view for your [Orders] table. This can be seen in your browser URL when you have the [Orders] table open. The full URL will probably look like this:

https://airtable.com/tblxxxxxxxxxxxxxx/viwyyyyyyyyyyyyyy?blocks=hide

Copy everything in that URL up to (but not including) the question mark.

In your [Products] table, make a button field named {Edit Latest}. Set it to open a URL, with the URL formula as follows, inserting your copied URL to replace my sample, and making sure to add a single forward slash after it inside the string:

IF({Latest Record ID}, "https://airtable.com/tblxxxxxxxxxxxxxx/viwyyyyyyyyyyyyyy/" & {Latest Record ID})

After you save the field, clicking on the button for a given product will switch to the [Orders] table and open the expanded view of the order record tied to that product’s latest order.


NOTE: This system will only match order creation down to the second because Airtable doesn’t track record creation times with more precision than that. With that in mind, there’s a very slim chance that multiple orders made at the same second that include a given product will mess up the system by returning multiple record IDs. I wasn’t able to find a way around this, so I’m hoping this won’t be an issue for you.

Shuyen
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey @Justin_Barrett just wanted to thank you for your helpful post. It was easy to follow and totally worked for my own context! 

Even though I ended up ditching the use of it in my app, I still wanted to express some gratitude 😀

This is awesome, thank you for sharing! Solving several issues and helping me learn how to handle records with ID a bit deeper than had known before. Thanks!