In our company we have a custom field we call punch in OrderHed. I have a BAQ that shows NeedByDate, customer name, ordernum, shipviacode, shiptoaddress, and punch. What we are wanting to do is basically have a calc field that shows if the Punch in the order, is the only punch goign to the shipto address or not.
Thoughts on how to accomplish this?
Here is the baq, and I am not done with it fully, but gives you an idea of what we have and are doing.
ShippingRegionRoute.baq (41.3 KB)
So…you’ve got a custom field on OrderHed. It’s either a boolean or some kind of field that is sometimes filled in (or marked true) but sometimes isn’t. If true then order equals “punched”. You want to look at orders and see if there’s only one punched order, and if so, identify it?
You’ll need a subquery on OrderHed, grouped by ShipToAddress, with a calculated field that looks something like sum(case when punch = punched then 1 else 0 end)". I don’t know what exactly punch is, if it is a boolean, you can also just cast punch to an int and sum that.
Top level query is also OrderHed, joined to the subquery on the ShipToAddress. Put a calculated field on the top query that says if SubQ.Calculated_PunchCount = 1 then you’ve got a one-punch address.
Yeah, Grouping by that “punch” field will be required. But there’s always a devil hiding in the details (actually in the “releases” in this case).
ShipTo’s are a strange beast.
You may want to implement a BPM to copy the ShipTo from the header to each new release when it is created, instead of the system relying on an unset ShipTo on a release meaning that it should use the same ShipTo as the header.
And while you might say “We never have different ShipTo’s on a single order”, that’s almost a guarantee that you’ll get one the very next day. If your business practice is to not allow different ShipTo’s on the same order, then make a BPM to enforce it.