Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Ax AOT Query for Hierachy

Posted on by 545

Hello,

An example, table with two fields ParentId and ChildId. Is it possible to create AOT query and based on range value for ParentId to find lowest ChildId in hierarchy? 

Thanks.

*This post is locked for comments

  • Verified answer
    MarisR Profile Picture
    MarisR 545 on at
    RE: Ax AOT Query for Hierachy

    Hi Martin and Jonathan,

    Yes, that was my thinking as well. I just asked in case if some solution exists for such case.

    Thanks.

  • Verified answer
    Jonathan  Halland Profile Picture
    Jonathan Halland 11,304 on at
    RE: Ax AOT Query for Hierachy

    Hi AXUSer.

    I don't believe this is possible via the Queries or X++ select statements in AX. There is no particular handling of this recursion or advanced joins etc...

    I would suggest you flatten your heirarchy into a temp table and then do a select as needed or simply loop through your results

    http://stackoverflow.com/questions/13992244/how-to-select-lowest-level-in-hierarchy-from-table

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 229,918 Most Valuable Professional on at
    RE: Ax AOT Query for Hierachy

    Such a data model can't be easily queried by usual SQL statements; you would have to do recursive queries. Because this is generally bad for performance, it's often better to change the data model. For example, if you maintain the hierarchy level on each line, getting what you want in a single query will become trivial.

  • MarisR Profile Picture
    MarisR 545 on at
    RE: Ax AOT Query for Hierachy

    Hi, 

    Actaully idea is that I have table with following values: 

    ParentId ChildId
    - V1
    V1 V2
    V2 V3
    V2 V4

     

    So, I need to have AOT query, who can find V3 and V4 as those are lowest values (don't have childs) in this hierachy .

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at
    RE: Ax AOT Query for Hierachy

    Hi AxUser,

    this should be possible using the minOf() grouping funtion. (Assumed the 'lowest' is the one with the lowest 'order relation' on the field type - string, real, ...)

    Create a query: DynamicFields=no

    Add the ParenID field and add the Min Field ChildId

    this will create something like 'select ParentID, min(ChieldID) from table group by [partition,datatarea]ParentID

    But I never checked this.

    So theoretically you should be able to add a (QueryBuild)Range on the parentField to the QueryBuidDataSource 'table'

    regards

    Douglas

  • Jonathancm Profile Picture
    Jonathancm 416 on at
    RE: Ax AOT Query for Hierachy

    You mean like an "order by" clause?

    Regards.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans