Join first only from joined table

(0) Share

Report

Posted on by KCDeVoe

1,450

I'm coming up blank on a way to do this in AX.

We have a need for the latest internal packing slip ID to be displayed on the VendPackingSlipJournal form. A straight inner join results in multiple records being displayed if there are multiple versions. A display method is not acceptable to the users as they filter on this field. I have tried every combination of QBDS, Views, and Queries that I can think of. If I wanted just the internal packing slip ID I could do that easily as well but if we ever need another field from the Version I want that to be available. Maybe I'm just taking the wrong approach? Anyone have any ideas?

I can write it pretty simply in SQL:

SELECT *
FROM VENDPACKINGSLIPJOUR T1
JOIN VENDPACKINGSLIPVERSION T2
ON T2.RECID =
(
    SELECT TOP 1 RECID 
    FROM VENDPACKINGSLIPVERSION T3
    WHERE T3.VENDPACKINGSLIPJOUR = T1.RECID
	ORDER BY T3.VERSIONDATETIME DESC
)
--WHERE T1.PURCHID = 'PO1345678'

*This post is locked for comments

All responses (4)

Answers (1)

Verified answer

KCDeVoe 1,450 on at

Like (0)

Report

RE: Join first only from joined table

The solution I went with was an intermediate view. View of VendPackingSlipJournal column and max(InternalPackingSlipId) grouped by VendPackingSlipJournal from VendPackingSlipVersion then joined VendPackingSlipVersion on VendPackingSlipJournal that way.
KCDeVoe 1,450 on at

Like (0)

Report

RE: Join first only from joined table

"Use the firstonly qualifier maybe?"

-The kernel drops it as it doesn't translate to SQL.

"What about computed field msdn.microsoft.com/.../gg845841.aspx that has you select statement behind ?"

-I thought of using computer columns, but I would have to do one for every column.
Suggested answer

Mea_ 60,278 on at

Like (0)

Report

RE: Join first only from joined table

What about computed field msdn.microsoft.com/.../gg845841.aspx that has you select statement behind ?
Konrad U 800 on at

Like (0)

Report

RE: Join first only from joined table

Use the firstonly qualifier maybe?