Find oldest/newest linked record

[Helmi]

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.

Thanks,
Frank

[W_Vann_Hall]

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.

[Arlo_Haskell]

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

Most Recent Date from another table

Hi Arlo! There's a way to do this, but it's a bit convoluted, so bear with me :slightly_smiling_face: 1) Use the MAX(values) trick described above to pull the most recent transaction date into your clients table. 2) Create a lookup field in your transactions table that looks up the most recent date you just calculated in the client table. 3) Create a formula field in your transactions table that compares the most recent transaction date that you just looked up to the date of this specific transaction.…

Embedding other data in 'most-recent' date

@W_Vann_Hall nice hack! It is in fact possible to set this up using a few intermediate lookup and rollup fields: On the item table, add a Rollup of the date field from the outRecord table (which let’s call “Checkout date”), with the formula MAX(values). Let’s call it “Most Recent Checkout”. On the outRecord table, add a Lookup of the “Most Recent Checkout” field from the item table. On the outRecord table, create a Formula field IF({Checkout date} = {Most Recent Checkout}, Status, ‘’), call…

[W_Vann_Hall]

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

Thanks,
Vann