Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Join first only from joined table

Posted on by 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

  • Verified answer
    KCDeVoe Profile Picture
    KCDeVoe 1,450 on at
    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 Profile Picture
    KCDeVoe 1,450 on at
    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_ Profile Picture
    Mea_ 60,278 on at
    RE: Join first only from joined table

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

  • Konrad U Profile Picture
    Konrad U 800 on at
    RE: Join first only from joined table

    Use the firstonly qualifier maybe?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,232 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans