web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / the duplicated ines wh...
Finance | Project Operations, Human Resources, ...
Suggested Answer

the duplicated ines when linking Inventtransposting and Ledgertrans tables

(0) ShareShare
ReportReport
Posted on by mangosteenlu 19

Dear All,

I wonder if anyone on this forum can enlighten me with your knowledge/thoughts please. I am trying to built a report to see how much stock write offs on all  stock items each month. To capture the right data, I need to link ledgertrans with inventtransposting table to show stock item ID and how much cost being posted to our general ledger codes (stock w/offs --- there are several being set up in AX). I know the relationship between two tables is voucher number, however there are duplicated lines (please see the examples below). Can someone tell me how to get rid of the duplicates please? thanks 

select P.ITEMID,P.TRANSDATE,P.VOUCHER,T.AMOUNTMST,P.ACCOUNTOFFSET,P.ACCOUNT from INVENTTRANSPOSTING P
join LEDGERTRANS T
on P.VOUCHER=T.VOUCHER
where P.TRANSDATE= '2022-03-01' and P.ACCOUNTOFFSET like '2%'

pastedimage1650384411895v1.png  

I have the same question (0)
  • Suggested answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

    Hi mangosteenlu,

    There are records in the InventTransPosting table for both the physical as well as the financial update. When you post a write off via an inventory journal, for the same voucher there is a record for both. You need to filter on the type to get only the records related to the financial updates.

  • mangosteenlu Profile Picture
    mangosteenlu 19 on at

    Hi Andre,

    Thank you very much for reply. I cannot find any value for transactions in Inventtransposting table. (please see the below screenshot) I've filtered voucher by journal number starting with 'J' but I need to link to other table to show the value of stock journal. Can you please advice? thanks 

    pastedimage1650434085203v1.png

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

    Hi mangosteenlu,

    Can you elaborate? I'm not able to understand your reply. Is this a new follow up question? What do you mean with 'other table'? What exactly have you now shared as screenshot?

  • mangosteenlu Profile Picture
    mangosteenlu 19 on at

    Hi André,

    At each month end, we are trying to identify how much stock items being written offs (stock items, Qty, and value). Therefore I need to link Inventransposting and Legertrans table together to get the information I need.

    All stock items with stock journal numbers are listed on the Inventtransposting table but no costs attached to them, in order to find the costs for these stock w/offs, I find there is a relationship between the Inventtransposting and Ledgertrans table (Inventtransposting. voucher=Ledgertrans.voucher) however there are duplicated lines when i execute my SQL query. Do you know any better way to do so?

    Thank you so much for your help.  

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,020 Super User 2025 Season 2 on at

    Hi Mangosteenlu,

    To get the cost, you can also link the InventTrans record which contains the quantity and the cost amount. This table has a direct link based on the InventTransId, (financial) Voucher and (financial) TransDate

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 646 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 529 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 285 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans