Help

List of most popular item

Topic Labels: Extensions
Solved
Jump to Solution
3528 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Jared_Miller
5 - Automation Enthusiast
5 - Automation Enthusiast

My family business makes baskets. Each basket is numbered and with that number we track multiple things such as date it was made, type of wood used, basket size/style, and inlay style. I created a form to make it easy to enter all that info and numerous other tracking features, however, the one thing I can’t seem to figure out (and maybe its not possible?) is how to get a list of say the 10 most popular baskets for a year… we have about 25 different basket styles, 10 different wood species, and 5 different inlays… I can easily see what was the most popular basket style or wood species but I would like to have a list of each individual style… (not sure if I am making sense?) As an example: we make a napkin basket and a condiment basket and we use both cherry and walnut wood species. So let’s say we made 10 cherry napkin baskets, 2 walnut napkin baskets, 7 cherry condiment baskets, and 3 walnut condiment baskets. I would like my list to first list cherry napkin baskets with the amount made and then the cherry condiment basket with the amount made and then then walnut condiment basket with the amount made and so on… I currently have a table for basket styles, wood species and inlays that are referenced in the main list… would like to keep it that way rather than making a long list of every last possible individual basket…

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Here’s a base that demonstrates the system I was thinking of the other day. Feel free to copy it and pick it apart:

In the [Line Items] table, the “Main” view is the full, unordered list. “Group by Count” groups them by how many of each type (based on the content in the {Descriptor} formula field) were ordered. However, this potentially puts some items in the same group (see the group for items only ordered once). To fix this, another formula field is used to attach the count to the descriptor, which is how the “Group by Count & Descriptor” view operates. The “All Fields” view shows all the helper fields that make the system work.

This is a variation on the system I described in this thread:

Which relies on the method described here:

If you have any questions, let me know.

See Solution in Thread

7 Replies 7

Welcome to the community, @Jared_Miller! :grinning_face_with_big_eyes: What you’re looking to do can be achieved fairly easily using the Chart block. Before setting up the block, though, you would need to do a little setup in your base.

First make a formula field in your order table that builds a unique descriptor for each ordered basket, combining the wood, style, inlay, etc. Just be aware that the more detailed this descriptor becomes, the more data you’re going to end up with in your chart.

Next make a view that uses a filter to limit the orders shown to a given time frame: calendar year, month, etc. Whatever makes sense for the level of reporting you want.

Now make a new Chart block. Pick the view you created, choose the descriptor formula field for the X axis, and set the Order control to Y Value, Descending. You should get something like this (the names across the bottom have been truncated for privacy):

Screen Shot 2019-12-29 at 1.10.45 AM

You can mouse over the bars to see the exact number for each item. Unfortunately there isn’t a way to limit this chart to show just the top N items, which is one of the reasons I suggested keeping your formula-built descriptors from getting too specific. The more specific they are, the more bars you’ll have in your chart. However, you could build other views that narrow things down; e.g. a view that only shows things made from walnut, one that only shows a certain type of basket, etc.

Thanks for the welcome! I am very new to AirTable but really like what I see! :slightly_smiling_face:
So this may be a rather ignorant question but what would my formula need to look like?
Also wondering if there would be a way to do this without using blocks? If not thats fine however I am not sure yet if we want to upgrade to a paid version…
I will play with this in the meantime and see what more I can figure out! :slightly_smiling_face: Thanks for the reply and the help!

The formula needs to concatenate data from other fields that describe the item. For example, say you have a {Wood} field and a {Basket Type} field. Both are single select, and contain a number of different options:

  • Walnut, cherry, oak, etc. in the {Wood} field
  • Napkin, condiment, centerpiece, etc. in the {Basket Type} field

Your formula would then look something like this:

Wood & " " & {Basket Type}

Creating output like this:

Screen Shot 2019-12-30 at 5.18.28 PM

Maybe? :slightly_smiling_face: I’m trying to wrap my head around some possible ways to pull that off. There are tricks you can do with Airtable involving linking records between tables that might help. Let me work on that a bit and get back to you.

Thanks for your help!
Formula works like a charm! :slightly_smiling_face: I also created a chart (I still have the 14 day free pro trial…) however one other issue I can see with the chart is I am not sure how to go about printing it off? I might be missing something however I can’t seem to find a way to print off a chart whereas I have no problem printing a table?
I really do appreciate the help! :slightly_smiling_face:

The chart block currently doesn’t have a print option, sadly. It’s been requested, and adding your support couldn’t hurt, though there’s no telling when that option will be added.

Still working on the other approach. Will probably have something tomorrow.

Justin_Barrett
18 - Pluto
18 - Pluto

Here’s a base that demonstrates the system I was thinking of the other day. Feel free to copy it and pick it apart:

In the [Line Items] table, the “Main” view is the full, unordered list. “Group by Count” groups them by how many of each type (based on the content in the {Descriptor} formula field) were ordered. However, this potentially puts some items in the same group (see the group for items only ordered once). To fix this, another formula field is used to attach the count to the descriptor, which is how the “Group by Count & Descriptor” view operates. The “All Fields” view shows all the helper fields that make the system work.

This is a variation on the system I described in this thread:

Which relies on the method described here:

If you have any questions, let me know.

Sorry it took me so long to reply! I like what I see… Lots to digest as far as how everything works together but I have done some digging into it and will definitely do more! I really appreciate the help and will likely end up back here from time to time to reference everything you showed/explained! Also, I would just like to thank you for the warm welcome to the Airtable community! Your friendly assistance helped to further ‘sell’ me on Airtable! Thanks!