Create Multiple Sheet Excel Book - Also send on email in BC SaaS

Hello Friends,

Below sample code to save multiple sheet data in final merge sheet

    var     

        ExcelBuf: Record "Excel Buffer" temporary;

        ExcelBuf2: Record "Excel Buffer" temporary;

        ExcelBuf3: Record "Excel Buffer" temporary;

        MergeExcelBuf: Record "Excel Buffer" temporary;

procedure CreateExcelbook()

    begin

        if not SendEmil_gBln then begin

            ExcelBuf.Reset();

            IF ExcelBuf.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf;

                    MergeExcelBuf.Insert();

                until ExcelBuf.Next() = 0;

            end;

            CreateExcelSheet('Sales', TRUE);

            ExcelBuf2.Reset();

            IF ExcelBuf2.FindFirst() THen begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf2;

                    MergeExcelBuf.Insert();

                until ExcelBuf2.NExt = 0;

            end;

            CreateExcelSheet('Collection', false);

            ExcelBuf3.Reset();

            IF ExcelBuf3.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf3;

                    MergeExcelBuf.Insert();

                until ExcelBuf3.NExt = 0;

            end;

            CreateExcelSheet('Receivables', false);

            CreateExcelBook_Final;

        end else begin

            ExcelBuf.Reset();

            IF ExcelBuf.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf;

                    MergeExcelBuf.Insert();

                until ExcelBuf.Next() = 0;

            end;

            CreateExcelSheet('Sales', TRUE);

            ExcelBuf2.Reset();

            IF ExcelBuf2.FindFirst() THen begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf2;

                    MergeExcelBuf.Insert();

                until ExcelBuf2.NExt = 0;

            end;

            CreateExcelSheet('Collection', false);

            ExcelBuf3.Reset();

            IF ExcelBuf3.FindSet() Then begin

                repeat

                    MergeExcelBuf.Init();

                    MergeExcelBuf := ExcelBuf3;

                    MergeExcelBuf.Insert();

                until ExcelBuf3.NExt = 0;

            end;

            CreateExcelSheet('Receivables', false);

        end;

    end;

    local procedure CreateExcelSheet(SheetName: Text[250]; NewBook: Boolean)

    begin

        if NewBook then

            MergeExcelBuf.CreateNewBook(SheetName)

        else

            MergeExcelBuf.SelectOrAddSheet(SheetName);

        MergeExcelBuf.WriteSheet(SheetName, CompanyName, UserId);

        MergeExcelBuf.DeleteAll();

        MergeExcelBuf.ClearNewRow();

    end;

    local procedure CreateExcelBook_Final()

    var

        TempBlob_lCdu: Codeunit "Temp Blob";

    begin

        MergeExcelBuf.CloseBook();

        MergeExcelBuf.SetFriendlyFilename('Stock Report');

        MergeExcelBuf.OpenExcel();

    end;

    procedure GetExportExcelFileToBlob(var TempBlob: Codeunit "Temp Blob")

    var

        OutStr: OutStream;

    begin

        MergeExcelBuf.CloseBook();

        MergeExcelBuf.SetFriendlyFilename('Stock Report');

        TempBlob.CreateOutStream(OutStr);

        MergeExcelBuf.SaveToStream(OutStr, true);

    end;

=============

Now below code is use to send email for multiple sheet email

    Clear(FortnightlyReport_lRpt);

    FortnightlyReport_lRpt.SetMail_gFnc(CalcDate('<-CM>', Today), Today);  //T12358-N

    FortnightlyReport_lRpt.UseRequestPage(false);

    FortnightlyReport_lRpt.RunModal;

    FortnightlyReport_lRpt.GetExportExcelFileToBlob(TempBlob_lCdu);

  if not FortnightlyReport_lRpt.GetDataInserted_gBln then

      CurrReport.Break;

  TempBlob_lCdu.CREATEINSTREAM(Instr);

  EmailMessage.AddAttachment('Fortnightly Report.XLSX', 'xlsx', Instr);

Smart SQL Query to Get Combine Customer Age Reports from all Companies in Database

 Hi Friends,

I want to share one Smart SQL Query tto Get Combine Customer Age Reports from all Companies in Database.

It is very useful when same customer code created in multiple companies and we need to get combine age reports.

SP1:

/****** Object:  StoredProcedure [dbo].[CustomerAging_duedate_1]    Script Date: 11/17/2022 12:51:28 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Alter proc [dbo].[CustomerAging_duedate_1]

 @asondt datetime,

 @comp varchar(50)

 as

 begin

 if OBJECT_ID('#report') is not null drop table #report

 create table #report

 (

 [Company] varchar(50),

[Customer No.]  varchar(20),

[Customer Name] varchar(50),

[Currency] varchar(10),

[Posting Date] datetime,

[Document Type]  integer,

[Document No.] varchar(20),

[Description] varchar(100),

[Document Date] datetime ,

[Due Date] datetime,

[Original Amount] numeric(38,20),

[Balance] numeric(38,20),

[Balance Due] numeric(38,20),

[Outstanding Days] numeric(38,20),

[Balance (LCY)] numeric(38,20)

)

 declare @sql  nvarchar(max)

 

 set @sql = 'Insert into #report ( [Company],[Customer No.],[Customer Name],[Currency],[Posting Date],[Document Type],[Document No.],[Description],[Document Date],[Due Date],[Original Amount],[Balance],[Balance Due],[Outstanding Days],[Balance (LCY)] )'

 set @sql = @sql + 'SELECT  [Company] = '''+ @comp  + ''',[Customer No.] = c.No_ ,[Customer Name] = c.Name,[Currency] = l.[Currency Code],[Posting Date] = l.[Posting Date]  ,

[Document Type] =l.[Document Type],[Document No.] = l.[Document No_],[Description] = l.Description,[Document Date] = l.[Document Date],

[Due Date] = l.[Due Date],[Original Amount] =a.[Amount (LCY)],[Balance] = a.[Remening Amount],[Balance Due] = a.[Remening Amount (LCY)],[Outstanding Days] = DATEDIFF(dd, l.[Due Date], @asondt),

[Balance (LCY)] = a.[Remening Amount (LCY)] FROM ['+ @comp + '$Customer] c inner join [' + @comp  +'$Cust_ Ledger Entry] l on c.No_=l.[Customer No_]

OUTER APPLY  (SELECT  sum(case when [Entry Type] = 2 then 0 else [Amount (LCY)] end) [Amount (LCY)], sum([Amount (LCY)]) [Remening Amount (LCY)]

        ,sum(case when [Entry Type] = 2 then 0 else [Amount] end) [Amount], sum([Amount]) [Remening Amount]

FROM [' + @comp + '$Detailed Cust_ Ledg_ Entry]

WHERE [Cust_ Ledger Entry No_] = l.[Entry No_] and [Posting Date] <=  CONVERT(smalldatetime,@asondt ,103)  ) a where l.[Posting Date] <=  CONVERT(smalldatetime,@asondt ,103)' 

print(@sql) 

EXEC sys.sp_executesql @sql, N'@asondt smalldatetime',@asondt

--select * from #report

select 

[Company],

[Customer No.],

[Customer Name],

[Currency],

[Posting Date],

[Document Type],

[Document No.],

[Description],

[Document Date],

[Due Date],

[Original Amount],

[Balance Due] = case when [Outstanding Days] >= 0 then [Balance] else 0 end,

[Not Due] =case when [Outstanding Days] < 0 then [Balance] else 0 end,

[0 - 30 Days] = case when  [Outstanding Days] between 0 and 30 then [Balance] else 0 end,

[31 - 60 Days] = case when  [Outstanding Days] between 31 and 60 then [Balance] else 0 end,

[61 - 90 Days] = case when  [Outstanding Days] between 61 and 90 then [Balance] else 0 end, 

[Over 90 Days] = case when  [Outstanding Days] >90 then [Balance] else 0 end, 

[Balance in LCY] = [Balance (LCY)] 

from #report where Balance <>0

end

=======================

SP2:

/****** Object:  StoredProcedure [dbo].[CustomerAging_duedate_2]    Script Date: 11/17/2022 12:51:53 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc [dbo].[CustomerAging_duedate_2]

@asondt datetime

 as

 begin

  if OBJECT_ID('#report1') is not null drop table #report1

 --& ( ) . / \

 

  select  ROW_NUMBER () over (order by Name) as sr, * into #temp1 from Company

 declare @i integer

 declare @icnt integer

 set @i = 1

 set @icnt = (select max(sr) from #temp1)

 create table #report1 (

[Company] varchar(50),

[Customer No.]  varchar(20),

[Customer Name] varchar(50),

[Currency] varchar(10),

[Posting Date] datetime,

[Document Type]  integer,

[Document No.] varchar(20),

[Description] varchar(100),

[Document Date] datetime ,

[Due Date] datetime,

[Original Amount] numeric(38,20),

[Balance Due] numeric(38,20),

[Not Due] numeric(38,20),

[0 - 30 Days] numeric(38,20),

[31 - 60 Days] numeric(38,20),

[61 - 90 Days] numeric(38,20),

[Over 90 Days] numeric(38,20),

[Balance in LCY] numeric(38,20)

)

 declare @comp varchar(100)

 while @icnt >= @i begin

 set @comp = (select Name from #temp1 where sr = @i)

 --& ( ) . / \

set @comp = REPLACE(@comp,'&','_')

set @comp = REPLACE(@comp,'.','_')

set @comp = REPLACE(@comp,'/','_')

set @comp = REPLACE(@comp,'\','_')

print(@comp)

 insert into #report1 (

 [Company] ,

[Customer No.] ,

[Customer Name] ,

[Currency] ,

[Posting Date] ,

[Document Type] ,

[Document No.] ,

[Description] ,

[Document Date] ,

[Due Date] ,

[Original Amount] ,

[Balance Due] ,

[Not Due] ,

[0 - 30 Days],

[31 - 60 Days] ,

[61 - 90 Days] ,

[Over 90 Days] ,

[Balance in LCY] )

exec CustomerAging_duedate_1 @asondt,@comp

set @i = @i +1

end

select * from  #report1

 end

=====================================

Run Second Query only pass date to get age data

EXEC [CustomerAging_duedate_2] '2022-11-01'

It will be very useful query.

Keep Sharing....Keep Growing....

The Tenant Media does not exist. Identification fields and values: ID='{00000000-0000-0000-0000-000000000000}'

Hello Friends,

This error come while we send email with attachment from business center

The Tenant Media does not exist. Identification fields and values: ID='{00000000-0000-0000-0000-000000000000}'

Reason for this error is attachment instream variable we try use is blank and doesn't contain any data.

So avoid this error we need to take dummy instream variable and check it contain any data or not.

see below sample code.

Var

              Instr2: InStream;

              Instr2_Chk: InStream;

Dummy instream variable use

                TempBlob1_lCdu.CreateOutStream(Out1);

                ReportInvHdr_lRec.Reset;

                ReportInvHdr_lRec.SetRange("No.", "No.");

                Clear(PostedSalesInvWithoutTax_lRpt);

                PostedSalesInvWithoutTax_lRpt.SetEmailRun_gFnc;

                PostedSalesInvWithoutTax_lRpt.SetTableview(ReportInvHdr_lRec);

                PostedSalesInvWithoutTax_lRpt.UseRequestPage(false);

                PostedSalesInvWithoutTax_lRpt.SaveAs('', REPORTFORMAT::Pdf, Out1);

                TempBlob1_lCdu.CREATEINSTREAM(Instr1);

                TempBlob1_lCdu.CREATEINSTREAM(Instr1_Chk);  //Dummy instream variable

to check it contain any value

While send email we check instream have value

we read instream value in dummy text variable and verify that contain any values

                ReadTextChk_lTxt := '';

                Instr1_Chk.ReadText(ReadTextChk_lTxt);

                IF ReadTextChk_lTxt <> '' THen

                    EmailMessage.AddAttachment(FirstPDFName_lTxt, 'PDF', Instr1);

if you add above condition then issue will solve.

Thank you for reading...

Keep Sharing....Keep Growing....

Business Central API 2.0 Version - Structure of API and Syntax for Get

 

Hello Friends, 

Business Central API 2.0 

Below are some best blog to learn it.

https://yzhums.com/6117/

List of API BC - https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/api-reference/v2.0/

Develop new API Page - https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-develop-custom-api

https://businesscentralgeek.com/business-central-api-guide-in-2022

 

API Structure

https://api.businesscentral.dynamics.com/v2.0/TenantID/<serverinstance>/api/<API publisher>/<API group>/v2.0/CompanyID/EntitySetName

 

We have create two new API page

Below are useful list of API with structure

 

1) Get all companies id - https://api.businesscentral.dynamics.com/v2.0/0cd33610-3443-4d21-a9a1-5b0f3ae1dc84/Sandbox_Intech/api/v2.0/companies

 

2) Get all standard API list - https://api.businesscentral.dynamics.com/v2.0/0cd33610-3443-4d21-a9a1-5b0f3ae1dc84/Sandbox_Intech/api/v2.0

 

3) Get all the custom API list by API Group and Publisher - https://api.businesscentral.dynamics.com/v2.0/0cd33610-3443-4d21-a9a1-5b0f3ae1dc84/Sandbox_Intech/api/ISPL/PowerBIReport/v2.0/

 

4) Get API for Customer Led Entry - https://api.businesscentral.dynamics.com/v2.0/0cd33610-3443-4d21-a9a1-5b0f3ae1dc84/Sandbox_Intech/api/ISPL/PowerBIReport/v2.0/companies(8cd9882b-acb1-ec11-8aa5-6045bda57eaf)/customLedgerEntries

page 79131 API_CustomerLedgerEntry

{

    APIGroup = 'PowerBIReport';

    APIPublisher = 'ISPL';

    APIVersion = 'v2.0';

    Caption = 'apiCustomerLedgerEntry';

    DelayedInsert = true;

    EntityName = 'customLedgerEntry';

    EntityCaption = 'customLedgerEntry';

    EntitySetName = 'customLedgerEntries';    //Make Sure First Char in Small Letter

and don't use any special char or space

    EntitySetCaption = 'customLedgerEntries';

    ODataKeyFields = SystemId;

    PageType = API;

    SourceTable = "Cust. Ledger Entry";

5) Get API for Standard Inventory Posting Group Get - https://api.businesscentral.dynamics.com/v2.0/0cd33610-3443-4d21-a9a1-5b0f3ae1dc84/Production/API/v2.0/companies(8cd9882b-acb1-ec11-8aa5-6045bda57eaf)/inventoryPostingGroups

 

I have attached here postman collection for call this API, It will help us in future for develop new API Page

Thank you for reading.

Keep Sharing...Keep Growing...

Snapshot Debuging for Debug Error in BC SaaS Production Environment

 Dear Friends,

We can use the Snapshot debugging to Debug in Production Environment

Below are simple setup process.

Step1: AL: Initialize a snapshot debugging session on cloud

Go to Launch.json file and select below option to generate configuration

 

Configuration Update as Below

 {

                "name": "snapshotInitialize: Microsoft production cloud",

                "type": "al",

                "request": "snapshotInitialize",

                "environmentType": "Production",

                "environmentName": "production",

                "tenant": "032eef50-e555-41c9-95b2-3e2e2fbd8bdd",

                "breakOnNext": "WebClient",

                "userId": "BCADMIN",

                "executionContext": "DebugAndProfile",

                "snapshotVerbosity": "Full"

            }

Below Message will show once successfully snapshot debug run

On Press of F7 – It will start snapshot debug

Snapshot debugging keyboard shortcuts:

F7 = Start a snapshot debugging session

Shift+F7 = List all available snapshots

Alt+F7 = Finish a snapshot debugging session

 

Now when you login to BC Production Environment using BCADMIN user you will receive below message

 

Now you can process the steps and generate the error.

I have block 10000 customer and just try to create the sales order for that customer

I have receive below error.

When the scenario is finished, you can press Ctrl+Shift+P and then select AL: Finish snapshot debugging on the server or by pressing ALT + F7.

The snapshot is downloaded as a ZIP file to the .snapshots folder in your VS Code project. And the debug icon on the status bar will be orange now.

By clicking on debug icon, you can see the list of the downloaded snapshots

Select the snapshot and start the Snapshot Debugging and it will automatic break at a point when the error is coming.

PS: Breakpoints can be added and removed and they will be hit if given a breakpoint; the breakpoint is in the execution context of a recorded state. This means that if walking the execution stack for a breakpoint and the next stepped line is reached, then the code will break on the breakpoint.

You can also set breakpoints in Base Application (dal file).

For example:

Done....

I hope you find it very easy and simple process.

Thank you for reading, 

Keep Sharing....Keep Growing....

Recover the currupted table data from database

 Hello Friends,

 For some reason, maybe due to sudden computer shutdown/crash or a forced shutdown, one of my client BizTalk DEV virtual machines presented strange behaviors this morning. The Host Instances were always restarting for no apparent reason. When I started to diagnose the problem, and inspect the machine Event Viewer I found the following error:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1848; actual 0:0). It occurred during a read of page (1:1848) in database ID 10 at offset 0x00000000e70000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\BizTalkMsgBoxDb.mdf’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Start receive error while run select query on some specific record

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:19473258; actual 1:5603466).

It occurred during a read of page (1:19473258) in database ID 7 at offset 0x000025246d4000 in file 'F:\Data\xxxxx_nav2016_Live_Data.mdf'. 

Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.

Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors;

for more information, see SQL Server Books Online.

 

While in NAV you will get this error for table have issue

There are few page corrupt in database for table

To solve this issue we can run below steps

https://blog.sandro-pereira.com/2017/07/25/sql-server-detected-a-logical-consistency-based-biztalkmsgboxdb-database/

 Set the database to Single User

You can do using query OR manual from database property

 

ALTER DATABASE [DB_NAME]

SET SINGLE_USER;

GO

 

Or we can do it from UI

Right Click On Database à Properties à Options à Restrict Access à Select Single User

Run the below Query for Table where Record is corrupted

DBCC CHECKTABLE([Table Name], REPAIR_ALLOW_DATA_LOSS)

GO

 

Note: Above command run with data loss, so make sure that you take backup before run this command and system will delete the records which page is corrupted and system can’t able to read that data

Once this command run you can able to run query properly but few records deleted which is  corrupted – so you need to get it back from old database backup

 

We found the missing record and insert into new table (using insert into [new table name])

After we generate the insert record query from temp table using Generate Script Open from new table (Only Data)

Run this query to create record

 Run the below Query to set database as multiple user

 

ALTER DATABASE BizTalkMsgBoxDb

SET MULTI_USER;

GO

 

Or same way you can do it from UI

======

SELECT * FROM [msdb].[dbo].[suspect_pages]

DBCC TRACEON (3604);

DBCC PAGE (7, 1, 23773535, 0);

DBCC TRACEOFF (3604);

GO

SELECT OBJECT_NAME (808167912);

=============

Run Check bd command to found error in database page

DBCC CHECKDB

This will take 1 or 2 hours for around 200 GB database

So if any error found at last then repair that page or table

==============

Done....

System will recover the old data or it might possible that curreupted raw can be deleted from database

Thank you for reading...

I hope this help to someone in future...

Keep Sharing....Keep Growing....

Business Central Migration Steps from BC 14 to BC 20 (India Version)

 Hello Team,

List of steps for Business Central Migration from BC 14 to BC 20 (India Version)

===========================================================

1.       Import-NAVServerLicense -ServerInstance BC140 -LicenseFile 'C:\Intech_BC20.flf'

2.       Restart-NAVServerInstance -ServerInstance BC200

3.       Get-NAVAppInfo -ServerInstance BC140 -Tenant default

4.       Get-NAVAppInfo -ServerInstance BC140 -Tenant Default | % { Uninstall-NAVApp -ServerInstance BC140 -Name $_.Name -Version $_.Version -Tenant Default }

5.       Get-NAVAppInfo -ServerInstance BC140 | % { Unpublish-NAVApp -ServerInstance BC140 -Name $_.Name -Version $_.Version }

 

Issue - In this step, we get error as per below snapshot.

Solution : to resolve this issue, execute this command 2 or 3 times, error will resolved.

 

6.       Get-NAVAppInfo -ServerInstance BC140 -SymbolsOnly | % { Unpublish-NAVApp -ServerInstance BC140 -Name $_.Name -Version $_.Version }

7.       Stop-NAVServerInstance -ServerInstance BC140

8.       Execute below command as SQL Query

DELETE FROM [ABC_Client_BC_TestUpgrade].[dbo].[Server Instance]

DELETE from [ABC_Client_BC_TestUpgrade].[dbo].[Debugger Breakpoint]

 

9.       Close the BC 14 Powershell and open new powershell and execute below command.

10.   Import-Module 'C:\Program Files\Microsoft Dynamics 365 Business Central\180\Service\NavAdminTool.ps1'

11.   Invoke-NAVApplicationDatabaseConversion -DatabaseName "ABC_Client_BC_TestUpgrade" -DatabaseServer databaseVM

12.   Set-NAVServerConfiguration -ServerInstance BC200 -KeyName DatabaseName -KeyValue "ABC_Client_BC_TestUpgrade"

13.   Set-NAVServerConfiguration -ServerInstance BC200 -KeyName "DestinationAppsForMigration" -KeyValue '[{"appId":"63ca2fa4-4f03-4f2b-a480-172fef340d3f", "name":"System Application", "publisher": "Microsoft"},{"appId":"437dbf0e-84ff-417a-965d-ed2bb9650972", "name":"Base Application", "publisher": "Microsoft"}]'

14.   Set-NavServerConfiguration -ServerInstance BC200 -KeyName "UsePermissionSetsFromExtensions" -KeyValue false

15.   Set-NavServerConfiguration -ServerInstance BC200 -KeyName "EnableTaskScheduler" -KeyValue false

16.   Restart-NAVServerInstance -ServerInstance BC200

17.   Publish-NAVApp -ServerInstance BC200 -Path "C:\Erp Sw\Dynamics.365.BC.41423.IN.DVD\Applications\system application\source\Microsoft_System Application.app”

18.   Publish-NAVApp -ServerInstance BC200 -Path "C:\Erp Sw\Dynamics.365.BC.41423.IN.DVD\Applications\BaseApp\Source\Microsoft_Base Application.app”

19.   Publish-NAVApp -ServerInstance BC200 -Path "C:\Erp Sw\Dynamics.365.BC.41423.IN.DVD\Applications\Application\Source\Microsoft_Application.app”

 

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Company Hub.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Contoso Coffee Demo Dataset.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Data Archive.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Troubleshoot FA Ledger Entries.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Email - Outlook REST API.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Email - Microsoft 365 Connector.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Email - Current User Connector.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Email - SMTP API.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Email - SMTP Connector.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Essential Business Headlines.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Late Payment Prediction.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Universal Print Integration.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_OnPrem Permissions.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Payment Links to PayPal.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Recommended Apps.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Sales and Inventory Forecast.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Send To Email Printer.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Simplified Bank Statement Import.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Permissions Mock.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Test Runner.app"

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\Microsoft_Performance Toolkit.app"

 

 

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_Tax Engine.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India Tax Base.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_QR Generator.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India GST.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India Gate Entry.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India TCS.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India TDS.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India Voucher Interface.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_Fixed Asset Depreciation for India.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India Reports.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\India_Apps\Microsoft_India Data Migration.app" -SkipVerification

 

Below are the app files which having one version higher than the BC 14 App Files

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\ABC_Client_Apps_UpdVersion\ISPL_ABC_Client_HR_Development_1.0.0.3.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\ABC_Client_Apps_UpdVersion\ISPL_ABC_Client_Development_1.0.0.4.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\ABC_Client_Apps_UpdVersion\ISPL_Quality Control_1.0.0.9.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\ABC_Client_Apps_UpdVersion\ISPL_Purchase Indent_1.0.0.5.app" -SkipVerification

Publish-NAVApp -ServerInstance BC200 -Path "C:\BC_20_Base_App_Files\ABC_Client_Apps_UpdVersion\ISPL_EInvoice and Eway Bill Development ISPL_1.0.0.1.app" -SkipVerification

 

Stop-NAVServerInstance -ServerInstance BC200

Start-NAVServerInstance -ServerInstance BC200

 

Sync-NAVTenant -ServerInstance BC200 -Mode Sync -Tenant Default\

 

 

Run Below three command one by one.

Sync-NAVApp -ServerInstance BC200 -Name "System Application" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Base Application" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Application" -Version 20.2.41144.41423

 

 

Sync-NAVApp -ServerInstance BC200 -Name "Company Hub" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Contoso Coffee Demo Dataset" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Data Archive" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Troubleshoot FA Ledger Entries" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Email - Outlook REST API" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Email - Microsoft 365 Connector" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Email - Current User Connector" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Email - SMTP API" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Email - SMTP Connector" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Essential Business Headlines" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Late Payment Prediction" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Universal Print Integration" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "OnPrem Permissions" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Payment Links to PayPal" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Recommended Apps" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Sales and Inventory Forecast" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Send To Email Printer" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Simplified Bank Statement Import" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Permissions Mock" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Test Runner" -Version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Performance Toolkit" -Version 20.2.41144.41423

 

Sync-NAVApp -ServerInstance BC200 -Name "Tax Engine" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India Tax Base" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "QR Generator" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India GST" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India Gate Entry" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India TCS" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India TDS" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India Voucher Interface" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "Fixed Asset Depreciation for India" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India Reports" -version 20.2.41144.41423

Sync-NAVApp -ServerInstance BC200 -Name "India Data Migration" -version 20.2.41144.41423

 

 

Sync-NAVApp -ServerInstance BC200 -Name "ABC_Client_HR_Development" -version 1.0.0.3

Sync-NAVApp -ServerInstance BC200 -Name "ABC_Client_Development" -version 1.0.0.4

Sync-NAVApp -ServerInstance BC200 -Name "Quality Control" -version 1.0.0.9

Sync-NAVApp -ServerInstance BC200 -Name "Purchase Indent" -version 1.0.0.5

Sync-NAVApp -ServerInstance BC200 -Name "EInvoice and Eway Bill Development ISPL" -version 1.0.0.1

 

Start-NAVDataUpgrade -ServerInstance BC200 -Tenant Default -FunctionExecutionMode Serial -SkipAppVersionCheck

 

Get-NAVDataUpgrade -ServerInstance BC200

 

Install-NAVApp -ServerInstance BC200 -Name "Application" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Company Hub" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Contoso Coffee Demo Dataset" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Data Archive" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Troubleshoot FA Ledger Entries" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Email - Outlook REST API" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Email - Microsoft 365 Connector" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Email - Current User Connector" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Email - SMTP API" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Email - SMTP Connector" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Essential Business Headlines" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Late Payment Prediction" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Universal Print Integration" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "OnPrem Permissions" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Payment Links to PayPal" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Recommended Apps" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Sales and Inventory Forecast" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Send To Email Printer" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Simplified Bank Statement Import" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Permissions Mock" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Test Runner" -Version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "Performance Toolkit" -Version 20.2.41144.41423

 

 

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "Tax Engine" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India Tax Base" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India TCS" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India TDS" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India Voucher Interface" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "Fixed Asset Depreciation for India" -version 20.2.41144.41423

 

Install-NAVApp -ServerInstance BC200 -Name "QR Generator" -version 20.2.41144.41423

Install-NAVApp -ServerInstance BC200 -Name "India Reports" -version 20.2.41144.41423

 

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India GST" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India Gate Entry" -version 20.2.41144.41423

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "India Data Migration" -version 20.2.41144.41423

 

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "ABC_Client_HR_Development" -version 1.0.0.3

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "ABC_Client_Development" -version 1.0.0.4

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "Quality Control" -version 1.0.0.9

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "Purchase Indent" -version 1.0.0.5

Start-NAVAppDataUpgrade -ServerInstance BC200 -Name "EInvoice and Eway Bill Development ISPL" -version 1.0.0.1

 

$InstanceName = 'BC200'

$ServicesAddinsFolder = 'C:\Program Files\Microsoft Dynamics 365 Business Central\200\Service\Add-ins'

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.BusinessChart' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'BusinessChart\Microsoft.Dynamics.Nav.Client.BusinessChart.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.FlowIntegration' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'FlowIntegration\Microsoft.Dynamics.Nav.Client.FlowIntegration.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.OAuthIntegration' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'OAuthIntegration\Microsoft.Dynamics.Nav.Client.OAuthIntegration.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.PageReady' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'PageReady\Microsoft.Dynamics.Nav.Client.PageReady.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.PowerBIManagement' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'PowerBIManagement\Microsoft.Dynamics.Nav.Client.PowerBIManagement.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.RoleCenterSelector' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'RoleCenterSelector\Microsoft.Dynamics.Nav.Client.RoleCenterSelector.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.SatisfactionSurvey' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'SatisfactionSurvey\Microsoft.Dynamics.Nav.Client.SatisfactionSurvey.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.SocialListening' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'SocialListening\Microsoft.Dynamics.Nav.Client.SocialListening.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.VideoPlayer' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'VideoPlayer\Microsoft.Dynamics.Nav.Client.VideoPlayer.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.WebPageViewer' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'WebPageViewer\Microsoft.Dynamics.Nav.Client.WebPageViewer.zip')

Set-NAVAddIn -ServerInstance $InstanceName -AddinName 'Microsoft.Dynamics.Nav.Client.WelcomeWizard' -PublicKeyToken 31bf3856ad364e35 -ResourceFile ($AppName = Join-Path $ServicesAddinsFolder 'WelcomeWizard\Microsoft.Dynamics.Nav.Client.WelcomeWizard.zip')

 

Get-NAVApplication -ServerInstance BC200

Set-NAVApplication -ServerInstance BC200 -ApplicationVersion 20.2.41144.41423 -Force

 

Sync-NAVTenant -ServerInstance BC200 -Mode Sync -Tenant Default

 

Restart-NAVServerInstance -ServerInstance BC200

 

 

 

Start-NAVDataUpgrade -ServerInstance BC200 -FunctionExecutionMode Serial -Tenant Default

 

Get-NAVDataUpgrade -ServerInstance BC200

 

Set-NAVServerConfiguration -ServerInstance BC200 -KeyName SolutionVersionExtension -KeyValue "437dbf0e-84ff-417a-965d-ed2bb9650972" -ApplyTo All

 

Set-NavServerConfiguration -ServerInstance BC200 -KeyName "EnableTaskScheduler" -KeyValue true

 Restart-NAVServerInstance -ServerInstance BC200

Done....

You have migrated BC 14 to BC 20 Successfully.

Thank you for reading....

Keep Sharing...Keep Growing....