Good morning,
I am struggling with a complex BAQ. I have a UD table that contains a list of parts, dates, and quantities that the customer wants. We use this table to compare to the existing orders in Epicor. We will add/remove/update releases to match this data file.
Sometimes there is a case where the customer wants three releases on the same day for the same amount. For example, they might want 1 piece on 8/6/24, three times. In the UD02 table it looks like three rows with the same data. I want to match this to the existing data in Epicor. Order/Line/Release are not stored as part of the customer’s UD02 table, so I have to match on just part number, quantity, and date (technically a few others but not relevant here).
The ‘OurOrders’ Subquery is simply a join of the Order tables and displaying the open releases, dates, quantities. You can see how I joined this to the UD02 table in the screenshot. The tricky part is that we are looking at the quantity the customer wants (from UD02), and comparing it to the actual quantity in the release, or the remaining quantity in the release.
If I have three identical records in UD02, and they match a single release from our order tables, then I want to see just the two records that didn’t match the release. The tricky part is that technically all three records match the existing release. So I need a way to have the first match take off the first record from UD02, and then I would only see the two records that didn’t match.
Does this make sense? I am having a hard time putting it into words. If three records in UD02 match a single release in OrderRel, how can I get the BAQ to show me the two remaining records that, while they still match, do not have a corresponding record in OrderRel.
Thank you for your time.
