D365 Snippets & Power BI

In the previous article, we have already discussed Custom Table Methods Through an Extension in Microsoft Dynamics 365 Finance & Operations. In this article, we are discussing one of the interesting topics Delete Actions in D365 FO. 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.

When a Delete Action comes into the picture? Normally delete actions come 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. Then"How is the database consistency maintained?".

Actually, a delete action is performed when related tables are existing in the project, and deleting records from any table has to be restricted for keeping the database consistent. 

Imagine we have two tables PP_EmpTable and PP_EmpFamily, where PP_EmpTable keeps all the basic details of the employee, but PP_EmpFamily keeps the family details of each employee existing in the EmpTable. Here PP_EmpTable is the parent table and PP_EmpFamily is the child table.

EmpFamily Table refers EmplId as the reference key. PP_EmpFamily alone has no existence without PP_EmpTable. So deleting any records from both tables has to be restricted. 

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

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

All four Delete Action methods have major roles in Dynamics 365 development process.

Consider two tables PP_EmpTable and PP_EmpFamily tables, here EmpId is the foreign key for PP_EmpFamily which refers to PP_EmpTable. Now we are going to set the deletion action property to the table PP_EmpFamily.

1. None

If you are choosing None, As the name shows None means Delete Action is disabled. In this case, while deleting records from the table nothing will occur on the related table.

In our example, if we are deleting any record from PP_EmpTable it also deletes the matching records from the table PP_EmpFamily where the primary key EmpId matches the foreign key of the PP_EmpFamily.

Note: - Cascade Delete Action takes place both records are deleted through code or directly by the user through the user interface.

Read More:  Learn How to add cascade delete action in d365 FO

3. Restricted

Setting up the Delete Action property to Restricted also extends the functionality of the tables' delete method. In this case, the system checks that while deleting a record from the parent table first the system checks whether any related records exist in the related child tables if exist the system will generate a warning message. This warning message will generate only if the delete action is performed through the user interface. If you are trying to delete through X++ code this will delete only the records from the child table.

Note: - A programmer can use .validateDelete() and this will return true or false values, based on the result, a developer can do the action.

As a result, super(), in validateDelete, checks whether records exist on related tables. If records do exist, validateDelete returns false. The forms system ensures that the deletion is not performed. In your own X++ code, check the return value of validateDelete. Don't delete the primary or related records if the method returns false.

Example :

On the VendTable table, a restricted delete action has been defined for the VendTrans table. When a vendor is deleted in the VendTable table, the validateDelete method ascertains whether transactions exist for the vendor in the VendTrans table. If so, validateDelete returns false.

4. Cascade + Restricted

Setting up the Delete Action property to Cascade + Restricted also extends the functionality of the tables' delete method. In this case, deleting a record from the parent table will show a warning and if we delete the record from the parent table also delete the related child records from the child table. 

As a result, super(), in validateDelete, ascertains whether records exist on related tables. Whether deleting records from forms or X++, if validateDelete returns false, the primary record isn't deleted and the cascading delete isn't performed. You should first delete the records in the related table before deleting the primary record.

If the primary record is being deleted as part of a cascading delete, the primary record and the records in the related table will be deleted.

Example

The Cascade+Restricted delete action is used in the standard application for LedgerJournalTrans on LedgerJournalTable.

This type of delete action is useful when you prefer a total clean-up—when you delete a customer, you also delete all the transactions associated with that customer.

In our example deleting records from PP_EmpTable also deleted the records from the related records from PP_EmpFamily. So here this property brings the functionality of both Cascade and Restricted.

Note: - However if the delete operation is done through X++ code it will not show any error and will delete the records from both tables.

This article explains the basic concept of delete action and what are the different types of delete action in D365 FO. In the next article, I will explain different types of delete actions in detail. if this article is useful, please try to share this with your friends.

Happy coding with D365 Snippets

Conclusion

So as a D365 Developer you must have made and idea about Delete Actions in D365 Finance & Operations. At the initial stage of project development, we have to consider which delete actions to be set for the tables. Delete action is one of the important & interesting features of D365 Finance & Operations. These articles are mainly for beginners and learners to get the basic important concepts of Dynamics 365 Finance and Operations. Please put your comments to improve the quality of the articles.

In the previous article we already discussed the topic Query Based SSRS Report creation in Microsoft Dynamics 365 Finance and Operations. There you can see all the necessary steps to configure and generate query based SSRS reports in Microsoft Dynamics 365 Finance and Operations. 

In this article i will show you How to create Custom Table Methods Through Extension in Microsoft Dynamics 365 Finance and Operations. If you are a beginner You can also access this free VM from Microsoft learning portal, read the article Microsoft provides a VM Mode for a free,web based virtual machine environment for D365 F&O Learners.

Imagine a requirement that you have add one more column to the all sales orders  grid view under the  Accounts Receivable menu.

The All sales order  form showing the list of all sales order summaries with the columns Sales Order refer no, Customer Account, Customer Name , Order type , Invoice account etc...Here we need to display one more extra column TotalSales which gives the total sales value of each sales order.

Create a Model

Create a project

Create a class

Here we are creating one class named SalesTable_Extension which extending the table SalesTable. In D365 sales header details are stored in SalesTable and associated sales transaction details stored in SalesLine Table. The reference key for the tables are SalesId .

Extend the form 

All the core steps are completed now. Method getTotalSalesOrderAmount() returns the TotalSales value of each Sales Orders. Next step we have to create one more column in the GridView of the form SalesTableListPage which lists all the sales orders summary.

For this create extension of the form SalesTableListPage, for this right click SalesTableListPage from AOT  and click on create extension. After few second the form is extend to the project. see the below image for more clarification.

Add TotalSales Column

For adding TotalSales  column in the form SalesTableListPage we have to add one more FormControl field with Real Type, which holds the TotalSales  value in the form SalesTableListPage. For adding one Real Type FormControl  field double click on the form extension, visual studio opens one design window. 

Right click on the Grid --> New  -- > Real.

After creating FormControl field with Real Data Type go to the property window, change the Name property of FormControl  to SalesTotal. Also change the  DataSource, here SalesTable is the data source and put the display method getTotalSalesOrderAmount() as the  Data Method.In X++ for calling a static Data Method is by using the syntax,

ClassName :: MethodName();

So here we put SalesTable_Extension::getTotalSalesOrderAmount();  in the Data Method property of the FormControl  TotalSales. Now all the steps are over. finally deploy the project.

Go to the functional environment and go to Accounts Receivable then select All Sales Order menu, You can see the TotalSales column in the Grid view, which shows the total sales amount of each sales order. Customization is over.

So Happy coding with D365Snippets. Please post your comments and share the post.

Useful Links

Query Based SSRS report in Dynamics 365 Finance & Operations

Set up Online VM for Dynamics 365 Finance and Operation for Technical and Functional Learning Purposes.

Package, Model , Project and Element Concepts in D365 F&O

If you are a learner or beginner you can Set up Online VM for Dynamics 365 Finance and Operation for Technical and Functional Learning Purposes

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

• Query Based Approach.
• Data Provider Approach.

Here we are explaining the first method that is Query Based Approach,This approach we are normally using for generating simple SSRS report which are straight forward reports. For example; you want to generate SSRS report for the customers master data,Here we can use very straight forward method, because we are generating report only based on the physical fields of the CustTable.

But Data provider Approach is little bit different from Query Based Approach, because there we are using some complex logic. For example ; we want to generate a customer wise sales report in between a particular period.In this case we have to join different table and also have to check different conditions. So in this situations we can use Data Provider Approach.

As we already mentioned Here we are creating a SSRS report for displaying customer master details from table name CustTable.
for this we have to follow the below steps.

• Create a Query Qry_Customers.

• Link or refer this query to table named CustTable(ie. CustTable is the data source of the created Qry_Customers).

• Create a Report Rpt_Customers.

• Set Query Qry_Customers as the Datasets of the Report. For this Drag and  Drop Qry_Customers to DataSet of Report Rpt_Customers.

• Drag & Drop the Qry_Customers  from DataSet to design node of the Report.

• It automatically create AutoDesign.

• Under AutoDesign node one table also created Qry_CustomersTable1.

• Now you can see all the physical fields in the Data Node of the Qry_CustomersTable1.

• Add a sorting field under the Sorting node of AutoDesign of the report.

• Change sorting order based on your requirement (it will be ascending by default).

• Change Style Template Property of Qry_CustomersTable1 to the required one.

• Change the Layout Template property of AutoDesign to ReportLayoutStyle Template.

• Create one Output Menu item for displaying the menu in the Home screen for the Report.

Then i am going to show you how to do the above steps in Details, So launch your Sandbox Virtual Machine environment,

If you are a learner or beginner you can use Microsoft you can Set up Online VM for Dynamics 365 Finance and Operation for Technical and Functional Learning Purposes.

How to do it..

Here i am going to create a model SSRS_Model, Under this model i am creating a project SSRS_Customers.

Next step is create a query, for this from solution explore right click your project then follow the steps.Add-->New Item-->Data Model Node-->Query.Name the query to Qry_Customers.

Next step is Linking or referring the CustTable as the Data Source of the created Query. For this Open AOT.

View Menu-->Application Explorer-->Data Model-->CustTable. Drag and drop this table to the Data Source node of the Qry_Customers.For this you must add the reference model Application Suit in to your project.

Change the property Dynamic Fields of CustTable to Yes.Then go to Fields node of the CustTable and remove the unnecessary fields.

Next step is Create a SSRS report. for this from solution explore right click your project then follow the steps.Add-->New Item-->Reports Node-->Report.Name the Report to Rpt_Customers.

Next Step is set the query Qry_Customers as the dataset of the Report.

For this Drag and Drop Qry_Customers to the DataSet of Report Rpt_Customers.

Then Drag & Drop the Qry_Customers from DataSet node of report to Design node of the report. It will automatically create and AutoDesign Node under Design. Under AutoDesign node you can see one Table with name  Qry_CustomersTable1 is automatically created.

Now you can see all the physical fields in the Data Node of the Qry_CustomersTable1. Add a sorting field under the Sorting node of AutoDesign of the report.

Change sorting order based on your requirement (it will be ascending by default). Change Style Template Property of Qry_CustomersTable1 to the required one.

Change the Layout Template property of AutoDesign to ReportLayoutStyle Template.

Finally Create one Output Menu item for displaying the menu in the Home screen for the Report and change the label property to Customers. Put this Output menu under AccountsReceivable or wherever you required. I created one Output menu name Mnu_Customers and change the label property to Customers. Set the object type property of Mnu_Customers to SSRS Reports and Object property to Rpt_Customers.

Now all setup has been completed. Next go to Solution Explorer and right click on the report Rpt_Customers then select Deploy Reports.

Finally Build the Project. Then go to functional environment and go to the module Account Receivable under the Main menu Inquiries and report you can see one output menu name Customers as shown in the below figure.

Click the output menu Customers. Then click OK. then it will navigate to Report preview window.

Ensure the service SQL server Reporting service is running on the Virtual Machine. if no go to services and start the service.

Finally the report will generated , By default SSRS report preview window has the features for  find , Zoom and export the file to different formats(PDF,Word,Excel etc...)

Happy coding with D365Snippets. Please post your comments and share the post.