Last Time a Product Type Was Ordered

[Ben_H]

I have tables “customers” “orders” and “products”

Products has a multiple choice for product type.

Orders is linked to the customer and the product with a date.

I am looking for a solution to find the last time a product was ordered by a client in an overview/ filter/ group.

Example:

Product Type A
“customer” “product name” “order id” “order date”
“customer” “product name” “order id” “order date”
Product Type B
“customer” “product name” “order id” “order date”

I would love to hear some ideas or if you know anywhere else in the form where it is discussed.

Thanks in advance
BH

[Mohamed_Swella1]

Hi @Ben.H,

Welcome to Airtable Community! :slightly_smiling_face:

I assume you have in the order multiple products? Are you making several records for each order representing each product?

What you need is a rollup field with the argument Max() for the date, you can also make a formula to concatenate the info into one array. But I need to see screenshots to be able to help you further.

BR,
Mo

[Ben_H]

Thanks for the quick reply Mo!

What I don’t understand is how to correlate the different columns. Filter by customer, date and product. Except they are all on different sheets. Can you do IF(customer, product type) then max date?

I just want to know the last time I sent a client shampoo :slightly_smiling_face:

[Mohamed_Swella1]

Hi @Ben.H,

It would be easier to do so in the Product table with a Group By view that groups with Product Type and Client, then use the Sort option to get the Max date on top. This way you will have a view divided by product type, then inside each product type you will see a group for each customer with all their orders then you will see the date of the last order on the very top.

Let me know if that works for you :slightly_smiling_face:

If it does, please mark this as solution so others can see it easily.

BR,
Mo

[Ben_H]

Hi Mo,

Almost. I don’t have customers on the product page. I can group by product and order(invoice) but that shows me the last time the product was ordered company wide and doesn’t correlate specifically to when a client ordered a product type last.

Is there a way to expand the order details once it grouped? Then I could easily filter the data

BH

Edit: in the mean time I’ll try and link a customer field

[Mohamed_Swella1]

You can bring the customer name by a lookup field?

[Ben_H]

I tried a number of things with the look up and group functions. The closest I was able to get is customers grouped then product type. The problem here is if two customers received the same product it groups them together. Which includes the dates grouped together. I think if we can separate the customers and at the same time the dates, I will be able to sort the date and get the results I am looking for.

thanks for your help and patience

BH

this particular customer sorted in the desired fashion, due to their product selection

[Justin_Barrett]

How many product types do you have? Are you only looking to find the most recent purchase of a given type, or of a specific item?

[Ben_H]

7 product types. I’m not interested in the individual product since various brands could be delivered but only the type.

Thanks Justin

[Justin_Barrett · Accepted solution]

With only seven types, this should be doable.

On the [Orders] table, add a lookup that pulls in the product type for the ordered product:

On your [Customers] table, add a rollup field driven by the links to the [Orders] table that finds the latest date for those orders using the MAX(values) aggregation formula. To focus only on orders of a specific product type, add a condition to the rollup, so that it only pulls orders matching a single type, like “Shampoo” in this example.

Duplicate this field, changing to a different type in the name and condition settings. Repeat until you have all seven types represented (I only have three in this screenshot).