I’m confused by my results. All I’m trying to do is raise an exception when a user closes a shipment with more than one order. However, the “number of rows > 1” in my query in my condition is true even when I only have a single order on the shipment. Am I missing something in the query itself?
My pack ID 152777 has only one order 327855 and I the exception gets raised. Why?
ERP.ShipDtl is related to ttShipHead via Company and PackNum (the default relation)
Multiple ShipDtl records (for the same ShipHead) with the same OrderNum would return multiple records. You would need to have be able to group the records in that query by ShipDtl.OrderNum - Which I’m not sure is possible.
Did you know that you can prevent shipping multiple orders together, with a Customer setting?
I think I was wrong on that last part. Customer’s can be set to prevent combining orders or packers on Invoices. Not prevent multiple orders per packer.
Well, in SQL the ShipDtl table for this packnum has only one row. I don’t understand why my query doesn’t return a count of 1.
Our main issue is this: If we have two customer orders to ship, both CC, and one has a deposit, when we freight the total amount for both orders are charged (the system ignores the deposit on the deposit order). Epicor has told us that this is known, but so far nothing has been fixed, requiring later adjustments on our end.
SO, if I can flag the shipper when multiple orders are added and one of those has a deposit, that would save us some later adjustments.
Right now on my query I’m just trying to determine when a shipment has more than one order (shipdtl), but can’t get it working.
Also, I think I understand what you mean about the ordernum. Although my test only has one row in ShipDtl, if I added a single order with multiple lines I’d have multiple lines in my ShipDtl table, causing another problem… grouping on order num of some sort would be needed.
Adam,
A good trick is to return an info message after the criteria to show what the actual field result is during BPM processing. The tt tables / fields often lack data to link on, even though the field shows in the available fields. One way to get around that is to pass a value from a table to a BPM data field, then use that field in your criteria.
Isn’t this one of those RowMod = “U” scenarios?
I’m not great with that, but I think the tt tables can have multiple rows for one record–the old one and the changed one.
That sound right to anyone?
Here’s the Query you need in your condition:
Table Relationship between 1 & 2
Table Relationship between 2 & 3
Table criteria for table 3
That should find rows where SHipDtl’s have the same PackNum (same as the ShipHead), but different OrderNums
That’s excellent. Thank you!
You might want the other half of the condition to be when ShipStatus changes from “OPEN” to anything.
Right. In our case, we are interested in when the pack is attempting to be closed… Anything to “CLOSED”
“Closed” is actually an odd status. The normal flow is
OPEN -> SHIPPED -> INVOICED
CLOSED usually indicates that the Packer was “abandoned”. If you create a packer, add lines to it, then mark it closed, it’s like being voided. No Part Trans happen, and it is not billable.
If it is SHIPPED, and you uncheck the Shipped Box,
it goes from
SHIPPED -> CLOSED
Once it is INVOICED, you cannot change the status.
Ahh. Thank you for that information!
grumble grumble grumble be careful about joining tt tables in a BPM query