E10.0 - SubQuery return maximum transaction date (Bin Ex.)

Epicor ERP 10
1

I have a BAQ designed to show a certain bin locations and any quantities located in those bins. I would like to see the last time a specific part was transacted in that bin. To do this a subquery was setup for the PartTran table. The only fields selected for the PartTran table are: PartNum, BinNum, and a calculated field named MaxTranDate. The PartNum & PartBin from the subquery are used to link to the PartBin table like-named fields. I would like to get MaxTranDate from the subquery to show the last transaction date for that Part Number + Bin.

In Crystal, I would have had the subreport read all of the details (suppressed), and added a group footer to show the max(trandate).

From the BAQ Designer, how do I say max(trandate)? Note, the max function only appears to work on integers.
Do I need to use grouping to somehow to use eliminate duplicated records? If so, how?

Thank you,

Bruce B.

2

Group By PartNum, BinNum

image.png743Ă—133 7.17 KB

Then use Max(PartTran.SysDate)

5 Likes
3

Perfect! I am in the Crystal 12-step program. It’s a tough addiction that I battle everyday :slight_smile: Thanks for the help.

3 Likes
4

I am doing a similar subquery with date and I receive the following error.

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

I used the group by display columns

5

Any calculated field that uses aggregates (like MAX(), MIN(), COUNT(), SUM(), etc…) must have it’s GroupBy box unchecked.

If you need to group by that calculated field, you need to add another subquery layer.

6

Just to add on to what Calvin says, when you use grouping you have 2 rules:
Any field that is aggregated (sum, count, avg, etc) must be set to NOT GroupBy
All other fields MUST be set to GroupBy

If you need other info not needed in this grouping, as Calvin mentions, you can make a subquery and link the results back.

1 Like
7

On the top level Query, I can get results based on my subquery, but it does not filter out the dates.

29
29.JPG1581Ă—555 73.4 KB
30
30.JPG1220Ă—710 87.9 KB

8

You may have created subqueries, but you need to add them to the top level query

9

Here is a simple BAQ that retruns the MAX(PartTran.TranDate) for each part and TranType

SubQuery1 (obviously it was completed after Subquery2 was made)
image

Subquery2 (it could have more tables and joins)
image

Subquery2 display fields. Note that all but the calculated field are “Group By”.
The expression for the calculated field is MAX(PartTran.TranDate)

image

Subquery1’s table relations

image
image.png783Ă—551 21.4 KB

Subquery1’s display fields.
Note that none are group by.

image
image.png705Ă—299 7.13 KB

And the results:
image

3 Likes
10

Were you able to get this working? I’m having the same issue. I linked the SubQuery but it’s returning all of the transaction’s not just the last one.

11

Yes I did get it working, maybe your subquery isn’t linked correctly, I do know there are multiple ways to do this. @ckrusen is far more fluent than me. I use BAQ sometimes, but not constantly.

12

Sorry for the Necropost, but this one has me stumped.

How do I get only the cost associated the max tran date? more details…

This time I want the the cost associated with the last transaction date pulled from the subquery from above. As soon as I bring in the cost field it adds the grouping by each different cost. I did a max cost along with the max trandate which brings in a single record for the part, but the max cost is associated with a previous date.
image

13

@Bruce_B set the subquery sort by date descending and return top 1?