Repeated records in my query

(0) Share

Report

Posted on by Community Member

while Select inventJournalTable
join inventJournalTrans
join inventTrans
join inventTransOrigin
where inventJournalTable.JournalId == inventJournalTrans.JournalId
&& inventTrans.InventTransOrigin == inventTransOrigin.RecId
&& inventJournalTrans.InventTransId == inventTransOrigin.InventTransId
&& inventJournalTrans.JournalId == "ICD-000016"

{
while select generalJournalEntry
where inventTrans.Voucher == generalJournalEntry.SubledgerVoucher
{
select generalJournalAccountEntry
where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId;

i++;
info(strFmt("Journal : %1 , I : %2",inventJournalTrans.JournalId, i));
info(strFmt("Qty : %1",inventTrans.Qty));
info(strFmt("Voucher : %1", generalJournalEntry.SubledgerVoucher));
info(strFmt("Amount : %1", generalJournalAccountEntry.TransactionCurrencyAmount));

}

}

Please Check and run the job

I am getting the repeated amount from generalJournalAccountEntry Please reply me soon .

*This post is locked for comments

All responses (4)

Answers (0)

Sort by

André Arnaud de Cal... 307,408 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Hi Pravinjoshi,

It is likely that one inventTransOrigin can have multiple InventTrans records. If you did not enable the Inventory paramter for automatic merge of inventory transactions, you can get depending on situations multiple records in the InventTrans. Have you verified this? Also note that one GeneralJournalEntry at least has two records in the GeneralJournalAccountEntry table. The account and also offset account.

One more reason could be that you didn't filter on the Ledger field in the table GeneralJournalEntry. This field determines to what legal entity the transactions belong.
Suggested answer

Shankar Das 205 on at

Like (2)

Report
Copy link

Link copied!

Your job would be fetching repeated amount since for generalJournalEntry subledgervoucher is same every time. I would suggest you to explain the issue in detail adding detail that you are going to achieve through this job. Also check the ERD (Entity relationship diagram) of all the tables that are mentioned in the job and find the data manually at first.
Community Member on at

Like (0)

Report
Copy link

Link copied!

HI Shankar Das,

Thanks for responding ,

Here my issue is i want to that

in Inventory Managment -> journal-> Movement

Select journal Id -> Lines

after selecting Lines -> inventory -> transaction

where i found the quantity now when I go to Ledger -> finicial voucher

from this form i need amount in transaction with voucher ?

I get all the details expect amount in transaction.
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
Copy link

Link copied!

The general solution on how to find information in tables have been explained in your other thread for the same problem:

community.dynamics.com/.../217555

In this thread, the same has been recommended: check what are the relations between those tables, use table browser and simple jobs to figure out how your tables are linking to each other based on the query ranges, and you will have your answer.