I am still getting my head wrapped around using BAQ’s to return data.
I have a list of parts
Part A
Part B
Part C
And I need to return the most recent transaction that was a purchase. I know I can filter part tran by that trantype, but how do I only return the most recent?
Thank you.
Linking to PartTran is always crappy but if you choose the right index you can speed things up.
Create a subquery with PartTran and sort by date descending. In the subquery options choose the TOP 1 records. You may want to filter out non-inventory transactions (cost adj, etc). Your top-level query then links to the subquery. Use the Data Dictionary viewer to find the most efficient index to use. (Company, PartNum, TranDate or something like that.)
Mark W.
The query below is also a good place to start. This will bring back the most recent entered transactions in the system by partnum.
You can always make the b table more to what you are looking for by adding the TranType criteria there.
Select a.PartNum, a.TranType, a.TranDate, a.TranQty, b._RowNum
From Dbo.PartTran a
Inner Join ( -- Use Left Join if the records missing from Table B are still required
Select SysRowID,
ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY SysRevID DESC ) As _RowNum
from Dbo.PartTran
) b On b.SysRowID = a.SysRowID
Where 1 =1 and b._RowNum = 1Thanks Mark - I will be giving this a try. Appreciate the heads up on the nuances of the Part Tran.
I am hoping to do this in a BAQ. I am guessing you run this directly to the database?
You would recreate this SQL using the BAQ editor. It can be challenging if you don’t know SQL. But you can compare what shows up on the general tab for the BAQ this to see if you are on the right track. The one Ken Posted is a little advanced.
These are the fields you grab from part tran table in field selection, except for RowNum. That’s a calculated field.
Select a.PartNum, a.TranType, a.TranDate, a.TranQty, b._RowNum
This is what you put in the calculated field go get your row numbers.
ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY SysRevID DESC )
This created by putting a criteria in your subquery criteria tab under the table canvas.
Where 1 =1 and b._RowNum = 1
If that don’t know what the 1=1 is for. @knash can you fill us in on that part?
Sort by tran date and you can make a calculated field in the subquery to find the MAX(trandate)
Brad
Is sorting the sub-query and using the “TOP …” better than making the sub-query with a calculated field of MAX(PartTran.TranDate) ?
EDIT: DO’H !! (scooped)
You won’t get more than one row, or whatever rows you specified if you do that. If you use the TOP it doesn’t specify Top part, or top “insert value here”, just the overall top. So that works quite differently.
I’ve never used the TOP … I asked because @Mark_Wonsil mentioned it. And he seems like a guy that knows whet he’s talking about.
I think he’s talking about a different technique where you pull the subquery in through the calculated field, and like them through subquery criteria instead of joining tables the normal way. It works great if you are returning a single value. It doesn’t work if you want more than one value from the table you are pulling from. This thread explains that technique.
this can be converted into a baq. which version of epicor are you on?
I can create one and post, we are on 10.2.200
the 1=1 is an old habit. it is something I do while testing queries. you can ignore it.
1 =1 is always true.
then I add
and partnum = ‘part’
When I am done I comment out the and partnum line. This is for when I have multiple conditions. Then I don’t have to rewrite the query when I remove the top condition.
As you can see, there are many ways to skin a cat!
Haha, I think you’ve probably told me that 3 or 4 times, and I forget every time. 
“Your top-level query then links to the subquery.”
How do I link the two queries? LastPurchasedE10.baq (30.9 KB)
I attached my BAQ for reference. Here is the screenshot:
I believe I have this solved.
If it helps anyone, here is the query. It defaults to Current Company and Current Plant for Performance.
LastPurchasedE102.baq (43.1 KB)
Thanks for this. Your post gave me the clue on a unique SysRow.
Oh no. Ask my wife, or Chris Conn. I do make mistakes… All. The. Time. Brandon is correct here. Better to use the ROW_NUMBER() OVER (PARTITION BY… logic in this case. Mine works if you’re looking at one record but not a group.
Mark W.
I tried this solution on vendpart trying to get part cost. But I get no results.
When I do it without TOP and Row numbers 1, it displays two results.
The query is like this.
Top level: Part → PartPlant
Innersubquery: Vendpart
Cannot seem to figure out a way to get 1 result out of this. I want the most recent part cost from vendpart. Sorting by EffectiveDate in vendpart.
