Help with count of specific items ordered by a client

[Gebby]

Hi all, I feel like this probably a very simple answer that I’m just too deep in to it to see. But here’s the situation. I have an Orders table that has a Client column and an Product column. What I need to check is that a specific client, has not ordered over X amount of a specific product.

Here’s a mock up:

As you can see, I want to get a count in column 4 of how many Cokes for example, ClientA has ordered. I can do the Limit and Error fields just fine once I have the count of orders per client.

My attempts so far have been to do a self relation column based on Client field = Client field and then a rollup through that link of the Product field but that gives me crazy numbers. The other method is a self relation on the Product field but that gives me the total number of that product ordered, instead of the total number of that product ordered per client. Any help would be greatly appreciated.

I don’t have a lot of wiggle room in terms of the structure of the data.

[Nicolas_Joseph]

Hello @Gebby

@Gebby

I don’t have a lot of wiggle room in terms of the structure of the data

Without changing the structure, you could do the following:

1. Create a Query column (instead of a Relation). It will allows you to filter on both the Client and Product. Query for this Client and Product for example. The filter must be Category is This row > Category (and same for Product).

   

2. Create a Rollup column (Count of HMTCHO, I was lazy copying your column’s name here ). You can apply a Count unique if you have a real RowID (your’s is not in the screenshot). Or just a Count of basicaly… whatever column.

   

3. Lastly, an If → Then → Else column to check if the value is over the limit, let’s call it Over Limit?.

   

You should obtain this in your Orders table:

Hope you’ll find this helpful

[Gebby]

Wow this is literally perfect. Thank you so much. I’ve not really used the Query very much so this is a great learning experience!