Tag: MS

Corfu Navision 2016, Development Tips, Dimension, How To, Information, Instalation & Configuration, upgrade

Design Overview of Dimension handling in Navision 2016

Ashwini Tripathi

Hold on,

Way of handling Dimension have changed from Navision 2009 not a new concept. Till now everyone is aware of this technical change, but still to keep handy reference today I decided to share the same with community, which can help for new comers in this industry.

Detailed technical insight into the concepts and principles that are used to redesign the dimension entry storing and posting feature in Microsoft Dynamics NAV 2016. Helpful when upgrading from earlier version to 2009.

Dimension Sets

A dimension set is a unique combination of dimension values. It is stored as dimension set entries in the database. Each dimension set entry represents a single dimension value.

The dimension set is identified by a common dimension set ID that is assigned to each dimension set entry that belongs to the dimension set.

Dimension Set Entries

Dimension sets are stored in the Dimension Set Entry table as dimension set entries with the same dimension set ID.

When you add, edit and close the Edit Dimension Set Entries window, a check is performed to see whether the combination of dimension values exists as a dimension set in the table.

If the combination occurs in the table, then the corresponding dimension set ID is assigned to the journal line, document header, or document line.

Otherwise, a new dimension set is added to the table, and the new dimension set ID is assigned to the journal line, document header, or document line.
Performance Improvement
By storing dimension sets once in the database, database space is preserved, and overall performance is improved.

Searching for Dimension Combinations

Building Search Tree
Table 481 Dimension Set Tree Node is used when Microsoft Dynamics NAV evaluates whether a set of dimensions already exists in table 480 Dimension Set Entry table.

The evaluation is performed by recursively traversing the search tree starting at the top level numbered 0.

The top level 0 represents a dimension set with no dimension set entries. The children of this dimension set represent dimension sets with only one dimension set entry.

The children of these dimension sets represent dimension sets with two children, and so on.

Finding Dimension Set ID

At a conceptual level, Parent ID, Dimension, and Dimension Value, in the search tree, are combined and used as the primary key because Microsoft Dynamics NAV traverses the tree in the same order as the dimension entries.

The GET function (record) is used to search for dimension set ID.

DimSet.”Parent ID” := 0;  // ‘root’

IF UserDim.FINDSET THEN

REPEAT

DimSet.GET(DimSet.”Parent ID”,UserDim.DimCode,UserDim.DimValueCode);

UNTIL UserDim.NEXT = 0;

EXIT(DimSet.ID);

However, to preserve the ability of Microsoft Dynamics NAV to rename a dimension and dimension value, table 348 Dimension Value is extended with an integer field of Dimension Value ID.

This table converts the field pair Dimension and Dimension Value to an integer value.

When you rename the dimension and dimension value, the integer value is not changed.

DimSet.”Parent ID” := 0;  // ‘root’IF UserDim.FINDSET THEN  REPEAT      DimSet.GET(DimSet.ParentID,UserDim.”Dimension Value ID”);  UNTIL UserDim.NEXT = 0;EXIT(DimSet.ID);

Table Structure

New Tables

Three new tables have been designed to manage dimension set entries.

Table 480 Dimension Set Entry
Table 480 Dimension Set Entry is a new table. You cannot change this table. After data has been written to the table, you cannot delete or edit it.

Deleting data requires that you check against all occurrences of the dimension set ID in the entire database, including partner solutions.

Field No. Field Name Data Type Comment
1 ID Integer >0.0 is reserved for the empty dimension set. References field 3 in table 481.
2 Dimension Code Code 20 Table relation to table 348.
3 Dimension Value Code Code 20 Table relation to table 349.
4 Dimension Value ID Integer References field 12 in table 349. It is the secondary key that is used when traversing table 481.
5 Dimension Name Text 30 CalcField. Lookup to table 348.
6 Dimension Value Name Text 30 CalcField. Lookup to table 349.

Table 481 Dimension Set Tree Node

Table 481 Dimension Set Tree Node is a new table. You cannot change this table.

It is used to search for a dimension set. If the dimension set is not found, a new set is created.

Field No. Field Name Data Type Comment
1 Parent Dimension Set ID Integer 0 for top level node.
2 Dimension Value ID Integer Table relation to field 12 in table 349.
3 Dimension Set ID Integer AutoIncrement. Used in field 1 in table 480.
4 In Use Boolean False if not in use.

Table 482 Reclas. Dimension Set Buffer
Table 482 Reclas. Dimension Set Buffer is a new table.

The table is used to edit a dimension set ID.

It is required when you edit a dimension value code and a new dimension value code, for example, in the Item Reclas. Journal table.

Field No. Field Name Data Type Comment
1 Dimension Code Code 20 Table relation to table 348.
2 Dimension Value Code Code 20 Table relation to table 349.
3 Dimension Value ID Integer References field 12 in table 349.
4 New Dimension Value Code Code 20 Table relation to table 349.
5 New Dimension Value ID Integer References field 12 in table 349.
6 Dimension Name Text 30 CalcField. Lookup to table 348.
7 Dimension Value Name Text 30 CalcField. Lookup to table 349.
8 New Dimension Value Name Text 30 CalcField. Lookup to table 349.

Modified Tables
All transaction and budget tables have been modified to manage dimension set entries.

Changes to Transaction and Budget Tables
A new field has been added to all transaction and budget tables.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.

Changes to Table 83 Item Journal Line
Two new fields have been added to table 83 Item Journal Line.

Field No. Field Name Data Type Comment
480 Dimension Set ID Integer References field 1 in table 480.
481 New Dimension Set ID Integer References field 1 in table 480.

Changes to Table 349 Dimension Value
A new field has been added to table 349 Dimension Value.

Field No. Field Name Data Type Comment
12 Dimension Value ID Integer AutoIncrement. Used for references in table 480 and table 481.

Tables That Get New Field 480 Dimension Set ID
A new field, 480 Dimension Set ID, has been added to the following tables.

For the tables that store posted data, the field only provides a non-editable display of dimensions, which is marked as Drill-down.

For the tables that store working documents, the field is editable. The buffer tables that are used internally do not need editable or non-editable capabilities.

The 480 field is non-editable in the following tables

Table No. Table Name
17 G/L Entry
21 Cust. Ledger Entry
25 Vendor Ledger Entry
32 Item Ledger Entry
110 Sales Shipment Header
111 Sales Shipment Line
112 Sales Invoice Header
113 Sales Invoice Line
114 Sales Cr.Memo Header
115 Sales Cr.Memo Line
120 Purch. Rcpt. Header
121 Purch. Rcpt. Line
122 Purch. Inv. Header
123 Purch. Inv. Line
124 Purch. Cr. Memo Hdr.
125 Purch. Cr. Memo Line
169 Job Ledger Entry
203 Res. Ledger Entry
271 Bank Account Ledger Entry
281 Phys. Inventory Ledger Entry
297 Issued Reminder Header
304 Issued Fin. Charge Memo Header
5107 Sales Header Archive
5108 Sales Line Archive
5109 Purchase Header Archive
5110 Purchase Line Archive
5601 FA Ledger Entry
5625 Maintenance Ledger Entry
5629 Ins. Coverage Ledger Entry
5744 Transfer Shipment Header
5745 Transfer Shipment Line
5746 Transfer Receipt Header
5747 Transfer Receipt Line
5802 Value Entry
5832 Capacity Ledger Entry
5907 Service Ledger Entry
5908 Service Header
5933 Service Order Posting Buffer
5970 Filed Service Contract Header
5990 Service Shipment Header
5991 Service Shipment Line
5992 Service Invoice Header
5993 Service Invoice Line
5994 Service Cr. Memo Header
5995 Service Cr. Memo Line
6650 Return Shipment Header
6651 Return Shipment Line
6660 Return Receipt Header
6661 Return Receipt Line

The 480 field is editable in the following tables

Table No. Table Name
36 Sales Header
37 Sales Line
38 Purchase Header
39 Purchase Line
81 Gen. Journal Line
83 Item Journal Line
89 BOM Journal Line
96 G/L Budget Entry
207 Res. Journal Line
210 Job Journal Line
221 Gen. Jnl. Allocation
246 Requisition Line
295 Reminder Header
302 Finance Charge Memo Header
5405 Production Order
5406 Prod. Order Line
5407 Prod. Order Component
5615 FA Allocation
5621 FA Journal Line
5635 Insurance Journal Line
5740 Transfer Header
5741 Transfer Line
5900 Service Header
5901 Service Item Line
5902 Service Line
5965 Service Contract Header
5997 Standard Service Line
7134 Item Budget Entry
99000829 Planning Component

The 480 field has been added to the following buffer tables.

Table No. Table Name
49 Invoice Post. Buffer
212 Job Posting Buffer
372 Payment Buffer
382 CV Ledger Entry Buffer
461 Prepayment Inv. Line Buffer
5637 FA G/L Posting Buffer
7136 Item Budget Buffer

Codeunit 408 Dimension Management
Codeunit 408 Dimension Management is a function library that handles common tasks that are related to dimensions, such as copying from one table to another or from one document to another.

Many functions are deleted because there is no need for copying between dimension tables at the other hand many functions are modified.

Modified Functions

Function Name Modification Description
CheckDimSetIDComb New function that substitutes the other check functions and takes a Dimension Set ID as an argument instead of a dimension table.
CheckDimSetIDComb

CheckDocDimComb

CheckServContractDimComb

CheckDimBuffer

CheckDimComb

CheckDimValueComb

 Delete. All usage should be changed to CheckDimSetIDComb.
GetDefaultDim Modify to return an integer Dimension Set ID instead of a set of records.
CopyJnlLineDimToICJnlDim

CopyICJnlDimToJnlLineDim

CopyDocDimtoICDocDim

CopyICDocDimtoICDocDim

 Modify to work with DimSetID -> ICJnlLineDim

Deleted Functions

Functions that are deleted from codeunit 408 in connection with the Dimension Set Entries feature are listed below.

During the upgrade of application code from Microsoft Dynamics NAV 2009 or earlier versions to Microsoft Dynamics NAV 2016, the following functions are not available in Microsoft Dynamics NAV 2016.

If you have customizations that use one or more of the functions, you must upgrade that code accordingly.

InsertJnlLineDim

UpdateJnlLineDefaultDim

GetJnlLineDefaultDim

GetPreviousDocDefaultDim

GetPreviousProdDocDefaultDim

InsertDocDim

UpdateDocDefaultDim

ExtractDocDefaultDim

InsertProdDocDim

UpdateProdDocDefaultDim

InsertServContractDim

UpdateServcontractDim

UpdateDefaultDimNewDimValue

GetDocDim

GetProdDocDim

TypeToTableID1

TypeToTableID2

TypeToTableID3

TypeToTableID4

TypeToTableID5

DeleteJnlLineDim

DeleteDocDim

DeletePostedDocDim

DeleteProdDocDim

DeleteServContractDim

ShowJnlLineDim

SaveJnlLineDim

ShowJnlLineNewDim

SaveJnlLineNewDim

ShowDocDim

SaveDocDim

ShowProdDocDim

SaveProdDocDim

ShowTempDim

SaveTempDim

ShowTempNewDim

SaveTempNewDim

SaveServContractDim

MoveJnlLineDimToLedgEntryDim

MoveDocDimToPostedDocDim

MoveOneDocDimToPostedDocDim

MoveLedgEntryDimToJnlLineDim

MoveDimBufToJnlLineDim

MoveDimBufToLedgEntryDim

MoveDimBufToPostedDocDim

MoveDimBufToGLBudgetDim

CopyJnlLineDimToJnlLineDim

CopyLedgEntryDimToJnlLineDim

CopyDocDimToDocDim

CopyPostedDocDimToPostedDocDim

CopyDocDimToJnlLineDim

CopyDimBufToJnlLineDim

CopyDimBufToDocDim

CopySCDimToDocDim

MoveDocDimToLedgEntryDim

MoveDocDimToDocDim

MoveDocDimArchvToDocDim

MoveLedgEntryDimToDocDim

MoveProdDocDimToProdDocDim

MoveJnlLineDimToProdDocDim

MoveJnlLineDimToDocDim

MoveJnlLineDimToJnlLineDim

CopyLedgEntryDimToLedgEntryDim

MoveTempFromDimToTempToDim

TransferTempToDimToDocDim

MoveJnlLineDimToBuf

CopyICJnlDimToICJnlDim

TestDimValue

TestNewDimValue

MoveDimBufToItemBudgetDim. (Delete because the ItemBudgetDim Table is deleted.

GetServContractDim

MoveTempDimToBuf

UpdateSCInvLineDim

CopyJnlLineDimToBuffer

UpdateDocDefaultDim2

Code Examples of Changed Patterns in Modifications

Posting a Journal Line

Key changes are listed as follows:

  • Journal line dimension tables are removed.
  • A dimension set ID is created in the Dimension Set ID field.

Old Code

ResJnlLine.”Qty. per Unit of Measure” :=  SalesLine.”Qty. per Unit of Measure”;

TempJnlLineDim.DELETEALL;

TempDocDim.RESET;

TempDocDim.SETRANGE( “Table ID”,DATABASE::”Sales Line”);

TempDocDim.SETRANGE( “Line No.”,SalesLine.”Line No.”);

DimMgt.CopyDocDimToJnlLineDim( TempDocDim,TempJnlLineDim);

ResJnlPostLine.RunWithCheck( ResJnlLine,TempJnlLineDim);

New Code

ResJnlLine.”Qty. per Unit of Measure” := SalesLine.”Qty. per Unit of Measure”;

ResJnlLine.”Dimension Set ID” := SalesLine.” Dimension Set ID “;

ResJnlPostLine.Run(ResJnlLine);
Posting a Document
When you post a document in Microsoft Dynamics NAV 2016, you no longer have to copy the document dimensions.

Old Code

DimMgt.MoveOneDocDimToPostedDocDim(

TempDocDim,DATABASE::”Sales Line”,

“Document Type”,

“No.”,

SalesShptLine.”Line No.”,

DATABASE::”Sales Shipment Line”,

SalesShptHeader.”No.”);

New Code

SalesShptLine.”Dimension Set ID”  := SalesLine.”Dimension Set ID”
Editing Dimensions from a Document
You can edit dimensions from a document. For example, you can edit a sales order line.

Old Code

Table 37, function ShowDimensions:

TESTFIELD(“Document No.”);

TESTFIELD(“Line No.”);

DocDim.SETRANGE(“Table ID”,DATABASE::”Sales Line”);

DocDim.SETRANGE(“Document Type”,”Document Type”);

DocDim.SETRANGE(“Document No.”,”Document No.”);

DocDim.SETRANGE(“Line No.”,”Line No.”);

DocDimensions.SETTABLEVIEW(DocDim);

DocDimensions.RUNMODAL;

New Code

Table 37, function ShowDimensions:

“Dimension ID” := DimSetEntry.EditDimensionSet( “Dimension ID”);
Showing Dimensions from Posted Entries
You can show dimensions from posted entries, such as sales shipment lines.

Old Code

Table 111, function ShowDimensions:

TESTFIELD(“No.”);

TESTFIELD(“Line No.”);

PostedDocDim.SETRANGE(“Table ID”,DATABASE::”Sales Shipment Line”);

PostedDocDim.SETRANGE(“Document No.”,”Document No.”);

PostedDocDim.SETRANGE(“Line No.”,”Line No.”);

PostedDocDimensions.SETTABLEVIEW(PostedDocDim);

PostedDocDimensions.RUNMODAL;

New Code

Table 111, function ShowDimensions:

DimSetEntry.ShowDimensionSet(“Dimension ID”);
Getting Default Dimensions for a Document
You can get default dimensions for a document, such as a sales order line.

Old Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

DimMgt.GetPreviousDocDefaultDim( DATABASE::”Sales Header”,”Document Type”,

“Document No.”,0, DATABASE::Customer, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”);

DimMgt.GetDefaultDim(TableID,No,SourceCodeSetup.Sales,

“Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

IF “Line No.” <> 0 THEN

DimMgt.UpdateDocDefaultDim( DATABASE::”Sales Line”,”Document Type”,

“Document No.”,”Line No.”, “Shortcut Dimension 1 Code”, “Shortcut Dimension 2 Code”);

New Code

Table 37, function CreateDim()

SourceCodeSetup.GET;

TableID[1] := Type1;

No[1] := No1;

TableID[2] := Type2;

No[2] := No2;

TableID[3] := Type3;

No[3] := No3;

“Shortcut Dimension 1 Code” := ”;

“Shortcut Dimension 2 Code” := ”;

GetSalesHeader;

“Dimension ID” :=  DimMgt.GetDefaultDimID(

TableID,No,SourceCodeSetup.Sales, “Shortcut Dimension 1 Code”,

“Shortcut Dimension 2 Code”, SalesHeader.”Dimension ID”, DATABASE::”Sales Header”);

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-3

Ashwini Tripathi

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV 2016 Administration Shell

  • If you are modifying an existing Microsoft Dynamics NAV Server instance, run the Set-NAVServerConfiguration cmdlet.

Syntax & Parameter explaination:

Set-NAVServerConfiguration (cmdlet)

-KeyName <String>

(The configuration key name. Examine the CustomSettings.config file to determine the correct key name.)

[-Element <String> ]

(Specifies the navigation path from the root element to the appSettings section of the configuration document.)

[-ServerInstance] <String>

(Specifies the name of a Microsoft Dynamics NAV Server instance. The default instance name is DynamicsNAV90. You can specify either the full name of an instance such as MicrosoftDynamicsNavServer$myinstance or the short name such as myinstance.)

-DatabaseCredentials <PSCredential>

(The user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to connect to the Microsoft Dynamics NAV database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database. The login account must be a member of the db_owner role on the database.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-KeyValue <String> ] (The configuration key value.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

Example:

C:\PS>Set-NAVServerConfiguration MyInstance -KeyName DatabaseServer -KeyValue DatabaseServer.Domain.Com

  • If you are creating a new Microsoft Dynamics NAV Server instance, run the New-NAVServerInstance cmdlet.

Syntax & Parameter explaination:

New-NAVServerInstance

[-ServerInstance] <String>

(Specifies the name of the Microsoft Dynamics NAV Server instance. The default instance name isDynamicsNAV90. You can specify either the full name of an instance, such as MicrosoftDynamicsNavServer$DynamicsNAV90, or the short name, such as DynamicsNAV90. You must use single-quotes around the instance name.)

-ManagementServicesPort <ServicePort>

(Specifies the TCP port that is used to manage the Microsoft Dynamics NAV Server instance. The Management Services port has no exceptions in the firewall, and will only be accessed from the local computer. The port is used by Windows PowerShell for access Microsoft Dynamics NAV Server management data.)

[-ClientServicesCredentialType <String> ]

(The type of client credential used for client authentication.Possible values are: Windows, Username, NavUserPassword and AccessControlService.)

[-ClientServicesPort <ServicePort> ]

(Specifies the listening TCP port for clients such as Microsoft Dynamics NAV Windows client and Microsoft Dynamics NAV Web client.)

[-DatabaseCredentials <PSCredential> ]

(The user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to connect to the Microsoft Dynamics NAV database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database. If the Microsoft Dynamics NAV Server instance is configured for multitenancy, then parameter configure SQL Authentication on the connection to the application database, not the tenant database. The login account must be a member of the db_owner role on the database.)

[-DatabaseInstance <DatabaseInstance> ]

(Specifies the SQL Server instance on which the Microsoft Dynamics NAV database is installed.)

[-DatabaseName <DatabaseName> ]

(Specifies the name of the Microsoft Dynamics NAV database.)

[-DatabaseServer <DatabaseServer> ]

(Specifies the name of the computer on which the SQL Server instance for the Microsoft Dynamics NAV database is installed.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-Multitenant]

(Specifies the Microsoft Dynamics NAV Server instance to be a multitenant instance.)

[-ODataServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV OData web services.)

[-ServiceAccount <ServiceAccount> ]

(Specifies the Windows-based computer account that the Microsoft Dynamics NAV Server instance must use to log on. The default value is NT AUTHORITY\NETWORK SERVICE. Only NetworkService and User values are supported. This parameter accepts values from the enum System.ServiceProcess.ServiceAccount.)

[-ServiceAccountCredential <PSCredential> ]

(Specifies a set of security credentials that you must use when configuring the service account.)

[-ServicesCertificateThumbprint <ClientServicesCertificateThumbprint> ]

(Specifies the certificate thumbprint for the x509 certificate that is going to be used for securing communication with the server. The certificate must be stored in the local machine store and in the personal sub-store in the certificate store. The private key of the certificate must be present and exchangeable. The certificate must be in .pfx format, not .cer format. The certificate can be either self-signed or issued by a trusted certification authority (CA).

When specifying a ServicesCertificateThumbprint, SOAP web services and OData web services become HTTPS.)

[-SOAPServicesPort <ServicePort> ]

(Specifies the listening HTTP port for Microsoft Dynamics NAV SOAP web services.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the application database.

Examples:

C:\PS>New-NAVServerInstance NewInstance -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

C:\PS>Get-Credential | New-NAVServerInstance NewInstance -ServiceAccount User -ManagementServicesPort 8099 -ClientServicesPort 8100 -SOAPServicesPort 8101 -ODataServicesPort 8102 –verbose

 

For Multitenant Environment

  1. Configure SQL Server Authentication with the application database as above.
  2. To configure SQL Authentication with the tenant database, run the Mount-NAVTenant

Syntax & Parameter explaination:

Mount-NAVTenant

[-AlternateId] <System.Collections.ObjectModel.ReadOnlyCollection[string]>

(Specifies the alternative IDs for the tenant, such as host names for the Microsoft Dynamics NAV Web client, SOAP web services, OData web services, or the Microsoft Dynamics NAV Windows client.

If you use alternative IDs for tenant resolution in the Microsoft Dynamics NAV Web client, you must also enable some of the UrlRewrite rules in the Web.Config file for the Microsoft Dynamics NAV Web Server components.)

[-AzureKeyVaultSettings] <Microsoft.Dynamics.Nav.Types.AzureKeyVaultSettings>

(Specifies the Azure key vault settings. This parameter is available only if the EncryptionProvider is set to AzureKeyVault.)

[-ServerInstance] <String>

(Specifies the Microsoft Dynamics NAV Server instance that you want to mount the tenant against, such as DynamicsNAV90. You can specify either the fully qualified name, such as ‘MyServer$DynamicsNAV90’, or the short name, such as ‘DynamicsNAV90’.)

[-DatabaseInstance] <System.String>

(Specifies the name of the SQL Server instance that hosts the database. You can also specify the instance in the DatabaseServer parameter, such as MyServer\MyInstance.)

[-DatabaseName] <System.String>

(Specifies the name of the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance, such as ‘Demo Database NAV (9-0)’.)

[-DatabaseServer] <System.String>

(Specifies the name of the database server that hosts the Microsoft Dynamics NAV database that you want to mount against the Microsoft Dynamics NAV Server instance.)

[-DefaultCompany] <System.String>

(Specifies the name of the company that NAS services, OData web services, and SOAP web services use if no other company is specified.)

[-DefaultTimeZone] <System.TimeZoneInfo>

(Specifies the default time zone that is used by the NAS services, OData web services, and SOAP web services for this tenant.

You can set the parameter to UTC, ‘Server Time Zone’, or the ID of a Windows Time Zone.

UTC specifies that all business logic for services on the server instance runs in Coordinated Universal Time (UTC).

‘Server Time Zone’ specifies that services use the time zone of the computer that is running Microsoft Dynamics NAV Server instance.

ID of a Windows Time Zone specifies that services use a Windows time zone as defined in the system registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. For example, Romance Standard Time is a valid Windows time zone value.

If this parameter is not specified, the value is taken from the ServicesDefaultTimeZone setting in the CustomSetting.config file for the Microsoft Dynamics NAV Server instance.)

[-NasServicesEnabled]

(Specifies to enable NAS services on the tenant. The default value is false.)

[-RunNasWithAdminRights]

(Specifies the NAS services to run with administrator rights. This grants the NAS service the same permissions as the SUPER permission set in Microsoft Dynamics NAV without having to add the Microsoft Dynamics NAV Server service account as a user. The default is false.)

[[-ApplicationDatabaseCredentials] <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the application database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the application database.

The login account must be a member of the db_owner role on the database.

This parameter is only relevant when you set with the ApplicationDatabaseServer and ApplicationDatabaseName parameters )

[[-ApplicationDatabaseName] <System.String> ]

(Specifies the name of the application database to use with the tenant database.

This parameter is only relevant if the Microsoft Dynamics NAV Server instance is configured for multitenancy. This parameter, together with the ApplicationDatabaseServer parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect a running Microsoft Dynamics NAV Server instance.)

[[-ApplicationDatabaseServer] <System.String> ]

(Specifies the SQL Server name and instance, such as MyServer\MyInstance, that hosts the application database that you want to use with the tenant database,.

This parameter, together with the ApplicationDatabaseName parameter, enables you to mount a tenant to the same Microsoft Dynamics NAV Server instance as the application database without having to connect to a running Microsoft Dynamics NAV Server instance.)

[-AllowAppDatabaseWrite]

(Specifies if the tenant can write to the application database. The default value is false.)

[-DatabaseCredentials <PSCredential> ]

(Specifies the user name and password of the login account that the Microsoft Dynamics NAV Server instance will use to access the tenant database in SQL Server. This parameter configures the Microsoft Dynamics NAV Server instance to use SQL Server Authentication instead of Windows Authentication on the connection to the database.

The login account must be a member of the db_owner role on the database.)

[-EncryptionProvider <Microsoft.Dynamics.Nav.Types.EncryptionProvider> ]

(Specifies the name of the encryption provider.)

[-Force] (Forces the command to run without asking for user confirmation.)

[-OverwriteTenantIdInDatabase]

(Specifies if the Mount-NAVTenant cmdlet must overwrite the tenant ID in the database if the database has been mounted as a tenant earlier. If this is false, and the tenant database has previously been mounted with a different tenant ID, an exception is thrown.)

[-Confirm] (Prompts you for confirmation before running the cmdlet.)

[-WhatIf] [ <CommonParameters>]

Use the DatabaseCredentials parameter to provide the login credentials of the database user that you want to use to access the tenant database.

Examples:

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’

PS C:\> Mount-NAVTenant DynamicsNAV90 Test Test_Database

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential)

PS C:\> Get-NAVTenant Server1 | Mount-NAVTenant Server2

PS C:\> Get-NAVTenant Server1 | Dismount-NavTenant Server1 -Force | Mount-NAVTenant Server2

PS C:\> Mount-NAVTenant DynamicsNAV90 -Id ‘Test’ -DatabaseName ‘Test_Database’-AlternateId @( “test.mydomain.com”, “http://mydomain.sharepoint.com/sites/teamsite&#8221; )

PS C:\> Mount-NAVTenant -Id ‘Test’ -DatabaseName ‘Test_Database’ -DatabaseCredentials (Get-Credential) -ApplicationDatabaseServer ‘MySQLServer\NAV’ -ApplicationDatabaseName ‘MyNavAppDatabase’ -ApplicationDatabaseCredentials (Get-Credential) -KeyFilePath ‘C:\key\nav.key’ -KeyFilePassword (Get-Credential).Password

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-2

Ashwini Tripathi

Recall from my previous post Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1, action is almost similar with few small differences when dealing with Multitenant Deployment.
Configure SQL Server Authentication on Microsoft NAV Server Instance in a Multitenant Deployment
How to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV Server instance.

To configure a SQL Server Authentication on a Microsoft Dynamics NAV Server instance, you set up the server instance with the login credentials (user name and password) for the user accounts for the application and tenant databases in SQL Server.

To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  • Open the Microsoft Dynamics NAV Server Administration tool.
  • In the console tree, which is the left pane, expand the node for the computer that contains the Microsoft Dynamics NAV Server instance, and then select the Microsoft Dynamics NAV Server instance.
  • Configure SQL Server Authentication with the application database as follows:
    • In the Actions pane, choose Database Credentials.
    • On the Database Credentials page, choose the Edit button.
    • Set the Database Authentication Mode to SQL Server Authentication.
    • In the Database User Name field, type the login name for the database user that you want to use to access the Microsoft Dynamics NAV application database in SQL Server.
    • In the Password field, type the login password for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.
    • Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

Encryption keys are used to help secure the login credentials over the connection between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server.

  • On the Information dialog box about encryption, choose the OK button.

This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.

  • If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.
  • To configure SQL Server Authentication with the tenant database, mount the tenant to the Microsoft Dynamics NAV Server instance and specify the login credentials (user name and password) for the database user that you want to use to access the Microsoft Dynamics NAV tenant database in SQL Server.

If the tenant is already mounted to the Microsoft Dynamics NAV Server instance, you must dismount the tenant, and mount it again.

Restart the server instance.

Corfu Navision 2016, Development Tips, How To, Information, Instalation & Configuration

Configuring SQL Server Authentication in Microsoft Dynamics NAV 2016 – Part-1

Ashwini Tripathi

Microsoft Dynamics NAV 2016 supports SQL Server authentication between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server. Previously only Windows authentication was supported.

Set Up an Encryption Key

When using SQL Server authentication, Microsoft Dynamics NAV requires an encryption key to encrypt the credentials (user name and password) that the Microsoft Dynamics NAV Server instance uses to connect to the Microsoft Dynamics NAV database in SQL Server.

The encryption key must be installed on the computer where the Microsoft Dynamics NAV Server is installed and also in the database in SQL Server.

In a multitenant deployment, the encryption key must be installed in the application database.

To set up an encryption key, you can use one of the following methods:

  • You can create and import your own encryption key by using Microsoft Dynamics NAV 2016 Administration Shell cmdlets.
  • If you are configuring SQL Server authentication on a Microsoft Dynamics NAV Server instance for the first time, you can use the Microsoft Dynamics NAV Server Administration tool which can automatically create and install a system encryption key. If you decide to use this method, no action is required.

To create and import encryption key

  • In the Microsoft Dynamics NAV 2016 Administration Shell, run the New-NAVEncryptionkey

Create an encryption key and stores it in a file in a specified path on the computer or network.

Syntax

New-NAVEncryptionKey [-KeyPath] <String> [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

The New-NAVEncryptionKey cmdlet enables you to specify a destination file for the key and specify a password to protect the file.

This creates a file that contains an encryption key. If you already have an encryption key file, you can skip this step.

Example: New-NAVEncryptionKey  -KeyPath “C:\UserData\SQLKey\MySQLKey” -Password (Get-Credential).Password

SQLServerAuthentication1

  • Run the Import-NAVEncryptionkey cmdlet to install the encryption key on the Microsoft Dynamics NAV Server instance and database.

Imports an encryption key from a file to a Microsoft Dynamics NAV Server instance and database in SQL Server.

Syntax

Import-NAVEncryptionKey [[-ServerInstance] <String> ] [-KeyPath] <String> -ApplicationDatabaseName <String> -ApplicationDatabaseServer <String> [-ApplicationDatabaseCredentials <PSCredential> ] [-Force] [-Password <SecureString> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

Example: Import-NAVEncryptionKey -ServerInstance ‘DynamicsNAV90’ -KeyPath “C:\UserData\SQLKey\MySQLKey.key” -ApplicationDatabaseServer ‘INDEL-AXT5283VM’ -ApplicationDatabaseName ‘Demo Database NAV (9-0)’ -Password (Get-Credential).Password

SQLServerAuthentication2

You cannot import an encryption key on the Microsoft Dynamics NAV Server instance if an encryption key file already exists. You must first delete the encryption key from the computer where Microsoft Dynamics NAV Server is installed.

By default, encryption keys are stored in the C:\ProgramData\Microsoft\Microsoft Dynamics NAV\90\Server\Keys folder.

Configure SQL Authentication on the Database

This section describes how to configure a Microsoft Dynamics NAV database to use SQL Server Authentication with a Microsoft Dynamics NAV Server instance. You can complete the steps in this procedure by using SQL Server Management Studio or Transact-SQL.

Important : In a deployment where the Microsoft Dynamics NAV Server instance is configured as a multitenant server instance, you must complete the following procedure on the application database and tenant database.

To configure SQL Server Authentication on the database in SQL Server

  • Configure the SQL Server instance (Database Engine) that hosts the Microsoft Dynamics NAV database to use SQL Server Authentication.

To use SQL Server authentication, you configure the database instance to mixed authentication mode (SQL Server and Windows Authentication).

In the SQL Server instance, create a login that uses SQL Server authentication.
SQLServerAuthentication3

  • Map the login to a user in the Microsoft Dynamics NAV database, and add the user to the db_owner role of the Microsoft Dynamics NAV database.

SQLServerAuthentication4
Configure SQL Server Authentication on Microsoft NAV Server Instance (Non-Multitenant)
You configure the Microsoft Dynamics NAV Server instance with the login credentials (user name and password) of the user account in the Microsoft Dynamics NAV database in SQL Server that you want to use for authentication. You can do this using the Microsoft Dynamics NAV Server Administration tool or Microsoft Dynamics NAV 2016 Administration Shell.
To configure SQL Authentication on Microsoft NAV Server Instance using Microsoft Dynamics NAV Server Administration tool

  • Open the Microsoft Dynamics NAV Server Administration tool.
  • In the Actions pane, choose Database Credentials.
  • Set the Database Authentication Type to SQL Authentication.
  • In the Database User Name field, type the login name for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.

In the Password field, type the login password for the database user that you want to use to access the Microsoft Dynamics NAV database in SQL Server.
SQLServerAuthentication5

  • Choose the Save button, and then on the Enable Encryption on SQL Server Connections dialog box, choose the OK button.

SQLServerAuthentication6

Encryption keys are used to help secure the login credentials over the connection between the Microsoft Dynamics NAV Server instance and the Microsoft Dynamics NAV database in SQL Server.

  • On the Information dialog box about encryption, choose the OK button.

This dialog box is to inform you to enable encryption on SQL Server connections, which is disabled by default.
SQLServerAuthentication7

  • If you want to enable encryption on SQL Server connections, in the Action pane, choose Configuration, and then choose the Edit button. In the Database tab, select Enable Encryption on SQL Connections, choose the Save button, and then the OK button.

SQLServerAuthentication8

  • Restart the server instance.

Checkout my upcoming posts for more details on this.

Corfu Navision 2016, Information

Microsoft Dynamics Navision 2016 – ‘Corfu’ Rewind

Ashwini Tripathi

In last 1 month we all were very much concentrated on new release of Navision 2016.

Here I am presenting link of all post posted during this period for quick reference.

Still there is lot of things to share and yet many of the topics have not been covered in details, also many yet not touched still many pages of this story remains unturned.

I will be covering them in my upcoming posts.

Navision 2016 – ‘Corfu’

Get Ready for Microsoft Dynamics NAV 2016

What’s New: Application Changes for Microsoft Dynamics NAV 2016

What’s New: Developer Changes for Microsoft Dynamics NAV 2016

Changes in C/AL Behaviour C/AL Functions in Nav 2016

Changes in C/AL Behaviour C/AL Properties in Nav 2016

Changes in C/AL Behaviour C/AL Statements in Nav 2016

Redesigned C/AL Editor in Navision 2016

Record Permissions and Apply Permissions Sets to User Groups in Navision 2016

Posting Preview in Navision 2016

Filter Pages for Filtering Tables using FILTERPAGEBUILDER

Events in C/AL Navision 2016

Using a Timestamp Field in Navision 2016

Implementing Events in Navision 2016

Deferral Functionality in Navision 2016

Workflows in Dynamics NAV 2016

Multiple namespace support in XML Ports – Microsoft Dynamics NAV 2016

Modify, Assign and Process Email for Word Forms in Navision 2016

How Do I Set Up Microsoft Dynamics NAV to work with Azure SQL Database | Dynamics NAV Team Blog

Microsoft Dynamics NAV 2016 Works Natively with Microsoft Dynamics CRM | Dynamics NAV Team Blog

Introducing Extensions in Microsoft Dynamics NAV 2016

PowerBI.com content package for Microsoft Dynamics NAV 2016

Using Try Functions in Navision 2016

Approval Types in Workflow for Microsoft Dynamics NAV 2016 | Dynamics NAV Team Blog

Upgrade Considerations for Document Approvals | Dynamics NAV Team Blog

Accounting Services Role Center for Dynamics NAV 2016 | Dynamics NAV Team Blog

Integration Events in Microsoft Dynamics NAV 2016 | Dynamics NAV Team Blog

Email Logging with Exchange Online in Microsoft Dynamics NAV 2016 | Dynamics NAV Team Blog

Test Isolation in the Application Test Automation Suite | Dynamics NAV Team Blog

Upgrade in Microsoft Dynamics NAV 2016

Upgrading the Application Code in Microsoft Dynamics NAV 2016

Announcing the Microsoft Dynamics NAV Universal App (Dynamics NAV v2.0)

Upgrading the Data in Navision 2016

Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016

Few Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016 – Part 2

NAV Design Pattern: TryFunction – .NET Exception Handling in C/AL

I will come up with more topics on this version in coming days. Stay tuned for more details.

Corfu Navision 2016, Data, Development Tips, How To, Information, Instalation & Configuration, PowerShell, upgrade

Few Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016 – Part 2

Ashwini Tripathi

You can use Windows PowerShell scripts to upgrade the latest version of Microsoft Dynamics NAV. Microsoft Dynamics NAV 2016 provides sample scripts that you can adapt for your deployment architecture.

Automating the Upgrade Process

When you upgrade to Microsoft Dynamics NAV 2016, you must first upgrade the application code, and then you upgrade the data.

In my earlier post I have explained this using PowerShell commands, you can find the link here: Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016

By using Windows PowerShell, you can automate both parts of the upgrade process. Also, you can use the same scripts to test each step in your upgrade process before you upgrade production databases.

You can combine this automated upgrade with a migration to multitenancy this makes maintenance easier for you.

The Sample Scripts for Code Upgrade

Microsoft Dynamics NAV includes sample scripts that illustrate how you can use Windows PowerShell cmdlets to upgrade your application to the latest version of Microsoft Dynamics NAV.

The sample scripts are located in the ApplicationMergeUtilities folder under the WindowsPowerShellScripts folder on the Microsoft Dynamics NAV product media.

However you can follow above post link steps explanation to get it done.

The Sample Scripts for Data Upgrade

Microsoft Dynamics NAV includes sample scripts that illustrate how you can automate the upgrade of data to the latest version of Microsoft Dynamics NAV.

The sample scripts are located in the Upgrade folder under the WindowsPowerShellScripts folder on the Microsoft Dynamics NAV product media. You can run the sample script using a partner license or a customer license.
PowerShell-3

To learn and follow MS suggested steps you can find details using this link. Automating the Upgrade Process using Windows PowerShell Scripts in Microsoft Dynamics NAV 2016

To run the sample script for the data upgrade of a Microsoft Dynamics NAV database, you must have a Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database that is available on a SQL Server instance and is ready to be upgraded.

Here I present my version derived from above Steps:

To continue we will do some setup. Copy the Upgrade folder from above path and save as DataUpgradePSKit.

PowerShell-4

Create Folder OriginalScript and move the PS1 file on root to this folder although we don’t require for this exercise but you can safe copy for your reference. (Example, Set-PartnerSettings, Set-PowerShellEnvironment)

Create Backup folder, script will use to store backup of the database previous to start Upgrade process.

Create Upgrade Folder and place these files:

  • License File
  • New Merged Objects fob
  • Upgrade Toolkit / or your own prepared Upgrade Codeunits

PowerShell-5

  • Create ProcessLogs Folder, which will be used for recording log of Shell Script.

Here is the script which we will be using to perform our Data Upgrade process:

You can find this script here http://1drv.ms/1NyolVV or you can download from Menu of my Blog using Link Shared Files.

 

# Added below parameter values globally for ease of maintenance

# You just do correction on values here (as per your environment) and will be in effect for rest of below script

# No need to scan and change every occurrence for same value in different steps of the script.

# Select this section and Execute first so that these Variables value are available for rest of the script.

Import-Module ‘C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1’

$NAVUpgrade_NAVServerInstance = “UpgradedDBfrom2013R2”

$NAVUpgrade_NAVServerServiceAccount = “NT AUTHORITY\NETWORK SERVICE”

$NAVUpgrade_FinSqlExeFile = “C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client\finsql.exe”

$NAVUpgrade_IDEModulePath = “”

$NAVUpgrade_DatabaseServer = “INDEL-AXT5283VM”

$NAVUpgrade_DatabaseInstance = “”

$NAVUpgrade_DatabaseName = “Demo Database NAV (7-1)”

$NAVUpgrade_DatabaseToUpgradeBakFile = “C:\UserData\DataUpgradePSKit\Backup\DynamicsNAV70_BeforeUpgrade.bak”

$NAVUpgrade_NewVersionObjectsFobFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\NewObjects.fob”

$NAVUpgrade_UpgradeToolkitObjectsFobFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\Upgrade710900.FOB”

$NAVUpgrade_UpgradeObjectsFilter = “Version List=UPGTK9.00.00”

$NAVUpgrade_UpgradeLogsDirectory = “C:\UserData\DataUpgradePSKit\Upgrade\ProcessLogs”

#$NAVUpgrade_RapidStartPackageFile = ‘C:\UserData\DataUpgradePSKit\Upgrade\PackageSTCODES.rapidstart’

$NAVUpgrade_CurrentVersionLicenseFile = “C:\UserData\DataUpgradePSKit\Upgrade\DevLicense.flf”

$NAVUpgrade_PreviousVersionLicenseFilePath = “C:\UserData\DataUpgradePSKit\Upgrade\DevLicense.flf”

 

# Upgrade Steps:

Import-Module (Join-Path (Get-Location) ‘Cmdlets\NAVUpgradeCmdlets.psm1’) -DisableNameChecking

#1. Prepares the Windows PowerShell session by importing the required modules.

        # Import the NAV IDE Module.

Import-NAVIdeModule -IDEModuleSuggestedPath $NAVUpgrade_IDEModulePath -FinSqlExeFile $NAVUpgrade_FinSqlExeFile

Import-NAVManagementModule

Import-SqlPsModule

 

#2. Saves the current license from the Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database.

# Backup current license from the application part of the database (table ‘$ndo$dbproperty’) , if it exists

        Export-NAVLicenseFromApplicationDatabase `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-LicenseFilePath $NAVUpgrade_PreviousVersionLicenseFilePath

 

#3. Creates a backup of the Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, or Microsoft Dynamics NAV 2015 database, and then converts the database to Microsoft Dynamics NAV 2016.

        Backup-NAVSqlDatabase `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseBackupFilePath $NAVUpgrade_DatabaseToUpgradeBakFile

$NAVUpgrade_DatabaseSQLServerInstance = Get-SqlServerInstance -DatabaseServer $NAVUpgrade_DatabaseServer -DatabaseInstance $NAVUpgrade_DatabaseInstance

$NavServerInfo = New-Object PSObject

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerName -Value “$NAVUpgrade_DatabaseServer”

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerInstance -Value (Get-NAVServerConfigurationValue  -ServerInstance $NAVUpgrade_NAVServerInstance -ConfigKeyName “ServerInstance”)

Add-Member -InputObject $NavServerInfo -MemberType NoteProperty -Name NavServerManagementPort -Value (Get-NAVServerConfigurationValue -ServerInstance $NAVUpgrade_NAVServerInstance -ConfigKeyName “ManagementServicesPort”)

 

# Perform technical upgrade of the NAV database

        Invoke-NAVDatabaseConversion `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory\”Database Conversion”

 

#4. Connects the Microsoft Dynamics NAV 2016 Server instance to the converted database, imports the Microsoft Dynamics NAV 2016 license file, and then synchronizes the table schema.

 

# Connect the NAV Server to the NAV database

        Connect-NAVServerToNAVDatabase  `

-NAVServerInstance $NAVUpgrade_NAVServerInstance `

-NAVServerServiceAccount $NAVUpgrade_NAVServerServiceAccount `

-DatabaseServer $NAVUpgrade_DatabaseServer `

-DatabaseInstance $NAVUpgrade_DatabaseInstance `

-DatabaseName $NAVUpgrade_DatabaseName

# Import the new version license into the application database, and restart the server in order for the license to be loaded

        Import-NAVServerLicense -ServerInstance $NAVUpgrade_NAVServerInstance -LicenseFile $NAVUpgrade_CurrentVersionLicenseFile -Database NavDatabase

Set-NAVServerInstance -ServerInstance $NAVUpgrade_NAVServerInstance -Restart

# Synchronize the NAV database

        Sync-NAVTenant -ServerInstance $NAVUpgrade_NAVServerInstance -Mode Sync -Force

 

#5. Imports the application objects and upgrade toolkit objects from the specified .fob file, and then synchronizes the table schema again.

#   This updates the SQL Server database based on the new table schema that is defined by the imported application objects. Data that must be mapped to another table is saved in upgrade tables.

# Delete the tables from the previous version, using SynchronizeSchemaChanges Later.

# The new  objects we import will contain the new version of the tables.

        Delete-NAVApplicationObject `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-Filter “Type=Table;ID=<2000000000” `

-SynchronizeSchemaChanges “No” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

# Import all the new objects and the upgrade objects, by delaying the schema synchronization

# If an $UpgradeToolkitObjects value has not been provided, then

#  the assumption is that the upgrade toolkit is within the same .FOB as the new objects

           if(!$UpgradeToolkitObjects)

{

# Import FOB file containing the new version of the application objects, including the upgrade toolkit

Import-NAVApplicationObject `

-Path $NAVUpgrade_NewVersionObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

}

else

{

 

# Import FOB file containing the new version of the application objects

Import-NAVApplicationObject `

-Path $NAVUpgrade_NewVersionObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-Confirm:$false

 

# Import FOB file containing the upgrade codeunit and upgrade tables

Import-NAVApplicationObject `

-Path $NAVUpgrade_UpgradeToolkitObjectsFobFilePath `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-ImportAction “Overwrite” `

-SynchronizeSchemaChanges “No” `

-Confirm:$false

}

# Synchronize the metadata changes to SQL

        Sync-NAVTenant -ServerInstance $NAVUpgrade_NAVServerInstance -Mode Sync -Force

#6. Calls the Start-NAVDataUpgrade cmdlet to verify the data upgrade preconditions and transfer data from the upgrade tables to the destination tables.

# Invoke the Data Upgrade process

        Invoke-NAVDataUpgrade -ServerInstance $NAVUpgrade_NAVServerInstance

 

#7. Deletes all obsolete tables and the upgrade toolkit objects.

# Delete Upgrade Toolkit objects

        Delete-NAVApplicationObject `

-DatabaseName $NAVUpgrade_DatabaseName `

-DatabaseServer $NAVUpgrade_DatabaseSQLServerInstance `

-LogPath $NAVUpgrade_UpgradeLogsDirectory `

-Filter “$NAVUpgrade_UpgradeObjectsFilter;ID=<2000000000” `

-SynchronizeSchemaChanges “Force” `

-NavServerName $NavServerInfo.NavServerName `

-NavServerInstance $NAVServerInfo.NavServerInstance `

-NavServerManagementPort $NavServerInfo.NavServerManagementPort `

-Confirm:$false

 

#8.       Initializes all companies in the upgraded database. If you specified a RapidStart package in the Set-PartnerSettings.ps1 file, the package is applied to all companies.

# Optionally, run RapidStart package import

        if($NAVUpgrade_RapidStartPackageFile)

{

Invoke-NAVRapidStartDataImport -ServerInstance      $NAVUpgrade_NAVServerInstance -RapidStartPackageFile $NAVUpgrade_RapidStartPackageFile

 

}

 

The sample script is intended to be run in the context of a Microsoft Dynamics NAV 2016 deployment, including the Microsoft Dynamics NAV Server instance.

The Microsoft Dynamics NAV Server instance cannot be multitenant. When the sample script runs successfully, the result is a Microsoft Dynamics NAV 2016 database that is connected to a Microsoft Dynamics NAV 2016 Server instance, and which uses a Microsoft Dynamics NAV 2016 license.

You may face some permission related issues, take help of you IT person if not sure about the nature of issue or use Administrator login.

I will come with more details in my next posts.

 

 

Corfu Navision 2016, Development Tips, How To, Information, upgrade

Helpful PowerShell Commands which you can use for Upgrade Process in Navision 2016

Ashwini Tripathi

In today’s post we will see some Power Shell Commands which will be helpful while performing Upgrades using PowerShell. For below commands to exercise you will need to create/prepare Folder Structure as below else you will have to modify the path in below script as one which you will be using.

For your ease I have uploaded the below script in form of text file named ‘MyPowerShellScript.txt’ which you can get using this link http://1drv.ms/1OOf7If alternatively from Menu of my blog side use Shared Files to access the file.
PowerShell-1

Extract you objects in Text format from Base, Customized and Target Database and place in respective folders. One text file per object will be better option for getting more clear insight on Results. You can find the command for Splitting the File per object in below Script.

After opening the PowerShell or ISE change your folder to Upgrade Demo as in my case it will be:

PS C:\userdata\upgrade demo>
PowerShell-2
Select the Script and Press Button in Toolbar Run Selection (F8) in Windows PowerShell ISE (Desktop App).

#1. Start Import NAV Module

Import-Module “${env:ProgramFiles(x86)}\Microsoft Dynamics NAV\90\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1” -force -DisableNameChecking

Get-Help “NAV”

This is must in order to run all below commands.

#2. Merge Objects

# Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Standard PowerShell table formatting with sorting on Object Type, Id

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Sort-Object ObjectType, Id |

Format-Table

# Use PowerShell VARIABLE, PIPING, FILTER, and LISTS – capture result in variable, then list file names of ORIGINAL and TARGET files in conflict 

$myVariable = Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -TargetPath .\TARGET\*.txt `

-ModifiedPath .\MODIFIED\*.txt -ResultPath .\RESULT -Force

$myVariable.Summary

$myVariable |

Where-Object MergeResult –eq ‘Conflict’ |

Select Original, Target |

Format-List

# Open NOTEPAD for each CONFLICT file 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -TargetPath .\TARGET\*.txt `

-ModifiedPath .\MODIFIED\*.txt -ResultPath .\RESULT -Force -PassThru |

Where-Object MergeResult -eq ‘Conflict’ |

foreach { NOTEPAD $_.Conflict }

# Handling Documentation triggers: Merged by default, but conflict can either be treated as real conflict (Strict) or both inserted (ModifiedFirst or TargetFirst)

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\pagXXXX.txt -ModifiedPath .\MODIFIED\pagXXXX.txt `

-TargetPath .\TARGET\pagXXXX.txt -ResultPath .\RESULT\pagXXXX.txt -Force -DocumentationConflict Strict

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\pagXXXX.txt -ModifiedPath .\MODIFIED\pagXXXX.txt `

-TargetPath .\TARGET\pagXXXX.txt -ResultPath .\RESULT\pagXXXX.txt -Force -DocumentationConflict ModifiedFirst

#3. Merge Format Output

# Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Same: Capture the rich output in a PowerShell variable for further processing

$result = Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Using the variable: Use standard PowerShell outputting of variable 

$result

# Using the variable: Use the Summary property of the result 

$result.Summary

# Using the variable: Use standard PowerShell table formatting 

$result | Format-Table

# Use standard PowerShell table formatting 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Format-Table

# Use standard PowerShell table formatting with sorting on ObjectType, Id 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Sort-Object ObjectType, Id |

Format-Table

# Use standard PowerShell graphical output (GridView) 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Out-GridView

#4. Merge Filter Output

 # Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Same: Plus use PIPING, PASSTHRU and FILTER – show objects with CONFLICT only

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -TargetPath .\TARGET\*.txt `

-ModifiedPath .\MODIFIED\*.txt -ResultPath .\RESULT -PassThru -Force |

Where-Object MergeResult –eq ‘Conflict’

# Same: Plus use PIPING, PASSTHRU, FILTER, and COUNT – count MERGED objects

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -TargetPath .\TARGET\*.txt `

-ModifiedPath .\MODIFIED\*.txt -ResultPath .\RESULT -PassThru -Force |

Where-Object MergeResult –eq ‘Merged’ |

Measure-Object

# Same: Plus use PIPING, PASSTHRU, FILTER, and LISTING FILES – list file names of ORIGINAL and TARGET files in conflict 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -TargetPath .\TARGET\*.txt `

-ModifiedPath .\MODIFIED\*.txt -ResultPath .\RESULT -PassThru -Force |

Where-Object MergeResult –eq ‘Conflict’ |

Select Original, Target |

Format-List

#5. Merge and open Conflicts files using External Tools

 # Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT 

$result = Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Open NOTEPAD for each CONFLICT file 

$result |

Where-Object MergeResult -eq ‘Conflict’ |

foreach { NOTEPAD $_.Conflict }

#6. Merge and Documentation Triggers

 # Compare object DOCUMENTATION modifications: Body-text (can be merged) and version list additions (potential conflict). 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\pagXXXX.txt -ModifiedPath .\MODIFIED\pagXXXX.txt `

-TargetPath .\TARGET\pagXXXX.txt -ResultPath .\RESULT\pagXXXX.txt -Force -DocumentationConflict ModifiedFirst

# Same: But STRICT on conflicts. 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\pagXXXX.txt -ModifiedPath .\MODIFIED\pagXXXX.txt `

-TargetPath .\TARGET\pagXXXX.txt -ResultPath .\RESULT\pagXXXXa.txt -Force -DocumentationConflict Strict

#7. Merge and import in CSIDE

# Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

# Locate subset (code units) of partionally merged object files, combine them into a single file for performance, and import them into C/SIDE (parameter -Database for you to provide). All throught piping. 

Get-ChildItem .\RESULT\COD*.txt |

Join-NAVApplicationObjectFile -Destination .\RESULT\partially-merged.txt |

Import-NAVApplicationObject

#8. Merge and Export Result to Excel Output 

# Compare ORIGINAL and MODIFIED and merge onto TARGET, then put the merged files in RESULT 

$result = Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

$AutoMerged = ($result | Where-Object MergeResult -eq ‘Merged’).Count

$Conflict   = ($result | Where-Object MergeResult -eq ‘Conflict’).Count

$Unchanged  = ($result | Where-Object MergeResult -eq ‘Unchanged’).Count

$Inserted   = ($result | Where-Object MergeResult -eq ‘Inserted’).Count

$Unknown    = ($result | Where-Object MergeResult -eq ‘Unknown’).Count

$Deleted    = ($result | Where-Object MergeResult -eq ‘Deleted’).Count

$Title      = “MySample”  # update Excel template file to allow blanks and special characters

$excelPath = “$(Get-ItemProperty ‘HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe’ | select -exp Path)\Excel.exe”

& $excelPath “.\Merge Result Sample.xlsm” “/e/$Title/$AutoMerged/$Conflict/$Unchanged/$Inserted/$Unknown/$Deleted”

# Start-Process -FilePath “.\Merge Result Sample.xlsm” -ArgumentList “/e/BAS/$AutoMerged/$Conflict/$Unchanged/$Inserted/$Unknown/$Deleted”

#9. Compare

 # Compare ORIGINAL and MODIFIED and output MULTIPLE separate DELTA files 

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -DeltaPath .\DELTA -Force

# Compare ORIGINAL and TARGET, pipe the result into the Update-cmdlet (in particular the DELTA parameter) 

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -DeltaPath .\DELTA -Force -PassThru |

Update-NAVApplicationObject -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

#10. Compare Piped

 # Compare ORIGINAL and TARGET and output ONE summary DELTA file 

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\TARGET\*.txt -DeltaPath .\RESULT\sum-of-deltas.txt -Force

#11. Update Piped

# Compare ORIGINAL and MODIFIED and output MULTIPLE separate DELTA files, capture result in variable to apply multiple times 

$myDifferences = Compare-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -DeltaPath .\DELTA -Force

# Apply a captured set of differences to TARGET objects using the Update-cmdlet 

$myDifferences |

Update-NAVApplicationObject -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

#12. Join & Split Object Files

# Join all Codeunit-TXT-files into a single TXT-file

Join-NAVApplicationObjectFile -Source .\ORIGINAL\COD*.txt -Destination .\RESULT\all-codeunits.txt -Force

# Join list of Codeunit-TXT-files into a single TXT-file 

Join-NAVApplicationObjectFile -Source .\ORIGINAL\COD1.txt, .\ORIGINAL\COD2.txt, .\ORIGINAL\COD3.txt -Destination .\RESULT\3-codeunits.txt -Force

# Split a single TXT-file with multiple application objects into separate files in the DESTINATION folder 

Split-NAVApplicationObjectFile -Source .\RESULT\all-codeunits.txt -Destination .\SEPARATE -Force

Make sure you have created Separate Folder before executing this script.

#13. Get Application Properties 

# Show values of application properties in CODXXXX.TXT 

Get-NAVApplicationObjectProperty -Source .\RESULT\CODXXXX.txt

#14. Set Application Properties

# Set all object properties on named object COD1.TXT: VersionList to DemoV1, Modified as modified, and Date and Time to current date/time (show outcome) 

Set-NAVApplicationObjectProperty -TargetPath .\RESULT\COD1.txt -VersionListProperty “DemoV1” -ModifiedProperty Yes -DateTimeProperty (Get-Date -Format g)

Get-NAVApplicationObjectProperty -Source .\RESULT\COD1.txt

# Set date/time to a fixed, machine-locale agnostic date (January 1st, 2015) on Merged objects 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Where-Object MergeResult –eq ‘Merged’ |

foreach { Set-NAVApplicationObjectProperty -TargetPath $_.Result -DateTimeProperty (Get-Date -Year 2015 -Month 1 -Day 1 -Hour 0 -Minute 0 -Format g) }

# Capture merge-result in variable, set VersionList property as an concatenation of Modified and Target values with trailing ‘!’ 

$result = Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force

$result |

Where-Object MergeResult –eq ‘Merged’ |

foreach { Set-NAVApplicationObjectProperty -TargetPath $_.Result -VersionListProperty “$_.Modified.VersionList,$_.Target.VersionList!”;

Get-NAVApplicationObjectProperty -Source $_.Result }

# Display property VersionList of CODXXXX.TXT in MODIFIED, TARGET, and RESULT 

Get-NAVApplicationObjectProperty -Source .\MODIFIED\CODXXXX.txt | select VersionList

Get-NAVApplicationObjectProperty -Source .\TARGET\CODXXXX.txt | select VersionList

Get-NAVApplicationObjectProperty -Source .\RESULT\CODXXXX.txt | select VersionList

#15. Set Application Properties

# Set date/time to fixed date (January 31st, 2015) on Merged objects 

Merge-NAVApplicationObject -OriginalPath .\ORIGINAL\*.txt -ModifiedPath .\MODIFIED\*.txt -TargetPath .\TARGET\*.txt -ResultPath .\RESULT -Force -PassThru |

Where-Object MergeResult –eq ‘Merged’ |

foreach { Set-NAVApplicationObjectProperty -TargetPath $_.Result -DateTimeProperty “31-1-2015” }

#16. Export Language

# Export ONE language (Spanish) from a single object file.  

Export-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId “ESP” -Destination .\result\TAB14-ESP.TXT -Force

# Export one language (Spanish) from a single object file with non-standard code page ENCODING.  

Export-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId “ESP” -Destination .\result\TAB14-ESP-UNICODE.TXT -Encoding Unicode -Force

# Export ONE language (Spanish) from ALL objects into a SINGLE file. 

Export-NAVApplicationObjectLanguage –Source .\original\*.TXT -LanguageId “ESP” -Destination .\result\ALL-ESP.TXT -Force

# Export MULTIPLE languages (Spanish, US English) from ALL objects

Export-NAVApplicationObjectLanguage –Source .\original -LanguageId “ESP”,”ENU” -Destination .\result -Force

# Export ALL languages (Spanish, US English, Danish) from ALL objects into result folder  

Export-NAVApplicationObjectLanguage –Source .\original -Destination .\result -Force

# Export ALL languages (Spanish, US English, Danish) from ALL objects into a SINGLE file

Export-NAVApplicationObjectLanguage –Source .\original -Destination .\result\single.txt -Force

#17. Import Language  

# Import Spanish language from FULL translation file into a single object 

Import-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId “ESP” -LanguagePath .\result\ALL-ESP.TXT -Destination .\result -Force

#18. Remove Language

# Remove Spanish captions from a single object 

Remove-NAVApplicationObjectLanguage –Source .\target\TAB14.TXT -LanguageId “ESP” -Destination .\result\TAB14-ESP-REMOVED.TXT -Force

# Remove all captions from multiple objects, result in a single file 

Remove-NAVApplicationObjectLanguage –Source .\target\TAB14.TXT, .\target\PAGXXXX.TXT -Destination .\result -Force

#19. Test-Languages 

# Test all Spanish captions are present. PowerShell error returned, if translations are missing.

Test-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId ESP

# Test all Danish captions are all present. Catch error situation and report nicely back to the script. 

try

{

Test-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId DAN -ErrorAction Stop

}

catch

{

Write-Host “One or more translations are missing for the DAN language.” -ForegroundColor Yellow

}

# Test all Danish and Spanish captions are all present. With -PassThru a warning is reported and tranlation lines are returned for processing. 

Test-NAVApplicationObjectLanguage –Source .\original\TAB14.TXT -LanguageId ESP,DAN -PassThru

#20. Split or Join Language File

# Split an application translation file into per-object files

Split-NAVApplicationObjectLanguageFile -Source .\result\single.txt -Destination .\result -Force

# Join multiple application translation files into one combined file  

Join-NAVApplicationObjectLanguageFile -Source .\result\*-ESP.TXT -Destination .\result\JOINED-ESP.txt -Force

I will come with more details in my upcoming posts.

Corfu Navision 2016, Data, Development Tips, How To, Information, upgrade

Upgrading the Data in Navision 2016

Ashwini Tripathi

Continuing from my earlier post Upgrading the Application Code in Microsoft Dynamics NAV 2016

At end of previous post we have imported the objects and compiled also resolved any conflicts and compilation error. In the same process we have got the list of objects which were having destructive table schema, means the objects which will be having the changes due to which we could lose some data. Scan the objects and if we wish to save those data then we will be requiring Data Upgrade codeunits to handle any such situation.

How to create you can refer to my earlier post Data Upgrade – in Navision 2015 this it still valid for 2016.

Now we have all the upgraded application objects and Data Upgrade codeunits if any required for the upgrade. So we are good to go with Upgrading Data for Old database.

We will follow below steps to continue, make sure you have followed the process for preparation/ converting of database as discussed in my earlier post Upgrade in Microsoft Dynamics NAV 2016

Import the application objects to the converted database

In the development environment, import all the application objects that you extracted in previous step as in my earlier post discussed above in the Microsoft Dynamics NAV 2016 database. This includes the FOB file that contains all the Microsoft Dynamics NAV 2016 objects from the application code upgrade and upgrade toolkit objects if any.

When you import the FOB file, if you experience metadata conflicts, use the Import Worksheet to handle these conflicts.

Finally, on the dialog box for selecting the schema synchronization, set the Synchronize Schema option to Later.
Upgrade2016-29

If the upgrade toolkit objects are stored in a separate FOB file, then import the upgrade toolkit FOB file after the application objects are imported.

 

Run the schema synchronization to synchronize the new tables

To publish the data schema changes of the newly imported tables to the SQL tables, run the Sync. Schema For All Tables – With Validation option from the development environment.
Upgrade2016-30

If you are confident that any loss of data you are ok with same you can choose Force option which will drop the data for deleted fields, else use Upgrade Codeunit prepared for same as discussed above.

Or alternatively run the Sync-NavTenant cmdlet from the Microsoft Dynamics NAV 2016 Administration Shell.

Sync-NAVTenant –ServerInstance UpgradedDBfrom2013R2 (My Server Instance Name)

Upgrade2016-31

Note this command runs in Administration Shell not in Development Shell as we used in our previous post.

Run the data upgrade process

A data upgrade runs the upgrade toolkit objects, such as upgrade codeunits and upgrade tables, to migrate business data from the old table structure to the new table structure. You can start the data upgrade from the Microsoft Dynamics NAV Development Environment.
Upgrade2016-32

Upgrade2016-33

Upgrade2016-34

Upgrade2016-35

Ooops………….

Or Alternatively Microsoft Dynamics NAV 2016 Administration Shell.

In the last phase of data upgrade, all companies will be initialized by running codeunit 2 Company Initialization. This is done automatically.

If you want to skip company initialization, then use the Start- NavDataUpgrade cmdlet and set the SkipCompanyIntitialization parameter.

Syntax:

Start-NAVDataUpgrade [-ServerInstance] <String> [[-Tenant] <TenantId> ] [[-FunctionExecutionMode] <FunctionExecutionModeValue> ] [[-ContinueOnError]] [-Force] [-Confirm] [-WhatIf] [ <CommonParameters>]

Parameters
-ContinueOnError

Specifies whether the Microsoft Dynamics NAV Server instance continues to execute other upgrade functions when an error occurs while executing an upgrade function.

If you do not set this parameter, then when an error occurs, the Microsoft Dynamics NAV Server instance will suspend the data upgrade process. It will cancel the execution of upgrade functions currently in progress and roll back any changes that were applied. Completed functions will not be rolled back.

The process remains in suspended state until you take one of the following actions:

– Fix the problems in the upgrade functions that failed, and then resume the process by using the Resume-NAVDataUpgrade cmdlet. You should not add new upgrade functions at this time because they will be ignored when you resume the process.

– Stop the data upgrade process by using the Stop-NAVDataUpgrade cmdlet. Stopping the process will not roll back changes made by upgrade functions that have already been executed.

If you set this parameter, then when an error occurs, the Microsoft Dynamics NAV Server instance will continue executing other upgrade functions. At the end of the process, you can use the Get-NAVDataUpgrade cmdlet to see the list of failed upgrade functions. Changes that were applied by completed functions will not be rolled back.

When upgrading a large database, you should increase the SQL Command Timeout setting for the Microsoft Dynamics NAV Server instance that connects to the database to avoid timeouts during schema synchronization. The default setting is 30 minutes

-ServerInstance<String>

Specifies the Microsoft Dynamics NAV Server instance that the application database and the tenant database are mounted against, such as DynamicsNAV90.

You must include the name within single quotation marks.

-Tenant<TenantId>

Specifies the ID of the tenant that you want to synchronize with the application, such as Tenant1.

This parameter is required unless the specified service instance is not configured to run multiple tenants.

-Force

Forces the command to run without asking for user confirmation.
-FunctionExecutionMode<FunctionExecutionModeValue>
Specifies whether the Microsoft Dynamics NAV Server instance executes upgrade functions in series or parallel.
-Confirm
Prompts you for confirmation before running the cmdlet.

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Few Examples of Usage:

[1] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 –Force

[2] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 -ContinueOnError –Force

[3] PS C:\> Start-NAVDataUpgrade -ServerInstance DynamicsNAV90 -FunctionExecutionMode Serial –Force
Upgrade2016-36

Start-NAVDataUpgrade -ServerInstance UpgradedDBfrom2013R2 –Force

Ooops………….

Now what to do?

To learn how to Create Data Upgrade Codeunits you can see my earlier posts:

Data Upgrade Codeunit in Navision 2015 – Part -1

Data Upgrade Codeunit in Navision 2015 – Part -2

These posts are still valid for 2016, you can follow to get you task done at this step resolution to above error.

To resolve above issue I have followed the instruction as suggested in error message, but in real scenario you will definitely will be having such codeunits. Since I have taken the 2013-R2 Std. Database I am having this issue. I have created on Upgrade codeunit with an empty upgrade function as below:
Upgrade2016-37

Let us run the above process again.
Upgrade2016-38

This time I was able to complete the process successfully.

Delete the upgrade objects

At this point, you have upgraded the database to Microsoft Dynamics NAV 2016. Now, you can delete the upgrade codeunits and upgrade table objects that you imported in above step.

When you delete tables, on the Delete dialog box, set the Synchronize Schema option to Force.

Import upgraded permission sets and permissions by using the Roles and Permissions XMLports

You import the permission sets and permissions XML files according to the following procedure.

To import the permission sets and permissions

  • Delete all permission sets in the database except the SUPER permission set.

In Object Designer, run page 9802 Permission Sets, and then delete the permission sets.

  • Run XMLport 9171 Import/Export Permission Sets to import the permission sets XML file,

In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permission sets XML file.

  • Run XMLport 9172 Import/Export Permissions to import the permission XML file.

In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permissions XML file.

Set the language of the customer database

In the development environment, choose Tools, choose Language, and then select the language of the original customer database.

Add new control add-ins

The database is now fully upgraded and is ready for use. However, you may want to add the new client control add-ins that are included in Microsoft Dynamics NAV 2016. These are not added by the upgrade process. The following client control add-ins are available from the Microsoft Dynamics NAV product media:

  • Microsoft.Dynamics.Nav.Client.BusinessChart
  • Microsoft.Dynamics.Nav.Client.PageReady
  • Microsoft.Dynamics.Nav.Client.PingPong
  • Microsoft.Dynamics.Nav.Client.VideoPlayer
  • Microsoft.Dynamics.Nav.Client.SocialListening

You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client.

I will come up with more details on this topic in my upcoming posts.

Data Model, Development Tips, Excel, How To, Information, Instalation & Configuration, Office Integration, PivotTable, Power View, PowerBI.com, PowerPivot

PowerPivot in Excel 2013

Ashwini Tripathi

I started the series in End of September and Starting of October on PowerPivot, Power View, PivotTable & Reports but in-between the release of Navision 2016 all the topics got scattered between other posts and I didn’t ended the topic.

Here I present all the posts link at one place which you can use as table of content for easy access and to help if any one wish to start from beginning and learn all the features & Topic on same.

PowerPivot for Excel

Start the Power Pivot in Microsoft Excel add-in

Troubleshooting: Power Pivot Ribbon Disappears

PowerPivot Creating a Data Model in Excel 2013

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

How to add Filter for data retrieval in PowerPivot Data model.

Create a calculated column in PowerPivot

Creating My First Report using PowerPivot

Basics of Power Pivot for Excel – 2013

Add Slicers to PivotTables in PowerPivot

Power View in Excel 2013

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

Add Excel Sheet/Table to the PowerPivot Data Model

Add a relationship using Diagram View in Power Pivot

Extend the Data Model using calculated columns

Create a hierarchy in PowerPivot Data Model

Use hierarchies in PivotTables

Create a Power View report

Create a calculated field in PowerPivot

Set field defaults in PowerPivot

Set Table Behaviour in PowerPivot

Set Data Categories for fields in PowerPivot

I will come up with more details once I get some time to explore and find anything which I feel is good to share with the community.

Till then keep Learning, Exploring and Practicing.

Corfu Navision 2016, Development Tips, How To, Information, upgrade

Upgrading the Application Code in Microsoft Dynamics NAV 2016

Ashwini Tripathi

Continuing from my earlier post Upgrade in Microsoft Dynamics NAV 2016

No doubt typically, customers want all the customizations that have been implemented in their existing Microsoft Dynamics NAV databases to be migrated to their new Microsoft Dynamics NAV 2016 databases.

Depending on the version of Microsoft Dynamics NAV that a database is being upgraded from, the amount of code changes between the two versions is large or small.

To upgrade the application code, you must merge code from different versions of the application. This merge process is known as a code upgrade or application upgrade.

You must upgrade the application before you upgrade the data.

Application Upgrade Overview

You must analyze and process code changes by comparing and evaluating three separate versions of the Microsoft Dynamics NAV database:

Version Description
Original version This is the baseline version of the solution that you want to upgrade, such as the original release of Microsoft Dynamics NAV 2013.
Modified version This is the version that you want to upgrade, such as a customer’s database with customizations and add-on solutions.
Target version This is the target of the merge process that you want to upgrade your application to, such as the standard version of the Microsoft Dynamics NAV 2016 database.

When you merge the application objects from these three versions, you can import the result into a new Microsoft Dynamics NAV 2016 database that then contains the upgraded application.

At the end of the process, you export the merged Microsoft Dynamics NAV 2016 objects from this database to a .fob file that you will use during the data upgrade.

The following list describes the main steps of upgrading the application code:

  • Prepare the application object text files.
  • Merge versions.
  • Resolve conflicts
  • Import and compile the merged application objects.
  • Export all objects.

To begin, create five folders on the computer and name them as follows:

In my example, the UpgradeDemo folder on the C drive contains five folders: ORIGINAL, MODIFIED, TARGET, DELTA, and RESULT. The DELTA and RESULT folders are empty. The ORIGINAL, MODIFIED, and TARGET folders contains one or more text files that contain application objects.

Preparing the Text Files

The Microsoft Dynamics NAV cmdlets take text files as input. You must prepare three sets of text files that contain application objects as describes in the list above. You can export application objects to text files from the development environment, or by running the ExportObjects command. The following list describes the main steps of preparing the text files that you must provide as input for the application merge process.
Upgrade2016-15

  • First, export all application objects from the original version, such as the original Microsoft Dynamics NAV 2013 R2 database. Do not export system tables. Name the file OldBaseVersion.txt, and then save the file in the ORIGINAL folder that you created earlier. For example, the Microsoft.Dynamics.Nav.Model.Tools.psd1 module includes a function, Export-NAVApplicationObject, that runs the ExportObjects command. This means that you can run a command such as the following:

Export-NAVApplicationObject –DatabaseServer MyServer –DatabaseName “Demo Database NAV (7-1)” –Path C:\Upgrade\ORIGINAL\OldBaseVersion.txt

  • Next, export all relevant application objects from the modified version, such as the customized Microsoft Dynamics NAV 2013 database. Do not export system tables. Name the file OldCustomVersion.txt, and then save the file in the MODIFIED folder that you created earlier.
  • Finally, export all application objects from the new base version, such as the original Microsoft Dynamics NAV 2016 database. Do not export system tables. Name the file NewBaseVersion.txt, and then save the file in the TARGET folder that you created earlier.

Optionally, you can use the Split-NAVApplicationObjectFile cmdlet to split each text file into separate text files for each application object. This can make it easier to keep track of the process. The end result at this stage is three folders with one or more text files that contain the three sets of application objects that you want to merge.

To compare two sets of application objects

  • Open the Microsoft Dynamics NAV 2016 Development Shell in administrator mode.
  • Navigate to the location of your folders by typing a command such as the following:

cd c:\UserData\UpgradeDemo  (in my case)

  • To run the cmdlet to compare all application objects in the ORIGINAL folder to the application objects in the MODIFIED folder, type the following command:

Compare-NAVApplicationObject -OriginalPath ‘C:\UserData\Upgrade Demo\Original’ -ModifiedPath ‘C:\UserData\Upgrade Demo\Modified’ -DeltaPath ‘C:\UserData\Upgrade Demo\Delta’

Upgrade2016-16

Upgrade2016-17
This generates a number of DELTA files that describe the difference between ORIGINAL and MODIFIED. You can open the DELTA files in text editors such as Notepad. The following example illustrates how a DELTA file identifies the difference between ORIGINAL and MODIFIED.
Upgrade2016-18
Let us understand how the changes are reported when we compare the objects.
Upgrade2016-19

Upgrade2016-20

You can apply those differences to TARGET by using the Update-NAVApplicationObject cmdlet.

To apply DELTA files to application objects

  • Open the Microsoft Dynamics NAV 2016 Development Shell in administrator mode.
  • Navigate to the location of your folders by typing a command such as the following:

cd c:\UserData\UpgradeDemo  (in my case)

In this example, the folder structure is the same as described above procedure. You can now run the cmdlet.

  • To run the cmdlet to apply the differences that are stored in the DELTA folder to the application objects in the TARGET folder, type the following command:

Update-NAVApplicationObject –DeltaPath ‘C:\UserData\Upgrade Demo\Delta’ -TargetPath ‘C:\UserData\Upgrade Demo\Target’ -ResultPath ‘C:\UserData\Upgrade Demo\Result’

Upgrade2016-21

Upgrade2016-22

This merges the difference between ORIGINAL and MODIFIED with the target solution and puts the resulting solution in the RESULT folder.

You can now import the merged objects into a Microsoft Dynamics NAV database.

Merging Versions

Alternatively to above process you can merge the three sets of application objects to create the application for the new database.

You can use the Merge-NAVApplicationObject cmdlet to merge the three sets of application objects.

The following example illustrates the type of command that you can run.

Merge-NAVApplicationObject -OriginalPath ‘C:\UserData\Upgrade Demo\Original’ -TargetPath ‘C:\UserData\Upgrade Demo\Target’ -ModifiedPath ‘C:\UserData\Upgrade Demo\Modified’ -ResultPath ‘C:\UserData\Upgrade Demo\Result’

Upgrade2016-23

Upgrade2016-24

Depending on the number of objects that you are merging and the number of differences found, this can take a few seconds, a few minutes, or longer.

The RESULT folder will contain a text file for each application object. The result of the merge is shown when the cmdlet completes, including a description of any application objects with conflicting code.
Upgrade2016-25

These conflicts are stored in .CONFLICT files in the RESULT folder. You can import all objects in the RESULT folder into the new Microsoft Dynamics NAV 2016 database, or you can analyze the conflicts before you import the objects.

Handling Conflicts

Depending on the application that you are upgrading, you can choose to analyze the conflicting code before you import the merged objects into the development environment.

The conflicts are shown in the merged text files but are also identified in .CONFLICT files in the RESULT folder. Subfolders then contain copies of the source files from the versions that have conflicting code.
Upgrade2016-26

You can analyze the conflicts in any tool, make the relevant changes, and then run the merge operation again. Alternatively, you can import the merged files into the development environment, and resolve the conflicts there.

Importing and Compiling Merged Objects

After you have completed the merge, import the new custom version into the new Microsoft Dynamics NAV 2016 database, and then compile all objects. You must resolve any compilation errors before you can continue.

The text files with the merged application objects include successfully merged code, and code that is partially merged. You can import the partially merged objects into the Microsoft Dynamics NAV 2016 development environment and resolve the conflicts there.

You can use Import-NAVApplicationObject, that runs the ImportObjects command.

This means that you can run a command such as the following:

Join-NAVApplicationObjectFile -Source ‘C:\UserData\Upgrade Demo\Result\*.txt’ -Destination ‘C:\UserData\Upgrade Demo\FinalMergedObjects\all-merged.txt’

Upgrade2016-27

First we will join the many text files into a single file using above command and as a result we get all-merged.txt file which we will import in database using below command.

Import-NAVApplicationObject –DatabaseServer MyServer –DatabaseName “My Upgraded App” –Path C:\Upgrade\all-merged.txt

Now we import objects obtained from previous step into an existing, empty database.
Upgrade2016-28
When you compile the objects, an error is thrown for each code conflict, and you can use the tools that are available in the development environment to resolve the conflicts.

You now have a new database with a fully upgraded application.

Exporting All Objects

Now, you must export all objects to an objects.fob file so that you can import them when performing the data upgrade. The export must include customized objects, upgraded reports, and all other Microsoft Dynamics NAV 2016 objects.

This completes the upgrade of the application code. Next, you must upgrade the data in the database.

However you are free to do object merge and upgrade as you do earlier, here I concentrated using Dynamics Navision 2016 Development Shell. As this is the new way also much faster than what we used to do earlier.

We can use this in identifying the Objects under different categories like Modified, having conflicts and New Objects which can help in estimating also and planning the action in advance.

I will come up with further step and more details in my upcoming posts.