Behind the Scenes: Understanding Data Entity & Table Mapping for D365 F&O Form Fields
March 5, 2024
When working with data in various forms in D365 F&O, knowing the table or data entity name where the data is stored can assist in a variety of functions. From extracting data for further analysis to being able to mass update records through a data management import, knowing how to access this data "behind the scenes" is a valuable tool.
It is important to note that different fields on the same form can live in different tables and different data entities. Form fields can also live in more than one data entity. It is important to note that not all users can utilize this method to its fullest potential. System Admins will have the most access to see all table names, use SysTableBrowser, and see all data entities. With that in mind, let's take a look at how to determine the table name or data entity for a form field.
Determine Table Name
To find the table name of a field on a form in D365 F&O, follow these steps:
Open the Form:
Navigate to the form you want to identify the underlying table.
Right-Click on the Field:
Locate the specific field for which you want to determine the table name.
Right-click on that field.
Access Form Information:
From the context menu, select “Form Information”.
Click on "Form Name: (form name here)" to open the dialog
View Control Information and Field Details:
A dialog will appear on the right side of the screen.
Look for the “Control Information” section in the "Administration" tab of the dialog.
Here, you’ll find the following information:
DataSource: This is 9 times out of 10 going to be the same as your table name.
Check the table using SysTableBrowser to see if you have the correct table name.
If the Data source is not the table name (no data in SysTableBrowswer), sometimes looking for table names in the Query statement can help identify additional table names.
View Form Name and Field Details:
While we are here--here is a little more information that is always very helpful!
Look for the “Form Name” section in the "Administration" tab of the dialog.
Here, you’ll find the following information:
Form Name: The name of the form.
Menu Item Name: The name of Menu item
⭐This is super helpful for building custom security!
Menu Item Type: Is this a display, action, or output menu item
⭐Also very helpful when building out custom security
If the Data source is not the table name (no data in SysTableBrowswer), sometimes looking for table names in the Query statement can help identify additional table names.
If you need a refresher on using SysTableBrowser, refer to my post here.
Determine Data Entity
To find the table name of a field on a form in D365 F&O, follow these steps:
Open the Form:
Navigate to the form you want to identify the underlying data entity/entities.
Select "Open in Microsoft Office"
In the top right of your form, select "Open in Microsoft Office".
Review the data entity listing
Review the list for entities that are available for the data on the form.
You may need to do a little hunting to find the right entity for the field you want to extract/review, but remember it may appear in more than one entity.
Review target fields in Data Management
Utilizing data management or opening the entity in excel will allow you to see what fields are available in the entity
Using the Data
Once you have the data you need you can do so many things. Both data entity connections and table extracts can be used to build out pivot tables, power query or other ad hoc reporting as needed. Occasionally you may need to join tables together by connections on a common field.
Whether you are trying to quantify an issue, build out a report, or find where an item was used, enjoy the search. Each table and data entity you use is helping to understand the data architecture in D365 F&O!
Have questions about data behind the scenes of D365 F&O? Send me a message and I will be in touch!
Don't forget to subscribe to my newsletter to get exclusive content and stay up to date with all my latest posts.