Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX forum / How to join InventTran...
Microsoft Dynamics AX forum
Answered

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

Categories:
Development / Customization / SDK
  • 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 Kashperuk Profile Picture
    Ivan 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 Kashperuk Profile Picture
    Ivan 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.

Helpful resources

Quick Links

Dynamics 365 Community Update – Sep 9th

Welcome to the next edition of the Community Platform Update. This is a weekly…

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Community Spotlight for September 2024

Kudos to Mohana Yadav!

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,263 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,112 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans