Dynamics Community Forum Thread Details

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

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

Quick Links