Help

Re: Last Time a Product Type Was Ordered

Solved
Jump to Solution
2150 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Ben_H
6 - Interface Innovator
6 - Interface Innovator

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

1 Solution

Accepted Solutions

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:

Screen Shot 2020-07-03 at 10.10.23 PM

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.

Screen Shot 2020-07-03 at 10.15.44 PM

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

Screen Shot 2020-07-03 at 10.18.08 PM

See Solution in Thread

10 Replies 10

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

Thanks for the quick reply Mo!

image image image

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:

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

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

You can bring the customer name by a lookup field?

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

Screen Shot 2020-07-03 at 5.11.39 PM

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

Screen Shot 2020-07-03 at 5.23.21 PM

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
6 - Interface Innovator
6 - Interface Innovator

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

Thanks Justin

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:

Screen Shot 2020-07-03 at 10.10.23 PM

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.

Screen Shot 2020-07-03 at 10.15.44 PM

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

Screen Shot 2020-07-03 at 10.18.08 PM