Find oldest/newest linked record

Hey there,

Here’s the situation:

Table 1: Customers
Table 2: Orders

Both are linked via a customer ID. I’d now like to show the date of the first order and the date of the last order in two columns on the customers table. Is there any chance to do that? I do have a date field on the orders table of course.


Frank -

I’m not sure what you mean by ‘both are linked via a Customer ID’; if you’re linking them through a join table, the process here will need to be modified.

Assuming there’s a field in ‘customers’ that’s a linked record to ‘orders’, though, you can create two rollup fields in the ‘customer’ table, ‘first order’ and ‘latest order’. Configure them to rollup values from the ‘orders’ table using the ‘order date’ field based on the ‘MIN(values)’ and ‘MAX(values)’ rollup formulas, respectively.

You can format the field (using the format tab) to match the desired style.

@Helmi think these items from @Zoelle_Egner & @Matt_Bush may help you get there

Actually, the two procedures to which you linked are overkill for what Frank appears to want to do. For his purposes, simple roll-up fields should do the trick. Take a look at the last two columns in this base, {First Order} and {Latest Order}. (The other columns illustrate the more-involved construction explained in your linked posts.)