There are many instances where you need to know exactly which table field is tied to an entry on a form:
- Interested in applying table field level security
- Want to turn on some type of audit trail tracking
So how do we go about doing this?
Right Click -> Form Information
The easiest method I have found is to right click on the form field (or in some cases the form field label) and going to Form Information -> Selecting the Form Name option. This will cause a dialog to appear out of the right side of the screen that has additional information. If you are assigned the SysAdmin role you will have an Administrative menu option, under there will be a Data Source and Data Field parameters that will tell you the table and field that ties to that form entry.
For example, if I go to a Customer and want to see which field ties to the Account field:
Now you are shown the ‘Form Information’ dialog and you can see this field is tied to the CustTable -> AccountNum.
Sometimes It’s Not That Easy
While the above solution seems like it is fairly straightforward not every situation is that easy. The reason for this is the database normalization that occurs within AX/D365FO. This basically means that data is not duplicated in the database and that some information about an object may exist on a separate table. A simple example of this are names or addresses of objects, if we continue to use the customer scenario from above you may think that the name and address of a customer exist on the CustTable as well. But because of database normalization this is not the case.
If we perform the same process as above on the Customer Name field you can see that the customer name is actually stored on the DirPartyTable -> Name field.
And if we do the same for the Address we can see this field is actually on the LogisticsPostalAddress -> Address field.
How Do We Tie All of These Tables Together?
When data about a base object is stored in other tables normally the connection between tables is done via RecIds, but how do we determine what the connection between the table actually is? One option is to use an entity relationship diagram or ERD. There are currently no ERDs for D365FO but there is one for AX 2012 which works for most objects. Microsoft used to host these files themselves but retired it some time ago, instead now they offer the source files on their GitHub for others to host. I actually host the AX 2012 ERD on my website and use it frequently.
If we look at trying to find the connection between CustTable and DirPartyTable we can use the ERD to see there is a connection between the CustTable -> Party field and DirPartyTable -> RecId
In SQL, a query to show the account number and name of customer would look like this:
Now if we want to see how the CustTable and LogisticsAddressLocation tables you will notice that there is no direct connection between these tables. Instead we have to go through a couple different tables to find the connection. Since we already have a connection between the CustTable and DirPartyTable we can utilize this to see that the DirPartyTable can connect to the LogisticsLocation and this table can connect to the LogisticsPostalAddress table.
In SQL, a query to show this will have multiple JOINS to be able to get the address:
While looking at this query you may notice that we can simplify this query slightly as the LogisticsLocation RecId field is used to connect the DirPartyTable and LogisticsPostalAddress tables:
What if the Form Information Method Doesn’t Work?
There are times where the right click -> Form Information method does not work, either the dialog does not appear (I have not been able to determine a reason why this happens) or you may run into a scenario where the table and/or field is not filled in on the Form Information dialog. This happens quite often with Financial Dimensions or certain parameters on objects. For example on the Main Account form there are many parameters on the object you can set, one is the ‘Do Not Allow Manual Entry’ checkbox. Where does the value for this checkbox come from?
Well if we try our right click -> Form Information method from above we are met with this:
We can see in this case that the Data Source is listed as the DimensionAttributeValue table but no Data Field is listed, also we can see that there is a Form Control (Administration_IsBlockedForManualEntry).
In cases like these, the only real way to determine the actual table field is to jump into the actual code to see how this is being populated. So let’s go to the MainAccount form and look for a control named ‘Adminsitration_IsBlockedForManualEntry’. Once we find this we can see there is a Data Method that is actually tied to this control, ‘dimensionAttributeValueIsBlocked’.
If we search for that particular method in the form code we can see that this value can actually come from two separate tables depending on how this method is called. It will either be from the corresponding entry in the DimensionAttibruteValue table -> IsBlockedForManualEntry field for this main account or from the MainAccountTemplate table -> IsBlockedForManualEntry field that is tied to this main account.
Conclusion
I hope that this helps answer questions on how to determine which table field ties to a form entry. For me, I normally start with the right click -> Form Information method described and if that doesn’t work head to the form’s code to see how the value is being determined.
Hello, I am trying to give a user the right to see “Form Information” to find data sources and control names.
I know you can use security diagnostics to see what rights are necessary, but I can’t find a way to see the security diagnostics for a popup on the right.
Bill,
If the ‘Form Information’ option does not work, the options are:
1) Create a task recording of someone opening the form and then use something like the ‘Security Diagnostics for task recordings’ to analyze the task recording and see which objects are consumed
2) Dive into code / Visual Studio and see which form(s) are being utilized and which menu items those tie to
Hi Alex,
I am trying to determine which Data Entities specific D365 fields belong to. I don’t have acces to Visual Studio. Is there a way to do this in data management?
Thanks
Dan
Dan,
Not that I am aware of, the data is only available via metadata which can be accessed through Visual Studio and/or via X++ code.
If you have a specific field I can do a lookup for you, otherwise I can look at adding this functionality to the Data Entity Excel file I came up with in the blog post I sent previously.
On Vendor details form there is a field “Duns Number”, I am trying to determine in which table the value which i entered was saved. I could not a column in DirPartyTable, but some how i see the data exists in “dirorganizationbase” table and to the surprise I cannot find this table in the sql db, but I am able to open in Table browser and I can see the value which I have entered. I need help on how to identify these kind of fields and for duns number what is the relation with vendtable so that i can extract this field.
Vinodh,
Here is the process I used, the easiest way to do this is via the AOT.
Navigate to the VendTable form and find the DUNS field:
We can see that this field is controlled by DirPartyTable_DirOrganizationBase datasource and the editDunsNumber data method.
The DirPartyTable_DirOrganizationBase data source is tied to the DirOrganizationBase data source.
On the DirOrganizationBase table, you can find the data methods:
This shows that this value comes from the DirDunsNumber table and the DunsNumber field.
Thank you, it was very helpful.
I also got stuck on how to find a query behind a particular screen. Example- In Accounts Payable we have “Vendors Past Due” screen, I am able to find the query by right clicking on the screen and in the form properties, but when i execute that query in SQLServer(after converting to TSQL) i am getting like 240 rows while on the screen i see only 10 rows. How can I check what filters/parameters being applied on the dataset, I want to get the exact query for that screen along with the filters/parameters.
Vinodh,
I would assume this is because of a company / legal entity filter, most master data within D365FO is stored on a per company basis and by default all X++ queries are done within the current company context unless the ‘crosscompany’ keyword is added to the query.
This does not apply to queries ran within SSMS.
But if you want to see what queries are being executed for a particular form I would look at using trace parser and then analyzing the output: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/perf-test/trace-parser