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

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, ... / Which table to look/jo...
Finance | Project Operations, Human Resources, ...
Answered

Which table to look/join for to see each segments in an account number into separate fields?

(0) ShareShare
ReportReport
Posted on by Kashiru Kito 46

I want to create a query from GeneralJournalAccountEntry table but I need each segments into fields.

From:

pastedimage1611616219021v2.png

To:

pastedimage1611616189335v1.png

Image taken from Trial Balance set to display account number segments in separate columns

Fields GeneralJournalAccountEntry.LedgerAccount & DimensionAttributeValueCombination.DisplayValue are not separated.

I really appreciate if you can help me find the other table/s which I can join to GeneralJournalAccountEntry to get each separate segment.

As much as possible, I would like to avoid using string runtime functions to separate the segments.

Thank you so much.

I have the same question (0)
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 239,364 Most Valuable Professional on at

    Look at DimensionAttributeLevelValueAllView. It has a relation to DimensionAttributeValueCombination.RecId.

  • Kashiru Kito Profile Picture
    Kashiru Kito 46 on at

    Thanks Martin! Found it and was able to write the appropriate query. Thanks alot!

    static void testGetGLSeparateAccountSegments(Args _args){
    GeneralJournalEntry                     generalJournalEntry;
    GeneralJournalAccountEntry              generalJournalAccountEntry;
    DimensionAttributeValueCombination      dimensionAttributeValueCombination;
    DimensionAttributeLevelValueAllView     dimensionAttributeMainAccount
                                           ,dimensionAttributeCostCenter
                                           ,dimensionAttributeSubsidiaryCode;
    ;

    while select 
        JournalNumber
       ,SubledgerVoucher
    from generalJournalEntry
    join LedgerDimension from generalJournalAccountEntry
        where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
    join DisplayValue from dimensionAttributeValueCombination
        where dimensionAttributeValueCombination.RecId == generalJournalAccountEntry.LedgerDimension
    join DisplayValue from dimensionAttributeMainAccount
        where dimensionAttributeMainAccount.ValueCombinationRecId == dimensionAttributeValueCombination.RecId
           && dimensionAttributeMainAccount.GroupOrdinal == 1 /*MainAccount*/
    join DisplayValue from dimensionAttributeCostCenter
        where dimensionAttributeCostCenter.ValueCombinationRecId == dimensionAttributeValueCombination.RecId
           && dimensionAttributeCostCenter.GroupOrdinal == 2 /*CostCenter*/
    join DisplayValue from dimensionAttributeSubsidiaryCode
        where dimensionAttributeSubsidiaryCode.ValueCombinationRecId == dimensionAttributeValueCombination.RecId
           && dimensionAttributeSubsidiaryCode.GroupOrdinal == 3 /*SubsidiaryCode*/
     && generalJournalEntry.JournalNumber == 'GJT-00305101' 
     && dimensionAttributeValueCombination.DisplayValue == '33000-120-HE-001'
    {
        info(strFmt('%1 | %2 | %3 | %4 | %5 | %6 | %7'
            ,generalJournalEntry.JournalNumber
            ,generalJournalEntry.SubledgerVoucher
            ,generalJournalAccountEntry.LedgerDimension
            ,dimensionAttributeValueCombination.DisplayValue
            ,dimensionAttributeMainAccount.DisplayValue
            ,dimensionAttributeCostCenter.DisplayValue
            ,dimensionAttributeSubsidiaryCode.DisplayValue));

    } /*...outputs "GJT-00305101 | YE_2019 | 5637370392 | 33000-120-HE-001 | 33000 | 120 | HE-001"*/
}

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

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

#1
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 694

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 646 Super User 2026 Season 1

#3
CP04-islander Profile Picture

CP04-islander 337

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans