Middleware options for Dynamics 365 Finance and Operations integrations

We know now a days integration requirements are increasing tremendously for d365fo implementations to make smooth communication with customers existing systems. This is to give an overview of different applications available which can help to explore middleware needs for D365 finance requirements and gives ideas on how to create our own middleware applications by considering different parameters.

There are several middleware solutions available for integrating D365 FO with other systems. Here are some suggestions with a quick overview:

1. Microsoft Power Automate (formerly known as Microsoft Flow): Power Automate is a cloud-based service that allows users to create workflows and automate tasks across multiple applications and services. It has pre-built connectors to many popular systems and applications, including D365 Finance Operations.
2. Dell Boomi: Dell Boomi is a cloud-based middleware solution that enables users to connect applications and data across on-premises and cloud environments. It has pre-built connectors to many popular systems and applications, including D365 Finance Operations.
3. Azure Logic Apps: Azure Logic Apps is a cloud-based service that enables users to create workflows and automate tasks across multiple applications and services. It has pre-built connectors to many popular systems and applications, including D365 Finance Operations.
4. MuleSoft: MuleSoft is a middleware platform that enables users to connect applications, data, and devices across on-premises and cloud environments. It has pre-built connectors to many popular systems and applications, including D365 Finance Operations.
5. Celigo: Celigo is a cloud-based integration platform that enables users to connect applications and data across on-premises and cloud environments. It has pre-built connectors to many popular systems and applications, including D365 Finance Operations.
6. Azure Service Bus: Azure Service Bus is a messaging service that enables reliable and asynchronous communication between D365 F&O and other applications. It's particularly useful for scenarios where you need to decouple systems and handle large volumes of messages.
7. Azure Functions: Azure Functions allow you to create serverless, event-driven functions that can be used for integration tasks. You can trigger these functions based on events in D365 F&O and perform data transformations or send/receive data from other systems.
8. TIBCO Cloud Integration: TIBCO's cloud-based integration platform provides a range of connectors and tools for integrating D365 F&O with banking systems and other applications. It emphasizes real-time data integration and automation.
9. SnapLogic: SnapLogic is an integration platform that offers a visual, drag-and-drop interface for creating integrations. It supports D365 F&O integrations with various banking and financial systems.
10. Informatica: Informatica's Integration Cloud provides capabilities for connecting D365 F&O with banking systems and other applications. It offers data integration, API management, and application integration features.
11. Jitterbit: Jitterbit offers an integration platform with pre-built connectors for D365 F&O and various banking systems. It focuses on ease of use and scalability.
12. Talend: Talend provides data integration and transformation capabilities for integrating D365 F&O with banking and financial systems. It offers both cloud and on-premises deployment options.

When selecting a middleware solution for D365 Finance Operations integrations, it’s important to consider factors such as the complexity of the integration, the volume of data to be processed, and the level of customization required. It’s also important to ensure that the middleware solution has adequate security measures in place to protect sensitive data.

Database Synchronization using PowerShell in Dynamics 365 Finance and Operations Tier1 Environment

We will see how we can synchronize the database through PowerShell in Microsoft Dynamics 365 Finance and Operations

You can either execute the script for cloud-hosted, onebox VHD, or UAT environment. This is not applied with one-box Microsoft hosted environment.

Open PowerShell, run the following script

Steps:

K:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe 
-bindir "K:\AosService\PackagesLocalDirectory" 
metadatadir "K:\AosService\PackagesLocalDirectory" 
-sqluser "axdbadmin" -sqlserver "." -sqldatabase "AxDB" 
-setupmode "sync" -syncmode "fullall" 
-isazuresql "false" -sqlpwd "************" 
-logfilename "C:\Temp\dbsync.log"

Example:

K:\AOSService\webroot\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe 
-bindir "K:\AosService\PackagesLocalDirectory" 
metadatadir "K:\AosService\PackagesLocalDirectory" 
-sqluser "axdbadmin" -sqlserver "." -sqldatabase "AxDB" 
-setupmode "sync" -syncmode "fullall" 
-isazuresql "false" -sqlpwd "AOSWebSite@123" 
-logfilename "C:\Temp\dbsync.log"

AX DB user you can get from LCS, if you want to sync Azure DB please change -isazuresql to True

You can also use d365fo.tools to trigger the DB sync.

Tier 1

Invoke-D365DBSync -Verbose

Tier 2

Invoke-D365DbSync -DatabaseServer .database.windows.net  -DatabaseName db_d365opsprod -SqlUser JIT-6h8cg -SqlPwd NjT -LogPath "C:\Temp\d365fo.tools\dbsync.log"

Import CSV file in Dynamics 365 for Finance and Operation using X++

Below is a simple example for importing data from CSV file in D365 Finace and Operations using X++

/// <summary>

/// import color code

/// </summary>

class ImportColorCode

{

    /// <summary>

    /// main

    /// </summary>

    /// <param name = “_args”>_args</param>

  public static void main(Args _args)

    {

        AsciiStreamIo                       file;

        Array                               fileLines;

        FileUploadTemporaryStorageResult    fileUpload;

        EcoResColorCode                  colorCode;

        ImportColorCode                  importColorCode = new ImportColorCode();

        Counter                             counter = 0;

        EcoResColorName                     color;

        #OCCRetryCount

        try

        {

            //Upload a file

            fileUpload  = File::GetFileFromUser() as FileUploadTemporaryStorageResult;

            file        = AsciiStreamIo::constructForRead(fileUpload.openResult());

            if (file)

            {

                if (file.status())

                {

                    throw error(“@SYS52680”);

               }

                file.inFieldDelimiter(‘;’); //separator

                file.inRecordDelimiter(‘\r\n’);

            }

            //Read a CSV File

            container rec;

            ttsbegin;

            while (!file.status())

            {

                counter++;

                rec = file.read();

                if (conLen(rec))

                {

                    color = conPeek(rec, 2);

                    colorCode = EcoResColorCode::find(color);

                    if(!colorCode.RecId)

                    {

                        colorCode.clear();

                        colorCode.Name  = color;

                        colorCode.Code  = conPeek(rec, 1);

                        colorCode.insert();

                    }

                }

            }

            ttscommit;

            info(“Operation complete.”);

        }

        catch (Exception::Deadlock)

        {

            retry;

        }

        catch (Exception::UpdateConflict)

        {

            if (appl.ttsLevel() == 0)

            {

                if (xSession::currentRetryCount() >= #RetryNum)

                {

                    throw Exception::UpdateConflictNotRecovered;

                }

                else

                {

                    retry;

                }

            }

            else

            {

                throw Exception::UpdateConflict;

            }

        }

    }

}

Caching display Methods in X++

The performance of display methods can be improved by caching them if they are calculated on Application Object Server (AOS). Caching display methods can also improve performance when records are transferred from the server to the client.

The value of all cached methods is set when data is fetched from the back-end database. In addition, the value is refreshed when the reread method is called on the form data source.

Add a display Method to the Cache

1. Locate the form that the method is used on.

2. Expand the Data Sources node.

3. Right-click the data source that the method is associated with, and then select Override Method > init.

4. Call the FormDataSource.cacheAddMethod method after the call to super() in the init method.

   The first parameter for cacheAddMethod determines the name of the method to be cached. The second parameter (set to true by default) determines whether the value of the display method is updated when a record is written to the database.

[SysClientCacheDataMethodAttribute(true)]
public display Reasoncode display_ReasonCode()
{
reasoncode ret = '';
.......
return ret;
}

Create and Importing Deployable Package to UAT/Production Using Lifecycle Services in Dynamics 365 Finance And Operations

Overview

In Microsoft Dynamics 365 Finance and Operations, development and customization can be done within development environment and deployed to UAT / Production environment. In this blog we are going to discuss how these changes can be deployed to UAT / Production environment

What is a deployable package?

Let’s first define what a development environment package or a deployable package is, before we dive into the deployment process. A deployable package is a collection of customizations and extensions that have been developed in a development environment. This package contains the code, metadata, and configurations that can then deploy to the UAT or Production environment.

Steps for deploying changes using Lifecycle Services

Step 1: Create Deployable package and save it in a folder.

Before creating a deployment package, it is recommended to perform a full build of the required models. 
This ensures that all changes and customizations are included in the deployment package. 
In Microsoft Visual Studio, select Extensions >Dynamics 365 > Build Models > Full Build and select all models. Alternatively, select only the models that have been modified or customized.

After you have completed the development stage, follow these steps to create a deployable package from Visual Studio.

In Microsoft Visual Studio, select Extensions >Dynamics 365  Deploy > Create Deployment Package.

Select the packages that contain your models, and then select a location in which to create the deployable package. 

For more information about the deployable package creation, please refer here

Step 2: Go to https://lcs.dynamics.com and enter your credentials

Step 3: Click on your implementation project. Then go to Asset Library using 3 bars icon as shown in the below screenshot.

Step 4: Go to Software deployable package Tab in Asset Library. Then add the new package here which we have created from dev environment.

Step 5: As we can see in below screenshot, we need to give name for uploading our package into Asset Library first. Then the package will be eligible to deploy to UAT or Production.

Step 6: Once package is added into asset library and if you want to publish it in the UAT environment, click on the highlighted option in the screenshot below.

Step 7: Now go to navbar Maintain and click on Apply updates

Step 8: Provide unique name for you Package. Such as write package name with date and then select the package uploaded in asset library and click on Apply 

Step 9: It can take about 1 to 3 or more hours depending on the size of the package.

Step 10: After successful deployment we can check the Activity status completed or Rollback. If we get any errors during the deployment, it will be rollback and we need to fix it in development environment. Afterwards the whole process of deployment will repeat.

Step 11: Once the deployment is done on the sandbox, make the deployed package as release candidate for moving to production.

Step 12: To initiate package deployment on production, navigate to production environment details page and select Maintain>Update Environment, and then select our package and mention the production downtime. 

Best Practices for Deploying a Development Environment Package to UAT/Production

To ensure a successful deployment, it’s important to follow these best practices:

1. Always test the deployment in a non-production environment first.
2. Include all the required models and packages in the deployable package.
3. Properly configure the target environment so that it can meet the requirements of the customizations and extensions.
4. Take a backup of the target environment before deploying the package.
5. Monitor the deployment progress and resolve any issues that arise during the deployment process.

Note: 

These all are manual steps, but till sandbox package deployment can be achieved through DevOps continuous deployment (Automated deployment) using Azure DevOps release pipeline.

Rollback:

We have the option to rollback our changes, also we have the option to create support ticket with Microsoft (LCS project>Work items>Support issues>Submitted to Microsoft) for deployment related issue, make sure you enabled the notifications on LCS user settings to get the deployment related email updates.

Install Quality update in a Sandbox or Production environment in LCS Dynamics 365 F&O

Install Quality update in a Sandbox or Production environment in LCS #d365fo

A quality update is a cumulative, roll-up build that contains fixes for known issues that are specific to the service update.

A quality update is available when your environment is running the same version of the current service update (n), or when your environment is running on one version older than the current service update (n-1). For example, if the current service update is version 10.0.2, you will have the option to choose a quality update if you’re running version 10.0.2, or if you’re running one version older, which is 10.0.1.

There will be no quality update available for any version that’s older than 2 versions of the current service update. You will have to apply the latest service update to stay current.

Access your LCS Project

Access the environment you want to check (choose Sandbox or Production environment)

To create a package for a Quality Update of an Environment you need to go in the “Available updates” section in the Environment details page and click on the “View update”

After which we need to click on “Save package” to create our package

Click on “Select all” and after that on “Save package” again

Give it a reasonable Name and Description for you

Save the package (it may take several minutes) and click done.

After that the package should appear in the Asset library give it some time to get validated and you can go and apply it.

Access again into your environment

Click on “Maintain” –> “Apply updates”

Select Quality update from your asset library list, give it a unique name then click “Apply” to install

AOT Table Properties in Dynamics 365 Finance and Operations

 Hello there. In this blog, I will delve into the properties of D365F&O AOT Tables in depth. When we create a new table, the first step is to update its properties, which is a straightforward task. Among all the available properties, we will focus our discussion on only the most important ones.

1. Abstract

This property determines whether the table supports Inheritance. By default, it is set to "No." If you set it to "Yes," the table cannot be directly used as a target in X++ SQL statements like "update_recordset" and "select."

Please note that this property is not available when the "SupportInheritance" property is set to "No."

1. AOSAuthorizationThis property defines the type of operation that a user is allowed to perform on a table, depending on their permissions. When this property is set to "None," it means that no authorization check is carried out.
2. CacheLookup

This property is closely related to the table type, and warnings will be issued if an inappropriate caching level is chosen. Here are the available cache levels:

1. None: No caching is applied.
2. NotinTTS: Data is fetched once for each transaction.
3. Found: Data is cached once it's found and not looked up again.
4. EntireTable: The entire table is loaded into memory.

The cache is only refreshed when records are updated or flushed.

1. ClusterIndex

This property is used only for SQL optimization.

1. ConfigurationKey

Configuration keys allow a system administrator to enable and disable certain parts of an application.

1. CreatedBy

Indicates whether the system maintains the CreatedBy field for the records in a table. This field contains information about who created a particular record.

1. CreatedDateTime

Indicates whether the system maintains the CreationDate and CreationTime fields for the records in a table. This field contains the date when a record was created.

1. CreatedTransactionId

Indicates whether the system maintains the CreatedTransactionId field for the records in a table. This field contains information about which transaction created the record.

1. CreateRecIdIndex

Indicates whether an index on the Record ID field is created.

1. DeveloperDocumentation

Describes the purpose of a table and explains how it is used. A description is typically no more than five sentences long and is written as a single paragraph.

1. EntityRelationshipType

Classifies a table according to common entity relationship (ER) data model notation. A table is classified as an entity or a relationship. An entity represents an object. A relationship represents an association between two objects.

1. Extends

Derives the table from another table that is chosen as the property value. The value is null when the SupportInheritance property is set to Yes.

1. FormRef

This property specifies the display menu item that becomes active when a table is referenced. A display menu item is typically linked to a form. When you utilize a primary index field in a report, this form is accessible as a hyperlink within the report. The primary index is defined using the "PrimaryIndex" property. If you leave this field empty, the system will try to display a form with a name that matches the table's name.

1. ID

Specifies the table ID generated by the system.

1. IsLookup

For report models, this property determines whether the information from the table is included in other tables that reference it when generating a report model. In the context of OLAP cubes, it decides whether to create a combined dimension or a separate dimension. You can choose from the following options:

• "Yes" - Signifies that attributes from the table should be combined into the parent dimension (known as a star schema).
• "No" - Signifies that a distinct dimension should be generated for the table (known as a snowflake schema).

1. Label

Specifies the label for a table.

1. Model

Specifies which model the table is in. A model is a logical grouping of elements in a layer. An element can exist in exactly one model in a layer. Examples of elements are a table or class. The same element can exist in a customized version in a model in a higher layer.

1. ModifiedBy

Indicates whether the system maintains the ModifiedBy field for the records in a table. This field records the person who performed the last modification to a record.

1. ModifiedDateTime

Indicates whether the system maintains the ModifiedDateTime field for the records in a table. This field records the date of the last modification of a record.

1. ModifiedTime

Indicates whether the system maintains the ModifiedTime field for the records in a table. This field records the time when a record was last modified.

1. Name

Specifies the table name.

1. OccEnabled

This property determines whether the optimistic concurrency mode is activated for a table. When this mode is enabled, data is not locked against future modifications when it's retrieved from the database. Instead, data is locked only when an actual update is carried out.

1. PrimaryIndex

This property indicates the primary index for the table. It's important to note that only a unique index can be chosen as the primary index. This property serves two main purposes:

1. It helps optimize database operations.
2. It specifies which unique index should be used as the key for caching purposes.

If you don't specify a primary index, the system will automatically use the unique index with the lowest ID as the caching key.

1. ReplacementKey

Specifies the fields to display as the identifier for data in some form controls.

1. ReportRef

This property defines the output menu item that becomes active when a table is referenced. An output menu item is typically linked to a report. When you utilize a primary index field within a report, this report is accessible as a hyperlink within the report. The primary index is specified using the "PrimaryIndex" property.

1. SaveDataPerCompany

Indicates whether the data for the current company is saved. If you set the property to No, data is saved without a company identifier (DataAreaId).

1. TableType

It refers that it is a regular database table or a temporary table.

TitleField1, TitleField2

Enables you to do the following:

• Add table field data to a form caption.
• Display additional fields in a lookup form.

For more information, check MS documentation on it,  How to: Add a Control with a Lookup Form. The TitleField1 property is also used when activating the lookup list in a field on a form. The fields you specify for TitleField1 and TitleField2 properties can be merged with the key value.

• Display field information in a tooltip.

1. ValidTimeStateFieldType

Specifies the type of date-time field for the system to use when it tracks data within time spans.

1. Visible

Specifies the access rights when the table is used as a data source in a form or a report. If the table is used as a data source in a form, then the access rights in the form cannot exceed the access rights defined for the table.