D365 Snippets & Power BI

In the previous article, I explained what Extended Data Types in D365 is and how to create Extended Data Types. In this article, I will explain how to create a table in Dynamics 365 FO. As we know tables are the core objects used to store data in Microsoft Dynamics 365. In Dynamics 365 FO before creating a form we need to create the tables first. By using visual studio, it is very easy to create a table.

In Dynamics 365 FO, you can use primitive data types or Extended data types for adding fields to the table. Before creating a new Extended data type (EDTs), you should check whether the EDT is already existing in the system, if already exists in the system, you can re-use it. Another important thing to remember while creating a table is, don’t forget to index the table to enhance its performance. Also in professional development, you can create field groups to organize your work and use these field groups in the design of forms and reports, etc.

So, in this article, I will only show you how to create a simple table in d365 fo, in the next articles I will show you how to set indexes and how to set field groups on a table and how to create a relation to another table.

So, in this article, we will go through the following steps,

•          Create a D365 Project.
•          Create a table.
•          Add new fields to the Table.

If you are a beginner or a learner, you can use Microsoft’slearning platform for practicing D365 FO scenarios. There you can access both the technical and functional environments for practicing more.

In this article, we are creating a table with the name TblEmployee with three fields as shown below.

After creating the table TblEmployee, it is very important to add fields to the table as per our requirement, so here we are adding three fields EmpId, EmpName, and Gender. For the field EmpId, I use data type EDT int and for the field EmpName, I use two EDT String, for the field Gender I use Enum as the data type.  You can refer above table structure for any doubts.

For this open your ‘Visual Studio’. Right-click on Visual Studio and select ‘run as administrator’. It is important when doing development that you run Visual Studio as an administrator. Then follow the steps.

1.  Create a D365 Project

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.

Next, right-click on your project, and select Properties. In the dialog that opens, set the ‘Model’ to your custom model, if you have already created a model, you can re-use the model. Here I used the Fleet Management model and Also set the ‘Synchronize Database on Build’ property to True. This is needed when creating or modifying tables so that the changes you make in Visual Studio get synched to the actual SQL Server Database.

2. 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 TblEmployee. Then press Add button.

You can see the table TblEmployee is created,

3. Add new fields to the Table

As we already discussed, here we are adding three fields EmpId, EmpName, and Gender. For the field EmpId, I use data type EDT int and for the field EmpName, I use two EDT String, for the field Gender I use Enum as the data type. You can refer above table structure for any doubts. So, we need to create two EDTs and Enum Data types.

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. For creating EDT for EmpId, Right click on the Visual Studio Project and select Add>New Item. Select EDT Integer. Set the name to EmpId. Then click the ‘Add’ button as shown below.

For this,

For creating EDT for EmpName, Right click on the Visual Studio Project and select Add>New Item. Select EDT String. Set the name to EmpName. Then click the ‘Add’ button as shown below.

For the field Gender, we do not need to create a new Enum Data type, because Gender Enum is a standard Data type, so you can reuse it,

You can assign EDTs to a table field in two different ways, first, you can drag and drop the EDT into the ‘Fields’ node on the table designer, and it will automatically create a field with the assigned type. Another way is creating a field with primitive data types and right-clicking on the field and selecting ‘Properties’. Set the Extended Data Type property to the already created EDT.

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. So, you can change these properties as per your requirements.

The next step is Open the TblEmployee table designer by double-clicking on it. Drag the EDT EmpId 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. In the same way, we Drag the EDT EmpName into the ‘Fields’ node on the table designer.

For creating the Gender field, we must Drag the EDT Gender into the ‘Fields’ node on the table designer as shown in the below figure.

For this open Application Explorer > AOT > Data Types > Base Enums > Drag the EDT Gender into the ‘Fields’.

Now our table TblEmployee is ready, after a successful build of the project, this table will be synchronized to your project, for testing purposes you can use Table Browser, for this Right click on the Table > Open Table Browser.

Through the next articles, we will also learn how to set indexes and how to set field groups on a table and how to create a relation to another table. If this post is useful for you, try to share this with your friends also. You can explore more tutorials from this blog. Happy coding with D365 Snippets.

Some Useful Tags

In this article, we will discuss the flash fill feature of Microsoft Excel. As the name mentioned, Flash fill executes tedious tasks in a flash, it is one of the amazing features introduced by Microsoft in 2013.

Before introducing flash fill functionality in excel, Microsoft excel uses used complex formulas to do the required tasks easily. But after introducing the flash fill tool, it reduced the complexity and execution time.

For example: before introducing the flash fill property, to split the first name from a full name excel users used a formula,

To extract the first name “Sachin” from “Sachin Tendulkar” we need to use a formula

= left (A2, search (“”, A2,1))

Similarly, users need to write another formula to extract “Tendulkar” from “Sachin Tendulkar”.

Where is Flash Fill Located in Excel?

In Microsoft Excel, the Flash Fill tool is in the path

Data > Data Tools > Flash Fill as shown below.

What is the shortcut key for Flash Fill?

You can easily access this tool using the shortcut key, select the required cell in excel and click the below keys,

Ctrl + E

Flash Fill Examples:

Here we can look at how these tasks are achieved by using the flash fill tool or functionality easily.

Example 1: How to Extract First Name from Full Name using Flash Fill in Excel? 

In this example, we created two columns “Full Name” which keeps all the full names, and column “First Name “which keeps all the extracted first names. Column “Full name” is the source column.

1. What is Dynamics 365 Finance & Operations?

Microsoft Dynamics 365 is a cloud-based business platform developed by Microsoft, which is designed as a cloud-based Enterprise Resource Planning (ERP) by Microsoft on the Azure Platform. The browser-based Microsoft Dynamics 365 Finance & Operations replaces the previous version on-premise version Dynamics AX. 

The responsibility of a Dynamics 365 Developer is to develop new modules or customize the existing modules. As Dynamics 365 is a high customization system, a developer with significant knowledge in D365 development can deliver high-quality solutions. 

The minimum Software requirements for Dynamics 365 Server is, It can be hosted locally or can use the Microsoft cloud platform, Microsoft Azure. A minimum of 16 GB of RAM and 2 CPU Cores are required, more RAM and CPU cores increase the speed of compilation. This is the basic requirement for running a Dynamics 365 application. 

The Three Tier Architectures of the Dynamics 365 process are, From the client environment (normally a Browser) request is sent to the AOS server. AOS server stands for Application Object Server will process this request. So all requests and responses are processed from the AOS environment. If required, this AOS is communicated with the Database server, and finally processed response is sent back to the client. This is the normal Three tier architecture followed by D365.

2. What is a form in D365 FO?

Forms in  d365 fo provide the user interface that people use to interact with the data they need to do their work. It is important that the forms people use are designed to allow them to find or enter the information they need quickly.

3. What is a Model in D365 FO?

As we know, the complete D365 Finance and Operation developments are based on these four terminologies Package, Model, Project, and Element. So as a developer you need to know processes how to create a model, how to update a model, and how to delete a model. 

Creating a model is a mandatory thing for any sort of customization in Dynamics 365. A model is a design-time concept. A particular model can contain multiple Visual Studio projects. Therefore you can say that a model is a collection of projects and a single project can have all or subset of elements from originating model. However, an association of a project is only with a single model. It is basically a unit of development/customization. Metadata for models is stored locally on an XML file called a descriptor XML.

4. What are the different types of Tables used in D365 FO?

1. Regular 

Regular tables are normal standard physical tables in the SQL server database. 

2. TempDB 

TempDB tables are the physical temporary tables held in the SQL server database. 

3. InMemory 

InMemory tables are temporary tables that are held in the memory and written to a local disc after reaching a certain limit of the system, especially in the Application Object Server (AOS).

5. What is the concept of extension in D365?

An extension is a way to add functionality to an object in D365FO without modifying the base code of that object. Your extensions are compiled into their own DLLs, which are separate from the D365 base system libraries. It also makes it easier for Microsoft to patch its SYS layer code. Microsoft has added to allow customization without allowing the base code to be changed because they plan to not allow any overlaying of SYS layer code.

6. What are OOPS concepts?

The main concepts of OOPS used in D365 FO are:

• Class and Objects 
• Data Abstraction: Showing only the essential information and hiding background details.
•  Encapsulation: Wrapping of data member and method to a single unit. 
• Inheritance:-The Flowing of property of the parent class to the child class. 
• Polymorphism:-The property of using the same method again to perform different things.

7. What is AOT in D365FO? 

The Application Object Tree (AOT) is a tree view of all the application objects in Microsoft Dynamics D365FO. The AOT contains every object you need to customize the look and functionality of a Dynamics D365FO application.

8. Difference Between TempDB and InMemory Tables?

TempDB: 

• TempDB temporary tables are faster than InMemory Temporary Tables.
• TempDB temporary tables are maintained in the SQL Server database.
• TempDB temporary tables support more features than standard physical tables.
• TempDB temporary tables support More powerful joins with physical tables are possible.

InMemory:

• InMemory temporary tables are instantiated in the active memory of the tier the process is running on. 
• The process can run on the client tier or the server tier. 
• The objects are held InMemory until the size reaches 128 KB. 
• The dataset is then written to a disk file on the server tier. 
• You can use InMemory temporary tables when the amount of data is small and Microsoft SQL Server round trips should be avoided.
•  Both InMemory and TempDb tables can be used as data sources on forms.

9. What is the Chain Of Command (CoC) in D365

Chain of Command (CoC) is the term that describes how we customize or extend, the base Microsoft code in Microsoft Dynamics 365. Microsoft’s base objects and code cannot be changed directly in D365. However, we are able to make changes to separate objects and classes that are then combined with the existing base object to form the final version.

Basic Syntax : 

[ExtensionOf(classStr(CustTable))] public final class CustTable_Extension { public display str doSomething(int arg) { // Part 1 var s = next doSomething(arg + 4); // Part 2 return s; } }

10. Why Microsoft implemented CoC?

In the previous versions 2012 and before a programmer can change the Base Microsoft's source code directly from the developing environment. Microsoft identified the problem and from the next version, Microsoft re-architected the code and made it that Base Microsoft's source code and Objects cannot changed directly in D365. 

Microsoft releases new features and hot fixes as a part of product improvement. In this cases the new code should be merged with customized code, depending on the depth of the customization the complexity of the merging process will increase. So Microsoft found this difficulty and removed the feature. This benefits the users to update the hot fixes and feature updated of the product from Microsoft very fast, secure and safer than ever could before.

11. What are Delete Actions in D365?

Normally a delete action comes in to picture when a user is trying to delete a database record. Delete actions are used to maintain the data integrity and consistency of the database. At the time of development, a developer has to take care of the delete actions to implement in the project. Actually, a delete action is performed when related tables are existing in the project, and deleting records from any table has to be restricted to keep the database consistent.

In Microsoft Dynamics 365 Finance & Operations, there are four types of Delete Actions. 

• None. 
• Cascade. 
• Restricted. 
• Cascade + Restricted

12. What are EDT and Base Enum?

EDT stands for Extended Data Type and Base Enumerations (Enums) are data types that are created and managed in the development environment. Extended data types can be primitive data types like integers, strings, real numbers, and so on. EDT extends the original properties of the data type from which they inherit in addition some extra properties are added. To reuse its properties. The properties of many fields can change at one time by changing the properties on the EDT. Relations that can be assigned to an EDT are known as Dynamic relations.EDT relations are Normal and the Related field is fixed. Why not field fixed – field fixed works on only between two tables 1- 1 relation. And Related field fixed works on 1- many tables. so EDT uses a related field fixed. 

Examples for EDT: EDT Real, EDT Date, EDT String, etc.

Base enums are a fixed set of values, and integers in the database, which have a name (as referenced from X++ code) and a label (visible to users and translatable into different languages).  They cannot be added to in real-time by the application, only by developers, and often with significant overhead, hence how they are fixed.  You can have up to 255 values for Base enums.  The integers in the database will take on the values 0 through 254. 

The Application Object Tree (AOT) in Finance and Operations apps contains many existing EDTs and base enums that can be extended for use in your project, or you can create new data types. This module will focus on creating new data types. 

Example for Base Enum: You can add one Base Enum for holding the gender of the employee, you can create a Base Enum and rename it to BEGender and add new elements with names "Male" & "Female"  with integer values "0" & "1".

13. What is SSRS Report in D365?

Microsoft Dynamics 365FO users use the SSRS report for business reporting purposes. In Microsoft Dynamics 365 SSRS reporting feature is one of the significant features provided by Microsoft for their users. This SSRS report will help the users to get the right information about the business processes and transactions.

Based on the complexity of the SSRS reports, the development will be divided into two methods,

• Query-Based Approach. 
• Data Provider Approach.

14. What are the different types of classes used in the SSRS Report?

A DP (Data Provider) class will provide three data sets for the SSRS report. The same class will also provide information about report parameters to the report’s RDL through an attribute that links a DP class with the corresponding report Data Contract. 

A Controller class that will handle the report dialog form, setting the SSRS report design and the value of the hidden parameter. 

A Data Contract class that will define and carry the values of the report parameters.

15. What are Table Relations in D365?

A table relation associates two tables that contain related information. Usually, the primary key field of one table appears as a foreign key field of the related table. The table with the primary key is called the parent table. The table with the foreign key is called the child table. 

In Microsoft Dynamics 365 there are four types of table relations,

• Normal Relation 
• Field Fixed Relation 
• Related Field Fixed Relation 
• Foreign Key Relation

16. What are the different types of Menu Items available in D365?

Mainly three types of Menu items available In dynamics 365 F&O. They are, 

Display Menu Item: Use this type when the menu item will open a form. 

Output Menu Item: Use this type when the menu item will opening an SSRS report. 

Action Menu Item: Use this type when the menu item will run a batch job. Or some other process, such as run by a class.

17. What is an index in D365 FO?

An index is a table-specific database structure that speeds the retrieval of rows from a table. Indexes are used to improve the performance of data retrieval and occasionally to ensure the existence of unique records. 

There are two types of indexes unique and non-unique. Whether an index is unique is defined by the index’s AllowDuplicates property. When this property is set to No, a unique index is created. The database uses the unique index to ensure that no duplicate key values occur. The database prevents you from inserting records with duplicate key values by rejecting the insert.

18. What are display methods?

The display method modifier indicates that the method has a return value that can appear on a form or a report. A display method is any method that includes the display keyword as a method modifier. ... Form methods. Form data source methods. 

Display methods will help you to show the data from some other table or hardcoded strings. These are not bound controls and we cannot use a filter or sorting options.

19. What is Surrogate Key in D365?

A surrogate key is a system-generated value that acts as the primary key of a table. This value is typically used instead of a set of fields called the natural key which would typically act as the primary key of a table. Microsoft Dynamics AX 2012 or D365 uses RecId as the surrogate key. 

A surrogate key value has no meaning to people. A large number generated by the system, such as RecId, could be a surrogate key.

20. What is Microsoft LCS?

Lifecycle Services (LCS) for Microsoft Dynamics is a collaboration portal that provides an environment and a set of regularly updated services that can help you manage the application lifecycle of your implementations of the Dynamics 365 Finance and Operations apps.

21. What are the new features of D365?

Some Useful Tags : 

In the previous article, I explained how to create a label file and to use a label file to display texts and titles on user interfaces. In this article, I will explain Extended data types (EDTs).

What are Extended Data Types (EDT) in D365?

Extended data types commonly referred to as EDTs are user-defined types, based on primitive data types like boolean, integer, real, string, and date, and the composite data type container. You can also base EDTs on other EDTs. An EDT is a primitive data type or container with a supplementary name and some additional properties. 

For example, You can create one EDT named Description with the property StringSize is 100 characters, which is derived from a Base primitive data type string. This newly created EDT you can use anywhere instead of using the primitive data type string. 

So, in standard development, we use EDTs instead of primitive data types. Microsoft also provides some standard EDTs, these EDTs you can use in variable and field declarations in the development environment.

Benefits Of EDT

1. We can put some meaningful name for EDTs for our newly created variables. For example Name instead of a string. Hence the coding will be easier to read and understand.

2.  The properties you set for an EDT are used by all instances of that type, which reduces work and promotes consistency. For example, An EDT “Name” with StringSize is 10 characters, which can be used in different table fields where you want to save the names like customer name, supplier name, employee name, etc.  In the future, if we want to increase the StringSize of the customer name, supplier name, and employee name to 20 characters. you can easily change the StringSize property of EDT “Name” to 20. This will reflect all the associated table fields automatically means all fields based on this EDT will be adjusted; and if we reduce the size, it will truncate the values to the new size.

3.     You can create an extended data type in the Application Object Tree (AOT) that contains multiple elements. This enables you to store multiple data elements in a single field that is based on this extended data type.  A typical example is an extended data type for handling an address. Instead of defining three fields on your form to enter the address, you can define an extended data type with three elements (the first element exists by default), as shown in the following figure.

4.    You can create a relationship with the help of EDT.  

Read More:  How to create Auto Lookup Using EDT with Table Reference.

How to create an Extended Data Type (EDT) in D365?

If you are a beginner or a learner, you can use Microsoft’s learning platform for practicing D365 FO scenarios. There you can access both the technical and functional environments for practicing more.

Here we will create one EDT named “Remarks” with property StringSize is 20, and then will create two tables “TblCustomer” with two fields “CustName” and “Remarks” and another table “TblVendor” with two fields “VendName” and “Remarks”.

We will assign the EDT Remarks to the Remarks fields of both tables.

To create the EDT, follow these steps:

1. Right-clicking on solution explorer and choose Add | New Item.

2. Select Data Types in the left-hand list, and then select EDT String.

3. In the Name field, enter the name of EDT, in our case, I put Remarks and then press Add button.

Now you can see the EDT is created under the Solution Explorer under the Folder Name EDT string, the folder will automatically be generated based on the type of EDT you created.

The next step is, we need to set the property StringSize to 20,

For this Right click on the EDT and select the Properties menu as shown in the below figure,

Right-click EDT > Properties> StringSize to 20 characters.

As we already mentioned, in our example we are assigning these EDT Remarks to the Remarks fields of the two different tables TblCustomer and TblVendor. So, we need to create these two tables,

For this, by Right-clicking on solution explorer and choosing Add | New Item, under Data Model you can create two tables TblCustomer and TblVendor as shown below.

Table Vendor

Table Customer

Now we created two tables TblCustomer and TblVendor, TblCustomer has two fields CustName and Remarks and TblVendor has two fields VendName and Remarks.

The next step is we are assigning the StringSize property of the Remarks field of TblCustomer and TblVendor to 10,

For this right click on the Remarks field of two Tables and set the property StringSize to 10 characters as shown in the figure.

Now we are going to assign the EDT Remarks to the Remarks field of the two tables TblCustomer and TblVendor. 

For this, Right click on the table field Remarks and select the property Extended Data Type, and assign the EDT, in our case select the EDT Remarks as shown in the below figure.

Now you can see the StringSize property of the field Remarks of both tables has been changed from 10 to 20 as shown in the below figures. This is because the StringSize of EDT Remarks is 20. When we assign the Extended Data Type property of tables it will reflect all the associated table fields automatically means all fields based on this EDT will be adjusted; and if we reduce the size, it will truncate the values to the new size. 

In the future, if you have a requirement to increase the StringSize of the fields of the two tables TblCustomer and TblVendor to 100. You can easily change the StringSize of the EDT, this will automatically reflect all the associated fields of the tables. 

Here I have changed the StringSize property of EDT Remarks from 20 to 100, you can see automatically the StringSize of the Remarks Fields of two tables TblCustomer and TblVendor have increased to 100.

In standard customization, we must deal with hundreds of tables and different elements in our application, so by using EDT, you can reduce effort and time also which promotes consistency. If this article is useful, please share it with your friends. Happy coding with D365 Snippets.