Find records where linked record no longer matches...

[Brian_Hendel]

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!

[Justin_Barrett]

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]

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?

[Justin_Barrett]

@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]

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 😀

[DamonH]

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!