BAQ for Best Price Supplier

Epicor ERP 10
1

Please let me know if there is already something inside Epicor that can do this.

I’m attempting to write a BAQ or Dashboard to display part numbers with their suppliers, that do not have the best price selected for the Primary Vendor. So far, I have been able to retrieve the latest price record for each possible vendor.
I’m not sure how I can display this so only the parts that we pay too much for are shown or highlighted somehow. A > B type of thing.

Any ideas?

Ignore the Dates in the BAQ. I realize that will be part of the struggle =)

image

image
image698×355 14.4 KB

image
image969×520 14.6 KB

2

SubQuery Criteria (instead of Table Criteria) would allow a comparison (A > B) between any fields in your query’s dataset. Would that work?

3

maybe, i just dont really know how i should do that.
PartNum: 12345 VendorA : 123 Base Unit Price: $10
PartNum: 12345 VendorB: 456 Base Unit Price: $5

Sometimes there will be 1 vendor, sometimes more than 2. Only one can be Primary which helps a little bit.

4

so you’re looking for either the Min() base unit price per Partnum?

You could use a calculated field for Min(), grouped by your other columns. I would do that in the subquery and include the basic fields you need to join to the top level query. And include the “primary” column, then filter out (subquery criteria) where “Primary” = 1 on the top level query

The result set should be parts and suppliers that are the best ( Min() ) price but not set as primary.

Without writing the BAQ, I’m pretty sure that would work.

1 Like
5

Thank you for the ideas Mike!

ROW_NUMBER() OVER(PARTITION BY Part.PartNum ORDER BY VendPart1.BaseUnitPrice ASC)

image
image922×304 15.4 KB

Dumped it into a dashboard to do some more filtering
image

Result is what I need, list all parts where there is a better price with an alternate supplier.

image
image1051×206 10.9 KB

1 Like
6

kpSupplierWatchNOPARAM.baq (54.5 KB)

2 Likes
7

Glad to be of help!

8

@MikeGross
Would you know how to make this dashboard pass a part number from the first query when clicked to the bottom query? I setup two seperate BAQ. My goal is that the bottom list will display all the price records for the highlighted part.

image
image1813×324 25 KB

Tracker does it for one row but almost need it to execute a BAQ for the row thats clicked, then display.

Payment Tracker does this.

9

@kylepbsps , out of curiosity, does this BAQ also evaluate quantity price breaks?
I always thought that PO suggestions should be smarter and evaluate price breaks in each supplier price list and suggest the best option (regarding price and delivery; not just primary supplier or supplier last purchased from) and then also offer an alternate suggestion to increase the quantity to get to the next favorable price break. The buyer could evaluate the alternate and if they choose to buy the alternate, then the primary suggestion would also drop off the New PO Suggestions list (and vice versa if the buyer chose to buy the primary suggestion, the alternate would drop off the list).

1 Like
10

It does not, but it would be possible with enough calculated fields =)

11

You’ll need to right click on the top query and look at “Publish”, then do the same on the bottom query but look at “Subscribe”. :slight_smile: This is a super powerful way to associate queries on a dashboard. we use the heck out of this feature so we don’t have a zillion dashboards.

12

You nailed it! Thanks again!

1 Like