Any ideas how I can build a BAQ or report that will look for orders that have releases due on different dates? It’s common for us to write an order that has 5 lines and then each line has 10 releases. Each line’s release dates should match the other lines. When we update dates on the orders, each release should be in sync with the other lines same release. It appears that it isn’t always happening and the result is some lines look past due while others are not due to be shipped yet. Anyone have a good suggestion or perhaps an existing BAQ that looks for these types of anomalies? I’ve started trying to write one but it’s tricky. If I don’t hear from anyone, I’ll post what I come up with when I get there.
I have an idea, but it might not be very efficient.
Pull the first one.
Compare the rest to those that match, and don’t.
Union them together.
Suppress the one that is a duplicate.
1 Like
So I have been studying it a little and it’s going to be harder than I thought. Might be impossible. Some orders are “a primary model with some options and accessories”. Those would follow the logic I put in the original request. But other orders are “multiple different models purchased in bulk with separate release dates that are not related to one another at all”. So now I need to qualify the order somehow. Still thinking. Might end up taking an obviously long list and just start plugging and chugging through it. For instance, I might be able to just manually review 100 orders faster than I can write a fool proof BAQ that whittles it down to the 10-20 orders I’m expecting to find.
I have a dashboard that does this kind of thing and just highlights with row rules things like dates offset or ship methods that are different so humans can look thru them quickly and decide if the issue is a problem or not. It does not try to fix anything just make them aware.
Sounds like the kind of thing I might need.
I built it as an updatable a decade ago so we could keep a 122 release order lined up for a chaotic customer that changed dates and quantities several days a week. Now you can change almost anything in a sales order and it is getting order job wizard soon.
Doesn’t solve your complication, but here’s a bit field that will tell you if any group of releases for a single order-line has more than one distinct date on a release:
case
when min(OrderRel.ReqDate) over(partition by OrderRel.Company, OrderRel.OrderNum, OrderRel.OrderLine) <> max(OrderRel.ReqDate) over(partition by OrderRel.Company, OrderRel.OrderNum, OrderRel.OrderLine) then 1
else 0
end
No duplicate rows either if you select distinct since there can be only one output value per order-line.
1 Like