View showing lowest price per category/sub-category

[100grammes]

Hi,

I need to supply to my team of buyers the "best deal" for any given category and sub-category of products in our database. At first, it was easy to group by category / sub-category into a view sorted by price ASC. But over time, the database grew big and we now have 7000+ products splitted into 25 categories and 50 sub-categories ...that makes it long and tedious to scroll through this view to locate manually the cheapest product for each category/sub-category combo (about 150 unique combinations in total).

My question: is there a way to get a view that'd output the best product per category / sub-category combo in a flat table format. In other words, this view would contain ONLY the 150 best deals.

Many thanks!

[kuovonne · Accepted solution]

Have you looked into back-and-forth rollup fields? A rollup field that shows the lowest price. Then another rollup that brings that lowest price to the item. Then a formula field that checks if the item is the lowest price. Finally, another conditional rollup of the name of the item with the lowest price.

[100grammes]

It works!