Announcements
No record found.
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
You mean like an "order by" clause?
Regards.
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
Hi,
Actaully idea is that I have table with following values:
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 .
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.
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
Hi Martin and Jonathan,
Yes, that was my thinking as well. I just asked in case if some solution exists for such case.
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.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
