Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How to query Financial...
Microsoft Dynamics AX (Archived)

How to query Financial Dimension Field in Sales Contract

(0) ShareShare
ReportReport
Posted on by FrankRaij 50

Hello,

For a new company we are going to record a frame contract number that is a level higher than the Sales Contract. The intention is that this happens when the contract is created and that the number is stored in the financial dimensions in the sales contract (header view - Financial dimensions / see screenshot). The number is also needed for financial analysis.

Only when I view the field do I see that it is not stored in a regular AXtable but as a Dimension Value. Does anyone know how I can link this field to a sales contract (salestable)? I have found that the fields appear in the DefaultdimensionView but that is then linked to the LedgerJournalTrans and LedgerJournalTable (with the defaultdimension field) but that is only filled later in the process, so when I create the Sales contract I can not show that value yet.

Hope somebody can help.

Attached a screenshot.

Thanks!

Frank

2018_2D00_09_2D00_21_5F00_15_2D00_32_2D00_39.jpg

*This post is locked for comments

  • FrankRaij Profile Picture
    FrankRaij 50 on at
    RE: How to query Financial Dimension Field in Sales Contract

    DIMENSIONFINANCIALTAG

  • Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: How to query Financial Dimension Field in Sales Contract

    Check the OMOperatingUnit table (for my example).

  • FrankRaij Profile Picture
    FrankRaij 50 on at
    RE: How to query Financial Dimension Field in Sales Contract

    Hi Rustem,

    Checked the view  OPERATINGUNITVIEW but this view does not contain the description of the DISPLAYVALUE (the OMDEPARTMENTVIEW also not). Maybe our AX Datasource is different?

    Thanks!

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: How to query Financial Dimension Field in Sales Contract

    The description of DisplayValue field you can get from related table.

    static void Job181(Args _args)
{
    SalesTable           salesTable;
    DefaultDimensionView defDimView;
    OMOperatingUnit      operationUnit;

    select firstOnly salesTable
        where salesTable.DefaultDimension;

    select defDimView
        where defDimView.DefaultDimension == salesTable.DefaultDimension
           && defDimView.Name             == "Department"
    join operationUnit
        where operationUnit.RecId                 == defDimView.EntityInstance
           && operationUnit.omOperatingUnitType   == OMOperatingUnitType::OMDepartment;
           
    info(operationUnit.Name);
}


  • FrankRaij Profile Picture
    FrankRaij 50 on at
    RE: How to query Financial Dimension Field in Sales Contract

    Thanks a lot Rustem!

    Any idea how to get the description of the DISPLAYVALUE (from DFAULTDIMENSIONVIEW)? I only found the DISPLAYVALUE ('VR') in the table but not the description.

    Checked google but I can't find it.

    Thanks!

    2018_2D00_09_2D00_24_5F00_10_2D00_15_2D00_58.jpg

  • Verified answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: How to query Financial Dimension Field in Sales Contract

    To get Sales Contract Number from Financial dimension of SalesTable you should use DefaultDimensionView.

    As example, how to find value of Department dimension by DefaultDimension value:

    static void Job181(Args _args)
{
    SalesTable           salesTable;
    DefaultDimensionView defDimView;
    
    select firstOnly salesTable
        where salesTable.DefaultDimension;
    
    select defDimView
        where defDimView.DefaultDimension == salesTable.DefaultDimension
           && defDimView.Name             == "Department";
        
    info(defDimView.DisplayValue);
}


    It's easiest way to get dimension value.

  • FrankRaij Profile Picture
    FrankRaij 50 on at
    RE: How to query Financial Dimension Field in Sales Contract

    Hi Rustem!

    Maybe a lack of knowledge. I checked the discussions, but I can't see how I can make a relation between the Dimension Value (AX Financial Dimensions in Sales Contract) and the Sales Contract Number. As said I only see a relation between financial posting tables and the dimension values. I need it for a custom made report via SQL Server Management Studio.

    Thanks!

  • Suggested answer
    Rustem Galiamov Profile Picture
    Rustem Galiamov 8,072 on at
    RE: How to query Financial Dimension Field in Sales Contract

    Hi FrankRaij!

    Take a look at this discussions:

    https://community.dynamics.com/ax/f/33/t/294280

    https://community.dynamics.com/ax/f/33/t/292227

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans