Business Central, Dataverse, Dynamics 365 Sales, Integration, Synchronize, Testing, Tip & Tricks

Test the Solution Integration of Business Central with Dataverse

This is the Twelfth post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-Testing:

As a Eleventh Step we will Verify & Test the Integration between Business Central and Dataverse for Custom table we created in both the environments.

Initial Stage post Deploying the Extension, we access the Pages for first time.

We have below data in Dataverse Prospects table:

In BC when we access to CDS Prospects Page:

In BC when we access to Prospects Page:

First we will Add one Record in Dataverse:

Now if we check CDS Prospects Page:

If you remember on CDS Prospects Page we have written code on OnInit trigger to run codeunit – CRM Integration Management, so when page is opened, the data is synchronized to CDS Prospects Page.

On CDS Prospects Page, we have also added action Create in Business Central, if we choose this action:

The record in Dataverse will get transferred to Prospect Table in Business Central

Now Let us Add one Record in Business Central, from Prospect Page.

Business logic for calculating Forcast Revenue is not written in BC, but we have in Dataverse table.

When we synchronize data from BC to Dataverse, that will get calculated automatically.

Select the record we just added in BC. Action-> Dataverse-> Coupling-> Setup Coupling

On the new page that opens, Select Create New, And click OK.

Since we have selected Synchronize After Coupling and direction BC to Dataverse, the record will get transferred to Dataverse Table.

Also you can see the other fields too got calculated. Now if we Synchronize back to BC these fields will get updated in BC.

You can see the Synchronization is happening in both directions.

You can make necessary changes to code in sample walkthrough to achieve your desired results.

Hope you enjoyed learning this trick, and will use in your projects, the skill learned from this series of posts.

I will come up with more similar stuffs. Till then keep exploring, learning and sharing with others.

Take care of yourself and your loved ones. Put your mask, maintain safe distance and don’t forget to get vaccinated.

Business Central, Business Central Dataverse Integration, Business Central Integration Business Central to Common Data Service, Dataverse, Dynamics 365 Sales, Integration, Organization, Permission, Power Apps, Roles, Security, Synchronize

Check & Assign Permissions in Power Apps Environment

This is the Eleventh post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-10:

As a Tenth Step we will Verify & Assign Permissions in Power Apps Environment.

This is the final Step, and we will be done with the goal we set in our first post of this series.

Open your Dynamics 365 Sales Environment and Go To Settings.

Under Users + permissions -> Application users.

On next screen Verify “Business Central Integration Business Central to Common Data Service” is selected. If not then select it from “Edit Security Roles”.

From Settings -> Users + permissions -> Security roles

From Security roles select the Business Central Dataverse Integration Then Edit

Select Custom Entities tab and find your table Prospects. Select each circle 4 times to assign Organization, circle should turn completely filled green color.

Select Save and Close.

Now you All done with the steps, Ready to test the solution.

In next post we will test the solution, we created in past 10 posts.

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

Business Central, Dataverse, Dataverse Connection Setup, Dynamics 365 Connection Setup, Fields, Integration, Mappings, Setup, Synchronize

Updating the default Mappings

This is the Tenth post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-9:

As a Nineth Step we will Update the default Mappings.

Open the Dataverse Connection Setup using TellMe.

From Actions -> Use Default Synchronization Setup.

Respond Yes to the Confirmation Message.

You will get the confirmation as completed, say OK to continue.

Next open the Microsoft Dynamics 365 Connection Setup from TellMe.

Go To Mapping -> Integration Mappings.

Now you can find your Custom table Prospects.

Go To Fields, you will see Field Mappings for you table as defined in Integration Codeunit in previous step.

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

Action, AL, Business Central, Coupling, Dataverse, Dynamics 365 Sales, Extension, Integration, Page, Procedure, Synchronize, Triggers, Varables, VS Code

Create Actions on the Page for managing Coupling and Synchronization

This is the Eighth post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-7:

As a Seventh Step we will Create Actions on the Page for managing Coupling and Synchronization created in previous post.

On page 50120 Prospects created in previous post

Add Variables:

var
   CRMIntegrationManagement: Codeunit "CRM Integration Management";
   CRMCouplingManagement: Codeunit "CRM Coupling Management";
   CDSIntegrationEnabled: Boolean;
   CDSIsCoupledToRecord: Boolean;

Add Triggers:

trigger OnOpenPage()
begin
    CDSIntegrationEnabled := CRMIntegrationManagement.IsCDSIntegrationEnabled();
end;

trigger OnAfterGetCurrRecord()
begin
    if CDSIntegrationEnabled then
        CDSIsCoupledToRecord := CRMCouplingManagement.IsRecordCoupledToCRM(Rec.RecordId);
end;

Add Actions:

    Actions
    {
        area(Processing)
        {
            group(ActionGroupCDS)
            {
                Caption = 'Dataverse';
                Visible = CDSIntegrationEnabled;

                action(CDSGotoProspect)
                {
                    Caption = 'Prospect';
                    Image = CoupledCustomer;
                    ToolTip = 'Open the coupled Dataverse Prospect.';
                    ApplicationArea = All;

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.ShowCRMEntityFromRecordID(Rec.RecordId);
                    end;
                }
                action(CDSSynchronizeNow)
                {
                    Caption = 'Synchronize';
                    ApplicationArea = All;
                    Visible = true;
                    Image = Refresh;
                    Enabled = CDSIsCoupledToRecord;
                    ToolTip = 'Send or get updated data to or from Microsoft Dataverse.';

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.UpdateOneNow(Rec.RecordId);
                    end;
                }
                action(ShowLog)
                {
                    Caption = 'Synchronization Log';
                    ApplicationArea = All;
                    Visible = true;
                    Image = Log;
                    ToolTip = 'View integration synchronization jobs for the Prospect table.';

                    trigger OnAction()
                    var
                        CRMIntegrationManagement: Codeunit "CRM Integration Management";
                    begin
                        CRMIntegrationManagement.ShowLog(Rec.RecordId);
                    end;
                }
                group(Coupling)
                {
                    Caption = 'Coupling';
                    Image = LinkAccount;
                    ToolTip = 'Create, change, or delete a coupling between the Business Central record and a Microsoft Dataverse row.';

                    action(ManageCDSCoupling)
                    {
                        Caption = 'Set Up Coupling';
                        ApplicationArea = All;
                        Visible = true;
                        Image = LinkAccount;
                        ToolTip = 'Create or modify the coupling to a Microsoft Dataverse Prospect.';

                        trigger OnAction()
                        var
                            CRMIntegrationManagement: Codeunit "CRM Integration Management";
                        begin
                            CRMIntegrationManagement.DefineCoupling(Rec.RecordId);
                        end;
                    }
                    action(DeleteCDSCoupling)
                    {
                        Caption = 'Delete Coupling';
                        ApplicationArea = All;
                        Visible = true;
                        Image = UnLinkAccount;
                        Enabled = CDSIsCoupledToRecord;
                        ToolTip = 'Delete the coupling to a Microsoft Dataverse Prospect.';

                        trigger OnAction()
                        var
                            CRMCouplingManagement: Codeunit "CRM Coupling Management";
                        begin
                            CRMCouplingManagement.RemoveCoupling(Rec.RecordId);
                        end;
                    }
                }
            }
        }
    }

On page 50122 “CDS Prospect List” created above

Add Variables:

var
    CurrentlyCoupledCDSProspect: Record "CDS cr95d_Prospects";

Add Trigger:

trigger OnInit()
begin
    Codeunit.Run(Codeunit::"CRM Integration Management");
end;

Add Procedure:

procedure SetCurrentlyCoupledCDSProspect(CDSProspect: Record "CDS cr95d_Prospects")
begin
    CurrentlyCoupledCDSProspect := CDSProspect;
end;

Add Action:

Actions
    {
        area(processing)
        {
            action(CreateFromCDS)
            {
                ApplicationArea = All;
                Caption = 'Create in Business Central';
                Promoted = true;
                PromotedCategory = Process;
                ToolTip = 'Generate the table from the coupled Microsoft Dataverse Prospect.';

                trigger OnAction()
                var
                    CDSProspect: Record "CDS cr95d_Prospects";
                    CRMIntegrationManagement: Codeunit "CRM Integration Management";
                begin
                    CurrPage.SetSelectionFilter(CDSProspect);
                    CRMIntegrationManagement.CreateNewRecordsFromCRM(CDSProspect);
                end;
            }
        }
    }

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

Business Central, Dataverse, Power Apps, Synchronize, Table

Create Custom table in Dataverse/ Power Apps

This is the Fifth post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-4:

As a fourth Step we will Create New Table(s) in Dataverse, below is the structure of the table.

This table will be used to Sync Data between Business Central and Dataverse.

Define all required columns. Same as we have done in Business Central, in previous post.

Next we will define Business Rule, This will make Probability field as mandatory.

See the properties of each component.

Sample data for the table.

If you are not comfortable with creating table in Power Apps environment, I will add one post step wise step how this table was created and data was uploaded in this table. Once this series is completed, will provide link to that post here.

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

Business Central, CDS, Common Data Services, Dataverse, Dynamics 365 Sales, Integration, Ownership, Permissions, Synchronize, Tip & Tricks, URL

Setup a connection to Dataverse

Integration with Business Central is done through Dataverse, and you will find lots of standard settings and tables that are provided by the integration.

This is the Second post in the series. If you want to go to previous post click here.

From the series of steps this post is dedicated to Step-1:

Create connection to integrate and synchronize data with another Dynamics 365 business app, such as Dynamics 365 Sales.

As a first Step we will setup a connection to Dataverse.

Before you start make sure you have below information ready with you:

  • URL for the Dataverse environment that you want to connect to
  • The user’s name and password of an account that has administrator permissions in Business Central and Dataverse.
  • The local currency for the company in Business Central must be the same as the base transaction currency in Dataverse. 

From Assisted Setup under Connect with other systems group choose Set up a connection to Dataverse, as shown below.

This will start the Setup wizard, just follow and provide your information asked on each page.

Click Next to Start configuring Dataverse & Business Central connection.

Here you can select your Dataverse Environment or can enter manually, above screen shows where you can find this url from.

Sign in with an administrator user account and give consent to the application that will be used to connect to Dataverse.


Choose Sign in with administrator user.

After Sign in with administrator user turns green and bold, choose Next.

Select Ownership and Click Next.

Click on Finish to complete the Setup.

Next open the Dataverse Connection Setup from TellMe.

Select from Connection -> Test Connection

You can check Microsoft docs for more information

Connect to Microsoft Dataverse

If all are OK it should show the Connection test Successful message.

Now you are good to proceed with Next Step.

You can jump to Next Step from here.

Data, Development Tips, How To, Information, Instalation & Configuration, Replication, SQL, Tip & Tricks

Database Replication – Part V

This post is in continuation to my earlier post. Please check if you missed.

Database Replication – Part I

Database Replication – Part II

Database Replication – Part III

Database Replication – Part IV

As committed in this post we will continue to cover practical approach, Next step from last post.

We will create a subscription using SQL Server Management Studio

To create the subscription

  • Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
  • In the Local Publications folder, right-click the Nav2018ItemTrans publication, and then click New Subscriptions.

DR-39

The New Subscription Wizard launches.

DR-40

  • On the Publication page, select Nav2018ItemTrans, and then click Next.

DR-41

  • On the Distribution Agent Location page, select Run all agents at the Distributor, and then click Next.

DR-42

  • On the Subscribers page, if the name of the Subscriber instance is not displayed, click Add Subscriber, click Add SQL Server Subscriber, enter the Subscriber instance name in the Connect to Server dialog box, and then click Connect.
  • On the Subscribers page, select the instance name of the Subscriber server, and select under Subscription Database.
  • On the New Database dialog box/Select from Drop Down List, enter Nav2018ReplDatabase in the Database name box, click OK, and then click Next.

DR-43

  • In the Distribution Agent Security dialog box, click the ellipsis (…) button, enter <Machine_Name>\repl_distribution in the Process account box, enter the password for this account, click OK.

DR-44

  • Click Next.

 

DR-45DR-46DR-47DR-48DR-49

  • Click Finish to accept the default values on the remaining pages and complete the wizard.

Setting database permissions at the Subscriber

  • Connect to the Subscriber in SQL Server Management Studio, expand Databases, Nav2018ReplDatabase, and Security, right-click Users, and then select New User.
  • On the General page, in the User type list, select Windows user.
  • Select the User name box and click the ellipsis (…) button, in the Enter the object name to select box type <Machine_Name>\repl_distribution, click Check Names, and then click OK.

DR-50

  • On the Membership page, in Database role membership area, select db_owner, and then click OK to create the user.

DR-51

When setting up SQL Server replication you might see an error message from the Transactional Replication Log Reader Agent which reads like the following.

Error messages:

  • The process could not execute ‘sp_replcmds’ on ”. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011
  • Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517) Get help: http://help/15517
  • The process could not execute ‘sp_replcmds’ on ”. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037) Get help: http://help/MSSQL_REPL22037

Often this error message can come from the database not having a valid owner, or the SQL Server is not being able to correctly identify the owner of the database.

Often this is easiest to fix by changing the database owner by using the sp_changedbowner system stored procedure as shown below. The sa account is a reliable account to use to change the ownership of the database to.

USE PublishedDatabase

GO

EXEC sp_changedbowner ‘sa’

GO

Once the database ownership has been changed the log reader will probably start working right away. If it doesn’t quickly restarting the log reader should resolve the problem.

While this does require changes to the production database, there is no outage required to make these changes.

To view the synchronization status of the subscription

  • Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
  • In the Local Publications folder, expand the Nav2018ItemTrans publication, right-click the subscription in the Nav2018ReplDatabase database, and then click View Synchronization Status.

DR-52

The current synchronization status of the subscription is displayed.

DR-53

  • If the subscription is not visible under Nav2018ItemTrans, press F5 to refresh the list.

Validating the Subscription and Measuring Latency

We will use tracer tokens to verify that changes are being replicated to the Subscriber and to determine latency, the time it takes for a change made at the Publisher to appear to the Subscriber.

To insert a tracer token and view information on the token

  • Connect to the Publisher in SQL Server Management Studio, expand the server node, right-click the Replication folder, and then click Launch Replication Monitor.
  • Replication Monitor launches.
  • Expand a Publisher group in the left pane, expand the Publisher instance, and then click the Nav2018ItemTrans publication.
  • Click the Tracer Tokens tab.
  • Click Insert Tracer.
  • View elapsed time for the tracer token in the following columns: Publisher to Distributor, Distributor to Subscriber, Total Latency. A value of Pending indicates that the token has not reached a given point.

DR-54

This way we are done with Database Replication Setup.

Will come up with more topic soon.

 

Development Tips

Schema Synchronization in Microsoft Dynamics Navision 2015

NAV 2015 provides you with flexibility of deciding when and how your table changes should be synchronized with corresponding SQL tables.

What is table synchronization?

SchemaSync-1

NAV 2013 R2:- Schema synchronization executes automatically on any connection to the Server.

NAV 2015:- Schema synchronization executes on demand by the Server.

Way the Synchronization works:

  1. Per Table Synchronization
  2. Synchronizing schema changes for all tables

SchemaSync-2

Now let us see how it works:

SchemaSync-3

Add/Modify/Delete Fields (change table definition) we will be prompted for Synchronizing the Schema.

It gives three options description is self-explanatory.

Some time we have to do major changes to the tables which takes lots of time to sync, we can postpone the sync process by selecting Later. Once we are done with all of our changes we can go with synchronization in one go.

SchemaSync-4

We get same options.

New option Check Only….

SchemaSync-5

This option will only report outcome and will not Sync the Schema.

Since we have just added new field so no risk as reported by above check.

Now I will delete one field from the table and let’s see what result comes.

SchemaSync-6

Summary of the 3 options as below:

SchemaSync-7

When data loss is reported while changes in table definition is referred as destructive changes.

SchemaSync-8

You use upgrade codeunits when you make changes to a table definition, either from the Microsoft Dynamics NAV Development Environment or during an upgrade. Upgrade codeunits migrate existing business data from the old table structure into the new table structure. An upgrade codeunit serves the following purposes:

  • Provides instructions for how to handle data changes to a table during schema synchronization.
  • Provides the logic for migrating existing data in the business data table from the old format to the new format after schema synchronization.

A typical example of when to use an upgrade codeunit is when you remove a field from a table definition and you do not want to lose the existing data in the business data table. Instead, you want to use it somewhere else in the new application.

You implement upgrade codeunits when you make destructive changes to tables in Microsoft Dynamics NAV Development Environment. After you have created an upgrade codeunit to handle table changes, you can use it when you upgrade data from an earlier version of Microsoft Dynamics NAV to the current version. A single upgrade codeunit will typically contain table schema synchronization instructions and data migration logic for multiple tables that have changed from one version of Microsoft Dynamics NAV to another.

SchemaSync-9

I will come back on this topic in another post with more details.

And many more options like PowerShell Sync with above options.

Benefits:-

Perform on demand – transparency -> As developer and system administrator, you are in control when to call the schema synchronization and how to synchronize table changes. You can invoke it, plan for it and schedule for it on a case by case basis. You can also monitor its progress and monitor the state of your database (tenant).

Not blocking other changes – productivity -> Schema Synchronization has become more granular. It can be performed per table object as well as for all table changes at once. When one table is being synchronized you are no longer blocked, and you can continue making changes to other tables.

Opened for additional instructions – flexibility -> Using upgrade codeunits you can provide input for the schema synchronization to do extra tasks, like automatically moving or copying data into upgrade tables, checking the changes or forcefully applying them where necessary. (Will come up with more details on this in my next post)