BAQ to select one row from partTran where MAX(TranDate)?

Epicor ERP 10
1

I have an existing BAQ that displays various information about parts for cycle counting purposes and I need to add a couple new columns in regards to the most recent cycle count adjustment. I’m thinking I need to pull in the PartTran table where PartTran.TranType = ADJ-QTY. Although it seems simple I can’t think of how to select only one row from the PartTran table for the most recent adjustment (PartTran.TranDate) so I can display the TranDate, TranQty, and EntryPerson for the last time the part had a qty adjustment.

Can someone point me in the right direction?

2

Try joining to PartWhse or PartBin. These have the LastCCDate value.

1 Like
3

@Jason_Woods thanks for the reply! I have that field, our inventory team is asking for the last qty adjustment(Part Tran table). I believe the idea is to get feedback on recent qty adjustments. As an example if there is a discrepancy on the on hand qty by say 10,000 to many and they look to see the last qty adjustment was for 10,000 then it’s a clue as to what is going on.

So my question is how can I just show the most recent row in the part tran table for a qty-adj transaction?

4

You could use a subquery to do it. Filter the partTran table by QTY-ADJ transactions, and find the MAX(TranNum) for each part (group by part). Then you can join that subquery back to the partTran table in order to get the other info you need.

1 Like
5

Make a subquery that gets the MAX(Tran-date), with a grouping on ADJ-QTY and the part. then you can join from the subquery back in to PartTran using that date as part of the criteria.

2 Likes
6

@Asz0ka is correct based on your last post, but you original post mentioned “the most recent Cycle Count adjustment”. The LastCCDate would likely work if you are truely talking about Cycle Count instead of any adjustment.

7

Another approach is to use SQL’s ROW_NUMBER.

Create a calculated field in your BAQ’s PartTran subquery called RowNum and number/partition it by however you want to get your data. Filter the table however you need. In the parent query, tie it together as an outer join and filter the subquery RowNum field on row number 1. It allows you to access the entire row easily and doesn’t create grouping issues when additional fields are added. Below is an example for the last entry in the PartTran table by part.

ROW_NUMBER() OVER (PARTITION BY PartTran.PartNum ORDER By PartTran.TranDate DESC)

Ross

1 Like