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.
If you want to see this app in action here is a YouTube link: https://youtu.be/8045Vnp8WZY
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.
Hopefully you find this helpful and as always looking forward to your feedback!
Very nice. Can we produce our own .json files as data source somehow?
If you want to modify the JSON files in the application you can feel free as long as they follow the same structure that is currently there.
Hey Alex. Thanks for you reply. I was mostly thinking about generating customer specific .json files. They may have customizations with extra relations.
The tablefieldassociations JSON file was created from the static AX 2012 ERD provided by Microsoft. There is no way (that I know of) to dynamically create this for a customer’s environment. Any changes to this file would have to be manually done.
Thank you for producing this. I’ve got this built on my Windows 11 PC and the prerequisite .net 3.1 download – but it doesn’t appear to be able to connect to your ERD resources. Has anything changed recently?
I’m currently trying to work out the common joins between BudgetTransactionHeader, Ledger, CompanyInfo, SystemParameters, FiscalCalendar, FiscalCalendarPeriod, DimensionAttributeValueCombination and MainAccount. Likewise for GeneralJournalAccount Entry, GeneralJournalEntry and Ledger. But no luck so far.
Really wish Microsoft would publish ERDs again 🙂
The ERD data is included in the actual solution in the tablefieldassociations.json file and does not ‘reach out’ to any external sources.
Keep in mind that this solution finds joins between two tables, so if you have a listing of tables you want to join to you will have to do them 2 at a time. For example, between the BudgetTransactionHeader and Ledger tables I was able to find these solutions:
Feel free to reach out to my email with additional questions: email@example.com