Skip to main content

Notifications

Announcements

No record found.

Business Central forum
Suggested answer

Query Shortcut Dimension 1,2 and 3 from G/L Entry

Posted on by 45

Hi all,

I have created a Query object which looks into G/L Entry. I have 3 dimensions named

  • Project
  • Department
  • Company

I have set them in General Ledger setup as Global Dimension1, Global Dimension 2 and Shortcut dimension 3.

When i query, I cannot find Shortcut dimension in  G/L Entry. when i tried to join it with Dimension Set Entry, i get 3 separate rows.

Here is my Dataitem query.

dataitem(G_L_Entry; "G/L Entry")
{
    column(G_L_Account_No_; "G/L Account No.") { }
    column(G_L_Account_Name; "G/L Account Name") { }
    column(Amount; Amount) { }
    column(Posting_Date; "Posting Date") { }
    column(Document_No_; "Document No.") { }
    column(External_Document_No_; "External Document No.") { }
    column(Description; Description) { }
    dataitem(Project; "Dimension Set Entry")
    {
        DataItemLink = "Dimension Set ID" = G_L_Entry."Dimension Set ID";
        column(Dimension_Value_Code; "Dimension Value Code")
        {
        }
        dataitem(Project_Name; "Dimension Value")
        {
            DataItemLink = "Dimension Value ID" = Project."Dimension Value ID";
            column(DimName; Name) { }
        }
    }
}

and my output is:

{
    "G_L_Account_No_":"60510",
    "Amount":-100,
    "Posting_Date":"2020-06-30",
    "Document_No_":"GEN000032",
    "External_Document_No_":"12232",
    "Description":"Drug Substance GMP Manufacturing",
    "G_L_Account_Name":"Drug Substance GMP Manufacturing",
    "Dimension_Value_Code":"LAB1636",
    "DimName":"Lab1636","Name":"",
    "AuxiliaryIndex1":302,
    "AuxiliaryIndex2":7,
    "AuxiliaryIndex3":"COMPANY",
    "AuxiliaryIndex4":"COMPANY",
    "AuxiliaryIndex5":"LAB1636",
    "AuxiliaryIndex6":0,
    "AuxiliaryIndex7":"",
    "Document_Type_FilterOnly":null
},
{
    "G_L_Account_No_":"60510",
    "Amount":-100,
    "Posting_Date":"2020-06-30",
    "Document_No_":"GEN000032",
    "External_Document_No_":"12232",
    "Description":"Drug Substance GMP Manufacturing",
    "G_L_Account_Name":"Drug Substance GMP Manufacturing",
    "Dimension_Value_Code":"BIOLOGY",
    "DimName":"BIOLOGY",
    "Name":"",
    "AuxiliaryIndex1":302,
    "AuxiliaryIndex2":7,
    "AuxiliaryIndex3":"DEPARTMENT",
    "AuxiliaryIndex4":"DEPARTMENT",
    "AuxiliaryIndex5":"BIOLOGY",
    "AuxiliaryIndex6":0,
    "AuxiliaryIndex7":"",
    "Document_Type_FilterOnly":null
},
{
    "G_L_Account_No_":"60510",
    "Amount":-100,
    "Posting_Date":"2020-06-30",
    "Document_No_":"GEN000032",
    "External_Document_No_":"12232",
    "Description":"Drug Substance GMP Manufacturing",
    "G_L_Account_Name":"Drug Substance GMP Manufacturing",
    "Dimension_Value_Code":"LAB-001|GABA ASD",
    "DimName":"LAB-001 - GABA - Ginty",
    "AuxiliaryIndex1":302
    ,"AuxiliaryIndex2":7,
    "AuxiliaryIndex3":"PROJECT",
    "AuxiliaryIndex4":"PROJECT",
    "AuxiliaryIndex5":"LAB-001|GABA ASD",
    "AuxiliaryIndex6":0,
    "AuxiliaryIndex7":"",
    "Document_Type_FilterOnly":null
}

How can i get all relevant dimensions in same row instead of 3 rows as shown.

Regards

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,139 on at
    RE: Query Shortcut Dimension 1,2 and 3 from G/L Entry

    If you’re not on the latest version this is another option: www.olisterr.tech/.../exposing-dimension-and-dimension-values.html

  • mscalamogna Profile Picture
    mscalamogna 80 on at
    RE: Query Shortcut Dimension 1,2 and 3 from G/L Entry

    This issue should be (partially) resolved in the most recent release of Business Central.

  • Suggested answer
    Marco Mels Profile Picture
    Marco Mels on at
    RE: Query Shortcut Dimension 1,2 and 3 from G/L Entry

    Hello,

    We currently do not have dedicated Dev support via the Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist.  If you need assistance with debugging or coding I would recommend discussing this on one of our communities.

    www.yammer.com/dynamicsnavdev

    dynamicsuser.net/.../developers

    I will open this up to the community in case they have something to add.

    Thanks.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans