BAQ Last Transaction

Epicor ERP 10
1

I am familiar with obtaining a Last Transaction, but I’ve come across something I can’t quite figure out.

What I would like to do is take from the PartTran table the last STK-MTL transaction (which is working fine) BUT I want it to pull that transactions:
LOT
TRANQTY
JOB

I thought I just needed to create a calculated field using MAX for LOT, TRANQTY and JOB but what I get is the largest TranQty for that LOT and the largest JOB number.

I’ve tried pulling this information from the subquery and also from the Top Level - but no luck.

Any thoughts?

2

Make a sub-query with just the PartTRan table, that returns Company, MAX(TranNun), with a table Criteria of TranType = STK-MTL (and whatever other criteria you need)

Then in your main query, join the sub-query to the PartTran table. That will make the PartTran unly return the single record, but you’ll have access to all fields.

Here’s the Sub Query

image
image.png699×494 16.5 KB

Here’s the Sub-Q’s displayed fields
image

And the Calc Field
image

Then back in the main query,

add SubQuery2

image
image.png792×451 17.9 KB

2 Likes
3

Thank you!

I’ll try this in just a bit…:slightly_smiling_face:

4

I am receiving multiple TranNum’s rather than just the Max TranNum for the STK-MTL trantype.

On the subquery I am including the LotNum, TranQty, PartNum, TranDate - are any of these fields telling the system to give me multiple records?

I’m asking for these fields so that I get the Last TranNum (of the STK-MTL transaction) so that it will give me the Lot No, Qty, Part No and Date that it was done. But instead it is giving me All the STK-MTL transactions.

5

The sub query should be limited to just the TranNum and any other non-unique fields (like PartNum).

If the sub had just one displayed field (a calculated field) for MAX(TranNum), and the table had the criteria of TranType = ‘STK-MTL’, then you’d get exactly one record. The very last STK-MTL transaction - regardless of the part, job, lot num, etc…

If the sub had just two displayed fields (a calculated field) for MAX(TranNum), and PartNum(Grouped), and the table had the criteria of TranType = ‘STK-MTL’, then you’d get exactly one record for each part that has had a STK-MTL part tran. The very last STK-MTL transaction - for that part, regardless of job, lot num, etc…

If you added LotNum to the displayed fields, you get a record for each unique PartNUm & LotNum combo.

Adding those other fields into the subquery makes it group on them as well. For example: assume the following PartTran info exists:

TranNum   PartNum   TranType  LotNum
-------   --------- --------  ----------
12345     WIDGET-01 STK-MTL   XYZ-123
23456     WIDGET-01 STK-MTL   XYZ-123
34567     WIDGET-01 STK-MTL   XYZ-9876

Displaying MAX(TranNum), PartNum (Grouped), LotNum(Grouped)

would give you two rows:

MAX(TN)   PartNum   LotNum
-------   --------- ----------
23456     WIDGET-01 XYZ-123
34567     WIDGET-01 XYZ-9876

Displaying just `MAX(TranNum), PartNum (Grouped)

would give you one rows:

MAX(TN)   PartNum
-------   ---------
34567     WIDGET-01
6

Hey Calvin thank you for responding to my question last week, I truly appreciate your time!

I’ve gone back and have built my BAQ with baby steps.

Everything works great until I try to add the TranDate or the TranQty getting up to 542 rows of data. Up until then I was receiving the Last TranNum, the PartNum and the LotNum with 32 rows.

Any idea’s?

7

You can’t add the TranDate to the sub-query. And make sure the TranNum of the sub-query is linked to the TranNum of the the PartTran in the main query.

Can you post a screen shot of the BAQ components?

Or at least copy the Query Phrase and paste it here.

8

remember that when you are grouping, you are telling SQl all of the categories that you want to group by.

Ridiculous example:

I have a sea full of whales. If the only column I pull in is “whales” then group by, I get one row, just “whales”

Now if I bring in another column with the type of whale, I get "whales, blue " "whales, killer " "whales,sperm " etc. So now you have a row for each type of whale, 3 in my example.

So if you are grouping your transactions by some category in your query, (and remember every column you bring in is now a category), if I bring in tran date, you are now saying “I want transactions, that are purchased, that are direct to job, that are blahblahblah, that are on this date”. So when you bring in that column you have another category that you just added into the grouping (a specific date), hence the more rows.

Another way to think about it, if I have a row that shows the count of the number of whales in my sea, and then I want to add the type of whale, but still only see one row, how could you display that? It’s not impossible, but it takes a lot more SQL magic to do something like concatenate the rows. So if you were to just add the column, it doesn’t make sense.

9

I love your answer - makes obvious sense. I sort of thought that was happening - thank you for confirming my suspicions in such a ‘creative’ way!

Thank you so much!