Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    PowerBI.com and Microsoft Dynamics NAV 2015

    • 6 Comments

    Power BI just got even more attractive – Microsoft Dynamics NAV 2015 is ready to take on all the benefits!

    At Convergence 2015, Satya Nadella, CEO of Microsoft, announced that the PowerBI.com preview would be released worldwide. This is an exciting opportunity for our Microsoft Dynamics NAV users around the world to leverage their existing technology to get more insight from the data in their Dynamics NAV application.

    Not only does PowerBI.com come  at a new attractive pricepoint; it also provides a range of very power full business insight and collaboration tools. You can learn more about PowerBI capabilities here.

    In this blog post, we walk you through the steps needed to expose data from Microsoft Dynamics NAV to PowerBI.com to enable the building of a Dashboard like the one shown below. 

    We will not go into details on how to set up the Dashboard – If you need information on that please go here for information on designing Dashboards in PowerBI.com.

    How to upload refreshable Microsoft Dynamics NAV data to PowerBI.com today

    During Convergence 2015, the Dynamics NAV team demonstrated the integration of data from Microsoft Dynamics NAV with visualizations in PowerBI.com. With this blogpost, we will walk through the steps needed to expose refreshable data in PowerBI.com.

    For the data to be refreshable from PowerBI.com, the instance of Microsoft Dynamics NAV that is used for this must be configured to use Username/Password authentication.

    First a dataset from Microsoft Dynamics NAV is needed. Typically we recommend using a query object to do this. Without going into details on this subject, a query object provides a performant way of reading even large datasets in Dynamics NAV.

    For this blog post, we created a simple query called CustomerAnalysis that looks like this:

    The query is saved and compiled with the object ID 50000. Once this is done, the query must be exposed as a web service for the outside world to be able to access the data. This is done in the Web Services page:

    Once this is done the query can be consumed outside Dynamics NAV using Excel.

    In Excel, start by adding a data source:

    Since our query is exposed as an OData web service, choose OData Data Feed in Excel. This brings up the Data Connection Wizard that will help us configure our data source for the Excel worksheet:

    For the location, specify the OData URL from the Web Services page in Dynamics NAV for this web service. You must specify the user name and password so that we can connect to the data in Dynamics NAV. When we click Next, we see the dataset in the web service as configured in Dynamics NAV:

    Click Next to get to the configuration of the data connection file:

    For the data to be refreshable from PowerBI.com, select the Save password in file field. This means that every time the data gets refreshed from PowerBI.com, the connection between the dashboard and reports will be made in the context of the user that created the spreadsheet. This doesn’t change the fact that all users of the spreadsheet must be licensed users of Dynamics NAV but rather that the data exposed in the spreadsheet will be available to everybody that is able to connect to the dashboard in PowerBI.com. In turn, this means that Dynamics NAV security does not apply to what is in the spreadsheet at this point. Everyone with access to the spreadsheet have access to the data that is in the sheet.

    At this point we're ready to import data into Excel. 

     

    To be able to upload this data, choose to create a Power View Report and click OK. We're now ready to design a Power View report in Excel:

    We create a basic report with three clustered column charts. One with Sales_LCY per Country/Region code, one with Sales_LCY per Salesperson_code and one with Sales_LCY per Customer_Posting_Group.

    Save the spreadsheet with the name CustomerAnalysis to your local computer. We're now ready to upload the report to PowerBI.com:

    Open PowerBI.com in your browser. Once logged in, you can import the spreadsheet by clicking Get Data.

    Choose Excel Workbook, and then choose Connect. Locate the saved workbook and then choose Connect to upload your workbook. Once uploaded, you have access to your dataset and report:

    When you right-click the data source, you have access to refresh the data in the report and dashboard, provided that you are the owner of the data. 

    If you want to, you can created a dashboard based on the data and visualizations that you first created in the spreadsheet. For more information on how create new visualizations, see this link.

    If you would like to see more information on Dynamics NAV and PowerBI.com, leave a message below, or you can go and sign up for the Power BI preview at https://www.powerbi.com/

     

    Best regards,

    Claus Busk Andersen from the Dynamics NAV team

  • Microsoft Dynamics NAV Team Blog

    Culture Settings on NAV Web Services

    • 0 Comments

    We live in a world of global interaction, and this is reflected in the software that we use at work and at home. As expected this culture differences brings also more complexity and one more extra care for our development side. For instance, a Germany company that is now expanding its business to USA do need to take care of the cultural differences present on our system as date formats, number formats, and so on. Since in Germany we do for instance use the date as DD/MM/YY and in USA MM/DD/YY, we do have an issue when integrating those two systems.

    So for such cases, in Microsoft Dynamics NAV 2013, Microsoft Dynamics NAV 2013 R2, and Microsoft Dynamics NAV 2015, you can set up the cultural settings so that our Microsoft Dynamics NAV system understands how to “translate” culture-specific data when exchanging data between the two countries.

    Here below I show an example of how to configure and use Microsoft Dynamics NAV web services on different cultural environments (as I explained before I did this using the example of Germany and USA culture settings).

    First lets discuss the necessary setup in Microsoft Dynamics NAV:

    1. In the CustomSettings.config file for the relevant Microsoft Dynamics NAV Server instance, add the following key:
      <add key="ServicesCultureDefaultUserPersonalization" value="true"/>
    2. Make sure that the server where our middletier (the Microsoft Dynamics NAV Server instance) is running has the right regional settings.  

      Please do use the standard format Additional settings such as Decimal, and so on. 
    3. Finally, in the Microsoft Dynamics NAV client, on the Personalization Card, set the relevant language for the Microsoft Dynamics NAV Server user  - in this example Language ID 1031 for German.

    For more information, see the Web Services and Regional Settings section here: https://msdn.microsoft.com/en-us/library/dd355398%28v=nav.80%29.aspx .

    This finishes the setup part. Now we need to be sure that our VB application is using the correct code to “translate” the input date into the correct format when using Microsoft Dynamics NAV web services. This will indicates to our Microsoft Dynamics NAV Server how we do want to insert the date in our NAV database.

    For this example I will take the Date and Decimal from our C# application project and convert them to pass the Data to Web Services that will finally “insert” it into our NAV System.
    As for my setup in Microsoft Dynamics NAV, for this example I'm using the German language/cultural settings. In this example, I create a table and a page called WebServicesCultureTable and WebServicesCulturePage.

     

    The page displays the following fields from the table:

    Actually we will only use now the CultureCode ( the primary key), CultureDate that we will use to set the Date, and finally the CultureDecimal that we will use to check if the decimals are been passed correctly.

    In the Visual Studio, in my C# project, I have created an application that can be used to insert the desired values for test.
    So there I will use my web services to “pass” to Microsoft Dynamics NAV the date 11-08-15 (with the format) and the decimal 33.73M (we need this M so that C# understands that this is a decimal number). This class refers to a button in my C# application, but it resumes the necessary setting up for the Culture format that we need to use in order to our Web Server to understand correct the formats.

            private void button3_Click(object sender, EventArgs e)

            {

                NavOData.NAV nav = new NavOData.NAV(new Uri("http://localhost:7148/DynamicsNAV71/OData/Company('CRONUS%20AG')"));

                //Here define the companies, if need set a new company

                nav.Credentials = CredentialCache.DefaultNetworkCredentials;

     

                //Cultural Setting to Use on Date/Decimal

                System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("de-DE");

                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("de-DE");

     

                //Converting Cultural Date

                String TESTDate = "11-08-15";

                DateTime finalDate = DateTime.ParseExact(TESTDate, "DD-MM-YY", CultureInfo.CurrentCulture);

     

               //Converting Cultural Decimal

               decimal DecimalTEST = 33.73M;

               System.Convert.ToDecimal(DecimalTEST, new System.Globalization.CultureInfo("de-DE"));

     

                var WebServicesCultureTable = new NavOData.WebServicesCulturePage

                {

                    //Here we can create new rows

                    CultureCode = "DE",

                    CultureDate = finalDate,

                    CultureDecimal = DecimalTEST,

                };

                nav.AddToWebServicesCulturePage(WebServicesCultureTable);

                nav.SaveChanges();

            }

    Finally I inserted manually direct on the page the following test:

    Then using my new button3 = Insert, I inserted the values to test on the web service side.

    There you go: the culture personalization does works as desired!

    Bonus info: This test I did using Microsoft Dynamics NAV 2013 R2 Cumulative Update 6 (build # 39665), but this functionality has been available since Microsoft Dynamics NAV 2013 Cumulative Update 9, and is also working as it should in Microsoft Dynamics NAV 2013 R2 and Microsoft Dynamics NAV 2015.

     

    Best Regards,

    Daniel De Castro Santos Silva 

    Microsoft Dynamics NAV Support Engineer

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 5 for Microsoft Dynamics NAV 2015 has been released

    • 17 Comments

    Cumulative Update 5 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2015. 

     The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    •   AU - Australia
    •   AT - Austria
    •   BE - Belgium
    •   CH – Switzerland
    •   CZ – Czech Republic
    •   DE - Germany
    •   DK - Denmark
    •   ES - Spain
    •   FI  - Finland
    •   FR - France
    •   IS - Iceland
    •   IT - Italy
    •   NA - North America
    •   NL - Netherlands
    •   NO - Norway
    •   NZ - New Zealand
    •   RU – Russia
    •   SE - Sweden
    •   UK - United Kingdom

    Note: You must convert the database if you are upgrading to Cumulative Update 5 from a cumulative update earlier than Cumulative Update 4 (build 39663). For more information, see Converting a Database in Help for Microsoft Dynamics NAV. 

    Where to find Cumulative Update 5

    You can download the cumulative update from KB 3039824  – Cumulative Update 5 for Microsoft Dynamics NAV 2015 (Build 40262). 

    Additional Information

    For information about how to install the cumulative update, see How to Install a Microsoft Dynamics NAV 2015 Cumulative Update

    For information about how to work around a recent process change, see How to Get Back the 'Hotfix Directories' from NAV 2015 Cumulative Update 1.

    For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2015.  

  • Microsoft Dynamics NAV Team Blog

    Error Message Processing

    • 4 Comments

    Do you remember when, a year and a half ago, at NAV Tech Days, you gave us feedback on the NAV Design Patterns session - saying that Journal Error Processing is not generic and decoupled enough?

    Well, my colleagues Andreas Moth and Jesper Schulz from the NAV localization team at Microsoft Development Center Copenhagen, had created a framework to extract and generalize error message processing. This framework can be used across NAV. It comes with an API and new objects that you can reuse and plug into your own implementation. And part one of how to use it is described below.

    Enjoy,

    Bogdana Botez

    NAV Design Patterns team Copenhagen

    Note: This pattern describes new functionality which makes it possible to generalize the Journal Error Processing pattern.

    Abstract

    Missing, invalid or incomplete data is a common issue during data processing in Microsoft Dynamics NAV. This article describes how to collect all error messages during processing using the error message component and present them to the user in a unified way, which enables the user to correct the errors efficiently. By leveraging the integrated error message logging functions, you can log a message with a single line of code and present it to the user with another one-liner.

    Description

    This article describes how to use the Error Message component in Microsoft Dynamics NAV, which in short gives you the possibility to:

    1. Link an error mesesage to the page which enables you to resolve the problem.
    2. Assemble all error messages in one central view instead of having to encounter them one by one.

    Validating data is a common task during data processing in Microsoft Dynamics NAV. Unfortunately, validation is often done using NAV’s integrated ERROR and TESTFIELD functions, which halt execution of the process. The user will then have to locate the invalid / missing data, correct it and reinitiate the process, possibly running into the next error, making the cycle repeat itself. This can be a very tedious, time-consuming and frustrating process. The error message component aims at improving this experience by providing a lightweight framework for error message logging and this article will explain how to leverage this functionality in your code. By doing so, all error messages are gathered during (pre-)processing and are finally presented to the user. The user then has the possibility to click on the error message, which will open the record where the invalid / missing data is located, thereby enabling the user to correct all mistakes efficiently, from one central place and in one go.

    The example below comes from a Mexican localization, where the user has to export financial balances and transactions into an XML file for government audit purposes. In order to generate valid files, some mandatory data needs to be entered in the system. By leveraging the error message component, the user will be presented with the following page, if missing or invalid data was discovered:

    By clicking on the error message, the user will be presented with the entity, where the invalid / missing data should be corrected / added.

    Read more on the NAV Design Patterns Wiki site...

     

    Best regards,

    Jesper Schulz at Microsoft Development Center Copenhagen

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Windows PowerShell Remoting

    • 3 Comments

    In this previous blog post we looked briefly at Windows PowerShell and remoting. Back then, we used a PSSession object to connect to a Microsoft Dynamics NAV virtual machine in Microsoft Azure. This time we will look a little closer at just the PSSession object.

    Coffee Break 8 - Windows PowerShell Remoting (On-Premises)

    Taking a closer look at ways to run PowerShell on remote machines on your network.

    But first, if you think you need more study to start with this, then please check out this free Professional Education online course, offered by Microsoft and EdX:
    Windows PowerShell Fundamentals

    Customer Story:

    The customer's IT administrator want to remotely check Microsoft Dynamics NAV Server instances on their network. And they want to be able to script tasks and run those scripts from their laptop. So connecting to each (NAV) server with RDC is not an option. In fact now we are at it, why not remote from your mobile phone?

    Basic installation guide for Windows PowerShell Web Access

    How to do PowerShell on your phone

    Customer Story II (The Sequel):

    A problem of some kind has occurred on one of the installations and services need to get restarted/updated/tenants mounted. The customer's IT administrator is in a traffic jam. A long phone call with instructions on what to press or not is one option. But PowerShell web access from the mobile, and simple web-run remote management while still stuck in that traffic jam might be a better one. More about this to come soon!

    Challenge:

    Next time you need to restart a NAV Server, change a config value or do any other (NAV) admin task that you can do with PowerShell, try to do it without actually connecting (RDC) to the machine. Do it via a PowerShell prompt on your machine.

     

    PowerShell Remoting:

    Imagine that you are sitting with your laptop, and from there you need to access (NAV) services on various different machines on your domain.

    Option 1: -Computername
    Many cmdlets implement the -Computername parameter, for example:

    $RemoteServer = "MyServerName"
    Get-Service -ComputerName $RemoteServer # shows you Windows services on [MyServerName]

    This is the simplest way to run a cmdlet remotely. But there is no general rule that every cmdlet must implement the -ComputerName parameter or if they do, then how.

     

    Option 2: Invoke-Command
    Invoke-Command is a cmdlet that will send whatever command to the remote machine. Example:
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{get-service}
    or run anything that you are used to run from a cmd prompt
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{ipconfig}
    or

      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{dir C:\}
    or even (though a bit pointless)
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{calc}

    If you want to prompt for credentials on the remote machine:

      Invoke-Command {Get-Service} -computername $RemoteServer -Credential Get-Credential

    Invoke-Command creates a new session, so to run a NAV cmdlet we first need to import the NAV management module and then run our NAV cmdlet. Each command separated by semi-colon:
      Invoke-Command -ComputerName $RemoteServer `
       -ScriptBlock {Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll' `
       ; Get-NAVServerInstance | Format-Table -AutoSize}

     Note that in this example you do not need to have NAV on the machine you are running from. The only cmdlet that runs on your machine is Invoke-Command.

    You can also run a script remotely. If you first create a script like this:

      Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'
      Get-NAVServerInstance | Format-Table -AutoSize

    Save it as c:\Tech\GetNAVServers.ps1

    Then run it:

      Invoke-Command -ComputerName $RemoteServer -FilePath{C:\Tech\GetNAVServers.ps1}

    Notice that the script is saved locally on your machine, but it runs on the remote machine.

    We can add parameters to this script by adding -ArgumentList Arg1, Arg2, Argn. Let's make a small addition to the script by adding a Microsoft Dynamics NAV Server instance name as a parameter:

      Param($NAVInstanceName)
      Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'
      Get-NAVServerInstance $NAVInstanceName | Format-Table -AutoSize

    (if you want more than one parameter, just comma-separate them like this Param($NAVInstanceName, $Tenant, $UserName, ... etc)

    Try to just run the script (locally):

      CD C:\Tech\

      .\GetNAVServers.ps1 -NAVInstanceName DynamicsNAV80

    And remotely:

      Invoke-Command -ComputerName $RemoteServer -FilePath{C:\Tech\GetNAVServers.ps1} -ArgumentList Dynamicsnav80

     

    Option 3: PSSession
    As mentioned before, Invoke-Command will create a new session. But we can also create a new session separately, and then re-use this session. Why?

    •   It takes time to open a session, so we can potentially save some time by re-using one session.
    •   The session is persistent (keeps variable values), which could also be of use.

    Creating a remote session of course requires authentication. As long as we are remoting to a machine on our domain then Windows will take care of this. Creating a remote session to a machine in the cloud requires certificate. For this, see the previous blog (linked in the top of this one). But here we just want to open session(s) on our own network. But notice that once you have your PSSession object, then the rest of the syntax is exactly the same, whether the session is to a machine under your desk, or to a machine in an Azure data centre. Create a session to a machine ($RemoteServer) on your network could not be easier:

      $MySession = New-PSSession $RemoteServer

    Now you can (re) use this session with Invoke-Command. So in the examples above, just use -Session parameter instead of -ComputerName:
      Invoke-Command -Session $MySession -ScriptBlock{get-service}

    The next example will show what it means that the session is persistent:
      Invoke-Command -Session $MySession -ScriptBlock{$i++;$i}

    Run that one a few times, and see that $i increments every time.

    Or you can enter a remote manual session:

      Enter-pssession $MySession

    Notice that the first part of the prompt changes to show you that the prompt is now running on the remote machine. To leave this machine and return PowerShell to your local machine, run EXIT.

    Troubleshooting:

    On some (client) OS, PowerShell remoting is not enabled by default. PowerShell remoting runs under a service called WinRM. To test it:

    Test-connection -ComputerName $RemoteServer
    Test-wsman -ComputerName $RemoteServer

    And as also mentioned by Pallea in comments to the blog linked above (thanks!), if remoting is not enabled you can enable it in this way:

    Enable-PSRemoting

     

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 24 for Microsoft Dynamics NAV 2013 has been released

    • 6 Comments

    Cumulative Update 24 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013.

    The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions: 

    • AU - Australia
    • AT - Austria
    • BE - Belgium
    • CH - Switzerland
    • DE - Germany
    • DK - Denmark
    • ES - Spain
    • FI - Finland
    • FR - France
    • IS - Iceland
    • IT - Italy
    • NA - North America
    • NL - Netherlands
    • NO - Norway
    • NZ - New Zealand
    • SE - Sweden
    • UK - United Kingdom

    Where to find Cumulative Update 24

    You can download the cumulative update from KB 3039828 - Cumulative Update 24 for Microsoft Dynamics NAV 2013 (Build 40118).

    Additional Information

    For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2013

    For a list of all hotfixes included in cumulative updates for this version, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource:

  • Microsoft Dynamics NAV Team Blog

    A Couple of RDLC Performance Optimization Tips

    • 5 Comments

    We have received several requests related to optimization tips for the Microsoft Dynamics NAV RDLC report experience. So in this blog post we provide you with a couple of tips for how you can refactor a report in order to increase its performance in terms of dataset reduction - the example is based on the standard report 4 “Detailed Trail Balance” from Microsoft Dynamics NAV 2015 Cumulative Update 4 German localized DVD version (DEU).

    NOTE: This approach is valid if you are crystal clear on the intrinsic technical limitations when printing client-side as was widely discussed and dissected in this blog post from March 2014, and it is intended to provide a general guidance as-is in the form of a proof of concept.

    TIP #1 : Create a header for constant and static values

    This is an old trick (described also in books and other collateral since Microsoft Dynamics NAV 2009), and it consists of pushing static values such as e.g. COMPANYNAME or TABLECAPTION in a DataItem based on an “Integer” record (virtual table) that will be repeated only one time (MaxIteration DataItem property needs to be set to 1).

    The “Integer” DataItem must be the very first DataItem in the report so that while refactoring the report in Visual Studio, developers could always refer to this by using the =First( statement in VB.NET.

    The great advantage is to avoid costly redundancy for static values (e.g. COMPANYNAME) all along the dataset since only the first record in the column is populated. This will reduce the dataset dimension, in terms of data quality, and consequently obtain a faster load of the dataset client side and raising higher the out of memory exception bar (the application is able to process more rows). 

    In this example, standard Report 4 has been refactored and 8 columns has been moved as part of the Header “Integer” DataItem:

    Data Source Name
    COMPANYNAME   CompanyName 
    ExcludeBalanceOnly    ExcludeBalanceOnly
    PrintReversedEntries  PrintReversedEntries
    PrintClosingEntries   PrintClosingEntries
    PrintOnlyCorrections  PrintOnlyCorrections
    GLFilter    GLFilter
    STRSUBSTNO(Text000,GLDateFilter)  PeriodGLDtFilter
    GLAcc.TABLECAPTION + ': ' + GLFilter   GLAccTableCaption

    TIP #2 : Use labels

    Multianguage (ML) labels are pushed to Report Viewer at runtime outside the dataset. This happens when the report is run, such as when there is code such as this:

    CurrReport.LANGUAGE := Language.GetLanguageID("Language Code");

    inside the DataItems (typically OnAfterGetRecord triggers). This does not have any influence in the label items since they are already loaded by Report Viewer as constant value in the Parameters read-only collection.

    Luckily, this is not always the case and we could use ML labels as constant values instead of redundant and repeated values added as Columns in the DataItems. Since labels are not part of the dataset, this will be reduced in the number of columns improving performance in loading the dataset client-side and raising higher the out of memory exception bar (the application is able to process more rows). 

    In this example, standard Report 4 has been refactored and 9 columns has been deleted and values added as ML Labels:

    Name

    DetailTrialBalCaptionLbl

    PageCaptionLbl

    BalanceCaptionLbl

    PeriodCaptionLbl

    NetChangeCaptionLbl

    GLEntryDebitAmtCaptionLbl

    GLEntryCreditAmtCaptionLbl

    TIP #3 : Refactor decimals and use FORMAT(decVar) where and if possible

    This is tricky. Microsoft Dynamics NAV RDLC decimal values are pushed to the dataset together with their formatting. This means that the dataset will have a column that store the value and another one that store the value format. This is costly since it will always add an extra column for every decimal value in the dataset.

    In some cases, you can transform the decimal to its equivalent string by applying the C/AL Statement FORMAT(decVar,0,1) in the Microsoft Dynamics NAV development environment, and then use a ML label for all the decimal value format (or several ML labels if there are different decimal formatting inside the report). In Visual Studio, remember to transform back the string into a valid decimal value using the VB .NET =CDec( function.

    In this example, the standard report 4 has been refactored, and 5 columns containing the same formatting clause has been silently deleted (not created):

    Data Source Name
    FORMAT(StartBalance,0,1)   StartBalance 
    FORMAT("VAT Amount",0,1)   VATAmount_GLEntry
    FORMAT("Debit Amount",0,1)    DebitAmount_GLEntry
    FORMAT("Credit Amount",0,1)   CreditAmount_GLEntry
    FORMAT(GLBalance,0,1)  GLBalance 

    And a ML Label has been created and referenced in Visual Studio where needed:

    DecimalFormat              #,##0.00

     

    OUTCOME and STATISTICS
    Report PRE   37 Columns
    Report POST   22 Columns (8 Header columns)
    Gain   Dataset reduced by 41 % (in quality – header – by 62 %)

    We have attached our version of report no. 4 as a text file so you can more easily compare this to your own version.

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

    Duilio Tacconi Microsoft Dynamics Italy
    Alessandra Pandini Microsoft Dynamics Italy

     

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 17 for Microsoft Dynamics NAV 2013 R2 has been released

    • 34 Comments

    Cumulative Update 17 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2.

     The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    •   AU - Australia
    •   AT - Austria
    •   BE - Belgium
    •   CH – Switzerland
    •   CZ – Czech Republic
    •   DE - Germany
    •   DK - Denmark
    •   ES - Spain
    •   FI  - Finland
    •   FR - France
    •   IS - Iceland
    •   IT - Italy
    •   NA - North America
    •   NL - Netherlands
    •   NO - Norway
    •   NZ - New Zealand
    •   RU – Russia
    •   SE - Sweden
    •   UK - United Kingdom

    Where to find Cumulative Update 17

    You can download the cumulative update from KB 3039826  – Cumulative Update 17 for Microsoft Dynamics NAV 2013 R2 (Build 40076). 

    Additional Information

    For more information about cumulative updates for this version, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2.

    For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2

    For a list of all hotfixes included in cumulative updates for this version, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource

  • Microsoft Dynamics NAV Team Blog

    The Multi-File Download Cookbook

    • 0 Comments

    Today's pattern is not a pattern but a "cookbook" that shows how to use the FileManagement library to download multiple files as a .zip file through Microsoft Dynamics NAV in a way that works on all types of clients.

    Abstract

    The goal of this pattern is to enable the users to download multiple files as a zip file instead of downloading one by one.  On the Microsoft Dynamics NAV Web client, this is the preferred way of delivering multiple files since it is one of the web patterns and we cannot use the File Management codeunit to place files silently on the machine.

    Description

    When generating reports that consists of multiple, and usually an unknown number of files, the developer will have to handle the download which also depends on the client the user is on. The problem is that the Microsoft Dynamics NAV Windows client has access to the user’s file system, whereas the web client does not. Following web guidelines, and the fact that client-side .NET is not available in Web client, you can’t initiate multiple downloads which requires the developer respond to the type of client. In some browsers it is possible to download files one-by-one in the Web client by using a confirm dialog, however this is a hack and should not be used.

    To solve this problem, a generic download mechanism is used that is client dependent event when multiple files need to be downloaded. For the Web client, the files are compressed using ZIP, and for the Windows client the files are downloaded directly to the file system.

    The pattern is usable for all objects that output multiple files and is available in both Windows client and Web client.

    Usage

    The pattern consists of two steps: 1) Gathering the files and 2) downloading the file(s)

    For first step consists of a loop that goes through the files that needs to be downloaded. If it is on the Web client, the files are added to a ZIP archive server-side using a naming convention defined by the integration function GetSeriesFileName. This function takes a filename and number, and transforms it to unique names following a meaningful deterministic pattern e.g. prepend an integer before the file extension. The same function is used when the temporary files are created server side, so the files can be found deterministically later. This removes the need for storing filenames and consequently allows an arbitrary number of files. The second integration function: GetTotalNumberOfFiles, returns the total number of files generated during the data processing and makes the pattern able to handle an arbitrary number of files.

    The second step is the actually download of file(s). For the Web client this consists of closing the ZIP achieve and downloading via the standard download handler that works in the Web client. For the Win client, the files are saved directly to the client during the first step.

    Code 1: File loop shows an example implementation of this pattern. ServerFileName is generated at the beginning of the report/codeunit, and is the base for GetSeriesFilename. The file that is actually written to during data processing is stored in another variable which holds the output from GetSeriesFilename on the current file number. Note; the example code will only create a ZIP file if there in fact are multiple files to be downloaded. 

    Read more on the NAV Design Patterns Wiki....

    Best regards,

    Martin Dam at Microsoft Development Center Copenhagen

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Creating and Using Windows PowerShell Profiles

    • 1 Comments

    When you open a Windows PowerShell command prompt, often you type the same commands to begin with. This post shows how to create a profile that will run the commands you want every time you start a new PowerShell command prompt.

    Coffee Break 7 - Creating and using Windows PowerShell profiles

    User Story

    Developer wants to automate importing of  Microsoft Dynamics NAV modules along with other often used scripts into the Windows PowerShell ISE environment, for simplicity and reliability. For this, a PowerShell profile can be used. A PowerShell Profile is a start-up script that runs when PowerShell starts. Here we can import modules , add snap-ins, run functions, etc. Things that will help to initialize and build up the work environment.

    $profile

    A profile path is contained in the automatically created variable $profile. Executing $profile in PowerShell will show the path to your existing profile, or path to where one would be created. It will look like this when run from the ISE environment: C:\Users\[user]\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1.

    To check if you already have a profile created, run:

    Test-Path $profile

    If the profile doesn’t exist, return value will be False. To create the profile you can run this:

    New-Item -path $profile -type fileforce
    (notice using –force to overwrite existing profile).

    The above will simply create a blank profile file (script). One can modify the script directly from the file explorer, and or by using

    Notepad $profile

     

    #Here we can for example import modules used  for NAV management.

    Import-module "C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll"

    #And any other modules we might use frequently (for ex. Azure module - requires having installed this in advance)

    Import-module Azure

    #Set prompt to be descriptive

    function Prompt { "NAV71 $(Get-Location)>" }

    # We can set background to our preferences, change default path, etc….

    Set-location C:\MyWorkEnv\
    $host.UI.RawUI.BackgroundColor = “DarkRed”; $Host.UI.RawUI.ForegroundColor = “Gray

    For complete list of colors that can be set using host, check this link: ConsoleColor Enumeration

    Note: Some modules get loaded automatically when PowerShell starts, even without Import-Module. This happens when a module is added to system folder %PSModulePath% at installation time. All modules that are in %PSModulePath% are loaded automatically whenever any of their commands are used. 

    Different profiles

    There are 4 possible profiles for a host:

    • All Users, All Hosts,
    • All Users, Current Host,
    • Current User, All Hosts,
    • Current User, Current Host

    Where the bottommost one has the highest priority. You can store different profiles for different scopes. To view the path of each, run each of the following commands:

    • $Profile.AllUsersAllHosts
    • $Profile.AllUsersCurrentHost
    • $Profile.CurrentUserAllHosts
    • $Profile.CurrentUserCurrentHost

     

    Note also that a Windows PowerShell profile is  just another ps script and like any other ps script is not exempt from execution policy. The Execution Policy in PowerShell determines which scripts can be run and which cannot. By default, no scripts can be run, including Profiles (Restricted Execution Policy). This can be changed (to All Signed, Remote Signed, Unrestricted). Note that the change is saved in the registry and only needs to be changed once per computer.

    Handling multiple versions of Microsoft Dynamics NAV on the same machine

    Finally, you might want to work with several Dynamics NAV versions in parallel (on the same machine). Working with each version requires it’s respective Dynamics NAV management module version to be loaded. The PS modules are .net assemblies which will load it into the AppDomain of the PowerShell Host (the application). Remove module cmdlet will just remove the module from  the current session. For more details, see:
    http://msdn.microsoft.com/en-us/library/ms173101(v=vs.80).aspx

    In short – once we have imported a module (dll) it will remain in the current session. So to work with parallel NAV versions, a session per version is needed. Instead of one common profile, a startup-script dedicated to each session can be used to initialize each NAV version environment.

    So to create an ISE environment for each Dynamics NAV version, we can open ISE with a startup script to import Dynamics NAV modules for the relevant version of Microsoft Dynamics NAV. For example, create a script file called MyNAV2013Env.ps1 and save it locally. The file could for example look like following:

     

    import-module "C:\Program Files\Microsoft Dynamics NAV\71\Service\Microsoft.Dynamics.Nav.Management.dll"  
    Set-Location C:\tech\MyNav2013Env\

    #here we can (for ex.) add different coloring for different version, to avoid any confusion about what NAV version we’re working with
    $host.UI.RawUI.BackgroundColor = “DarkRed”; $Host.UI.RawUI.ForegroundColor = “Gray”

    #the below will clear the screen
    Clear-Host

    # Welcome message
    Write-Host "Welcome to NAV 2013R2 Powershell: " + $env:Username
    Write-Host "Dynamics NAV version 2013 R2 module imported"

     

    Save the file and create a shortcut to the Windows PowerShell ISE on the desktop. Modify shortcut properties to set
    Target: C:\Windows\System32\WindowsPowerShell\v1.0\powershell_ise.exe -File "C:\mydocs\ MyNAV2013Env.ps1"
    Set shortcut name to NAV2013R2_ISE. Suggestion: Tick "Run as Administrator" on the shortcut as well.

    When doubleclicking either of the shortcuts an ISE environment will open with the MyNAV2013Env file.
    Press F5 to execute the file and set up environment, then Ctrl+N (or File-New) to open a new script file to work with.

    Repeat the above for Microsoft Dynamics NAV 2015 (modify the file name, module path , colors, welcome message and target path accordingly). And in this way have one shortcut for each version of Microsoft Dynamics NAV on your machine.

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Coffee Break | More piping with Dynamics NAV

    • 5 Comments

     Did you see our first coffee break about piping at Windows PowerShell and Piping? Let's dig a bit further.

    Coffee Break 6 - Return to piping

    This time we will use more piping and other ways to look at a PowerShell object and format it in different ways. For the example here we will use Get-NAVServerInstance and the results from that cmdlet. But everything in this post would apply in the same way on other cmdlets, like

    Get-NAVServerUser
    Get-NAVWebService
    Get-Process
    Get-Service

     

    Change how data is displayed

    Import the NAV management module so we can use the NAV cmdlets

    import-module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'

    Run the following commands and check how the result differs:
    Get-NAVServerInstance
    Get-NAVServerInstance | Format-Table
    Get-NAVServerInstance | Format-Table -AutoSize

     

    Select parameters:

    Select which columns to return

    Get-NAVServerInstance | Select-Object ServerInstance, State

    But... How do you know which columns are available? Simply pipe the cmdlet into Get-Member:

    Get-NavServerInstance | Get-Member

    This shows you a list of members, including these properties

    Default                                                                                                      
    DisplayName                                                                                                 
    ServerInstance                                                                                              
    ServiceAccount                                                                                            
    State                                                                                                    
    Version

    Formatting Output

    The most usual formats are list and table. Confusingly to a Dynamics NAV person, Format-List is like a card display, and Format-Table is just like a list. Run these to see the difference:
    Get-NAVServerInstance | Select-Object ServerInstance, State | Format-List
    Get-NAVServerInstance | Select-Object ServerInstance, State | Format-Table

    Some of the most useful other formats (to replace the last bit of the pipe above):

    Group-Object State
    Sort-Object State
    ConvertTo-Html
    Export-Csv -Path c:\x\servers.txt
    Out-gridview
    Clip

    Especially Clip is very useful - it sends the result directly to your clipboard so you can paste it into Notepad or somewhere else.

    Note that formatting pipes may destroy the object in order to display it, so always do the formatting as the last part of a pipe. Except if you want to follow it by an Out-cmdlet.

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    The AMC Bank Data Conversion Service for Microsoft Dynamics NAV 2015 now supports 35 additional banks

    • 0 Comments

    Based on requests from customers and partners, the AMC Bank Data Conversion Service for Microsoft Dynamics NAV 2015 has just released support for 35 new banks worldwide. To see the new banks being supported, please go to the service sign up page here where you can also find more information on the service.

    If your bank isn’t supported today, please place your vote here and influence which banks the AMC Bank Data Conversion Service should add support for next.

  • Microsoft Dynamics NAV Team Blog

    Application Design Pattern: SELECT DISTINCT with Queries

    • 2 Comments

    This pattern explains how to perform SELECT DISTINCT by using queries in Microsoft Dynamics NAV. It is brought to you by Bogdana Botez from the Dynamics NAV team here at Microsoft Development Center Copenhagen (MDCC).

    When you work with tables, sometimes you must perform a SELECT DISTINCT (also known as SELECT UNIQUE) from a table. As Dynamics NAV does not provide this out of the box, we present below a way to select unique records by using queries.

    Problem Statement

    Let's consider the VAT Entry table: 

    The goal is to select one line for each separate document that produced VAT Entries. In other words, we want records grouped by Type, Document Type and Document No.. However, if there are multiple lines with the same value of the triad Type, Document Type and Document No. in the VAT Entry table, we only want to see one of them.

    Solution

    Create a new query object VAT Entry Distinct Document No., with a single DataItem sourced from VAT Entry table. Add the three desired group-by fields Type, Document Type and Document No. as columns.

    To enable grouping, add one more column, with Method Type = Totals. This will automatically set the Group By checkbox to TRUE on the three precedent fields.

    Note that the Group By field is read-only and trying to set it by hand will clarify that:

    Running the query yields a single record per document. You can notice in the second line below for example, how the sales invoice number 103001 had 2 VAT Entries, but it shows up only once in the query:

    Example

    One thing is to be noted: there is a limitation to how much information you can take out from the records. For example, if we need to extract more information than just the one we already have in the columns, then the following apply: adding one more column of Method Type = None will indeed show more information, but it might affect the grouping. More details below.

    Read more on the Patterns Wiki site...

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Search in a Dynamics NAV object file using Windows PowerShell

    • 1 Comments

    This coffee break post illustrates how to search a text file for specific words or a phrase. You can do this with Windows PowerShell in any text files, but let's use some Dynamics NAV objects exported as text. Technically speaking we are reading a text file then piping it line for line through a search cmdlet, which pipes matching lines further to a log.txt file.

    Coffee Break 5 - Searching through a Dynamics NAV text file

    Customer story:

    The developer wants an automated way of locating all occurrences of a string (table name, field name, comment, ...) in a country-specific version of Dynamics NAV, in this example the Norwegian version. And we will log the output of this search to a log file in c:\NAVApp\Log. 

    Exporting objects from Dynamics NAV:

    Prerequisites:

    Crete a folder C:\MyNAVApp with a subfolder \Log so that the resulting full path is C:\MyNAVApp\Log.

    For this purpose we use the Microsoft Dynamics NAV Application Merge Utilities. Note that these install to the equivalent of C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client. This time we don't need to import the Management module, only the application merge utilities:

    Import-Module "${env:ProgramFiles(x86)}\Microsoft Dynamics NAV\80\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1"

    • Note 1: Make sure to import Microsoft.Dynamics.Nav.Model.Tools.psd1, and not NavModelTools.ps1.
    • Note 2: This will load the path for finsql.exe too, and use the finsql in the client folder.

    Set a few variables. Assuming that we work in folder C:\MyNAVApp\, and we will be searching for where "G/L Account" table reference is used. And we will log the output of this search to a log file in the c:\MyNAVApp\Log folder.

    $objectPath = 'C:\MyNAVApp'
    $sourcepath = Join-Path $ObjectPath '\MyObjects'

    $NAVobjects = Join-Path $ObjectPath 'NAVobjects.txt'
    $LogPath = Join-Path $ObjectPath '\log\whereused.txt'
    #Note, a search string can also be an array of strings
    $SearchString = '”G/L Account”'

     

    Export the objects you like, either all objects:
    Export-NAVApplicationObject  -DatabaseName "Demo Database NAV (8-0)" -DatabaseServer ".\NAVDEMO" -Path $NAVObjectFile

    Or filter (choose the filter you like):

    $FilterString = "Version List=*NAVNO*"
    #or

    $FilterString = "Modified=Yes"
    Export-NAVApplicationObject  -DatabaseName "Demo Database NAV (8-0)" -DatabaseServer ".\NAVDEMO" -Path $NAVObjects -Filter $FilterString 

    #Split into individual object files

    split-navapplicationobjectfile -Source $NAVobjects -Destination $sourcepath -PassThru -Force


    Now load the list of files in the folder. We're using the -File parameter with Get-ChildItem to limit the scope to files only (sub folders are not included).

    $myobjects = Get-ChildItem -Path $SourcePath -Filter *txt -File

    The next line shows a very simple way to read through all text files in the specified path (c:\MyNAVApp) and for each file searches for the search string (in our case "G/L Account) and for each hit pipe the source line to the log file along with the line number. For this we will use the Select-String cmdlet, that can work directly on Objects with File Info (objects returned by calling  Get-ChildItem cmdlet).

    $myobjects | Select-String $SearchString | Out-FileFilepath $LogPath

    Note that using the parameters and segments above implies that:

    • The script raises an error if the $ObjectPath does not exist.
    • Out-File will overwrite the existing file per pipeline
    • Out-File will append lines to the file, per pipeline object

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

     

  • Microsoft Dynamics NAV Team Blog

    Upgrading from Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2015

    • 11 Comments

    Quite a few people have asked that we excavate the guidance for how to upgrade from Microsoft Dynamics NAV 2009 R2 directly to Microsoft Dynamics NAV 2015. We provided this guidance in the blog post for Cumulative Update 1, but as time progresses, this becomes harder to remember and to find. So here you go:

    Introduction

    This blog post describes the tasks required for upgrading a Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2015.

    Using the upgrade toolkit and conversions tools that are available, the tasks will lead you through the process of converting the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2013, converting the Microsoft Dynamics NAV 2013 database to Microsoft Dynamics NAV 2015, and finally, converting the old data with the old table and field structure to function together with the table and field structure of Microsoft Dynamics NAV 2015.

    In order to use the toolkit to upgrade data, you will also need the Microsoft Dynamics NAV 2013 development environment and Microsoft Dynamics NAV 2015.

    Data Upgrade Short Overview

    The following lists provides a brief description of the steps involved in the data upgrade. The steps are detailed in the task sections that follow.

    1. In Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1, compile all tables.
    2. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    3. Import Microsoft Dynamics NAV 2015 license into the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database.
    4. Import Upgrade601800.[Country].1.fob (use Replace All action on the Import Worksheet).
    5. For each company in the database, run form 104001, Upgrade - Old Version, and choose the Transfer Data action.
    6. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    7. Run form 104001, Upgrade - Old Version, and then choose the Delete Objects action.
    8. Uninstall Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1.
    9. Change the compatibility level of the database by using SQL Server Management. Studio.
    10. Download the latest Cumulative Update of Microsoft Dynamics NAV 2013 and install the Microsoft Dynamics NAV 2013 development environment.
    11. Open the old database with Microsoft Dynamics NAV 2013 development environment and convert the database.
    12. Uninstall Microsoft Dynamics NAV 2013.
    13. Make a full SQL backup of the database.
    14. Install Microsoft Dynamics NAV 2015 including the Development Environment, Microsoft Dynamics NAV Server, and Administration Tool components.
    15. Open the old database with Microsoft Dynamics NAV 2015 Development environment and convert the database.
    16. Make sure the system tables (which have IDs in 2000000004…2000000130 range) are compiled (On the Compile window, set the Synchronize Schema option to Later).
    17. Set database role membership for the service account that is used by the Microsoft Dynamics NAV Server instance to db_owner.
    18. Connect Microsoft Dynamics NAV Server instance to the old database.
    19. Start the development environment as administrator.
    20. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    21. Import all new Microsoft Dynamics NAV 2015 objects (use the Replace All action on the Import Worksheet, and then set the Synchronize Schema option to Later).
    22. Import Upgrade601800.[Country].fob.
    23. Compile the tables which are not already compiled (set Synchronize Schema option to Later).
    24. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    25. Make a full SQL backup of the database
    26. Run the data upgrade process from the Tools menu by selecting Data Upgrade, and then Start.
    27. Delete obsolete tables and upgrade toolkit objects.
    28. Import updated permissions sets and permissions.
    29. Set language of the database.
    30. Import or register the control add-ins.

    Task 1: Run Upgrade Step 1 in the Microsoft Dynamics NAV 2009 R2 or SP1 development environment (Classic client)

    1. Make sure that all table objects have compiled successfully.
      To compile all table objects, in Object Designer, select all objects of type Table, and then on the Tools menu, choose Compile. During compilation, Microsoft Dynamics NAV generates or regenerates the BLOB content in the Object Metadata table that is used in the later steps.
    2. Create a copy or a backup of the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database, and open it in the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 development environment.
    3. Add your Microsoft Dynamics NAV 2015 partner license to the database.
      You can do this by selecting the Save License in Database field in the Alter Database window. If the field was not already selected, a dialog box opens so you can specify the location of your partner license. If the field was already selected, upload the partner license in the License Information window.
    4. Open Object Designer, and then import Upgrade601800.[Country].1.fob from the \UpgradeToolKit\Local Objects folder. In the Import Worksheet window, choose Replace All, and then choose the OK button to import the objects.
    5. For each company in the database, do the following:
      1. Open the company, and make the relevant changes to data. For more information, see Task 3: Data/Object Changes Prior to Step 1 in the MSDN Library.
      2. In Object Designer, run form 104001, Upgrade - Old Version. Choose the Transfer Data button.

        Important: After you have completed this step for all companies, we strongly recommend that you create a full SQL backup the database by using SQL Server management tools.
    6. When you have transferred all data for all companies, in the Upgrade - Old Version window, choose the Delete Objects button.
      This action deletes all objects in the database that are not tables, but also obsolete tables that belong to functionality that is not available in Microsoft Dynamics NAV 2015.

    Task 2: Convert the Microsoft Dynamics NAV 2009 R2 or SP1 database to a Microsoft Dynamics NAV 2013 database

    1. Uninstall Microsoft Dynamics NAV 2009 R2.
    2. Download the latest available Cumulative Update for Microsoft Dynamics NAV 2013.
      The following is article lists the Cumulative Updates that have been released for Microsoft Dynamics NAV 2013: https://mbs2.microsoft.com/Knowledgebase/kbdisplay.aspx?scid=kb,en-us,2842257 
    3. Run the installation program (setup.exe) for Microsoft Dynamics NAV 2013, choose Choose an installation option, choose Custom, and then choose the Client option (with the Development Environment).
    4. Change the compatibility level of the database according to the following:
      For SQL Server 2008 R2 set the compatibility level of the database to 100.
      For SQL Server 2012, set the compatibility level of the database to 110.
      To do this, you use Microsoft SQL Server Management Studio as follows:
      1.  Start Microsoft SQL Server Management Studio and connect to the SQL Server instance that includes the Microsoft Dynamics NAV database.
      2. Right-click the Microsoft Dynamics NAV database, and then select Properties.
      3. In the Database Properties window, choose the Options page, and then set the Compatibility Level to the correct value.
    5. In the Microsoft Dynamics NAV 2013 development environment, open the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database and agree to convert the database.
      During this step, Microsoft Dynamics NAV converts all text and code fields to Unicode format by changing their SQL Server data type. This conversion requires more disk space than usual, since both the database and the log file will grow in size considerably. It can also be a lengthy process.
      If your Microsoft Dynamics NAV 2009 R2 database is using SQL Server Collation, within the same step the collation will be changed to a suitable Windows collation. This is because Microsoft Dynamics NAV 2013 and higher versions only support Windows Collation.

      Important: After the database conversion has completed, we strongly recommend that you create a full SQL backup of the database.
    6. The technical upgrade of the database to Microsoft Dynamics NAV 2013 is complete. You can now uninstall Microsoft Dynamics NAV 2013.

    Task 3: Convert the Old Database to a Microsoft Dynamics NAV 2015 Format

    1. Install Microsoft Dynamics NAV 2015.
      During the installation of Microsoft Dynamics NAV 2015, select the Choose an installation option, then select the Custom option, where you choose to install the Client (with the Development Environment), Server, and Administration Tool components.
    2. To convert the old database to a Microsoft Dynamics NAV 2015 format, open the old database in the Microsoft Dynamics NAV 2015 development environment, and then follow the instructions.
    3. After the database conversion is completed, verify that all system tables are compiled.

    System tables have IDs in the range 2000000004 to 2000000130. If any tables are not compiled, then you must compile them. When you compile tables, on the Compile dialog box, set the Synchronize Schema option to Later.

    Task 4: Connect a Microsoft Dynamics NAV 2015 Server Instance to the Converted Database

    1. Using Microsoft SQL Server Management studio, add the service account that is used by the Microsoft Dynamics NAV Server instance (for example, NT AUTHORITY\NETWORK SERVICE) as a member of the db_owner role in the Microsoft Dynamics NAV database on SQL Server.
      For more information, Giving the account necessary database privileges in SQL Server.
    2. Using the Microsoft Dynamics NAV Server Administration tool, connect a Microsoft Dynamics NAV Server instance to the converted database.

    For more information, see How to: Connect a Microsoft Dynamics NAV Server Instance to a Database.

    Task 5: Run Schema Synchronization to initialize the database.

    You can run the schema synchronization from the Microsoft Dynamics NAV Development Environment or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Sync. Schema For All Tables, and choose With Validation, and then follow the schema synchronization instructions.

    If synchronization errors occur, run the schema synchronization from Microsoft Dynamics NAV 2015 Administration Shell as described in the following procedure. This enables you to get the complete list of tables that could not be synchronized.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    1. Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator.
    2. Run Sync-NavTenant cmdlet as follows:

    Sync-NavTenant –ServerInstance <ServerInstanceName> -Mode Sync

     Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database. For more information, see How to: Run the Sync-NAVTenant Cmdlet to Synchronize the Tenant Database with the Application Database.

    Task 6: Import the Application Objects to the Converted Database

    1. In the development environment, import all the application objects that you want in the Microsoft Dynamics NAV 2015 database.
      When you import the FOB file that contains the Microsoft Dynamics NAV 2015 objects, on the Import Worksheet, choose Replace All, and then choose the OK button to import the objects.
      On the dialog box for selecting the schema synchronization, set the Synchronize Schema option to Later.
    2. After you have imported Microsoft Dynamics NAV 2015 objects, import the upgrade toolkit FOB file Upgrade601800.[Country].fob from \UpgradeToolKit\Local Objects folder.
    3. Compile all objects that have not been compiled yet. On the Compile dialog box, set the Synchronize Schema option to Later.

    Task 7: Run the Schema Synchronization

    Similar to task 5, run the schema synchronization either from the development environment (Tools – Sync. Schema for All Tables – With Validation…) or from Microsoft Dynamics NAV 2015 Administration Shell.

    This will synchronize the data schema changes of the newly imported tables to the SQL tables. It will also run the Upgrade Toolkit Step 1 logic if it is defined in the upgrade codeunit. This step will be performed for all companies in the database, so you don’t have to re-run it for other companies.

    Note: After the schema synchronization has successfully completed, we strongly recommend that you take a full SQL Server backup of the database. 

    Task 8: Run the Data Upgrade to perform Upgrade Toolkit Step 2

    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 or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Data Upgrade, and then choose Start and follow the instructions on the Start Data Upgrade window.

    If you want to see the result of running the Data Upgrade action, run the following cmdlet from Microsoft Dynamics NAV 2015 Administration Shell:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Detailed | Out-GridView

    In the output of this cmdlet, you can see which upgrade codeunits and functions were run, which companies have been upgraded, the time it took to run each upgrade function, and a detailed description of errors that occurred during the data upgrade.

    If errors occurred while running the data upgrade, you can resolve them directly in the database (for example by updating the upgrade codeunits) and then resume the data upgrade. To resume the data upgrade, on the Tools menu, choose Data Upgrade, and then Resume.

    Note: To optimize upgrade performance and effectively use the available computer resources, the data upgrade functions are executed in parallel and across all companies by default. While performing this step, if you discover that some functions are locking each other from executing, then you can configure the data upgrade to execute data upgrade functions in serial instead of parallel. To do this, on the Start Data Upgrade window, set the Execution Mode option to Serial. For more information see http://msdn.microsoft.com/en-us/library/dn762348(v=nav.80).aspx.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator, and then run Start-NavDataUpgrade cmdlet as follows:

    Start-NavDataUpgrade -ServerInstance <ServerInstanceName> -Force

    Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database.

    To view the progress of the data upgrade, you can run Get-NavDataUpgrade cmdlet as follows:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Progress

    Note: The data upgrade process runs CheckPreconditions and Upgrade functions in the upgrade codeunits. If any of the preconditions are not met or an upgrade function fails, you must correct the error and resume the data upgrade process.

    Task 9: Delete the Upgrade Objects and Obsolete Tables

    At this point, you have upgraded the database to Microsoft Dynamics NAV 2015. Now, you can delete the tables which are not needed into the new application, plus the upgrade codeunits and upgrade table objects that you imported in task 6.

    For application tables, you can see which the tables are marked for deletion in the Version List column in the Tables list of Object Designer. During the data upgrade that you performed in task 8, these tables were marked with the text Old Unused Table - marked for deletion. Upgrade objects are marked with the text Upgrade Toolkit Object - marked for deletion.

    When you delete tables, if you don’t need the data that is saved in the tables, then on the Delete dialog box, set the Synchronize Schema option to Force.

    Task 10: 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

    1. 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.
    2. 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.
    3. 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.

    Task 11: 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.

    Task 12: 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 2015. 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.MicrosoftDynamicsOnlineConnectControl
    • Microsoft.Dynamics.Nav.Client.BusinessChart
    • Microsoft.Dynamics.Nav.Client.PageReady
    • Microsoft.Dynamics.Nav.Client.PingPong
    • Microsoft.Dynamics.Nav.Client.VideoPlayer
    • Interactive Timeline Visualization Add-in

    You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client. For more information, see How to: Register a Windows Client Control Add-in.

Page 3 of 51 (759 items) 12345»