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