Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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 40

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.

  • Kashiru Kito Profile Picture
    Kashiru Kito 40 on at
    RE: Which table to look/join for to see each segments in an account number into separate fields?

    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"*/
}

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,188 Most Valuable Professional on at
    RE: Which table to look/join for to see each segments in an account number into separate fields?

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

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,188 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans