Hi @tomasw ,
I’ve been mulling over this since you posted it the other week. I appreciate the work you and the team put into this, but I have to admit that I’m not sure how I could use this with regards to my original request without having to do HUGE data pulls to get what I’d be looking for.
> Would it be possible to have a query that shows the totes and the current contents of those totes? Being able to query by tote name, tote barcode, product sku or product barcode would be great as well.
What I was originally looking for was a way to be able to either return the contents of all totes, or supply a tote name (not an internal tote ID, because we have no way of knowing what that is) or barcode, and to be able to return what items are currently in that tote. On the flip side, we also wanted to be able to supply a SKU, even if it’s through a separate query, and have it return the tote names or barcodes (again, internal tote ID isn’t helpful for those in the warehouse that need it) that the SKU is currently in.
From what I can tell, in order to accomplish this using the solution that has been supplied, I’d have to:
- Manually determine the date of the oldest open order, and supply that as the “order_date_from” in the “orders” query in order to get the internal order ID and the current order status. Key note here: I’d have to pull every order from that date range because the orders query does not have a filter for order status. This is potentially thousands of orders.
- Once I have those orders and their statuses and their internal IDs, I’d then have to query, one by one, each order, and pull all orderlines down through the tote_picks section.
- Then I’d have to go through each order searching for either the specified SKU, or use it to build a table of all the currently occupied totes and their contents.
- Once I have all of the current tote information, then I’d have to run each one against the tote_history query and try to figure out how to use the sort function (I still haven’t quite figured out how that works for ShipHero because it seems to be different from other platforms) to give me the most recent record so I can get the tote name.
While this is something that could be done, it uses a lot of credits and will take a lot of time because it will get throttled like crazy trying to get there. I made the request because I thought it would be useful for others to be able to do a tote search as well.
I’m sorry your team went through the work to put this together. Maybe my original request wasn’t terribly clear. Either way, unfortunately, the work that was put into this isn’t going to be terribly helpful for what we need.