{"id":213,"date":"2017-05-02T18:30:16","date_gmt":"2017-05-02T08:30:16","guid":{"rendered":"http:\/\/bakke.online\/?p=213"},"modified":"2017-05-02T18:30:16","modified_gmt":"2017-05-02T08:30:16","slug":"how-to-tree-lookups-in-dynamics-ax-reference-group-controls-part-3","status":"publish","type":"post","link":"https:\/\/www.bakke.online\/index.php\/2017\/05\/02\/how-to-tree-lookups-in-dynamics-ax-reference-group-controls-part-3\/","title":{"rendered":"How-to: Tree lookups in Dynamics AX reference group controls, part 3"},"content":{"rendered":"

This is something that keeps coming up on different projects, so I’d like to share how I usually do this. \u00a0There are some examples out there which try to describe a very generic solution which can be used in almost any case. \u00a0However, such solutions very quickly become very<\/strong> complex. \u00a0To keep things simple, I have tried to keep this example fairly specific but adaptable to a wide range of circumstances. \u00a0Specifically this example is using a fixed hierarchy structure with a fixed number of levels, each of which will be represented by a different table.<\/p>\n

Earlier<\/a>, I have introduced the data model sitting behind the tree lookup, and last time<\/a> I showed how to build the actual tree structure.<\/p>\n

If you have not read these posts yet, I recommend you do that first, to help with the understanding of this post, which explains how to provide a more useful tree reference in the calling form.<\/p>\n

<\/p>\n

Introducing a better reference<\/h3>\n

Remember from last time, how the books form has a multi-segmented control for the shelf reference:<\/p>\n

\"\"<\/a><\/p>\n

This can be replaced by a single field providing a unique reference. \u00a0In a tree structure, a path is a useful and unique concept, so we’ll use that. \u00a0Note that this step is not required in any way, so if you are happy to use the segmented reference group control, go right ahead. \u00a0Have a read through the following, though, to compare the results and see what works best for you.<\/p>\n

First we create a new Extended Data Type called EB_ShelfPath:<\/p>\n

\"\"<\/a><\/p>\n

Drag this new data type to the EB_Shelves table to create a new field:
\n<\/a><\/p>\n

\"\"<\/a><\/p>\n

We’re going to put this into the reference group on the books form, so we’ll need a unique index in order for the reference group to do the right thing.<\/p>\n

\"\"<\/a><\/p>\n

The reference group will need a field group as well, so we add a new field group containing only the Path field:<\/p>\n

\"\"<\/a><\/p>\n

Note that we set the label to “Shelf”. \u00a0This will appear in the books form as the reference group’s label.<\/p>\n

Keeping the path up to date<\/h3>\n

We’ll need to make sure the path is kept up to date as it is possible to rename libraries, aisles, racks and shelves. \u00a0The path must also be set automatically when new shelves are added to the system.<\/p>\n

To support all of this, we’ll add a new method to the EB_Shelves table, called getPath:<\/p>\n

public display<\/strong><\/span> EB_ShelfPath getPath()\n{\n  EB_Racks     racks;\n  EB_Aisles    aisles;\n  EB_Libraries libraries;\n\n  select firstonly<\/strong><\/span> RackId\n    from<\/strong><\/span> racks\n   where<\/strong><\/span> racks.RecId     == this.RackRecId\n    join<\/strong><\/span> AisleId\n    from<\/strong><\/span> aisles\n   where<\/strong><\/span> aisles.RecId    == racks.AisleRecId\n    join<\/strong><\/span> LibraryId\n    from<\/strong><\/span> libraries\n   where<\/strong><\/span> libraries.RecId == aisles.LibraryRecId;\n\n  return strFmt<\/strong><\/span>( \"%1=>%2=>%3=>%4\"<\/span>, libraries.LibraryId, aisles.AisleId, racks.RackId, this.ShelfId );\n}<\/pre>\n
Quite simple, really. \u00a0We do a select of the ancestors of the current shelf, i.e. the rack, aisle and library. \u00a0For performance reasons we also use field lists to limit the amount of information retrieved. \u00a0(I’ll write a series of performance related posts in a few weeks, to explain how and why this improves performance) \u00a0Once we have the fields, we return a formatted path string.<\/p>\n
Also override the insert() and update() methods to keep the path up to date:<\/p>\n
public void<\/strong> <\/span>insert()\n{\n  this.Path = this.getPath();\n\n  super<\/strong><\/span>();\n}\n\npublic void<\/strong><\/span> update()\n{\n  \/\/ Only update the path if the shelf ID has changed<\/em><\/span>\n  if<\/strong><\/span>( this.ShelfId != this.orig().ShelfId )\n  {\n    this.Path = this.getPath();\n  }\n\n  super<\/strong><\/span>();\n}<\/pre>\n
We’ll also need to override the update() methods on the ancestor tables, racks, aisles and libraries, to update the path on the shelves whenever a rack, aisle or library ID changes. \u00a0We don’t need to worry about the insert() method for these tables, as there will be no shelves at the time of insert.<\/p>\n
First, override the update() method on the EB_Racks table:<\/p>\n
public void<\/strong><\/span> update()\n{\n  EB_shelves shelves;\n\n  \/\/ Only need to update the shelves if the rack ID has changed<\/em><\/span>\n  if<\/strong><\/span>( this.RackId != this.orig().RackId )\n  {\n    \/\/ Call super() here so getPath() can see the updated value<\/em><\/span>\n    super();\n\n    while select forUpdate<\/strong><\/span> shelves\n           where<\/strong><\/span> shelves.RackRecId == this.RecId\n    {\n      shelves.Path = shelves.getPath();\n      shelves.update();\n    }\n  }\n  else<\/strong><\/span>\n  {\n    super<\/strong><\/span>();\n  }\n}<\/pre>\n
And also on the EB_Aisles table, where we do the same but with a join to the racks:<\/p>\n
public void<\/strong> update()\n{\n  EB_Shelves shelves;\n  EB_Racks   racks;\n\n  \/\/ Only need to update the shelves if the aisle ID has changed<\/em><\/span>\n  if<\/strong><\/span>( this.AisleId != this.orig().AisleId )\n  {\n    \/\/ Call super() here so getPath() can see the updated value<\/em><\/span>\n    super<\/strong><\/span>();\n\n    while select forUpdate<\/strong><\/span> shelves\n        \u00a0 exists join<\/strong><\/span> racks\n           where<\/strong><\/span> racks.RecId      == shelves.RackRecId\n              && racks.AisleRecId == this.RecId\n    {\n      shelves.Path = shelves.getPath();\n      shelves.update();\n    }\n  }\n  else<\/strong><\/span>\n  {\n    super<\/strong><\/span>();\n  }\n}<\/pre>\n
And the last one for now, in the EB_Libraries table, where we have 3 tables in the while select loop:<\/p>\n
public void<\/strong><\/span> update()\n{\n  EB_Shelves shelves;\n  EB_Racks racks;\n  EB_Aisles aisles;\n\n  \/\/ Only need to update the shelves if the library ID has changed<\/em><\/span>\n  if<\/strong><\/span>( this.LibraryId != this.orig().LibraryId )\n  {\n    \/\/ Call super() here so getPath() can see the updated value\n<\/em><\/span>    super<\/strong><\/span>();\n\n    while select forUpdate<\/strong><\/span> shelves\n        \u00a0 exists<\/strong> join<\/strong> <\/span>racks\n           where<\/strong> <\/span>racks.RecId         == shelves.RackRecId\n          exists<\/strong> join<\/strong> <\/span>aisles\n           where<\/strong><\/span> aisles.RecId        == racks.AisleRecId\n              && aisles.LibraryRecId == this.RecId\n    {\n      shelves.Path = shelves.getPath();\n      shelves.update();\n    }\n  }\n  else<\/strong><\/span>\n  {\n    super<\/strong><\/span>();\n  }\n}<\/pre>\n
To mass update all the existing shelves, copy\/paste and run the following job:<\/p>\n
static void<\/strong><\/span> EB_SetShelfPath( Args _args )\n{\n  EB_Shelves shelves;\n\n  ttsBegin<\/strong><\/span>;\n  while select forUpdate<\/strong><\/span> shelves\n  {\n    shelves.Path = shelves.getPath();\n    shelves.update();\n  }\n  ttsCommit<\/strong><\/span>;\n}<\/pre>\n
Preparing the reference group<\/h3>\n
Find the reference group in the EB_Books form and change the ReplacementFieldGroup property to the Path group. \u00a0The list of field groups are selected from the EB_Shelves table, because that’s what the ShelfRecId field refers to. \u00a0Expand the reference group and see how AX has replaced the 4 individual fields with just the new Path field.<\/p>\n
\"\"<\/a><\/p>\n
Open the form and see how the shelf is represented now:<\/p>\n
\"\"<\/a><\/p>\n
Nice and tidy…<\/p>\n
In closing<\/h3>\n
I had originally planned to also show how to automatically navigate to the currently selected item when the lookup is opened, but due to the length of this post, I’m splitting that into a separate post<\/a> rather than introducing a new concept and\u00a0another big piece of code here.<\/p>\n","protected":false},"excerpt":{"rendered":"
This is something that keeps coming up on different projects, so I’d like to share how I usually do this. \u00a0There are some examples out there which try to describe a very generic solution which can be used in almost any case. \u00a0However, such solutions very quickly become very complex. \u00a0To keep things simple, I … Continue reading “How-to: Tree lookups in Dynamics AX reference group controls, part 3”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,1],"tags":[2,3,5],"class_list":["post-213","post","type-post","status-publish","format-standard","hentry","category-dynamics-ax","category-uncategorized","tag-ax2012r3","tag-dynamics-ax","tag-user-interface"],"_links":{"self":[{"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/posts\/213","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/comments?post=213"}],"version-history":[{"count":0,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/posts\/213\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/media?parent=213"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/categories?post=213"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/tags?post=213"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}