Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to join InventTran...
Microsoft Dynamics AX (Archived)

How to join InventTrans table with InventJournalTrans table?

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,I'm trying to get inventjournaltable.description field from inventtrans table, how can i join these tables? SO far I have tried the following query but with no luck...

select sum(qty)
from invTrans


join invDim
join inventtransorigin


where invTrans.InventDimId == invDim.InventDimId &&
invTrans.InventTransOrigin == inventtransorigin.RecId &&
//inventtransorigin.ReferenceCategory != InventTransType::InventTransfer &&
inventjournaltrans.InventTransId == inventtransorigin.InventTransId &&
inventjournaltable.JournalId == inventjournaltrans.JournalId &&

invDim.InventLocationId == "100" &&
invTrans.ItemId == this.ItemId &&
invTrans.qty < 0 &&
invTrans.DatePhysical > str2Date("01/01/2014", 123) &&
invTrans.PackingSlipReturned == false

*This post is locked for comments

  • Josan_ec Profile Picture
    Josan_ec 45 on at
    RE: How to join InventTrans table with InventJournalTrans table?

    Ivan, What join condition must be considered to show the item transactions associated with the inventjournaltrans?

  • Verified answer
    Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at
    RE: How to join InventTrans table with InventJournalTrans table?

    Note that Selvam's reply misses a very important join condition, making it incorrect. Right now it selects all transactions with that item, not only those related to the specific journal.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to join InventTrans table with InventJournalTrans table?

    I was intending to exclude some records based on InventJournalTable's description field,but I have found out that, inventtransorigin table's referenceid field also does the job for me.Thanks for the help :)

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to join InventTrans table with InventJournalTrans table?

    Hi,

    Try this,

    static void Job33(Args _args)

    {

       InventTrans invTrans;  

       InventJournalTrans inventjournaltrans;

       InventJournalTable inventjournaltable;

       InventTable invtbl;

       select invTrans  

       join invtbl

       join inventjournaltrans

       join inventjournaltable

       where inventjournaltrans.ItemId == invTrans.ItemId &&

       inventjournaltable.JournalId == inventjournaltrans.JournalId &&

       invtbl.ItemId == invTrans.ItemId && invTrans.ItemId == "YourItemID";

        info(strFmt("%1",inventjournaltable.Description));

    }

    Thanks,

    Selvam

  • Suggested answer
    Ivan (Vanya) Kashperuk Profile Picture
    Ivan (Vanya) Kashperuk on at
    RE: How to join InventTrans table with InventJournalTrans table?

    What is it exactly you are trying to achieve? In the question you say you want to pull out the Description field from the InventJournalTable (inventory journal header), but in the query you are trying to could the quantity of goods processed..

    InventJournalTrans::findInventTrans(inventTrans.inventTransOrigin().InventTransId).inventJournalTable().Description would get you the fist thing based on an inventTrans record.

    You should be able to figure out the relationship between InventTrans/InventTransOrigin/InventJournalTrans/InventJournalTable from the above methods.

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,605 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans