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.
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.