Help

Re: Rollup romula - count unique values in linked records

2240 0
cancel
Showing results for 
Search instead for 
Did you mean: 
LeighAnnMoltz
6 - Interface Innovator
6 - Interface Innovator

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.

Screenshot 2023-05-18 at 1.49.35 PM.png

 

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. 

Screenshot 2023-05-18 at 1.51.32 PM.png

 

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.  

 

 

 

 

10 Replies 10
Matthew_Carrano
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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: 

Screenshot 2023-05-18 at 3.17.07 PM.png

 

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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. 

Nathan_Gupta_0-1684439661941.png

Nathan_Gupta_1-1684439712466.png

 

Nathan_Gupta_2-1684439741626.png

 

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...

Nathan_Gupta_3-1684439953450.png

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

Nathan_Gupta_4-1684440109098.png  Nathan_Gupta_5-1684440124104.png

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.

Nathan_Gupta_6-1684440237476.png

Nathan_Gupta_7-1684440251247.png

 

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.

Nathan_Gupta_8-1684440402126.png

Nathan_Gupta_9-1684440513596.png

 

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)).

Nathan_Gupta_10-1684441009827.png

Nathan_Gupta_11-1684441055962.png

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

Nathan_Gupta_12-1684441138232.png

Nathan_Gupta_13-1684441178469.png

 

Hopefully this helps 🙂

LeighAnnMoltz
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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. 

your order line items table should look something like this

Matthew_Carrano_0-1684442182133.png

 

then have a field like this in the products table

Matthew_Carrano_1-1684442225542.png

 

 

then a field like this in the warehouses table

Matthew_Carrano_2-1684442247661.png

 

 

 

Matthew_Carrano
6 - Interface Innovator
6 - Interface Innovator

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

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

LeighAnnMoltz
6 - Interface Innovator
6 - Interface Innovator

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 ... .

LeighAnnMoltz
6 - Interface Innovator
6 - Interface Innovator

Thanks everyone for trying to help out!  

I opted to add an "orders" table and am doing a lookup from order details to pull the ship status over. I was able to accomplish my goal which for now is just to populate the content in an automation for open order updates to our fulfillment teams at each warehouse by using "find" conditions in the automation setup for each warehouse.  

It's not perfect and doesn't help me with reporting, but accomplishes the goal for today!