Rollup romula - count unique values in linked records

[LeighAnnMoltz]

I don't know if this is possible .... if anyone tells me to restructure my data, I'm quitting lol 

I have Orders, Products, and Warehouses table. 

Using linked records, I assigned various warehouses to products. And then when orders are placed, it's looking up the warehouse based on the product.  

I want to show open open orders per warehouse in a rollup, but it's rolling up the products .... not the count of linked orders. I cannot some come up with how to write this formula .... it feels like it should be simple!  The linked records field is set to only lookup orders that are not yet shipped.

This is what I currently have set up as the formula - it's counting the number of products assigned to the warehouse though .... not the number of orders. 

Which makes sense. I just don't know how to make it do what I want.  

Also - I have duplicate order numbers (expected) because it's creating a line for every item in the order, not the order itself. So if there are three unique items in the order, the order number will technically be in my orders sheet 3 times, which is fine .... just a note of rthe fact that it'll need to count the number of unique values, not total.  

[Matthew_Carrano]

First off, you definitely should have an 'Order line items' table to solve the duplicate order numbers issue. Each record in that table would link to an order and a product and would have a qty.

Secondly, in the products table, add a rollup of the orders, add a filter so that it only rolls up orders that have not yet shipped, use countall as the aggregation. then, add a rollup field in the warehouses table that sums the field you just created in the products table.

[LeighAnnMoltz]

Okayyyy fine. I added an Order numbers table and my order details links to that.  

The problem I'm running into is that the warehouse still is ultimately tied to the product.  So even when it's pulling the order number, the order number is "duplicated" if there's more than one product ordered. D oes that make sense?  

My Products configuration is below: 

Order lookup is only pulling order that are not shipped and rollup is currently a Count(all) of those order numbers.  

Warehouse is a sum formula of that countall rollup. 

Now what? 

[Nathan_Gupta]

This is interesting and a bit finicky! I think you have an error, and I have a solution for you 😌 Although it's a bit hard to test without jumping into your base.

First, you're filtering your open orders in the product table using a view of the Orders table. This seems to be an ineffective approach. Let me try to explain why.

I recreated your base with orders, products, and warehouses tables. 

Notice how I'm using a checkbox in the Orders table to determine if an order is fulfilled or not. You might use something similar or a Single Select status or some other mechanism (Date Shipped field being filled, Shipping label attachment field, etc.). Now even though I'm currently filtering the orders in my Orders table...

even if I'm filtering by view, this doesn't actually remove the link between Order 1 and product 1 ("Hairbrush"). 

  

Now, if I'm manually selecting the orders in the field filtered by view, then YES, Order 1 won't show up (as we want), but notice this isn't safe and it's assuming somewhat manual data entry.

A safer approach would be to use conditionals. My fundamental reasoning here is that I use Views to look at temporary snapshots of data, and I want to evaluate based on state conditions. I could be splitting hairs here, but 🤷‍♂️

In the products table, consider using a rollup field to find the open orders from the Orders table.

This is probably preferable because it makes sense to maintain links across your orders and products regardless of fulfillment status for other KPI and business tracking. 

Now, with this rollup providing an array of unique open orders, we answer your main question: how do I show a count of unique open orders by warehouse.

If we go to Warehouses table, we just need to create the following Rollup field on the linked products field using the formula COUNTA(ARRAYUNIQUE(values)).

And notice, if I shift the "book" product to "Seattle", Order 3 isn't duplicated. 

Hopefully this helps 🙂

[LeighAnnMoltz]

Thank you for such a thoughtful response!  

The problem with this is that it's assuming each order only has one item from one warehouse to process. Since we fulfill orders from multiple warehouses, the same order number exists in multiple places - we ship partials.  

So I can't just have the order data go into a single line for order.  

I've added an orders table and the fulfilment team is primarily working out of the order details table - where each record is now unique (order name + Item name) for the record name.  

Relationally, I just can't figure out how to make the order count rollup on warehouses since the direct link with warehouses is products. I don't know i fthat makes sense or not ...

[LeighAnnMoltz]

I considered one workaround could be a rollup of the relevant order numbers and then a formula that counts those entries in the string, but I can't quite figure out how to write that. 

[Matthew_Carrano]

your order line items table should look something like this

then have a field like this in the products table

then a field like this in the warehouses table

[Matthew_Carrano]

if that doesn't work, i would have to take a look at your actually base because i'm not understanding the problem lol

[Nathan_Gupta]

I believe COUNTA(ARRAYUNIQUE(values)) should do the trick to rollup the order numbers into a unique array to be then counted!

[LeighAnnMoltz]

I tried this .... and I don't know what it's rolling up, but it doesn't look correct. One of my warehouses should definitely be showing 4 right now, but instead it's showing 2 on the formula ... .