# Calculated field to find PO number based on Max PODate

I’m trying to create a BAQ that will look at all part numbers and if existing on a PO, return the Max PO Date and it’s associated PO number.
My query returns multiple lines for every PO number instead of only the PO number associated with the MAX PODate

Hi @Joscelynne and welcome to the EpiUsers club!

It’s a little trickier than it looks on the surface. Can you share your BAQ? Then we can look at what you got and help direct you.

Daher-FIN-Parts.baq (58.2 KB)

Looking at your BAQ, you need to remove the PONum from your SubQuery which will remove the duplicates and give you your MaxDate. I would create a second Subquery using your Subquery to identify the PONum associated with the MaxDate. Then use the second Subquery in your TopLevel to join to the Part table to get your association of MaxDate and Part Number.

1 Like

Hi Joscelynne,

I took a quick look and have a few suggestions.

First, when I run your BAQ I get this (I sorted by PartNum to get a clear picture).

Obviously, showing my part numbers and not yours but… the first thing that jumps out is I’m getting part numbers returned that don’t have ANY PO’s against them. This is because of your join here. (Left Outter Join) You’re asking for ALL parts to be returned… and if any match your subquery (PO Information) then return those details.

If you change that to an Inner Join to only return “Matching” records, it will weed out parts that don’t have any PO’s against them.

Better…

Next, it is showing multiple PO’s for each part. Which is not what you wanted.
You calculated the Max Order Date… which is good. But don’t forget that PO Numbers are integers, so you could just look at the Max PO Num as well.

I think the problem is you were calculating the max date value in your subquery. So that was giving you the max date… of each PO. Since each PONum only has (1) Order Date, you still brought in each record.

Instead, move your calculated fields into your top level query. So, my top level fields would be these:

And you now get one row per part (which has a matching PO record), and your calculated fields in the TOP LEVEL query, picks out the max PO Num and Max Date out of the subquery.

Perhaps I may have misinterpreted this… If you DO want to return ALL parts, then your original join was correct, and you just want to move your calculated fields to the top level query.

One thing that may/or may not be useful here. We would have value in seeing the RevisionNum as well on something like this. Just went through an exercise where we were showing costs on most recent Opened PO, Last Received, and PartCost by part rev.

That may not matter for you in your case, just throwing that idea out there now vs down the road.

2 Likes

This was exactly what I was looking for, thank you!

Is there a way to also return the OrderQty for that part on that PO?

Yes. This is possible.

You need to adjust your subquery to have a Calculated row on the PO table vs the group by. This lets you then bring back data from the PO record.

Calculated row would be something like this
(ROW_NUMBER() OVER (PARTITION BY PODetail.PartNum, PODetail.RevisionNum ORDER BY POHeader.OrderDate DESC))

Then select ROW = 1 when you join the subquery to the top query.

I think I did something wrong but I’m not sure what.
It’s returning multiple rows per part again and numbering the lines instead of returning the OrderQty from the PODtl table.

The BAQ will be a bit different. You will remove all of the group by on both of the subqueries.

You will have to do the work on Subquery2 first. else you might get errors.
SubQuery1 (Topquery)
Note the Table criteria

Subquery 2

This is the Row calculated in field
(ROW_NUMBER() OVER (PARTITION BY PODetail.PartNum ORDER BY POHeader.OrderDate DESC))

1 Like