Tag: MS

Development Tips, FixedLayout Control, Scope Property, Show Mandatory Property, UpdatePropagation Property

New Properties and Controls for Pages – in Navision 2015

Ashwini Tripathi

ShowMandatory Property

With Microsoft Dynamics NAV a new property called ShowMandatory has been introduced on page fields. The ShowMandatory property is set on page fields and marks the field with a red asterisk on the UI.

You can refer this in my earlier post Show Mandatory Property

UpdatePropagation Property

In Microsoft Dynamics NAV a new property called UpdatePropagation is added to page fields. The introduction of this property makes it possible to update a parent page from a subpage automatically without using the page Refresh button.

You can refer this in my earlier post UpdatePropagation Property

Scope Property

In Microsoft Dynamics NAV a new property called Scope is added to page actions. The introduction of this property specifies the scope of an action to be either page-specific, or specific to a repeater control.

You can refer this in my earlier post Defining Action Scope for Microsoft Dynamics NAV Pages in Navision 2015

FixedLayout Control

Microsoft Dynamics NAV now supports using the FixedLayout control on the Microsoft Dynamics NAV Web client.

You can refer this in my earlier post Using a FixedLayout Control

Development Tips

Using a FixedLayout Control

Ashwini Tripathi

Arrange Page Fields in Rows and Columns Using a FixedLayout Control

A FixedLayout group control is typically used to display statistical data.

Fields in a FixedLayout group control are displayed in a FastTab with a heading for each row and column.

Previous versions of Microsoft Dynamics NAV supported having a FixedLayout control directly under a ContentArea. In Microsoft Dynamics NAV 2013 and later versions of Microsoft Dynamics NAV, you must make sure that the FixedLayout control is nested in a Group control.

To arrange page fields in rows and columns

  1. Open the page in Page Designer.
  2. In Page Designer, insert a blank line where you want to locate the fields.
  3. Set the Type and SubType field to Group. In the Caption field, type the text that you want as the heading of the FastTab that contains the fields.
  4. On the next line, set the Type to Group and SubType field to FixedLayout.
  5. On the next line, set the Type and SubType field to Group. This defines the placeholder for the first column of fields. To add a heading to the column, type the text in the Caption field.
  6. On the next line, set the Type to Field. This defines the first field in the column. In the Caption field, type the text that you want to use as the heading for the row.
  7. Specify the data source for the field by doing one of the following:
    • If the data source is a field of the table that is associated with the page, then on the View menu, choose Field Menu, select the field, and then choose the OK button.
    • If the data source is a variable, then on the View menu, choose C/AL Globals or C/AL Locals, and then create the variable. After you create the variable, in the SourceExpr field in Page Designer, choose the AssistEdit button and select the variable.
  8. Repeat steps 6 and 7 to add the remaining fields in the first column.
  9. Repeat steps 5 through 8 to add group controls that define the remaining columns.

You do not need to specify captions for these fields. Only the captions of fields in the first column define the row headings.

To demonstrate above steps, find below screen shots which makes you understand the concept easily.

FixedLayout-1

We will create V1..V9 variable as decimal.

We will create R1..R3 variables as Text.

Next we will design and group the page as below:

FixedLayout-2

When we save and run the page we will get below output.

FixedLayout-3

You can use table fields or calculated variables.

You can nest the Layout for different views.

Development Tips, Functional Tips, Tip & Tricks

Using of Posting Groups

Ashwini Tripathi

Posting groups in Microsoft Dynamics NAV are very important part of configuration process. All G/L Entries depends of correctly configured posting groups.

We can find a lot of them: General Business and Product Posting Groups, VAT Business and Product Posting Groups, Customer and Vendor Posting Groups, Inventory Posting Groups…

Sometime, it is very confusing how it works, especially for unexperienced people.

You can find good explanation on same in this post by Totovic.

Nicely explained, you should see it here. I feel must to see this post for every Navision Consultants either fresher or experienced.

Development Tips, License, Multitenancy, Server

Uploading License to Tenant in Multitenant Environment

Ashwini Tripathi

When we deploy solution for Multitenant Environment ever Tenant will be required a Separate License. Depending upon the License agreement with Microsoft every Tenant will either share same License or individual License.

Assume we are implementing Distributor Management System. Where every Tenant is a different Business entity linked with the Parent Group.

In this case the Server & Network is provided by the Parent Group and other Business group will share information but they will use their specific License to use the system, doesn’t matter who pays for fee.

In any case we need to upload License to each database in Multitenancy.

Import-NAVServerLicense

Imports a license file into a Microsoft Dynamics NAV database.

Syntax

Parameter Set: __AllParameterSets

Import-NAVServerLicense [-ServerInstance] <String> [-Database <LicenseDatabase> ] [-Force] [-Tenant <TenantId> ] [-Confirm] [-WhatIf] [ <CommonParameters>]

Parameter Set: LicenseDataSet

Import-NAVServerLicense [-LicenseData] <Byte[]> [-Force] [-Confirm] [-WhatIf] [ <CommonParameters>]

Parameter Set: LicenseFileSet

Import-NAVServerLicense [-LicenseFile] <String> [-Force] [-Confirm] [-WhatIf] [ <CommonParameters>]

Detailed Description

Use the Import-NAVServerLicense cmdlet to import a license file into a Microsoft Dynamics NAV database. If the Microsoft Dynamics NAV Server account specified does not have access to the location where the license file is stored, an error results. Check the Event Log on the Microsoft Dynamics NAV Server computer to see what permissions are required.

After importing a new license, restart all Microsoft Dynamics NAV Server instances to activate the license for client users.

Parameters

-Database<LicenseDatabase>

Specifies the database into which to import the license file. The possible values are described in the following list:

Default = 0

Default; overrides the license file currently in use.

Master = 1

Forces the license file to be global.

NavDatabase = 2

Forces the license file to be local and stored in the Microsoft Dynamics NAV database that is used by the specified Microsoft Dynamics NAV Server instance.

Tenant = 3

Forces the license file to be local and stored in the Microsoft Dynamics NAV database that is used by the tenant that is specified in the Tenant parameter.

Aliases none
Required? false
Position? named
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? false

-Force

Forces the command to run without asking for user confirmation.

Aliases none
Required? false
Position? named
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? false

-LicenseData<Byte[]>

Specifies the content retrieved from the certificate file by using the Get-Content cmdlet. For more information, see the examples.

Aliases none
Required? true
Position? 2
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? false

-LicenseFile<String>

Aliases none
Required? true
Position? 2
Default Value none
Accept Pipeline Input? false
Accept Wildcard Characters? false

-ServerInstance<String>

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

Aliases none
Required? true
Position? 1
Default Value none
Accept Pipeline Input? True (ByValue, ByPropertyName)
Accept Wildcard Characters? false

-Tenant<TenantId>

Specifies the ID of the tenant in which you want to import the license, such as Tenant1. This parameter is required unless the specified service instance is not configured to run multiple tenants.

Aliases Id
Required? false
Position? named
Default Value none
Accept Pipeline Input? True (ByPropertyName)
Accept Wildcard Characters? false

-Confirm

Prompts you for confirmation before running the cmdlet.

Required? false
Position? named
Default Value false
Accept Pipeline Input? false
Accept Wildcard Characters? false

-WhatIf

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

Required? false
Position? named
Default Value false
Accept Pipeline Input? false
Accept Wildcard Characters? false

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable.

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

  • System.String

You can pipe a string that contains a Microsoft Dynamics NAV Server instance name to the cmdlet.

Outputs

The output type is the type of the objects that the cmdlet emits.

  • None

This cmdlet does not generate any output.

Examples

This example imports a license file that is named “fin.flf” from the current directory into the default database.

C:\PS>Import-NAVServerLicense DynamicsNAV80 -LicenseData ([Byte[]]$(Get-Content -Path “fin.flf” -Encoding Byte))

This example imports a license file that is named “fin.flf” from the current directory into the local Microsoft Dynamics NAV database that is used by the MyInstance Microsoft Dynamics NAV Server instance.

C:\PS>Import-NAVServerLicense MyInstance -LicenseData ([Byte[]]$(Get-Content -Path “fin.flf” -Encoding Byte)) -Database NavDatabase

This example imports a license file that is named “fin.flf” from the current directory into the master database.

C:\PS>Import-NAVServerLicense ‘MicrosoftDynamicsNavServer$MyInstance’ -LicenseData ([Byte[]]$(Get-Content -Path “fin.flf” -Encoding Byte)) -Database Master

This example imports a license file that is named “fin.flf” from the current directory into the database that is used by the specified tenant, Tenant1.

C:\PS>Import-NAVServerLicense ‘MicrosoftDynamicsNavServer$MyInstance’ -LicenseData ([Byte[]]$(Get-Content -Path “fin.flf” -Encoding Byte)) -Database Tenant –Tenant Tenant1

Let import the license to the Tenants which we created in our earlier post.

If you missed previous post you can find it here. Adding Tenants to multitenancy Environment

I have created the Folder and placed my Tenants License in this.

License-1

Set-ExecutionPolicy unrestricted -Force

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

Import-NAVServerLicense MTenantDemo -Tenant ‘Tenant-1’ -LicenseData ([Byte[]]$(Get-Content -Path “C:\User Data\NAV 2015\MultiTenantDemo\Tenant License\Tenant-1.flf” -Encoding Byte))

Import-NAVServerLicense MTenantDemo -Tenant ‘Tenant-2’ -LicenseData ([Byte[]]$(Get-Content -Path “C:\User Data\NAV 2015\MultiTenantDemo\Tenant License\Tenant-2.flf” -Encoding Byte))

Import-NAVServerLicense MTenantDemo -Tenant ‘Tenant-3’ -LicenseData ([Byte[]]$(Get-Content -Path “C:\User Data\NAV 2015\MultiTenantDemo\Tenant License\Tenant-3.flf” -Encoding Byte))

Import-NAVServerLicense MTenantDemo -Tenant ‘Tenant-4’ -LicenseData ([Byte[]]$(Get-Content -Path “C:\User Data\NAV 2015\MultiTenantDemo\Tenant License\Tenant-4.flf” -Encoding Byte))

Import-NAVServerLicense MTenantDemo -Tenant ‘Tenant-5’ -LicenseData ([Byte[]]$(Get-Content -Path “C:\User Data\NAV 2015\MultiTenantDemo\Tenant License\Tenant-5.flf” -Encoding Byte))

Copy the above Script and paste into the Microsoft Dynamics NAV 2015 Administration Shell.

License-2

Our License have now successfully uploaded to respective databases. As the message suggest we need to re-start the service so that new License comes into effect.

You can use above Script to Upload License to individual Tenants, License could be different or same depends how you discussed with Microsoft and purchased.

Development Tips, Multitenancy, Server

Adding Tenants to Multitenancy Environment

Ashwini Tripathi

In my previous post Creating the Multitenant Environment, We were done with the basic setup for MultiTenantDatabase NAV (8-0) Demo_App, the first Tenant MultiTenantDatabase NAV (8-0) Demo.

All other Tenants can now be created in similar fashion.

It is recommended to create a tenant template, and in turn use this template to make other tenants.

To create a template follow bellow steps:

  • Remove all users leaving only Administrator Account as Super Permission.
  • Remove any Transaction data specific to Company and other Setups
  • Take the backup of the database and keep it as template for other tenant’s database creation. Basically we will restore this database for each new tenant we need to add.

You can restore the database and mount it to the service one by one and rename the company name manually and do other modifications. But if you need to create 100 or 1000 tenants then this process will be quite time taking. To make this step easy we can use CSV file to store all necessary information and create a Shell Script to read information from CSV file and input as parameter to the commands to make this process fast and save with unnecessary effort.

Today I am going to discuss this, as a sample I have taken bare minimum information to get our task done. In real scenario many more things you may require but you can use this as a template and design your CSV and Script accordingly.

Let’s do the Preparation:

AddTenant-1
I have created the Folder structure as shown in above screen shot.

I will keep my CSV File in TenantInfo Folder.

AddTenant-2
I will store my Shell Script in ShellScript Folder.

AddTenant-3

$csv_NAVBuild = Import-Csv “C:\User Data\NAV 2015\MultiTenantDemo\TenantInfo\TenantDetails.csv”

foreach ($line in $csv_NAVBuild)
{
New-NAVDatabase “C:\User Data\NAV 2015\MultiTenantDemo\TenantBackup\TenantBussinessDataBackup.bak” -DatabaseServer $($line.DatabaseServer) -DataFilesDestinationPath “C:\User Data\NAV 2015\MultiTenantDemo\$($line.TenantID).mdf” -LogFilesDestinationPath “C:\User Data\NAV 2015\MultiTenantDemo\$($line.TenantID).ldf” -DatabaseName $($line.TenantID)
Mount-NAVTenant $($line.ServiceName) –Id $($line.TenantID) -DatabaseServer $($line.DatabaseServer) -DatabaseName $($line.TenantID) -OverwriteTenantIdInDatabase -DefaultTimeZone “$($line.DefaultTimeZone)”
Rename-NAVCompany $($line.ServiceName) -Tenant $($line.TenantID) -CompanyName “Tenant-1” -NewCompanyName $($line.TenantID) -Force
}

Save-NAVTenantConfiguration $($line.ServiceName)

I will place my tenant database template file in TenantBackup Folder prepared as per above discussed step. I will take the SQL backup and place the .bak file in this folder.

AddTenant-4
Creating and Mounting Tenants

It is possible to create PowerShell scripts that aids the setup of Tenants. Basically, what the script does, is:

  1. Calls a CSV file (Comma separated file) with settings for all the Tenants
  2. Restores and creates new Tenants based on the tenant template SQL backup
  3. Mounts the new database as a tenant on NAV Server Instance
  4. Renames the default company name to the specified company name
  5. Saves NAV tenant configurations of NAV Server Instance

Open the Microsoft Dynamics NAV 2015 Administrator Shell, Make sure you run it as Administrator. Copy and Paste your above created script.
AddTenant-5
On completion of execution of script, Verify your Tenants are Mounted to the Service using Microsoft Dynamics NAV 2015 Administration.
AddTenant-6
Verify using SQL Server Management your all database is created.

AddTenant-7

Verify that the database Files are created in Specified Folder in the Script.

AddTenant-8

Verify that you are able to login to each Tenant database in RTC.

AddTenant-9

Now you are done with Creating the Tenants and Mounting the same to the Navision Service using CSV file and Shell Script.

Shell Script had made your life so easy, think if manually you have to do it for 5000 tenants how much time will you take to get this task done.

Development Tips, Multitenancy, Security, Server

Creating the Multitenant Environment

Ashwini Tripathi

Before you start I will recommend to go through my earlier post on Multitenancy Concept & Overview here.

To start with the below steps you will require 2 Accounts preferably Domain Accounts:

  • An Account for running MS Dynamics Navision Service
  • An Administrator account for Database & RTC

How to setup these accounts and what permissions are required for these accounts I would recommend to go through my earlier post on Provisioning the Microsoft Dynamics NAV Server Account here.

Add both the above account to Navision Database with Super permission.

In order to create a multitenant environment (here for Demo Database NAV (8-0)), the following steps need to be done:

  • Take an SQL Backup of the running Demo Database NAV (8-0) database.

Multitenancy-1
Multitenancy-2
Multitenancy-3

  • Restore the newly made SQL Backup to a new database (MultiTenantDatabase NAV (8-0) Demo).

Multitenancy-4
Multitenancy-5
Multitenancy-6
Multitenancy-7

  • On the NAV Server, using the NAV 2015 Administration console, create a new NAV Server Instance named MTenantDemo

Multitenancy-8

  • Edit the instance and set the database to newly created MultiTenantDatabase NAV (8-0) Demo database, and set the instance to be started with NAV Service user.

Multitenancy-9
Multitenancy-10

  • Start MTenantDemo – and see if you can log in
  • Once you have confirmed the MultiTenantDatabase NAV (8-0) Demo database and MTenantDemo is running, split the database in an application part and a data part:
    • Open the NAV 2015 Administration Shell (or Windows PowerShell ISE and load the NavAdminTool)
    • Export the application part from the MultiTenantDatabase NAV (8-0) Demo database to a new database (MultiTenantDatabase NAV (8-0) Demo_App):

Export-NavApplication -DatabaseServer INDEL-AXT5283N1 -DatabaseName ‘MultiTenantDatabase NAV (8-0) Demo’ -DestinationDatabaseName ‘MultiTenantDatabase NAV (8-0) Demo_App’

  • Remove the application part from the MultiTenantDatabase NAV (8-0) Demo database:

Remove-NAVApplication -DatabaseServer INDEL-AXT5283N1 -DatabaseName ‘MultiTenantDatabase NAV (8-0) Demo’

Multitenancy-11
Multitenancy-12

  • Using the NAV 2015 Administration console, edit the MTenantDemo instance and set the database to newly created MultiTenantDatabase NAV (8-0) Demo_App database
    Multitenancy-13
  • And select Multitenant (set it to True):
  • Save and restart MTenantDemo

Multitenancy-14

  • Either through a Powershell commandlet in the NAV 2015 Administration Shell or by using the NAV 2015 Administration console mount a tenant named Tenant-1 on the MTenantDemo NAV Instance:

Multitenancy-15
Multitenancy-16
Multitenancy-17

  • Start NAV using the new tenant:

Multitenancy-18

  • In Companies rename the Company Name to Tenant-1
  • Take an SQL Backup using the MultiTenantDatabase NAV (8-0) Demo_App database, and call it “App_template.bak”
  • Take an SQL Backup using the MultiTenantDatabase NAV (8-0) Demo database, and call it “Tenant_template.bak”

You now have a template you can use when you want to create a application and a tenant.

Restore the Tenant Database as Tenant-2, Tenant-3, Tenant-4, Tenant-5 etc.

Mount the Tenant Databases to NAV Service MTenantDemo as per above steps.

Now you have One Application & 5 Business Data Tenants Mounted.

You will do development in Application Database MultiTenantDatabase NAV (8-0) Demo_App and then Sync your modifications to Business Data Databases named Tenant-1/2/3/4/5 etc.

To Sync your objects to Tenants you will use below script in NAV 2015 Administration Shell:

Set-ExecutionPolicy unrestricted –Force

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

Sync-NAVTenant MTenantDemo -Force -Tenant ‘MultiTenantDatabase NAV (8-0) Demo’

I will explain in more detail on Creating Tenants, Synchronizing Tenants and uploading License for Tenants in more details in my next upcoming post.

Development Tips, Multitenancy, Security, Server

Multitenancy Concept & Overview

Ashwini Tripathi

You will get step by step walkthrough on how to implement Multitenancy in my next upcoming post.

You can choose to migrate your Microsoft Dynamics NAV solution to a multitenant deployment architecture where you maintain a single application that is used by two or more companies that store their data in separate databases.

This can make maintenance of your solution easier if you support multiple customers with the same application functionality.

Tenants and Companies

When you upgrade your application and the data to Microsoft Dynamics NAV 2015, you have a database that has the same number of companies as you had before the upgrade.

This database is considered a tenant.

This does not mean that you have to turn your solution into a multitenant deployment. But it means that you can if you want to.

For example, your Microsoft Dynamics NAV deployment in the earlier version consisted of a database that has 20 companies.

In other words, you support 20 companies that all share the same application functionality.

Here we are considering, the companies are separate companies that have nothing to do with each other except that they are supported by you in one database.

In Microsoft Dynamics NAV 2015, you can choose to extract the application-wide tables into a separate database and keep the data for all 20 companies in the original database. This becomes a single-tenant business data database.

Then, you can choose to split the business data database into one for each company so that you run a truly multitenant environment.

The application is stored separately in the application database, and you maintain application functionality centrally.

Pros

When you modify the application, you make the changes available to one tenant at a time. As a result, if something goes wrong, all other tenants are not affected.

Compare this to earlier versions of Microsoft Dynamics NAV where a database could contain several companies. These companies could be related or not, but they would all use the same application and write to the same database. Also, when you modified the application, it would affect all companies immediately. So if something went wrong, all companies would be affected.

Cons

The email logging functionality in Microsoft Dynamics NAV requires the Microsoft Dynamics NAV Server service account to have access to the Exchange server. But in a multitenant deployment, this is not always possible.
Separating Application Data from Business Data
Microsoft Dynamics NAV 2015 separates tables that describe the application from the tables that contain business data. Depending on your deployment scenario, you can choose to store all Microsoft Dynamics NAV tables in one database, or you can export the application tables to a dedicated database.

In multitenant deployments, the application must be stored in a dedicated database.
Application Database versus Business Data Databases
The application database contains tables that describe your application.

This includes a description of the objects that your application consists of, and other data that is common to all tenants.

The data that users enter in your application is stored in the business data database because this data is specific to their company.

Optionally, you can create multiple business data databases, such as if you want to support your customers as tenants.

When you have exported the application tables to a separate database, you can no longer access the business database from the Microsoft Dynamics NAV Development Environment. This is because the metadata for the tables in the business database is stored in the application database and modified in that database.

For example, if you want to modify a report, you modify the report object in the application database. Then, when you deploy the updated application to your production environment, when a user accesses the report, they see the modified report.

Microsoft Dynamics NAV includes Windows PowerShell cmdlets that help you export application tables to a dedicated database, and other cmdlets to help you maintain a multitenant deployment.
Distribution of the System Tables in Each Database
The application tables are system tables that define the application. Other system tables remain in the business data database.

The following table describes which system tables are moved to the application database when you run the Export-NAVApplication cmdlet and which tables remain in the business data database.

Application database Business data database
Chart Access Control
Client Add-in Active Session
Client Add-in Resources Company
Debugger Breakpoint Device
Debugger Watch Document Service
Object Integration Page
Object Metadata Object Metadata Snapshot
Object Tracking Object Translation
Permission Page Data Personalization
Permission Set Printer Selection
Profile Record Link
Profile Metadata Report List Translation
Send-To Program Session Event
Server Instance User
Style Sheet User Default Style Sheet
Web Service User Metadata
User Personalization
User Property

Microsoft Dynamics NAV Windows PowerShell Cmdlets
Microsoft Dynamics NAV 2015 includes Windows PowerShell cmdlets for administration and for merging and modifying application object files.

The cmdlets are available in two Windows PowerShell modules: Microsoft Dynamics NAV 2015 Administration Shell and Microsoft Dynamics NAV 2015 Development Shell.

  • The Microsoft Dynamics NAV 2015 Administration Shell includes cmdlets for administering the Microsoft Dynamics NAV deployment, such as adding and configuring Microsoft Dynamics NAV server instances, databases, and users. The Microsoft Dynamics NAV 2015 Administration Shell is installed with the Microsoft Dynamics NAV Server.
  • The Microsoft Dynamics NAV 2015 Development Shell includes cmdlets for merging and modifying application object files. The Microsoft Dynamics NAV 2015 Development Shell is installed with the Microsoft Dynamics NAV Development Environment.

The cmdlets are implemented in Windows PowerShell 3.0, which is included with Windows Server 2012 and Windows 8.

Alternatively to running Microsoft Dynamics NAV 2015 Administration Shell and Microsoft Dynamics NAV 2015 Development Shell from where they are installed, you can import the relevant module into the Windows PowerShell Integrated Scripting Environment (ISE).
Starting a Microsoft Dynamics NAV 2015 Administration Shell Session
You must run the Microsoft Dynamics NAV 2015 Administration Shell as an Administrator. On the computer that is running Microsoft Dynamics NAV Server, choose Start, in the Search box, type Microsoft Dynamics NAV 2015 Administration Shell, right-click the related link, and then choose Run as Administrator.

To run the cmdlets in the Microsoft Dynamics NAV 2015 Administration Shell, you must be a member of the local Administrator group on the computer where the Microsoft Dynamics NAV 2015 Administration Shell is installed.
Starting a Microsoft Dynamics NAV 2015 Development Shell Session
On the computer that is running Microsoft Dynamics NAV Development Environment, choose Start, in the Search box, type Microsoft Dynamics NAV 2015 Development Shell, and then choose the related link.
Creating Tenants from Companies
If your Microsoft Dynamics NAV solution includes multiple companies in one database, you can choose to migrate to multitenancy where the Microsoft Dynamics NAV companies become tenants of your application database.
Companies in Multitenancy Deployments
In Microsoft Dynamics NAV 2015, you can maintain multiple companies in each business database, and you can choose to export all or some of the companies to separate tenant databases.

A tenant database can contain one or more companies. Therefore, you can deploy the number of tenants that suits your solution. This varies between solutions.

You may prefer to have a dedicated tenant database for each legal entity in your deployment. Or you may prefer to have a tenant database for each group of legal entities where each legal entity is a Microsoft Dynamics NAV company in that tenant database.

In this example, we assume that your current deployment consists of one or more databases with multiple companies in each database.

If you migrate your solution as suggested by the examples in this section, you will have a deployment where your application is stored in a dedicated application database.

Each company in the original database will be moved into a dedicated business database, which is mounted as tenants on a Microsoft Dynamics NAV Server instance.

Before you can create tenants from Microsoft Dynamics NAV companies, you must have exported the application to a dedicated application database.

Running a Windows PowerShell Script to Create Tenants from Companies

Microsoft Dynamics NAV includes a sample Windows PowerShell script and a sample SQL script, HowTo-MoveCompanyToTenant.ps1 and HowTo-MoveCompanyToTenant.sql.

These scripts illustrate how you can move company-specific data to new business databases and mount them as new tenants.

You can modify the scripts and then run them in the Microsoft Dynamics NAV 2015 Administration Shell.

Sample Windows PowerShell Scripts for Microsoft Dynamics NAV

The Microsoft Dynamics NAV product media includes a folder that has examples of how you can use the Microsoft Dynamics NAV Windows PowerShell cmdlets to upgrade, deploy, and maintain your Microsoft Dynamics NAV solution.

Microsoft Dynamics NAV Sample Scripts

The sample Windows PowerShell scripts are available in the WindowsPowerShellScripts folder on the Microsoft Dynamics NAV product media.

You can copy the folder to your computer and use the scripts as a starting point for managing your own solution.

Depending on your solution, you can then add the sample Windows PowerShell module into the Microsoft Dynamics NAV 2015 Administration Shell or Microsoft Dynamics NAV 2015 Development Shell and then run the scripts.

The current version of Microsoft Dynamics NAV includes the following sample modules.

Parent folder Module name Description
ApplicationMergeUtilities Contains sample scripts that illustrate different ways of using Windows PowerShell cmdlets to merge and modify application objects. The sample scripts use the Microsoft.Dynamics.Nav.Model.Tools.psd1 module, which you must import first.
Cloud HowTo Contains sample scripts and files for deploying a fully functional end-to-end Microsoft Dynamics NAV solution on Microsoft Azure virtual machines.
NAVAdministration Illustrates how Windows PowerShell can be used to automate the provisioning and management of Microsoft Dynamics NAV installations on a virtual machine (VM).
NAVRemoteAdministration Illustrates how Windows PowerShell can be used to automate the provisioning and management of Microsoft Dynamics NAV installations on Azure VMs.
NAVRemoteAdministrationSamples Provides examples of a fully automated deployment of Microsoft Dynamics NAV on Azure.
Multitenancy NAVMultitenancySamples Illustrates how you can automate the steps that are required to migrate to a multitenant deployment architecture..
Upgrade NAVUpgradeModule Illustrates how you can automate the upgrade of a Microsoft Dynamics NAV 2013 database to Microsoft Dynamics NAV 2015..

Running the Sample Scripts

The sample scripts are designed to be run from the context of the Windows PowerShell module that is part of each collection of sample scripts.

For example, to run the scripts for upgrading to Microsoft Dynamics NAV, import the NAVUpgradeSamples.psm1 module into the Microsoft Dynamics NAV 2015 Administration Shell.

Similarly, to run the sample scripts for merging application objects, open the HowTo-Start-Import-NAV-Module.ps1 script in the Windows PowerShell IDE, navigate the command prompt to the folder where you placed the samples, and then run the script.

The HowTo-MoveCompanyToTenant.ps1 script uses Microsoft Dynamics NAV cmdlets and SQL commands to copy data from a Microsoft Dynamics NAV company in a business database to a new tenant database.

It mounts the tenant database on a Microsoft Dynamics NAV Server instance, and then deletes the data and the company from the original database.

You can adapt the scripts to your solution and rename them so that they do not start with “HowTo-“. Then, you can run the MoveCompanyToTenant.ps1 script in Windows PowerShell with the relevant parameters as shown in the following example.

MoveCompanyToTenant -ServerInstance ‘DynamicsNAV’ -FromDatabase ‘Demo Database NAV (8-0)’ -CompanyName ‘CRONUS International Ltd.’ -OldTenantName default -NewTenantName CRONUS1 -ServiceAccount ‘NT AUTHORITY\NETWORK SERVICE’ -DatabaseServer MySQLServer

In the example, the script will move the CRONUS International Ltd. company from the Demo Database NAV (8-0) database to a new tenant database, CRONUS1.

The script also adds an account for the Microsoft Dynamics NAV Server instance in the new database and removes the company from the original database.

All information that is stored in shared tables, but which is specific to the company, is copied to the new tenant. The following table describes the parameters and the parameter values in the example.

Parameter Value Description
ServerInstance DynamicsNAV The name of the Microsoft Dynamics NAV Server instance.
FromDatabase Demo Database NAV (8-0) The name of the business database that the company must be moved from.

This database must have been upgraded to Microsoft Dynamics NAV 2015, and you must have exported the application tables to an application database..
CompanyName CRONUS International Ltd. The name of the company that you want to move to a new tenant database.
OldTenantName default The name of the tenant in the database that you are exporting from.

In many cases, you are working with a single-tenant business database that contains multiple companies. If you did not specify another ID for the tenant when you created the tenant database, specify default. If you have added more tenants to the solution, you must specify the tenant name.
NewTenantName CRONUS1 The name of the new tenant database.

In the example, this is based on the name of the original company. But because this value will identify the tenant, the value must be unique.

For example, if the name of the company is Spotsmeyer’s Furnishings, the name of the new tenant database can be SPOTSMEYERS. Alternatively, you can use a naming scheme of your choice, such as TENANT1, TENANT2, and so on.
ServiceAccount NT AUTHORITY\NETWORK SERVICE The account that Microsoft Dynamics NAV Server uses to access SQL Server. In a default deployment, this is the NT AUTHORITY\NETWORK SERVICE account.
DatabaseServer MySQLServer The name of the database tier server where the current business database is located. The new tenant database for the exported company will be created on the same server.
RemoveCompanyWhenMoved Specifies if each company must be deleted in the original tenant database when it is created in the new tenant database. The default value is true. Therefore, you must set the parameter to false if you do not want the script to delete the companies.

The script uses the sqlcmd utility to create the database, assign database permissions to the service account, and move the data. This utility is part of SQL Server. The script creates four temporary tables in the original database to handle user-specific data, company-specific data, tables that will not be moved, and user IDs. You must modify the MoveCompanyToTenant.sql script to change the default values.

After this step, you have an application database and a business data database for each company in the original database. The company-specific business data databases are tenants, and your solution is multitenant.

If you want to move back to storing application tables and business data in a single database, you can use the Microsoft Dynamics NAV Windows PowerShell cmdlets to merge the databases.

Merging an Application Database with a Tenant Database

In Microsoft Dynamics NAV 2015, you can separate the application data into a dedicated application database.

However, if you want revert the separation of application data and business data, you can merge the two databases.

Microsoft Dynamics NAV includes Windows PowerShell cmdlets that can help you merge the databases, and you can use the cmdlets in a script that runs in Windows PowerShell.

When you merge the two databases, you migrate to single-tenancy.

In a multitenant deployment, you can merge a single tenant with the application tables and leave the remaining tenants in the existing deployment.

In that case, you add the application tables to the relevant tenant database and leave the original application database unchanged. If you have separated the application data into a dedicated database but you have not used it in a multitenant deployment, you can also use the cmdlet and a sample script to merge the two databases.

Migrating to Single-Tenancy

Before you can start the merge of the two databases, you must collect the relevant information. If you write a script, you must be able to provide values for the parameters that the Windows PowerShell cmdlets require. The migration includes the following main steps:

  • Gather the prerequisite information.
  • Merge the two databases.
    • Migrate from multitenancy to single-tenancy.
    • Migrate from two databases to one database.
  • Change the client connection endpoints.
  • Post-migration clean-up.

Each step is explained in more details in the following sections.

Step 1: Gather the Prerequisite Information

The sample scripts require the following information:

  • Identify the name of the Microsoft Dynamics NAV Server server instance that the tenant database is connected to. This value is used as the input for the serverInstance variable in the sample scripts.
  • Identify the name of the application database that the tenant database is connected to. This value is used as the input for the appDatabaseName variable in the sample scripts.
  • Identify the name of the tenant database that you want to merge with the application database. This value is used as the input for the tenantDatabaseName variable in the sample scripts.
  • If you are migrating from a multitenant deployment, identify the tenant ID of the tenant database. This value is used as the input for the tenantId variable in the sample scripts.
  • Make sure that you have the server role called securityadmin in the relevant instance of SQL Server.
  • Make sure that you have the db_owner role for both the application database and the tenant database.
  • Make sure that you are a member of the Administrators security group on the server that runs the Microsoft Dynamics NAV Server instance and where the Windows PowerShell scripts run.

Step 2: Merge the Two Databases

To merge the application database and the tenant database, you must run the Microsoft Dynamics NAV 2015 Administration Shell as an administrator.

Depending on your scenario, you can copy one of the sample scripts and update the variables based on the prerequisite information that you gathered in the previous section.

This section includes two sample scripts that you can base your own script on.

The first script merges an application database and a tenant database that is currently a tenant in a multitenant deployment.

# PURPOSE: This sample script migrates a tenant in a multitenant deployment to a single-tenant deployment.

### Modify these variables with values appropriate to your environment ###

$serverInstance = “”

$appDatabaseName = “”

$tenantDatabaseName = “”

$tenantId = “”

### You should not need to modify any variables below this line ###

# Save the current multitenancy configuration.

Save-NAVTenantConfiguration -ServerInstance $serverInstance

# Create a new server instance for the requested tenant.

New-NAVServerInstance -ServerInstance $tenantId -ManagementServicesPort 7045

# Dismount the requested tenant from the multitenant server instance.

Dismount-NAVTenant -ServerInstance $serverInstance -Tenant $tenantId

# Save the updated multitenancy configuration.

Save-NAVTenantConfiguration -ServerInstance $serverInstance

# Remove any application tables from the tenant database if these were not already removed.

Remove-NAVApplication -DatabaseName $tenantDatabaseName

# Copy the application tables from the current application database to the tenant database.

Export-NAVApplication -DatabaseName $appDatabaseName -ApplicationDatabaseName $tenantDatabaseName

# Reconfigure the CustomSettings.config file for the new server instance to use the tenant database.

Set-NAVServerConfiguration -ServerInstance $tenantId -KeyName DatabaseName -KeyValue $tenantDatabaseName -WarningAction Ignore

# Reconfigure the CustomSettings.config to use single-tenant mode.

# Set-NAVServerConfiguration -ServerInstance $serverInstance -KeyName Multitenant -KeyValue false -WarningAction Ignore

# Start the new server instance if it is not running.

Set-NAVServerInstance -ServerInstance $tenantId -Start

# Dismount all tenants on the new server instance that are not using the current tenant database.

Get-NAVTenant -ServerInstance $tenantId | where {$_.Database -ne $tenantDatabaseName} | foreach { Dismount-NAVTenant -ServerInstance $tenantId -Tenant $_.Id }

# Save the new single-tenant configuration.

Save-NAVTenantConfiguration -ServerInstance $tenantId

Write-Host “Operation complete.” -foregroundcolor cyan

The second script merges an application database and a tenant database that has not been mounted on a Microsoft Dynamics NAV Server instance as a tenant.

# PURPOSE: This sample script merges an application database and a tenant database that are not part of a multitenant deployment.

### Modify these variables with values appropriate to your environment ###

$serverInstance = “”

$appDatabaseName = “”

$tenantDatabaseName = “”

### You should not need to modify any variables below this line ###

# Save the current server configuration.

Save-NAVTenantConfiguration -ServerInstance $serverInstance

# Stop the server instance if it is running.

Set-NAVServerInstance -ServerInstance $serverInstance -Stop

# Remove any application tables from the tenant database if these have not already been removed.

Remove-NAVApplication -DatabaseName $tenantDatabaseName

# Copy the application tables from the application database to the tenant database.

Export-NAVApplication -DatabaseName $appDatabaseName -ApplicationDatabaseName $tenantDatabaseName

# Reconfigure the CustomSettings.config to use the tenant database.

Set-NAVServerConfiguration -ServerInstance $serverInstance -KeyName DatabaseName -KeyValue $tenantDatabaseName -WarningAction Ignore

# Reconfigure the CustomSettings.config to use single-tenant mode

# Set-NAVServerConfiguration -ServerInstance $serverInstance -KeyName Multitenant -KeyValue false -WarningAction Ignore

# Start the server instance.

Set-NAVServerInstance -ServerInstance $serverInstance -Start

# Dismount all tenants that are not using the current tenant database.

Get-NAVTenant -ServerInstance $serverInstance | where {$_.Database -ne $tenantDatabaseName} | foreach { Dismount-NAVTenant -ServerInstance $serverInstance -Tenant $_.Id }

# Save the current single-tenant configuration.

Save-NAVTenantConfiguration -ServerInstance $serverInstance

Write-Host “Operation complete.” -foregroundcolor cyan

In both scenarios, you can create scripts that are based on these samples, provide value for the variables, run the script, and the review the output to make sure that you achieved the expected result.

Step 3: Change the Client Connection Endpoints

The first sample script creates a new Microsoft Dynamics NAV Server instance with a name that is based on the specified tenant ID.

The script only enables the management services endpoint in the Microsoft Dynamics NAV Server configuration settings.

You must also configure the server instance to enable client services, SOAP web services, and OData web services.

You must send the configuration changes to the users who access the reconfigured tenant. Otherwise, they are not able to connect to the database.

The second sample script illustrates a simpler scenario where you move the application tables to the only database that accessed the application database. As a result, all client endpoints continue as they did before, and you do not have to update any clients.

Step 4: Post-Migration Clean-Up

In the first scenario where you move a tenant from multitenancy to single-tenancy, you must update the Microsoft Dynamics NAV Server account and database permissions.

Also, any tenant-specific files that have been saved to the original server location must be moved to the relevant location on the new server.

You will get step by step walkthrough on how to implement Multitenancy in my next upcoming post.

Database Functions, Development Tips

Database Function

Ashwini Tripathi

EXPORTDATA Function (Database)

Exports data from the Microsoft Dynamics NAV database to a file. The data is not deleted from the database.

Syntax

[Ok:=] EXPORTDATA(ShowDialog, FileName[, IncludeApplicationData][, IncludeGlobalData][, CompanyRecord])

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that the data must be exported to. The file must have the .navdata extension.

IncludeApplication

Type: Variable

Specifies if you want to export the application objects.

Create a variable of type Boolean to specify this parameter.

IncludeApplicationData

Type: Variable

Specifies if you want to export the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

IncludeGlobalData

Type: Variable

Specifies if you want to export global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies that must be imported.

You can find the usage of this function in Std. Page Object 9901 [Export Data]
DatabaseFunction-1

IMPORTDATA Function (Database)

Imports data from a file that has been exported from a Microsoft Dynamics NAV database.

Syntax

[Ok:=] IMPORTDATA(ShowDialog, FileName[, IncludeApplicationData][, IncludeGlobalData][, CompanyRecord])

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that must be imported. The file must have been exported from a Microsoft Dynamics NAV database.

IncludeApplicationData

Type: Variable

Specifies if you want to import the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

IncludeGlobalData

Type: Variable

Specifies if you want to import global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies that must be imported.

You can find the usage of this function in Std. Page Object 9900 [Import Data]
DatabaseFunction-2

DATAFILEINFORMATION Function (Database)

Specifies data from a file that has been exported from a Microsoft Dynamics NAV database.

Syntax

[Ok:=] DATAFILEINFORMATION(ShowDialog, FileName, Description, HasApplication, HasApplicationData, HasGlobalData, tenantId, exportDate, CompanyRecord)

Parameters

ShowDialog

Type: Boolean

Specifies if you want to display a dialog box where the user can confirm the action.

FileName

Type: Text

Specifies the name and location of the file that you want to read information from. The file must have been exported from a Microsoft Dynamics NAV database.

HasApplication

Type: Variable

Specifies if the file contains application objects.

Create a variable of type Boolean to specify this parameter.

HasApplicationData

Type: Variable

Specifies if the file contains the data that defines the application in the database. This includes the permissions, permission sets, profiles, and style sheets.

Create a variable of type Boolean to specify this parameter.

HasGlobalData

Type: Variable

Specifies if the file contains global, non-company specific data.

Create a variable of type Boolean to specify this parameter.

tenantId

Type: Variable

Specifies the tenant ID of the database that the data was exported from.

Create a variable of type Text to specify this parameter.

exportDate

Type: Variable

Specifies the date and time when the data was exported.

Create a variable of type DateTime to specify this parameter.

CompanyRecord

Type: Record

Specifies the company or companies in the file.

You can find the usage of this function in Std. Page Object 9900 [Import Data]
DatabaseFunction-3

Development Tips, Excel, Office Integration, Report, Server

Viewing Page Data in Excel Using PowerPivot (OData)

Ashwini Tripathi

Here I will discuss how you can use OData to expose a Microsoft Dynamics NAV 2015 page as a web service and then analyse the page data using Microsoft PowerPivot for Excel 2013.

With OData and PowerPivot, you gain access to a powerful set of tools and technologies for data exchange and analysis.

This walkthrough illustrates the following tasks:

  • Publishing a Microsoft Dynamics NAV page as a web service.
  • Verifying web service availability from a browser.
  • Using the PowerPivot add-in for Excel to import the table data as a new worksheet.
  • This procedure also includes optional instructions about how to use a web service access key.
  • Creating a PivotTable from the worksheet, selecting relevant fields, and then organizing and formatting the data to highlight strategic data.

Optional:

If you want to use a web service access key to authenticate access to the web service, Microsoft Dynamics NAV must meet the following requirements:

The Microsoft Dynamics NAV Server is configured to authenticate users by using the NavUserPassword credential type.

There is a Microsoft Dynamics NAV user account that has a web service access key.

You can find more details in my earlier post here

Publishing a Page as a Web Service

You can publish a web service by using the Microsoft Dynamics NAV Web client or the Microsoft Dynamics NAV Windows client.

To register and publish a page as a web service

  • Open the RoleTailored client and connect to the CRONUS International Ltd. company.
  • In the Search box, enter Web Services, and then choose the related link.
  • In the Web Services page, choose New.
  • In the Object Type column, select Page. In the Object ID column, enter 21, and in the Service Name column, enter Customer.

This exposes the Customer Card page as an OData web service.

  • Select the check box in the Published column.

Choose the OK button to close the New – Web Services page.

PowerPivot-1
Verifying the Web Service’s Availability

Security Note

After publishing a web service, verify that the port that web service applications will use to connect to your web service is open. The default port for OData web services is 7048. You can configure this value by using the Microsoft Dynamics NAV Server Administration Tool.

To verify availability of a Microsoft Dynamics NAV web service

Start Windows Internet Explorer.

In the Address field, enter a URI using the following format: http://Server : WebServicePort/ServerInstance/OData/

Server is the name of the computer that is running Microsoft Dynamics NAV Server.

WebServicePort is the port that OData is running on. The default port is 7048.

ServiceInstance is the name of the Microsoft Dynamics NAV Server instance for your solution. The default name is DynamicsNAV80.

For example, if the Microsoft Dynamics NAV Server is running on the computer that you are working on, you can use: http://localhost:7048/DynamicsNAV80/OData/

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

The browser should now show the web service that you have published, as shown in the following illustration.

PowerPivot-2
Note

If the browser cannot find the web service, it may indicate that the specified Microsoft Dynamics NAV Server instance is not running.

Make Sure Enable OData Services is checked.
PowerPivot-3
Importing Microsoft Dynamics NAV Data into Excel

In the following procedures, you use PowerPivot to import Microsoft Dynamics NAV data into Excel. If you will be using a web service access key for authentication, only perform the second procedure; otherwise, only perform the first procedure.

To import Microsoft Dynamics NAV data into Excel

Start Microsoft Excel.

In Excel, on the PowerPivot tab, choose Manage.

PowerPivot-4
This opens the PowerPivot for Excel window.
PowerPivot-5
In PowerPivot, on the Home tab, choose Get External Data, choose From Data Service, and then choose From OData Data Feed.

The Table Import Wizard opens.

PowerPivot-6
If your Microsoft Dynamics NAV implementation requires that you use a web service access key, you must specify the NavUserPassword credentials as described in the following steps:

In the Advanced dialog box, in the Security section, set the Integrated Security field to Basic. If your OData is configured to use SSL, then set the field to SSPL.

In the Password field, type the web service access key.

In the UserID field, type the user name for the Microsoft Dynamics NAV user account. For this walkthrough, use NavTest.

In the Source section, in the Service Document URL field, type the URL for the OData web service that you verified in the previous procedure, for example, http://localhost:7048/DynamicsNAV80/OData/.

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

Choose the OK button to return to the Table Import Wizard.

In the Connect to a Data Feed page, in the Data Feed Url field, enter the OData URI that you verified in the previous procedure.
PowerPivot-7

Choose the Next button.

Important: The URI must end with a slash (/) as shown in the example.

Verify that Customer appears in the Source Table column.

Select the check box next to the Customer web service, and then choose Finish.
PowerPivot-8
After you see the Success message, choose the Close button.
PowerPivot-9

The data from the Customer OData web service displays, and you can use the data to build pivot-based views in the Excel workbook.

Creating a PivotTable Containing Key Microsoft Dynamics NAV Data

In this procedure, you use the Excel workbook with data from the Customer web service to create a PivotTable from the worksheet. You select relevant fields and then organize and format the data to highlight strategic data. Building a pivot table is a way to select and arrange data so as to highlight and focus on key elements.

To create a PivotTable

In Excel, select the cell where you want the PivotTable located.

In the ribbon, choose the Insert tab, and then in the Tables group, choose PivotTable.

In the Create PivotTable dialog box, select Use an external data source, and then choose the Choose Connection button.

In the Existing Connections dialog box, on the Connections tab, under Connections in this Workbook, choose the data feed for your OData web service, and then choose the Open button.
PowerPivot-10

Choose the OK button to add the PivotTable to the Excel worksheet.

The PowerPivot Field pane on the right side includes a list of fields from the Customer web service that where imported from PowerPivot.

In the PowerPivot Field List pane, choose Location_Code.

Tip

To quickly find a field in the field list, type part or all of the field name in the Search text box that is above the list of fields, and then press Enter to highlight the first field that contains the text. You can then choose the right arrow to proceed to the next field, and so on.

Select the Balance_LCY field.

Select the Name field.

You can now see the data in the body of the worksheet, as shown in the following illustration.

The PivotTable shows customers by location and individual customer balances, and also adds the balances by location. To make the information more readable, you can update the headings on the PivotTable.
PowerPivot-11

Select the cell that has the heading Sum of Balance_LCY, and then, in the formula field, type Balance.

Select the cell that has the heading Row Labels, and then in the formula field, type Customers by location.

Select the empty cell that is below the Customers by location cell, and then, in the formula field, type Location not specified.

The above illustration shows how the worksheet looks after you make these changes.

Next Steps

Now that you have created your PivotTable, you can continue to enhance the data to make it more useful and readable. You can:

Add a column to the data that shows average balance by region.

Enhance data presentation with a graph.

Post the data in a Microsoft SharePoint environment with live data from Microsoft Dynamics NAV 2015.