But in some cases X++ SQL Statements are not effective or not possible at all. In this cases a developer must write the SQL statements to fulfil the requirement, normally these cases are little complex and can deal only with direct SQL statements. One example for such case is data upgrade during an application version upgrade.

In this article I will show how to write and execute the SQL statement using X++ code or how to execute a direct SQL Statement, I write this article for beginners or learners who are very interested to learn the basic concepts of D365 finance and operations. Here I write very simple example for easy understanding. You can elaborate the example as per your requirement.If you are a learner or beginner, you can use free virtual machine for learning purposes.

Basic Steps

1. Create one Model with name D365SnippetsModel.
2. Create one project with name SQLStatementDirectProject.
3. Create one Runnable Class (Job) with name ExecuteSqlProcess.
4. After creating a runnable class, copy and paste the below code, 

For Best practice i have created one Table EmployeeSalaryDetailsTable which stores employees basic details(EmpId,EmpName,basicSalary,TelephoneAllowance). Then i have added two records to EmployeeSalaryDetailsTable.You can also use already existing Table for this practice for example: VendTable , CustTable etc.

First part of the code is for declaring the variables and objects. Where DictTable and DictField objects are used to handle the tables and its fields. A new SqlSystem Object is also created , which is used to convert D365 types to SQL Types.Next, we set up a SQL statement with a number of placeholders for the table or field names and field values to be inserted later.

Second part is the execution part ,when the query placeholders are replaced with the right values. Here, we use the previously created DictTable and DictField type objects by calling their name() methods with the DbBackend::Sql enumeration as an argument. This ensures that we pass the name in the exact manner it is used in the database-some of the SQL field names are not necessary, which is the same as field names within the application.

The results are returned in the resultSet object, and we get them by using the while statement and calling the next() method until the end. The Info method writes the resut of the screen.After creating a basic project you can copy and paste the code to you visual studio.

class ExecuteSqlProcess
{
 
   public static void main(Args _args)
   {
       UserConnection userConnection;
       Statement statement;
       str sqlStatement;
       SqlSystem sqlSystem;
       SqlStatementExecutePermission sqlPermission;
       ResultSet resultSet;
       DictTable EmployeeSalaryDetailsTbl;
       DictField EmpId;
       DictField EmpName;
       DictField BasicSalary;
       DictField TelephoneAllowance;
       EmployeeSalaryDetailsTbl= new DictTable(tableNum(EmployeeSalaryDetailsTbl));
       EmpId = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,EmpId));
       EmpName = new DictField( tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,EmpName));        
       BasicSalary = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,BasicSalary));
       TelephoneAllowance = new DictField(tableNum(EmployeeSalaryDetailsTbl),fieldNum(EmployeeSalaryDetailsTbl,TelephoneAllowance));
 
       sqlSystem = new SqlSystem();
       sqlStatement = 'SELECT %1, %2, %3, %4 FROM %5';
       sqlStatement = strFmt(sqlStatement,EmpId.name(DbBackend::Sql),EmpName.name(DbBackend::Sql),BasicSalary.name(DbBackend::Sql),
           TelephoneAllowance.name(DbBackend::Sql),
           EmployeeSalaryDetailsTbl.name(DbBackend::Sql),sqlSystem.sqlLiteral(CustVendorBlocked::No, true));
       userConnection = new UserConnection();
       statement = userConnection.createStatement();
       sqlPermission = new SqlStatementExecutePermission(
       sqlStatement);
       sqlPermission.assert();
       resultSet = statement.executeQuery(sqlStatement);
       CodeAccessPermission::revertAssert();
       while (resultSet.next())
       {
           info(strFmt(
          "EmpId : %1 , Emp Name : %2 , Basic Salary : %3 , Telephone Allowance : %4",
          resultSet.getString(1),
          resultSet.getString(2),
          resultSet.getString(3),
          resultSet.getString(4)
               
               ));
       } 
 
   }
 
}   
    
 

Conclusion

In this article i explained the concept of how to execute a direct sql statement using X++ code in D365. You can change this codes as per your requirements.For beginers this article will be very useful. Use microsoft's free virtual machine which is already discussed in this blog.

Happy coding with D365Snippets

Some Usefull Links

• Chain Of Commands in D365 with Example

• D365 Interview Questions

• OOPS Concept in D365

• Delete Actions in D365 with Example

• Dynamics 365 Interview Questions

In the previous article, I already discussed how to create and export Microsoft excel files using X++ programming in D365 F&O. In this article I am showing how to import data from excel files using X++ code in D365 FO.

The management and integration of Microsoft Dynamics 365 with Microsoft 365 products or Office 365 products are very good, especially with Microsoft Excel. 

But there are many situations in our development, there we need to write the x++ codes to import excel files and to manipulate with the excel data. These requirements may be special and does not available with the standard integration.

For this reason, I have written this article to get the basic coding concept to import an excel file and read the excel file using X++ coding. 

I have written the codes under Runnable Class (Job), which helps the learners to understant the code easily. Before writing x++ codes I have created a Model  D365SnippetsModel, under this model  i have created one project ImportExcelProject. Then added one new Runnable Class (Job) ImportExcel.

Before writing the logic for importing excel records we have to add the required names spaces and reference packages, because exporting excel related x++ classes and methods requires the below names spaces.

Using System.IO;
Using OfficeOpenXml;
Using OfficeOpenXml.ExcelPackage;
Using OfficeOpenXml.ExcelRange;

In addition to the basic reference packages, we also require to add some additional reference packages Directory.

Part 1 Code

In part 1 code, create a dialogue object which will work as an import form, and you can select the excel file by clicking the import button, Once the file is fully uploaded it will keep the data in temporary storage. 

Part 2 Code

In part 2 code, get the data from imported excel and get the row count to iterate the records to fetch all the records from excel. In each iteration the data is got and the data is put into the Info method.

After creating the model, project and Runnable Class , You can copy  the below codes and paste the codes between the main method of the runnable class. 

Using System.IO;
Using OfficeOpenXml;
Using OfficeOpenXml.ExcelPackage;
Using OfficeOpenXml.ExcelRange;
class ExcelReadRunnableJob
{
    /// <summary>
   /// Runs the class with the specified arguments.
   /// </summary>
   /// <param name = "_args">The specified arguments.</param>
   public static void main(Args _args)
   {
/*---------part 1 ------------------
     System.IO.Stream stream;
      ExcelSpreadsheetName sheet;       
      FileUploadBuild fileUpload,fileUploadBuild;
      DialogGroup dialogUploadGroup;
      FormBuildControl formBuildControl;
      Dialog dialog=new Dialog("Excel Import Example");
      dialogUploadGroup=dialog.addGroup("@SYS54759");
      formBuildControl=dialog.formBuildDesign().control(dialogUploadGroup.name());
      fileUploadBuild=formBuildControl.addControlEx(classStr(fileUpload),"UploadExcel");
      fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
      fileUploadBuild.fileTypesAccepted(".xlsx");
      if(dialog.run() && dialog.closedOk())
       {
      FileUpload fileUploadControl=dialog.formRun().control(dialog.formRun().controlId("Upload"));
      FileUploadTemporaryStorageResult
fileUploadResult=file::GetFileFromUser(classStr(FileUploadTemporaryStorageStrategy));
     //fileUploadResult=fileUploadControl.getFileUploadResult();
    /*------------------part 1 end---------------------*/
    /*------------------part 2---------------------*/
    if(fileUploadResult!= null && fileUploadResult.getUploadStatus())
      { 

     stream=fileUploadResult.openResult();
     using(ExcelPackage  package= new ExcelPackage(stream))
          {
          int rowCount, iterator;
           package.Load(stream);
           ExcelWorksheet worksheet= package.get_workbook().get_worksheets().get_Item(1);
           OfficeOpenXml.ExcelRange range=worksheet.Cells;
          rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
          for(iterator=2;iterator<=rowCount;iterator++)
          {
              Info(range.get_Item(iterator,1).Value);
              Info(range.get_Item(iterator,2).Value);
              Info(range.get_Item(iterator,3).Value);
           }
       }
}

/*------------------part 2 end---------------------*/
}
else
 {
 Error("error occured.");
 }
}
}    

After pasting the code, building, and running the project, a browser will open which will open a dialogue window to import the excel file, you can import the excel file by clicking on the import button, 

Here I have created one excel file named "Customer  Data.xlsx". Which have three columns "Customer Code", "Customer Name" and  "Customer Address" as shown below figure. In your virtual machine, you create one excel file with the required columns and put some records in each column.

In the browser you can find the import dialogue box, for better practice we can use this method, another method is you can put the excel file path in x++ code.

After clicking the import button, a file chooser dialogue will open, then you can choose your already created Customer Data.xlsx file.

If this post is useful try to share it on social media, Writing x++ code for importing excel data in d365 f&o is not a hard task, but the complexity of coding will increase if the requirement is complex. I wrote this article for d365 FO beginners and learners. 

Happy coding with D365 Snippets.

Some Useful Links

• Chain Of Commands in D365 with Example

• D365 Interview Questions

• OOPS Concept in D365

• Delete Actions in D365 with Example

• Dynamics 365 Interview Questions

Useful Tags

How to read excel records in d365 using x++ code
How to read excel data using x++ code in d365

How to read excel data using x++ code in d365

x++ code to import data from excel to D365 FnO

Import records from Excel using X++ code in D365FO

Importing data from Excel through X++ code

Dynamics 365 FO: Import data from Excel using X++

D365 import using excel

how to import data into dynamics 365 using x++ code

How to import the data by using the excel file path name in D365

How do I import Excel into d365?

How do I import a CSV file into Dynamics 365?

How do I import a set in Dynamics 365?

How do I export data from Excel to d365?

Import data from excel file D365 FO and AX 7

Import data from excel file AX 7

Generate and Import Excel files with X++ in d365 fo

Read excel file in d365

Read excel file in Dynamics 365

import csv file in d365fo

read csv file in x++

read csv file in d365fo

How to Import Data with a CSV or Excel File in Dynamics 365

How to read data from Excel using X++ Code in D365 F&O

Dynamics AX7 / D3FO: Code for Excel importing

Excel file import using X++ Code D365

Code to Import EXCEL File in D365 FO

There are many situations in our development, where we must write the x++ codes to generate excel files in a specific format. These requirements may be special and do not available with the standard integration.

For this reason, I have written this article to get the basic coding concept to create an excel file and export the excel file using X++ coding.

If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft's Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development

For this, I have created a Runnable Class (Jobs) with the name ExcelCreateAndExport,

After creating the runnable class(Job) copy and paste the below code, here I have written static and dynamic codes for adding records to the excel file.

class ExcelCreateAndExport
{      
public static void main(Args _args)
{
CustTable custTable;
DocuFileSaveResult saveResult =
DocuFileSave::promptForSaveLocation("@ApplicationPlatform:OfficeDefaultWorkbookFileName","xlsx", null, "excel create and export");
if (saveResult&& saveResult.parmAction() != DocuFileSaveAction::Cancel)
{
saveResult.parmOpenParameters('web=1');
saveResult.parmOpenInNewWindow(false);
System.IO.Stream
workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream
memoryStream = new System.IO.MemoryStream();
using(var package = new OfficeOpenXml.ExcelPackage(memoryStream))
  {
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add("Sheet1");
var cells = worksheet.get_Cells();
var currentRow=1 ;
 /*-------HEADER PART -------*/
var cell = cells.get_Item(currentRow,1);
cell.set_Value("Customer Name");
cell=null;
cell = cells.get_Item(currentRow,2);
cell.set_Value("Customer Address");
/*-------HEADER PART END-------*/
/*-------RECORD 1-------*/
currentRow=2;
cell= cells.get_Item(currentRow, 1);
cell.set_Value("ABCD Trading");
cell= null;
cell= cells.get_Item(currentRow, 2);
cell.set_Value("ABCD Complex, P.O Box :xxxxxx, XYZ Street");
/*-------RECORD 1 END-------*/
/*-------RECORD 2-------*/
currentRow=3;
cell= cells.get_Item(currentRow, 1);
cell.set_Value("XYZ Trading");
cell = null;
cell = cells.get_Item(currentRow, 2);
cell.set_Value("XYZ Complex, P.O Box :xxxxxx, ABC Street");
/*-------RECORD 2 END-------*/
package.Save();
 }
memoryStream.Seek(0,System.IO.SeekOrigin::Begin);
//Download the file.
DocuFileSave::processSaveResult(memoryStream,saveResult);
}
}
}

After writing these codes, build the project, and set this Runnable Class as starting Object by right-clicking on the class name in Solution Explorer. Then run the project, you can see the output,

Click on the Download button, generated excel file will be downloaded to the local download folder.

Writing x++ code for generating and exporting excel files in d365 fo is not a hard task, but the complexity of coding will increase if we require complex formats and adding more functionalities using x++ code. I have written this article for d365 for beginners and learners. Keep coding and Happy coding with D365 Snippets

Some Useful Links

• Chain Of Commands in D365 with Example

• D365 Interview Questions

• OOPS Concept in D365

• Delete Actions in D365 with Example

• Dynamics 365 Interview Questions & Answers

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.

For example, a parent table named Department can have a departmentId field as its primary key. A child table named Employee can have a departmentId field as a foreign key. All values in the foreign key field must exist in the primary key field of the parent table. Any value in the foreign key field can be used to find the parent row that contains the matching primary key value. Then any other field in the parent row can be accessed.

In the previous articles, we have already read Delete Actions In D365. If you are a beginner or learner use Microsoft's free VM, there you can access both the Technical and Functional environments for developing your knowledge in D365 Finance and Operations.

Use of table relations

A relation on a table can restrict the rows in the table, or restrict the values that can be in particular fields. A common use of relations is to associate rows in one table with corresponding rows in another table. Relations enable many forms to display data from multiple tables.

Some relations restrict the rows in the table by testing the values in each row against constant values. Other relations restrict the rows by comparing values in each row to values in a row in another table.

Example of Table Relation

A PurchaseOrder Table contains all the supplier orders and might have a field called VendId, which keeps the value of the associated Vendor Id for the purchase order. The VendTable contains all the vendor records and has a filed name VendId.

Here VendId in the VendTable is called as primary key, and VendId in the  PurchaseOrder Table is called the foreign key. This is a simple example of table relations. 

To create a table relation specifies that PurchaseOrder.VendId=VendTable.VendId

Table Relations in D365

How to add a relation to a table in D365

• In the AOT, move to Data Dictionary > Tables, and then expand the table that the relationship that will be added.

• Right-click the Relations node, and then select New Relation.

• Right-click the newly added relation, and then select Properties.

• Set the name of the new relationship by modifying the Name property.

• In the Table property, select the related table.

• Use the Validate property to determine whether the relationship should be used to validate data when information is entered into forms.

• Right-click the new relation, select New, and then click one of the following:

• Normal to specify relation fields without conditions.

• Field fixed to specify relation fields to restrict the records in the primary table.

• Related field fixed to specify relation fields that restrict the records in the related table.

• ForeignKey to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

In Microsoft Dynamics 365 there are 

• Normal Relation

• Field Fixed Relation

• Related Field Fixed Relation

• Foreign Key Relation

1. Normal

A normal relation is used to specify a relationship without any conditions. A normal relation specifies related fields in another table. Multiple fields can also be added in normal relations. you can read more about the Normal Relation in D365 from the article Normal Relation in the Article with Example

Condition is,  Table1.Field = Table2.Field

2. Field Fixed

A Field Fixed Relation is used to specify relation fields to restrict the records in the primary table. Only records that meet the condition are selected. The field fixed is normally an enum.

The condition is ANDed with your relation here

Table.Field = <EnumValue>

Read More: Field Fixed Relation in D365 with Example

3. Related Field Fixed

A Field Fixed Relation is used to specify relation fields to restrict the records in the related table. Only records that meet the condition are selected. The field fixed is normally an enum. The field fixed is normally an enum. 

The condition is ANDed with your relation here.

<EnumValue> = Table.Field

4. Foreign Key

A Foreign Key Relation is used to specify a correspondence between a foreign key field in the present table to the primary key field in another parent table.

In this article, I am writing the x++ code for getting the company address with two lines of code. If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development

In D365, it is easy to write codes for getting the company or legal entity address using x++ programming. For executing and testing the project you create a class (Runnable Job) and write the below code inside the main method of the Runnable class.

Here I have created one model with the name D365SnippetsModel and added one project with the name LegalEntityProject and added one class (Runnable Job) named LegalEntityDetails.

Here the runnable class will execute and show an output of the company address that you have currently logged in, for example, if you have logged in to the company Contoso Entertainment System (USMF), the output will be the address of the same company as shown in the below image.

When you execute the class, you will get the company address of the logged company on the screen as shown in the figure.

Some Useful Links

• Chain Of Commands in D365 with Example

• D365 Interview Questions

• OOPS Concept in D365

• Delete Actions in D365 with Example

Object Oriented Programming is a programming concept that works on the principle that objects are the most important part of your program. It allows users to create the objects that they want and then create methods to handle those objects. Manipulating these objects to get results is the goal of Object Oriented Programming. Object Oriented Programming popularly known as OOP, is used in a modern programming language.

If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development

All modern programming languages like X++, Java, C#, etc are working based on the OOP Concept. So to work as a D365 developer must learn what are the Core concepts of OOPs.

Core OOPS concepts are

Class is the most important concept of oops. A class is a group of similar entities. It is only a logical component and not a physical entity. For example, if you had a class called “Expensive Cars” it could have objects like Mercedes, BMW,, etc. Its properties(data) can be the price or speed of these cars. While the methods that may be performed with these cars are driving, reverse, braking, etc.

Wherever you want to use the properties of the class you can call the instance of the class and can reuse it. This helps the reliability of codes and reduces the length of coding.

Advantages of OOPS:

• OOP offers easy to-understand and clear modular structure for programs.
• Objects created for Object-Oriented Programs can be reused in other programs. Thus it saves significant development costs.
• Large programs are difficult to write, but if the development and designing team follow the OOPS concept then they can better design with minimum flaws.
• It also enhances program modularity because every object exists independently.

ERP Integration is the method of connecting and synchronizing the ERP system to other software applications or other data sources. ERP integration improves the productivity of an organization by sharing data between two or more software which are used for different purposes. Through this integration, organizations can generate reports from a single point.

For example, an ERP can integrate the biometric device's applications which are made by other manufacturers to generate the daily attendance of employees, and finally based on the data collected from biometric devices through their applications ERP can generate a payroll of employees of the organization effectively. Actually through this integration organizations can generate more accurate payroll data and can make the payroll process more effective.

How can achieve ERP integration?

Many software product companies are providing Application Programme Interfaces  (API) along with their products. So ERP products or other software can easily integrate these products by using these APIs. ERP integration is not an easy task because each piece of software has to satisfy some set of rules for integration.

For example, an ERP needs to integrate with a CRM application, the purpose of integration is to take the critical customer data from ERP to CRM. For this, the ERP and CRM application has to satisfy some rules for integration. API should be written for integrating both applications easily and without any vulnerability.

What is the Aim of ERP Integration?

Different Software Applications are generating different types of accurate data as their output for different purposes. So utilizing these data in a single point of reporting is a great thing about any organization. So organizations are using ERP integration for making more productive and accurate reports for their business by using ERP integration. 

Moreover, a business uses a mix of software applications for their different departments for different purposes. These applications full fill the specific needs of the department. Bringing these data to the ERP will be highly appreciable for the organization, this is why organizations are using ERP integration.

ERP-ERP Integration

Big companies may have multiple ERP systems which are used by their different business units in different locations worldwide. Because different business units of the same company may have different business activities with different behavior,

For example, A MNC company has 3 business units in different locations worldwide the main activities of different business units are trading, production, projects and activities. So the company uses three different ERP software for the activities. But the head office of the company can take the consolidated financial reports from a single point will be very helpful for the managers to check and evaluate the business progress. 

So in this case ERPs systems need to connect with each other to work as an integrated ERP system to create a consistent and accurate view of data.

In the previous articles, we have already discussed Custom Table Methods Through Extension in Microsoft Dynamics 365 Finance & Operations. In this article, we are discussing one of the interesting topics called Chain Of Commands (CoC). Before reading the concept of Chain Of Command (CoC) in D365, we just have a look at the history of the re-architecture process of D365 by Microsoft. 

In the previous versions of 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 so that Base Microsoft's source code and Objects cannot change directly in D365.

If you are a beginner or learner use Microsoft's free VM, there you can access both the Technical and Functional environments for developing your knowledge in D365 Finance and Operations.

What is the Chain of Command in D365? 

Chain of Command 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; } }

So we can go through the example of how to extend and overwrite an existing method with a Chain Of Command (CoC). For this, we are extending the Data Method ItemName of the Form SalesTable with the help of the Chain of Command.

Create Model

Let us create one model D365SnippetModel, 

for this go to Dynamics 365 > Model Management > Create Model

Create Project

Create one project Under the model D365SnippetModel, here I created one project with the name ExtensionProjectCoC.

Extend the form SalesTable

As we already read we are extending the Data Method ItemName of the form SalesTable with the help of Chain of Command. So the next step is to extend the form SalesTable for this,

Go to View > Application explorer > User Interface > Forms > SalesTable 

Right-click on the SalesTable and choose the option to create an extension.

This will create one extension object of form SalesTable under the project, you can view this object under the solution explorer.

The Data Method ItemName of the Form SalesTable is listed under the grid view SalesTable > SalesLineGrid > Item Name. You can find this data method name under the properties list of ItemName. 

finding the data method ItemName is a little difficult because the form pattern controls are very complex for the form SalesTable. So search the SalesLineGrid in the search area of the design view of the SalesTable.

Create Class

The next step is to create an extension class SalesLine_Extension for implementing the CoC method for this,

Right-click on the Project > Add > New Item > Code > Class

After creating the class SalesLine_Extension write the x++ code for implementing the CoC.

Before writing the x++ code for implementing the CoC we must know some rules,

• The name of the class you created for implementing the Chain of command must end with the _Extension keyword. In our example the class is SalesLine_Extension 

• Must add the attribute [ExtensioOf(TableStr(<name of the Base Class>))]

• The classes must start with public access modifier.

• The keyword "final" needs to be used in the class.

• The extension method name should be exactly the same as it appears in the Base Class.

• The base class method must be called inside your extension method and the method name call using the next keyword. Var v= next methodName();

after reading the rules for implementing CoC, you must write the code carefully.

then build your project.

The output of the Extension Project

After building the project go to the functional environment of D365 FO, and select the menu Customer Order, for this

Go to Account Receivables > Customer Order > Double Click on customer order.
it will display the Header and Line of items. There you can find out the output of our customization, we added the string "D365 Snippets" with every item.

Conclusion