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

This is the first part of a multi-post series that explains how to do a tree lookup in a reference group control on an AX form.<\/p>\n

It’s simple, just look at those trees up there…<\/p>\n

Oh, seriously…<\/p>\n

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

I’ll show how to build the\u00a0lookup, how to load the tree items on demand to speed up the loading times as well as how to properly select and return the selected record. \u00a0I’ll also show some suggestions for how to build the reference group properly so it is easy for the user to understand the information presented to him\/her and to understand what to do.<\/p>\n

This first part of the series shows how to set up the tables and their relationships.<\/p>\n

<\/p>\n

To use an example which is easy to relate to, imagine a local council operating multiple libraries. \u00a0I’ll organise this into a structure like Library => Aisle => Rack\u00a0=> Shelf, each of which will be represented by a separate table. \u00a0(I know this is very analogous to the Dynamic AX site\/warehouse structure, but this is just choosing a simple example.)<\/p>\n

In addition to the tables representing the levels of the tree, there will be a table representing books and containing a reference back to the specific shelf a book can be found on.<\/p>\n

For clarity, I’ll keep things simple and only add the bare minimums to each table. \u00a0Things will get fairly repetitive as a result, as the data model is virtually identical at each level of the tree. \u00a0If you don’t want to create all of this manually, there will be an XPO in the last part of this series, containing the full set of objects discussed.<\/p>\n

As usual, use whatever object name prefix you need, according to your naming conventions.<\/p>\n

Libraries\u00a0table<\/h3>\n

This is the top-level table in the hierarchy.<\/p>\n

\"\"<\/p>\n

Add the following fields:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

We’re going to need an index to use as a replacement key, so let’s add one on the LibraryId field. \u00a0Setting this as replacement key will also update the AutoIdentification field group to match the index keys.<\/p>\n

\"\"<\/p>\n

To support links from the following table back to this one, add a new Extended Data Type (EDT), int64 type. \u00a0The EDT needs a table reference to the RecId field in this table.<\/p>\n

\"\"<\/p>\n

That relation needs to be backed by an index, so make sure there is an index on RecId, and that it is the primary key for the aisles table. \u00a0The index-related properties on the table should look like this:<\/p>\n

\"\"<\/p>\n

Aisle table<\/h3>\n

This table describes the aisles within each library.<\/p>\n

\"\"<\/p>\n

Add the following fields:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

We need a relation on LibraryRecId to point to RecId in EB_Libraries. \u00a0This will be added automatically when adding the LibraryRecId field, based on the table relation specified on the EDT. \u00a0If you missed this, or clicked “No” on the prompt, add one manually now.<\/p>\n

\"\"<\/p>\n

We’re going to need an index to use as a replacement key, so let’s add one on the AisleId field. \u00a0However, there can be multiple aisles with the same ID as long as they are in different libraries, so we’ll need LibraryRecId as the leading key. \u00a0Setting this as replacement key will also update the AutoIdentification field group to match the index keys.<\/p>\n

\"\"<\/p>\n

To help speed up queries, we’ll make sure all aisles for a library are stored next to each other in the database, so we’re going to make this the clustered index for the table as well.<\/p>\n

To support links from the following table back to this one, add a new Extended Data Type (EDT), int64 type. \u00a0The EDT needs a table reference to the RecId field in this table.<\/p>\n

\"\"<\/p>\n

That relation needs to be backed by an index, so make sure there is an index on RecId, and that it is the primary key for the aisles table. \u00a0The index-related properties on the table should look like this:<\/p>\n

\"\"<\/p>\n

Racks table<\/h3>\n

This table describes the racks within each aisle.<\/p>\n

\"\"<\/p>\n

Add the following fields:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

We need a relation on AisleRecId to point to RecId in EB_Aisles. \u00a0This will be added automatically when adding the AisleRecId field, based on the table relation specified on the EDT. \u00a0If you missed this, or clicked “No” on the prompt, add one manually now.<\/p>\n

\"\"<\/p>\n

We’re going to need an index to use as a replacement key, so let’s add one on the RackId field. \u00a0However, there can be multiple racks\u00a0with the same ID as long as they are in different aisles, so we’ll need AisleRecId as the leading key. \u00a0Setting this as replacement key will also update the AutoIdentification field group to match the index keys.<\/p>\n

\"\"<\/p>\n

To help speed up queries, we’ll make sure all racks for an aisle are stored next to each other in the database, so we’re going to make this the clustered index for the table as well.<\/p>\n

To support links from the following table back to this one, add a new Extended Data Type (EDT), int64 type. \u00a0The EDT needs a table reference to the RecId field in this table.<\/p>\n

\"\"<\/p>\n

That relation needs to be backed by an index, so make sure there is an index on RecId, and that it is the primary key for the aisles table. \u00a0The index-related properties on the table should look like this:<\/p>\n

\"\"<\/p>\n

Shelves table<\/h3>\n

This table describes the shelves in each rack.<\/p>\n

\"\"<\/p>\n

Add the following fields:<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

\"\"<\/p>\n

We need a relation on RackRecId to point to RecId in EB_Racks. \u00a0This will be added automatically when adding the RackRecId field, based on the table relation specified on the EDT. \u00a0If you missed this, or clicked “No” on the prompt, add one manually now.<\/p>\n

\"\"<\/p>\n

We’re going to need an index to use as a replacement key, so let’s add one on the ShelfId field. \u00a0However, there can be multiple shelves with the same ID as long as they are in different racks, so we’ll need RackRecId as the leading key. \u00a0Setting this as replacement key will also update the AutoIdentification field group to match the index keys.<\/p>\n

\"\"<\/p>\n

To help speed up queries, we’ll make sure all racks for an aisle are stored next to each other in the database, so we’re going to make this the clustered index for the table as well.\"\"<\/p>\n

To support links from the books table back to this one, add a new Extended Data Type (EDT), int64 type. \u00a0The EDT needs a table reference to the RecId field in this table.<\/p>\n

\"\"<\/p>\n

That relation needs to be backed by an index, so make sure there is an index on RecId, and that it is the primary key for the aisles table. \u00a0The index-related properties on the table should look like this:<\/p>\n

\"\"<\/p>\n

Demo data<\/h3>\n

We need some demo data, which we can create through a simple job.<\/p>\n

static void<\/strong><\/span> EB_FillLibraries( Args _args )\n{\n  EB_Libraries libraries;\n  EB_Aisles    aisles;\n  EB_Racks     racks;\n  EB_Shelves   shelves;\n  int<\/strong> <\/span>l, a, r, s;\n\n  container<\/span> <\/strong>conLibraries = [ \"Top Ryde\"<\/span>, \"Eastwood\"<\/span>, \"West Ryde\"<\/span> ];\n  container <\/span><\/strong>conAisles    = [ \"A1\"<\/span>, \"A2\"<\/span>, \"A3\"<\/span>, \"A4\"<\/span>, \"A5\"<\/span>, \"A6\"<\/span> ];\n  container<\/span> <\/strong>conRacks     = [ \"R1\"<\/span>, \"R2\"<\/span>, \"R3\"<\/span>, \"R4\"<\/span>, \"R5\"<\/span>, \"R6\"<\/span>, \"R7\"<\/span>, \"R8\"<\/span> ];\n  container<\/span> <\/strong>conShelves   = [ \"S1\"<\/span>, \"S2\"<\/span>, \"S3\"<\/span>, \"S4\"<\/span> ];\n\n  ttsBegin<\/strong><\/span>;\n\n  for<\/span><\/strong>( l = 1<\/strong><\/span>; l <= conLen<\/strong><\/span>( conLibraries ); l++ )\n  {\n    libraries.clear();\n    libraries.LibraryId = conPeek<\/strong><\/span>( conLibraries, l );\n  \u00a0 libraries.Name      = libraries.LibraryId;\n    libraries.insert();\n\n    for<\/strong><\/span>( a = 1<\/strong><\/span>; a <= conLen<\/strong><\/span>( conAisles ); a++ )\n    {\n      aisles.clear();\n      aisles.LibraryRecId = libraries.RecId;\n      aisles.AisleId      = conPeek<\/strong><\/span>( conAisles, a );\n      aisles.Name         = strFmt<\/strong><\/span>( \"Aisle %1\"<\/span>, aisles.AisleId );\n      aisles.insert();\n\n      for<\/strong><\/span>( r = 1<\/strong><\/span>; r <= conLen<\/strong><\/span>( conRacks ); r++ )\n      {\n        racks.clear();\n        racks.AisleRecId = aisles.RecId;\n        racks.RackId     = conPeek<\/strong><\/span>( conRacks, r );\n        racks.Name       = strFmt<\/strong><\/span>( \"Rack %1\"<\/span>, racks.RackId );\n        racks.insert();\n\n        for<\/strong><\/span>( s = 1<\/strong><\/span>; s <= conLen<\/strong><\/span>( conShelves ); s++ )\n        {\n          shelves.clear();\n          shelves.RackRecId = racks.RecId;\n          shelves.ShelfId   = conPeek<\/strong><\/span>( conShelves, s );\n          shelves.Name      = strFmt<\/strong><\/span>( \"Shelf %1\"<\/span>, shelves.ShelfId );\n          shelves.insert();\n        }\n      }\n    }\n  }\n\n  ttsCommit<\/strong><\/span>;\n}<\/pre>\n
Copy\/paste this into a new job and run it. \u00a0It will create identical layouts for each library but it’s good enough for this purpose.<\/p>\n
Books table<\/h3>\n
This table describes books, which can be on any shelf within the whole library.<\/p>\n
\"\"<\/p>\n
Add the following fields:<\/p>\n
\"\"<\/p>\n
\"\"<\/p>\n
We need a relation on ShelfRecId to point to RecId in EB_Shelves. \u00a0This will be added automatically when adding the ShelfRecId field, based on the table relation specified on the EDT. \u00a0If you missed this, or clicked “No” on the prompt, add one manually now.<\/p>\n
\"\"<\/p>\n
As we’re not going to have anything referencing the books table, we’re not adding any indexes or replacement keys. \u00a0What we will do, however, is to add a form.<\/p>\n
Add a simple form containing EB_Books as a datasource and a single grid set to fill the entire form. \u00a0The grid should use EB_Books as its datasource, and contain two fields, ShelfRecid and BookId. \u00a0Let’s call the form EB_Books.<\/p>\n
Notice how AX automatically replaced ShelfRecId with a reference group containing the replacement keys for each hierarchy level all the way up to LibraryId.<\/p>\n
\"\"<\/p>\n
Quite helpful, actually. \u00a0Let’s see what it looks like.<\/p>\n
<\/a><\/p>\n
OK, it shows each field in the hierarchy.
\nThe drop-down only allows to select the Shelf level, and there are no drop-downs on any of the other levels. \u00a0And… \u00a0Every single shelf record is displayed. \u00a0 \u00a0 Uuuughhh….<\/p>\n
To solve this, we need a tree lookup, and that’s probably why you’re reading this anyway. \u00a0I’ll get back to that in the second part of the series<\/a>.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
This is the first part of a multi-post series that explains how to do a tree lookup in a reference group control on an AX form. It’s simple, just look at those trees up there… Oh, seriously… This is something that keeps coming up on different projects, so I’d like to share how I usually … Continue reading “How-to: Tree lookups in Dynamics AX reference group controls, part 1”<\/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-66","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\/66","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=66"}],"version-history":[{"count":0,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/posts\/66\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/media?parent=66"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/categories?post=66"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bakke.online\/index.php\/wp-json\/wp\/v2\/tags?post=66"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}