BAQ to Match Order Release Information - Only One Match Per Record

Epicor ERP 10
1

Hi Everyone,
This is a part of a larger more complicated problem, but I am going to try to simplify an example for us to talk about.

I have a table (UD03) that contains a customer’s requested release information. Here is an example:
image

I also have a set of order releases in Epicor that match this list of requests. For example:
image

If you study the rows for a bit you will see that we have one extra release in Epicor, compared to the list of requested orders from the customer.

This is where my problem comes in. I would like to have a query that lists all of the releases in our system that do not match a request from the customer’s file. In the end we will close, delete or update these releases so that our open releases matches the customer’s requested releases.

The problem I have is that since the date and quantity match an existing requested order, my BAQ is not flagging the extra order in our system. I need a way to match one of our releases to a single customer request, then remove that request from the list of available requests so that if the BAQ comes up to a second release in Epicor (that would have matched a customer request) then the BAQ will show me the second release indicating that it can be closed/deleted/updated, because the other requested release that matches has already been accounted for.

Does this make sense? Do you need more information or clarification?
Thanks for your time!
Nate

2

You could try assigning a rank/row_number to both tables based on your matching criteria, so rows that have the same matching criteria will also have a UID. Then you could use that to determine if you have extra releases.

3

A little late now, but that UD table should have had fields for the OrderNum, OrderLine, and OrderRel, that the cust request was tied to.

4

You’re right! But the table comes from the customer, and they don’t care what our order line or release numbers are. That is why I have to do all this magic with PO, Part, Price Date, Etc… to match them up.

1 Like
5

I like the sounds of this. I will look into ranking/numbering the rows and see if I can get that to work.
Thanks!
Nate

1 Like