Problem

For anyone that has dealt with creating queries in AX 2012 and D365FO, you know it can be extremely difficult to determine possible table relations between tables. To help with this Microsoft released an ERD for AX 2012 (this can still be used for D365FO), unfortunately they no longer maintain this project but do allow for others to host it themselves. This ERD helps to show the immediate relationships between tables, but what if there are no immediate relations between your tables in question and instead they need to go through intermediary tables? The process currently is to manually go through and try and find a common table (or set of tables) that tie your two tables together. This is where I thought I may be able to help by creating a solution to automate this manual process.

Step 1 – Obtaining the Table Relation Data

I started by looking at the ERD project itself, there are a set of files that start with ‘Fky…’, these files contain the table relation information we need to construct our solution. So as the first step of this project I wrote a command line application to parse these HTML files to extract the data we need into JSON files. These JSON files are what we will use in the next step and will act as the data source for our project.

The ERD files are separated into different categorizes, the ones we are interested in are the files with the ‘Fky’ name prefix. If we take a look at these files we can see that they store the relation data we are after:

If we look at the actual HTML source code behind this we can see the data is stored in an HTML table in a unstructured fashion, but it does follow certain patterns which allows us to build a parsing application.

After parsing the files we are able to generate two JSON files, one that stores the tables and one that stores the table to field associations:

Step 2 – Analyzing the Table Relation Data

Once I had the table relation data, the next step was to create an application to analyze this data to find table relations. The hardest part of the application was building the correct recursive function to find the table relations. I’m sure the logic I wrote can be simplified or can be sped up in some way but I am fairly happy that I was able to find a solution that is fairly stable in its memory usage.

To show this in action, if we select the CustTable and LogisticsPostalAddress table and select a Layers to search of 2, here are the results:

As you can see there are many ‘solutions’ this scenario as there are multiple different ways to get from the CustTable to the LogisticsPostalAddressTable. The solutions are separated by a line of ‘–‘.

One thing to keep in mind is that the results are sorted in the following order:

• Number of Layers
• First Child Table Name

Also the results here will be presented in the order of the joins required. So you should be able to read a solution from left to right and then go to the next row to find your next join.

One thing I will caution on is to be careful with the ‘Number of Layers to Search’ option, every additional layer you want to search adds in an exponential amount of time to get results. So I would recommend setting this as the lowest number possible to get results.

GitHub Link

https://github.com/ameyer505/MicrosoftDynamicsTableAssociations

YouTube Link

If you want to see this app in action here is a YouTube link: https://youtu.be/8045Vnp8WZY

Future Development

While this is a great first step I would like to add in some additional features including:

• Ability to export the solutions to Excel, CSV, JSON etc
• Ability to put in required ‘intermediary tables’ where a solution would have to require any number of tables in between the initial and destination

I would also like to hear back from others on what features and/or functionality they could see beneficial.

Conclusion

Hopefully you find this helpful and as always looking forward to your feedback!