Aug 18, 2021 04:22 PM
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!
Aug 24, 2021 11:41 PM
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.
Aug 25, 2021 01:02 PM
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?
Aug 28, 2021 11:38 AM
@Brian_Hendel It’s all done at the function level. No scripting required.
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.
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.
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.
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.
Jan 14, 2023 07:06 PM - edited Jan 14, 2023 07:06 PM
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 😀
Mar 12, 2023 08:47 PM
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!