Dynamics 365 FO - Data Model

 Dynamics 365 FO - Data Model

Nghia Song -  Microsoft Dynamics 365 Technical Consultant

Nghia Song

Tel - WhatsApp: +84967324794

Email: songnghia.uit@gmail.com

1. Data Model

1. Tables

The Microsoft Dynamics 365 SQL database created during installation contains the complete table repository. Tables are at the core of the Microsoft Dynamics 365 application and enable storage of a company's information and transactions.

Tables storing data about company transactions, inventory, general ledger, journals and more, form the base of the Microsoft Dynamics 365 application.

Other tables in Microsoft Dynamics 365 contain metadata. These tables contain descriptions of data. Metadata is housed in system tables.

System tables are differentiated from application tables by the Sys prefix that starts the name of the table. The following figure shows examples of system and application tables.

Microsoft Dynamics 365 Table Structure and Components 

Tables in Microsoft Dynamics 365 contain multiple elements that define the table. There are seven categories that define tables: 

• Fields 

• Field Groups 

• Indexes 

• Full Text Indexes 

• Relations 

• Delete Actions 

• Methods

The following figure shows these elements:

1.1 Fields

Access the Fields node using MorphX to create or modify the physical columns in the database table. The fields defined within the Fields node are the columns belonging to the physical table.

CAUTION: Any fields created, deleted, or modified using MorphX in the Table node are reflected in the database when the changes are saved. This can potentially damage the Microsoft Dynamics 365 application if not used correctly.

Display all the fields for the LogisticsAddressZipCodetable by opening the Fields node. The following figure shows how MorphX is used to view all the columns within a Microsoft Dynamics 365 table.

1.2 Field Group

Field groups in Microsoft Dynamics 365 are logical groupings of physical database fields. Microsoft Dynamics 365 uses Field Groups to cluster fields into logical groups so that these groups can be used on forms and reports. An example of such a group is the group Measurement located on InventTable. InventTable holds data relating to products, and the measurement fields are height, width, depth and density of the products.

NOTE: If a modification is made to a field group, application elements that refer to the field group are automatically updated to reflect the new content of the group. 

IntelliMorph is the term given to the technology that adjusts the layout of forms and reports which use the modified field group. Adding new fields to a field group can be a powerful technique for updating forms that use field groups with a new field.

BEST PRACTICES: Define a group when several fields logically belong together and are shown together on forms. Every field on a table should belong to a group.

1.3 Index

The indexes in the Microsoft Dynamics 365 table definition are the physical indexes that exist on the tables in the database. Indexes accelerate a search on a table's data

1.4 Full-text Indexes

A full-text index contains location information about each significant word in a string field of a table. Some queries canuse this information to run more efficiently and to complete more quickly. These are queries that search for words embedded in the middle of string fields. 

A full-text index can be created by creating a new full-text index under the full text index node on the table, and adding fields to this new full-text index. 

Queries can use the full-text index by creating a query range of type Full-Text Index. If the value in that range has multiple words, the space is treated like a boolean OR, so any records with at least one of the words in the full text indexed fields will be returned. 

X++ select statements cannot use a full-text index.

1.5 Relations

Microsoft Dynamics 365 uses a relational database, which means that relationships can be created between tables that contain related data. In Microsoft Dynamics 365, the relationship between tables is called a relation.

1.6 Delete actions

Microsoft Dynamics 365 uses a category of methods called Delete Actions to govern how data is handled when a deletion event occurs in a parent table. There are four types of delete actions:

• None: The row in this table is deleted but nothing occurs in the specified table. 

• Cascade: The row in this table is deleted and all related data in the specified table is also deleted. This ensures that no orphaned records remain after a deletion.

• Restricted: The row in this table is only deleted if there is nothing that relates to it in the specified table. This is used when a parent record should only be deleted when no child record exists, and the child record is important to keep, for example, transaction records. 

• Cascade + Restricted: Behaves the same as Restricted.

NOTE: It is important to create delete actions and to create them correctly to ensure data integrity.

1.7 Method

Microsoft Dynamics 365 uses methods within the table node to perform functions specific to the table. This can be data lookups to other tables based on data in that table, or manipulation of the data during specific event

1.8 Inheritance

A table can extend another table, by specifying the Extend property on the table. This means one table inherits all of the fields, properties and methods of the table that it extends. New fields and methods can be added to this extending table that will not be visible from the extended table.

2. Practice

Procedure:

In this practice we will create 2 tables NVWEmployee and NVWDepartment and make relation 2 tables

2.1 Create Tables

Step	Screen shots	Description
0		Start Visual Studio using Run as administrator.
1		Right click project > Add > New Item
2		On the property, modify label
3		On the project or Application Explorer, drag Edt and Enum to fields
4		Build and Sync project
		TIP: You can right click table design and open table brower to see design of table Also for Dev Enviroment, you can open SQL Server to check the design of table

2.2 Create Indexes and set primary Index

Indexes in databases are used to locate records. They are stored separately in the database and contain a key that can be quickly located in the index, and a reference to the record. For example, the CustIdx index of the customer table contains the account number of the customer. The account number is unique and sequenced. This is then used to quickly look up the customer record through the reference. 

There are two types of indexes: unique and non-unique. If a unique index is created based on a column, Microsoft Dynamics 365 ensures no duplicate key values occur in that column. Also, updates on a column that contain a unique index, which violates the uniqueness of the column, cause an error. 

Non-unique indexes are created for performance reasons. They provide a quick way of retrieving data, instead of performing a full-table search of all the records in a table. Do not create an index if it is not needed because they use system space and must be updated every time system data is created, edited, or deleted. This can slow down the updating process. However in most cases the performance gain when selecting records far outweighs the performance loss when updating. When an Index is created, it is recommended to give the Index a name reflecting the names of the fields in the index, followed by the suffix Idx.

Creating Indexes 

Creating an Index in Microsoft Dynamics 365 always relates to the table where it is created. The index can only be created on fields that exist in the table. These indexes can be unique or non-unique, and are based on a single column or multiple columns that exist within the table.

Step	Screen shots	Description
0		Start Visual Studio using Run as administrator.
1		Right click indexes and click New Index
2		Modify the Name of new index
3		Drag field to make it become member of key
4		Build and Sync project

Create Primary Index

A primary key is one or more columns that uniquely identify one record in a table from all the other records.

A primary index is a unique index for a table that defines the primary key for that table. To set an index as a unique index, set the index property Allow Duplicates to No.

Step	Screen shots	Description
0		Start Visual Studio using Run as administrator.
1		Right click properties of table and modify the Primary Index
2		Build and Sync project

2.3 Create a Relation

Create relation 

Relations between tables are used to associate rows in one table to rows in another table. 

Relations are also used to: 

• Enforce business rules across tables 

• Create auto joins in forms (join one table to another table). 

• Look up values in other tables (using lookups and the View Details command). 

• Validate data by providing constraints. 

• Automatically propagate changes from one table to another by defining cascading relations. 

• Generating field help (hover over a field, and data from the related table is displayed). Relationships can be created between tables using MorphX. 

• These relations are defined within the Microsoft Dynamics AX application and not at the physical database level. 

• Relations govern the relationships between data in different tables such as foreign key relationships. 

• Foreign key relationships between tables are always formed from the parent table.

Step	Screenshots	Description
0		Start Visual Studio using Run as administrator.
1		In table NVWEmployee make realtion to NVWDepartment. Right click Relation and select New > Relation
2		Modify Name and Related Table
3		Right on the new relation and select New > Normal
4		Modify Field, Related Field to related table
5		Build and sync

Next: Dynamics 365 FO - User Interface
https://www.songnghia.com/2022/09/dynamics-365-fo-user-interface.html