Consuming EpiBinding fields in a BAQ for a dashboard

Epicor ERP 10
, , , ,
1

I know this has been asked many times, but never answered in a way that would help with what I am trying to accomplish. Here is the goal…

I want a dashboard that a user can view 3-4 very specific fields in Epicor.

  1. Average cost from Cost Adjustment screen
  2. Last Purchase Price from Purchase Advisor screen
  3. Last Sales Price from Part Advisor
  4. Last Sales Date from Part Advisor (I think…)

I want them to be able to enter a part number and see all that information. The problem is that all those fields are EpiBinding and cannot be used in the formal BAQ system. How can I do this? Do I need to create an entire new form/screen with code?

2

You should be able to create BAQ to show all of this information.

One report with 3-4 subqueries, or CTE (@jkane )

The main part of the query will be part and left join to all of the subqueries.

Post here is you get stuck.

2 Likes
3

It’s getting all the information from the database, the screens are just calculating them.

Average cost is in the partCost table.
Last purchase price would be looking for the last purchase transaction in part tran, match it up to the PO tables to get the cost.
Same for last sales cost and date. Find the last thing in the part tran and join to the Order Tables.

You just have to do a little work to figure which record you are looking at, and to make sure you are doing it the same way epicor is.

(and if you’re lot costed, you’ll have to look at the ParLot table for costing)

2 Likes
4

So I have a pretty good start on this now. I have 2 queries. One to get the average cost per unit at one site, and one for the other. I just need the last time we purchased it, and sold it, and what that price was. It is a mess, but I think I am getting there.

5

We are not lot costed, and I am making progress. I just need to find where the specific spots are in Epicor for last purchased price, and last sold price. Then joining them is a whole different can of worms so I don’t get duplicate records.

6

The part tran will have the transaction data to find “last”.