How-to: Tree lookups in Dynamics AX reference group controls, part 3

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

Earlier, I have introduced the data model sitting behind the tree lookup, and last time I showed how to build the actual tree structure.

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.

Introducing a better reference

Remember from last time, how the books form has a multi-segmented control for the shelf reference:

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

First we create a new Extended Data Type called EB_ShelfPath:

Drag this new data type to the EB_Shelves table to create a new field:

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.

The reference group will need a field group as well, so we add a new field group containing only the Path field:

Note that we set the label to “Shelf”.  This will appear in the books form as the reference group’s label.

Keeping the path up to date

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

To support all of this, we’ll add a new method to the EB_Shelves table, called getPath:

public display EB_ShelfPath getPath()
{
  EB_Racks     racks;
  EB_Aisles    aisles;
  EB_Libraries libraries;

  select firstonly RackId
    from racks
   where racks.RecId     == this.RackRecId
    join AisleId
    from aisles
   where aisles.RecId    == racks.AisleRecId
    join LibraryId
    from libraries
   where libraries.RecId == aisles.LibraryRecId;

  return strFmt( "%1=>%2=>%3=>%4", libraries.LibraryId, aisles.AisleId, racks.RackId, this.ShelfId );
}

Quite simple, really.  We do a select of the ancestors of the current shelf, i.e. the rack, aisle and library.  For performance reasons we also use field lists to limit the amount of information retrieved.  (I’ll write a series of performance related posts in a few weeks, to explain how and why this improves performance)  Once we have the fields, we return a formatted path string.

Also override the insert() and update() methods to keep the path up to date:

public void insert()
{
  this.Path = this.getPath();

  super();
}

public void update()
{
  // Only update the path if the shelf ID has changed
  if( this.ShelfId != this.orig().ShelfId )
  {
    this.Path = this.getPath();
  }

  super();
}

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.  We don’t need to worry about the insert() method for these tables, as there will be no shelves at the time of insert.

First, override the update() method on the EB_Racks table:

public void update()
{
  EB_shelves shelves;

  // Only need to update the shelves if the rack ID has changed
  if( this.RackId != this.orig().RackId )
  {
    // Call super() here so getPath() can see the updated value
    super();

    while select forUpdate shelves
           where shelves.RackRecId == this.RecId
    {
      shelves.Path = shelves.getPath();
      shelves.update();
    }
  }
  else
  {
    super();
  }
}

And also on the EB_Aisles table, where we do the same but with a join to the racks:

public void update()
{
  EB_Shelves shelves;
  EB_Racks   racks;

  // Only need to update the shelves if the aisle ID has changed
  if( this.AisleId != this.orig().AisleId )
  {
    // Call super() here so getPath() can see the updated value
    super();

    while select forUpdate shelves
          exists join racks
           where racks.RecId      == shelves.RackRecId
              && racks.AisleRecId == this.RecId
    {
      shelves.Path = shelves.getPath();
      shelves.update();
    }
  }
  else
  {
    super();
  }
}

And the last one for now, in the EB_Libraries table, where we have 3 tables in the while select loop:

public void update()
{
  EB_Shelves shelves;
  EB_Racks racks;
  EB_Aisles aisles;

  // Only need to update the shelves if the library ID has changed
  if( this.LibraryId != this.orig().LibraryId )
  {
    // Call super() here so getPath() can see the updated value
    super();

    while select forUpdate shelves
          exists join racks
           where racks.RecId         == shelves.RackRecId
          exists join aisles
           where aisles.RecId        == racks.AisleRecId
              && aisles.LibraryRecId == this.RecId
    {
      shelves.Path = shelves.getPath();
      shelves.update();
    }
  }
  else
  {
    super();
  }
}

To mass update all the existing shelves, copy/paste and run the following job:

static void EB_SetShelfPath( Args _args )
{
  EB_Shelves shelves;

  ttsBegin;
  while select forUpdate shelves
  {
    shelves.Path = shelves.getPath();
    shelves.update();
  }
  ttsCommit;
}

Preparing the reference group

Find the reference group in the EB_Books form and change the ReplacementFieldGroup property to the Path group.  The list of field groups are selected from the EB_Shelves table, because that’s what the ShelfRecId field refers to.  Expand the reference group and see how AX has replaced the 4 individual fields with just the new Path field.

Open the form and see how the shelf is represented now:

Nice and tidy…

In closing

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 rather than introducing a new concept and another big piece of code here.