Need to get previous but one record from PO in ax 2009

[kashperuk]

I would suggest that you add a larger sample data set, so that we can better understand your problem statement.

Or at least confirm that my understanding of what you wrote is correct:

1. For any PO of type "P", there's exactly one corresponding PO of type "D", that is linked to it through the InventSiteId field on the PO table
2. There can be multiple POs of type "P" for the same "D" PO, so for example P1 linked to D1 and P2 linked to D1.
3. You want to create a query that would select all POs of type "P" and their respective "D" POs.
4. You want to do it with just 1 query.

My suggestion would be:

PO poP;
PO poD;
while select PurchId from poP
where poP.PurchaseType == "P"
join PurchID from poD
where poD.PurchaseType == "D"
&& poD.InventSiteId == poP.InventSiteId
{
info(strfmt("'P' PO: %1, 'D' PO: %2", poP.PurchId, poD.PurchId));
}