Import records from Excel using X++ code in D365FO

In this blog I will discuss how we can import records from Excel file in Dynamics 365 Finance and Operations.

Please see below screenshot with source code explanation:

Explanation of Source Code (1.1):

• Add / Use following namespaces in the class. 

Explanation of Source Code (1.2):

• Initialize and declare classes and data types.
• Create a dialog object, which will open as form.
• Add controls (Import, Upload and buttons).

Explanation of Source Code (1.3):

• Get and load Excel file.
• Get the row count for record iteration.
• Iterate record and get the columns values on every iteration.

Result:

That is all you need to do for creating a class which imports record from Excel.

Job to create default dimension having dimension attribute and value in D365FO

In this blog I will discuss how we create the default dimension having dimension attribute and dimension attribute values.

Please see below screenshot with source code explanation.

Explanation of source code (1.1):

• In this section of Job Main method I have called the createDefaultDimension Method.
• In this method I have passed three dimension attribute values.

Explanation of source code (1.2):

• createDefaultDimension Method with three argurments.
• Initialization and declaration of classes and data types.
• Pass argument having dimension values and store into container.
• After container is filled with dimension attributes and its values, iterate it.

Explanation of source code (1.3):

• Iteration of dimension attributes.
• Check dimension attribute exists or not.
• Get the dimension attribute value.
• Add the values of dimension in value Set Storage class by using add Item method.

Result:

Run the class (Job).

That is all you need to do to create default dimension record Id.

Job to post pending vendor invoice through X++ code in D365FO

In this blog I will discuss how you we can post pending vendor invoice. For that I have created a job and using PurchFormLetter_Invoice class which will post the invoice.

Please see below screenshot and explanation of source code.

Explanation of source code:

• Initialize of tables and class variables.
• Get purchase order table.
• Get vendInvoiceInfoTable table buffer by purchase order Id and pending vendor invoice number.
• Update match status invoice.
• Initialize PurchFormLetter_Invoice class.
• Update purchase order parameter table.
• Call PurchFormLetter_Invoice class update method.
• Argument-1: Purchase order table buffer.
• Argument-2: Pending vendor invoice number.
• Argument-3: Posting date.
• Argument-4: Purchase update type.
• Argument-5: Account order status.
• Argument-6: Proforma invoice is set to NO.
• Argument-7: Print management option is set to NO.

That is all you need to do for posting pending vendor invoice through X++ code in Dynamics 365 Finance and Operations. For creating pending vendor invoice you can follow my previous blog Job to create pending vendor invoice through X++ code in D365FO.

Job to create pending vendor invoice through X++ code in D365FO

A vendor invoice from a purchase order is an invoice that is produced when products or services are received according to a purchase order that was placed with a vendor. The vendor invoice contains a header, and one or more lines for items or services. A vendor invoice completes the cycle from purchase order to product receipt to vendor invoice. In this blog I will discuss how we can create pending vendor invoice through X++ code in Dynamic 365 finance and operations. So lets begin.

Please follow series of screenshots of source code with explanation:

Source code explanation: (1.1)

• Initialization of table buffers.
• Get purchase order table. 
• Get purchase order lines.
• Get vendor table.

Source code explanation: (1.2)

• Insert data in VendInvoiceInfoTable table.
• This table contains the vendor invoices for one or more purchase orders.
• Assignment of values.

Source code explanation: (1.3)

• Insert data in VendInvoiceInfoSubTable table.
• This table contains the associations between the vendor invoices and the purchase orders.
• Assignment of values.

Source code explanation: (1.4)

• Insert data in VendInvoiceInfoLine table.
• This table contains vendor invoice lines for a specific purchase order.
• Assignment of values.

Source code explanation: (1.5)

• Insert data in VendInvoiceInfoSubLine table.
• This table contains associations between the vendor invoice lines and the posted vendor packing slips.
• Assignment of values.

That is all you need to do for creating pending vendor invoice through X++ code.

Job to combine main account and default dimension to get ledger Dimension in D365FO

In this blog I will discuss how we can combine main account and default dimension to get ledger dimension in D365FO. So let's begin, please follow below screen shots.

For achieving this I have created a Job in which I have three methods, which I have call in my main method.

Main() Method:

Source Code Explanation:

• Set main account (in my case '61170005').
• Set default dimension (in my case '68719489575').
• Pass it into CombineMainAccoutAndDefaultDimension() method.

GetDimensionAttriuteName() Method:

Source Code Explanation:

• Get dimension cache.
• Iterate dimension attribute Set Item to get dimension attribute except main account.
• Return dimension attributes container.

GetDimensionAttributeValue() Method:

Source Code Explanation:

• Find dimension attribute value set storage through default dimension.
• Get display value by dimension attribute from dimension storage.

CombineMainAccoutAndDefaultDimension() Method:

Source Code Explanation:

• Initialize and declare objects.
• Get container which holds dimension attributes. (Get from previous getDimensionAttriuteName() method).
• Iterate dimension attributes and get its values. (Get dimension values from previous getDimensionAttributeValue() method).
• Pass attribute name and value in dimension attribute value contract.
• Pass main account and value contract object in ledger account contract class.
• Through dimension service provider get dimension storage.
• Get dimension value combination of main account and default dimension.
• Return value combination record id which is Ledger Dimension.

 

Result:

This example is taken from this Microsoft community post Combine main account with default dimension to ledger dimension

Job to get average unit cost of item through X++ code in D365

Recently I have encounter a scenario in which I want to get the average unit cost price of the item in D365FO. For that I have created a job to get the cost. Please follow below screenshots.

You can see the average unit cost price for item through this navigation Cost management -> Inventory accounting -> Released products -> Manage Costs (Tab) -> Costing (Section) -> Cost Object (Option).

Source Code Explanation (1.1):

• Initialize and declare objects.
• Set Item Id = 'F-001' (in my case).
• Get invent sum buffer through item id.
• Check inventory dimension, in case of not found created one.

Source Code Explanation (1.2):

• Create an object of invent cost price cache class.
• Get invent table buffer from invent sum.
• Get from & to unit of measurement.
• Construct secondary to invent unit converter buffer.
• Get the convert value.
• Get the OOTB cost price pieces method from cache class that will return average unit cost price of the item.

Result:

Email invoices using built-in email processing in D365FO

In my previous blog I explained how we can email invoices in Dynamics 365 for Finance and Operations with the nicely designed email body and additional email attachments such as Invoice specification or Term & Conditions. Everything worked just fine until we stumbled upon SMTP client timeout when we were emailing invoices in batch, and some emails failed to be sent. The reason was the limitations related to Online Exchange.

In order to resolve SMTP client timeout issue I have done some research on how this can be fixed, and I found the solution within the same free tool (Docentric) we used to email invoices with the nice body and additional attachments.

So, this problem can be solved with Docentric in two ways: 

• Setting up (increasing) the SMTP client timeout.
• Using email processing. This means that instead of sending an invoice right away after it is being generated, the invoice will instead be saved to Email sending status, the built-in outgoing email table used, e.g. for workflow email notifications.

SMTP Client Settings:

In Docentric free-edition tool global parameters we can set up SMTP client settings. Beside standard SMTP settings such as Outgoing email server, SMTP port, if SSL is turned on, etc. you can set up also SMTP client timeout, Body and subject encoding, Delivery format, etc.

Navigation: Organization administration -> Docentric AX -> Docentric AX parameters

Monitor & Save Outgoing Emails:

Out of the box in Microsoft Dynamics 365 Finance and Operations when we print an invoice to the Email print destination, it will be emailed synchronously, i.e. in process. This means that D365FO will wait for a response from the outgoing mail server, which can be Success or Failure. In case of Failure we will only get the error message without a possibility to resend the failed email.

Docentric free-edition tool offers the possibility to save outgoing emails with invoices to Email sending status. This is a standard table used for outgoing emails for workflows and retail notifications. The main advantage is that you can monitor the status of outgoing emails and define retry schedule.

Navigation: System administration -> Periodic tasks -> Email processing -> Email sending status

Now let’s demonstrate how to email invoices by using Docentric free-edition tool through Email sending status, i.e. outgoing email table. For this example I used Sales Invoice report which is emailed via Use print management from Invoice journal.

Navigation: Accounts receivable -> Inquiries and reports -> Invoices -> Invoice journal

Step: 1

Docentric report setup provides multiple options. There we can choose Email sending mode (Synchronous or Email processing) per report. If we choose Synchronous, it will run as out of the box, by sending the email with a generated invoice and additional attachments synchronously. On the other hand, if we choose Email processing, the email will be saved to Email sending status, i.e. the outgoing email table from where it will be sent later by Email distributor batch.

Navigation: Organization administration -> Docentric AX -> Reports

By navigating this link, it will open Docentric AX Reports. Click the Settings tab and select Email sending settings as shown on the image below.

When you click the Email sending settings menu item, it will open a form in which we can select Email processing as Email sending mode. Please see the image below.

That is all you need to do in order to enable emailing your invoices using Email processing. 

Step: 2

For this example we have used the Sales invoice report, which can be printed using Print management setup, so we will now set up the print management settings.

Navigation: Accounts receivable -> Setup -> Forms -> Form setup -> General (Tab) -> Print management

Select Customer invoice and click the marked button to change the target print destination.

Provide To address, Subject and Body, and save your settings.

Step: 3

Now run your Sales invoice report using the Print management option. Please note that when we run the Sales invoice report out of the box without using Docentric Email print destination it will show below message in case of success.

But since we are using Docentric Email print destination with chosen Email processing as Email sending mode for this report, this time the email will be saved to Email sending status, i.e. the outgoing email table, and also the different message will be shown – please check the image below.

Now we can find the email in Email sending status in status Waiting. Here, in this form, you will be able to monitor and see the status of all outgoing emails.

Navigation: System administration -> Periodic tasks -> Email processing -> Email sending status



To send our email from Email sending status, we first have to make sure that we have SMTP server configured and running. Go to System administration -> Setup -> Email -> Email parameters and fill the required settings. Next step is to make sure that Email distributor batch is up and running. Go to System administration -> Inquiries -> Batch jobs and check if the batch job (E-mail distributor batch) exists with status Executing or Waiting. If it doesn’t, you can activate it from System administration -> Periodic -> Email processing -> Email distributor batch.

Now when we run Email distributor batch (as a batch job or without it) it will send all emails with the status Waiting, one by one (with retries and status controlling). The email status will change to Sent or Failed.

Now refresh the Email sending status form. You can see in the image below that our email is successfully sent.

That is how Docentric free-edition tool helps us to save emails with generated invoices, send them reliably and monitor the progress of sending.

Email sending status enhancements:

Built-in Email sending status is also extended by Docentric free-edition tool.

• Additional columns.
• Context information (provides the information about the attached report)
• Company Id (provides the information about the legal entity)

• Download message as EML file that can be open with Outlook.

• Show message is improved to show the list of email attachments as well. You can download one by simply clicking on it.

I found saving outgoing emails with invoices in Email sending status very useful, because we can now monitor and resend them if needed. But there is more what you can do with Docentric free-edition tool. In one of the next articles, I will show you how to email reports with different From email addresses.

Job to get the print management report format name through X++ code in D365

Recently I have a got a requirement in which I want to get the SSRS report format name selected in print management settings, for that I have created a job to get the report format name in print management settings.

For this example, I am using Customer Invoice report (e.g., SalesInvoice.Report).

Note: We can use print management settings for SSRS report through "Use Print Management" menu.

Source code explanation:

• Document type here, Sales order invoice. (Enum)
• Table joins:
• PrintMgmtDocInstance table holds information about the overwritten destinations.
• PrintMgmtReportFormat table contains SSRS report names that users can choose from as alternatives for reports that are controlled by print management.
• PrintMgmtSettings table contains specific print management settings that are applied when a document is printed.
• Get the report name from PrintMgmtReportFormat table, Name field contains the report format name.

Result:

Job to apply vendor prepayment on (PO) invoice through X++ code in D365

Prepayments are a common business practice, with organizations issuing prepayments to vendors for goods or services before those goods or services are fulfilled. To minimize risk, you can track prepayments by defining the prepayment on a purchase order. Vendors can also create a prepayment invoice that is associated with a purchase order.

In this blog, I will discuss how we can apply prepayment through X++ code.in Microsoft Dynamics 365 finance & operation.

Note: For applying the prepayment you need to have the vendor invoice, you can create manually or you can create it by X++ code Job to create vendor invoice (PO) through X++ code in D365.

For this example I have created a job, please see source code screenshots below.

Source code explanation (1.1): 

• Initialize classes and tables buffers.
• Get Vendor invoice by passing purchase order and invoice number.
• Construct the VendAdvanceInvoice class.

Source code explanation (1.2): 

• Pass data to parm methods of VendAdvanceInvoice class.
• Get purchase order amount calculation through PurchTotals class.
• Iterate data of VendInvoiceInfoSubTable table.
• Populate data in temporary tmpVendStandardInvoice table.

Source code explanation (1.3): 

• Iterate record and populate data in tmpVendAdvanceInvoice table.

Source code explanation (1.4): 

• Pass amount to applied as prepayment.
• Pass temporary tables in OOTB of the vendAdvanceInvoice class.
• Update invoice match status.

That is all you need to do, now build and run the job. 

Result: