How to join InventTrans table with InventJournalTrans table?

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Suggested answer

Ivan (Vanya) Kashperuk Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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 :)

Was this reply helpful? Yes No
Verified answer

Ivan (Vanya) Kashperuk Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Josan_ec 45 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No