There are many scenarios in D365FO where knowing where data on a form is coming from is needed. A couple scenarios include trying to write custom reports or for tracking data changes using something like the Fastpath Audit Trail tool. There are a couple different options on how to accomplish this so I wanted to walk through them.
I’ve written about a similar topic to this in the past, but wanted to take a deeper dive into the different options for determining where data on a form is coming from.
Right Click -> Form Information
In most cases, this is the easiest and most straightforward approach. On any particular form, right click on the field you would like to see where data is coming from and go to Form Information. In the example below, I performed this on the Account field on a Customer form.
When I select the Form Information -> Form Name option, the Form Information dialog will be displayed from the right side of the screen. Within this dialog is information about where the data for this field is pulled from. In this case you can see the Data Source (SQL Table) is the CustTable and the Data Field (SQL Table Field) is AccountNum.
Note: You will need to be assigned the SysAdmin role for the Administration tab to show up on the Form Information dialog. For other options please look at this blog post: https://alexdmeyer.com/2021/05/03/how-to-allow-non-admin-users-to-access-security-features-in-d365fo/
When the Right Click -> Form Information Doesn’t Work
There are cases where the right click -> Form Information option listed above does not work. In this case we will need to go into the form designer in the AOT to find what the correct table and fields are.
In the scenario below, we are looking to determine where the ‘Contact Info’ data from a customer record is stored. If you try to use the right click -> Form Information option no dialog is displayed so we have to go to the AOT to continue our search.
The first thing to determine is what menu item or form are we currently on. In this case we know from our previous scenario that this is the CustTable form. If we open that up form in the AOT and navigate to the Contact Information area we can see this is actually a ‘grid form part’ which basically means that this form is embedding another form into itself. This is a very common scenario within D365FO and brings in the concept of database normalization as D365FO never wants to duplicate data if at all possible.
In this case, we can see it is using the CustomerLogisticsContactInfoGrid menu item display.
If we lookup this menu item display, we find that the form object this menu item opens in the LogisticsContactInfoGrid form. When we look at this form, we can find the different fields we see on the Customers form (Description, Type, etc)
To verify that this is the correct table and fields where the data is stored, let’s create a contact for a particular customer:
Now if we utilize the information from above and the AX 2012 Table ERD we can find how we can relate this customer record to this contact information record. In this case we have to go from the CustTable -> DirPartyLocation -> LogisticsElectronicAddress table. As you can see in this case there is not a direct link between the CustTable and the LogisticsElectronicAddress table. For our scenario there was only one intermediary table, but there can be multiple. Unfortunately, there is no map to show how to get from table A to table B so sometimes this is a somewhat manual process.
And if we want to combine this into a single query, we get:
Hopefully this shows a couple different options on how to determine where form data is actually stored within D365FO.
Did you know that further down in that form you will actually see the SQL query with all the tables and joins directly in the client?
This is a good point, for those unaware there is a Query Statement section within the Form Information dialog that shows the X++ query statement executed to fill in the data source.
I will say though that in some cases this query string can be extremely long and difficult to read/understand. For example, here is the query that populates the Customer form: