One of the first tasks you need to do to create a new form in Microsoft Dynamics 365 F&O is to create a new table. In this article I will show you how to create a table in D365. The minimal number of steps to create a table are quite simple. However, I will also show you all of the things you should do to make sure your table is following best practices. It is here that I think many people may learn something that they did not know before.
Create A D365 Project And Solution
First, in your D365 environment, go to Windows Start and type ‘Visual Studio’. Right click on Visual Studio and select ‘run as administrator’. It is actually important when doing development that you run Visual Studio as administrator.
Select File>New>Project. In the dialog that opens, select Dynamics 365 on the left hand side. Then select Finance Operations as the project type.
Note: If you do not see this option, you most likely do not have this project type installed. I recommend working in a Life Cycle Services Development environment, which will already have this installed and setup.
Enter in a name for your project, and check the ‘Create directory for solution’ checkbox. You do not need to check the ‘Add to Source Control’ checkbox, but you can if you have already setup your source control.
Next, right click on your project, and select Properties. In the dialog that opens, set the ‘Model’ to your custom model, if you have one. Also set the ‘Synchronize Database on Build’ property to True. This is needed when creating or modify tables, so that the changes you make in Visual Studio get synched to the actual SQL Server Database.
Create A Table
Next I will show you how to create a table. Right click on your project, and select Add>New Item. In the dialog that opens, select ‘Table’, and provide a name for the table. In my example, I will call my table TutCar. The Tut is short for tutorial. Then press Ok.
Until we actually build the solution, the table, and any fields we add, will not be created in the SQL Server Database. But lets add some fields first.
How To Add Fields Using Extended Data Types
Part of learning how to create a table, is learning how to add fields. In my example I want to create a table that has two fields to start. One field named CarId, and another field named Description.
First, I need to decide what type of data these fields will contain. In this example, both fields will contain characters, so I will create two ‘string’ fields.
Whenever you create a new field, it is common, but not required for the field to have the ‘Extended Data Type’ property set. An Extended Data Type (EDT) is another object setup in the AOT. It would require a whole other article to fully explain this, so I will be brief here.
An EDT allows you to associate a label, help text, and other useful properties to a base data type. The base data types are string, integer, integer64, real (this is for numbers with decimals), datetime, enum, and a few more. Creating an Extended Data Type (EDT) allows developers to specify properties on that EDT that will be used by all fields that use that EDT.
For example, the ItemId Extended Data Type, has a label of ‘Item number’. There are many fields throughout the system that use this EDT, and therefore all the forms that show those fields show the text ‘Item number’. If someone wanted to change this text to instead be ‘Item ID’, they could change the label used by the EDT, and it would change the text that is shown on all of those same forms.
Similarly, if a developer needed the length of this string to be 30 characters instead of 20 characters, they could make this change to the EDT, and all fields that use this EDT would be changed. This is incredible powerful and are a key part of how to create a table.
Deciding When To Create A New EDT
To create the two fields in our example, we need to first decide whether you should create a new Extended Data Type for this field, or if you should use an existing Extended Data Type.
There is not a hard and fast rule on this, but I will try to generalize. If the data you plan to put in the field exists somewhere else in the system, you should re-use the existing Extended Data Type.
- When you need to add the field ItemId to your table, this data already exists in the system in the table InventTable, in the field ItemId. That field uses the extended data type ItemId, so you should set the Extended Data Type property on your field.
- If you add a field that has a new purpose in the system, you should create an Extended Data Type for it.
- Lastly, if the field you are adding is generic in nature, such as a Description, and you would use the same label as an existing EDT that is used generically, then you can reuse an existing EDT. Or you can still choose to create a new Extended Data Type.
Add New Fields To The Table
When you know you are creating a field with a new Extended Data Type, it is more efficient to create the EDT first, then drag it to your table. Rather than create the field on the table, create the EDT, then set the Extended Data Type property on the field. Let’s continue with our example.
Right click on the Visual Studio Project, and select Add>New Item. Select ‘EDT String’. Set the name to ‘TutCarId’. Then click ‘Add’.
Right click on the EDT, and select Properties. While there are many properties we can set on an Extended Data Type, the main ones for a string are Label, Help Text, and String Size. Set Label to ‘Car Id’. Enter ‘The car unique identifier’ for Help Text. And leave the String Size at 10 characters long.
Note: It is a best practice when entering text to use a label file. I will not include those steps here to allow this tutorial to be shorter.
Open the TutCar table designer by double clicking on it. Drag the EDT TutCarId into the ‘Fields’ node on the table designer. This will create the field and set the Extended Data Type Property on the table all in one step. Rename the field to be just CarId.
Right click on the ‘Fields’ node and select New>String. Rename the field that is created to ‘Description’. Right click on the field and select ‘Properties’. Set the Extended Data Type property to ‘Description’, which is an existing EDT.
Set The Field Groups On The Table
At this point, you have now learned how to create a table. However, there are still several other nodes on the table designer that should be set in order to follow best practices. The first section is named ‘Field groups’. There are multiple types of field groups. I will explain two that are commonly used.
The ‘AutoReport’ field group is used to generate automate reports. These can show data that is in your table. Drag all fields from the ‘Fields’ node into the ‘AutoReport’ node that would be helpful to show on this report. For this example, drag both fields into this field group.
The ‘AutoLookup’ field group allows developers to specify which fields should show in lookup forms of this table. I will cover this in more detail in a later article. Typically the unique identifier field, and any description fields should be added to this field group. For this example, drag both fields into this field group.
Next, we will create our own field group. Right click on the ‘Field groups’ node and select ‘New group’. Rename the field group to something like ‘Identification’ or ‘All’. Then drag the two fields into this field group. Right click on the field group, and select Properties. Set the label property on the field group.
The main purpose of learning how to create a table, is so that we can use that table as a datasource in a form. Creating a field group on the table allows grid controls on forms to specify the field group, in the ‘data group’ property on the grid. Setting this property then automatically adds all of the controls for each field in the field group. This is much easier than creating the controls by hand. It also makes it very simple to add additional fields later. A developer just needs to add the field to the field group, and it will also be added to the grid that uses that field group. I will cover this in more detail in a later article.
Set Indexes On The Table
There are two main purposes main reasons for adding indexes to a table.
- Adding indexes will increase the performance of retrieving data from the table. Essentially if you have a ‘select’ statement written in x++, then the same fields that the statement references from the table to be able to retrieve records, should be fields in an index on the table. You may need to have multiple indexes on a table to cover different ways data in your table is selected.
- Adding a unique index to a table will cause an error to be thrown, and the process stopped, if a user or process tries to add a second record with the same values. Most tables rely on there being unique values in one or more fields, and indexes help enforce this.
Right click on the ‘Indexes’ node and select ‘New Index’. Drag the ‘CarId’ field into the new node that is created. Rename the index to something more readable. In this case, I named the index ‘CarIdx’. Right click on the CardIdx node, and select Properties. Set the ‘Allow Duplicates’ node to ‘No’. This makes is so if a user tries to insert a record with a value in the CarId field that already exists, the system will stop the process and throw an error.
Build the solution, to cause the system to create the table, fields, and index. Right click on the top table node ‘TutCar’ and select Properties. Set the ‘Primary Index’ property to the index ‘CarIdx’. Set the Label property to ‘Car’. Select ‘CarId’ in the ‘Title Field1’ property. And ‘Description’ in the ‘Title Field2’ property. These fields can affect what shows on the caption of a form that uses this table.
Additional Nodes and Properties
There are quite a few nodes and properties on a table. And this article was not intended to cover all of them. I will just make mention of a few other important nodes and properties when learning now to create a table.
The Relations node is very important if the fields on your table relate to fields on another table. For instance, had we added ItemId to our table, we would want to add a relation to the InventTable.ItemId field.
The Delete Actions node allows the system to delete records on related tables, when records on the current table are deleted. This helps make sure all related data is deleted, and that there don’t exist orphan records in the system that cannot be deleted by a user.
The Methods node allows x++ code to be run against the table. Developers often customize the initValue, update, and insert methods to perform business logic before or after the default logic is run. Also, it is a best practice on most tables to create a method named ‘find’. This method, will retrieve a unique record from your table, and will take as parameters values for each unique field on the table. This method can then be reused by other developers to read and update records on the table you just created.
Conclusion
In this article you not only learned how to create a table, you learned so much more. Many of the nodes and properties on the table allow the system to provide advanced functionality without the need of additional coding. These nodes are extremely powerful and allow users to focus on the functionality.
If there are areas you have questions on, or areas you would like me to cover in more detail, let me know in the comments.
