Filter on the Sum of a Group

1

Let’s say I have a table that lists purchases and sales of different items in my store (Inventory table), and each row represents either a purchase from a vendor (increase to inventory) or a sale to a customer (decrease in inventory). There are multiple products that are being added for sale and many that are sold for brief periods of time so the list of Products is constantly changing and most are “out of stock.”

Let’s say I want to create a view on this table grouped on the Product column to see my current inventory and I need to exclude any Products where I have no inventory (ie., sum of product-count for that Product = 0),

How would I do that? I know I can create a new column on the table (ie, named Current Inventory) and do something like this:

Inventory.filter(thisrow.Product).Quantity.Sum()

Is there a way of doing it without adding a new Column?

2

IMHO, you should never be afraid of making columns! You can always hide them, and they make your formulas more readable by:

  • breaking them up into more digestible chunks
  • naming the chunks (the column names give you a hint about what the heck some jumbled formula is trying to do)

6-months-from-now-you will thank present you for keeping it simple!

But if you must, you can wrap a formula like that into a filter. Choose the formula type of filter, and do something like:

Inventory.filter(thisRow.Product).Quantity.Sum() > 0

And because no data set is ever perfect (much as a programmer or doc maker might hope otherwise), you might consider adding a manual override checkbox of some kind called “Retired” that means “even though the system might be showing some stock, I happen to know that’s not true and we’re not selling this anymore”. And then filter based on that too.

4 Likes
3

I am always a little reluctant to add columns like that but that’s clearly the easiest thing to do and that’s what I did … thanks for the suggestion.

1 Like
4

Nice. A related tip is to always have the “true” version of any table tucked away in a hidden folder. That version can have all its guts spilled out for when you’re tinkering with things, and then all other instances in your doc are just views.

2 Likes
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.