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

Announcements

Introducing our New Video Series: Community Spotlight
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How can I query the GE...
Microsoft Dynamics AX (Archived)

How can I query the GENERALJOURNALACCOUNTENTRY table to join with the ASSETTRANS table to get the field ASSETID on AX 2012?

(0) ShareShare
ReportReport
Posted on by Community Member

How can I query the GENERALJOURNALACCOUNTENTRY table to join with the ASSETTRANS table to get the field ASSETID on AX 2012?

I have the lists of CAPEX account code to query the GL Transactions from GENERALJOURNALACCOUNTENTRY table and I need to get the related ASSETID from the ASSETTRANS table as well. I understand that apart from these 2 tables, I need to join with the other interconnection tables as well but I am not sure that which table should I use in the query. Please suggest me.

Thank you.

*This post is locked for comments

I have the same question (0)
  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 1 on at

    Unfortunately I am not going to code for you, due to shortage of time, but I can say that any GL transactions can be matched with origin based on two things

    1. Voucher Number

    2. Date of Posting

      It usually needs you to involve two more table in between

    1. GeneralJournalEntry

    2. SubledgerVoucherGeneralJournalEntry

      That’s is general pattern to Link GL transactions with Origin in AX. For you inspiration you may want to look at next thread.

  • Community Member Profile Picture
    Community Member on at

    Hi Sohaib,

    Thanks for your suggestion. I 've found that one Voucher Number that I'm interested has 62 records in the both GENERALJOURNALENTRY and ASSETTRANS tables and they have the same Posting Date as well. Then they make the joining results generate so many records (62 multiply by 62 records).

    Is it possible that there is some further key to make it join by one-to-one record between the GENERALJOURNALENTRY and ASSETTRANS tables ?

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 1 on at

    ASSETTRANS can be because of different types of asset journals. would you, please do us  a favor by telling what type of journal your ASSETTRANS exist for..?

  • Community Member Profile Picture
    Community Member on at

    I'm focusing on the AssetTrans Type 1 (Acquisition) and 2 (Acquisition adjustment).

  • Community Member Profile Picture
    Community Member on at

    Hi Sir Oad,

    This might help. Just convert it to AX code.

    select * from generaljournalentry

    inner join  assettrans on generaljournalentry.Subledgervoucher == Assettrans.Voucher

    where transtype in (1, 2)

    TIA.

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

News and Announcements

Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Sanhthosh.Kumar.K Profile Picture

Sanhthosh.Kumar.K 2

#2
Raed Salah Bzour Profile Picture

Raed Salah Bzour 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans