Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    Using Web Services to Access Microsoft Dynamics NAV 5.0

    • 49 Comments

    Introduction

    Dynamics NAV 2009 contains a new subsystem for dealing with Web Services. This feature has been well received by partners and customers alike. Partners have expressed interest in having web services available for earlier versions of Dynamics NAV. This feedback resulted in a technology talk at Directions2007 in Florida, where the topic was what could be done to day. The conclusion of the talk was that everything we where intending to deliver was already possible today, yes some code is needed but strictly from function/feature perspective all of it is possible, and it is not even all that ugly. Dynamics NAV 2009 will provide out-of-the-box programmatic web service access to the application and will therefore remove the need for this additional technology plumbing described here.

    I have to say that the response to my talk has been tremendous. After the response to my talk on Web Services in NAV 5.0 and previous versions I decided to write this blog post and make the source files available.

    This post is about how to bridge the gap between the need for web services now and the current platform, it will help you understand how you can provide Web Services directly from Dynamics NAV today, in a “simple” and flexible way, already today.

    To work with the samples in this post you will need: Visual Studio 2005, Dynamics NAV 5.0 and .Net 3.0 installed on your system. This sample should work on Dynamics NAV 4.0 to but has not been tested on that version.

    Architecture

    The system we will build contains 4 different components/moving parts: Web Service Listener, Event Dispatcher, Codeunit Eventhandler and XMLPort for stream handling.

    image

    Web Service Consumer

    Any client that understands how to communicate with Web Services; like InfoPath, Visual Studio, SharePoint or any custom application written by you.

    Port

    Is the physical communication port that the WCF listens to.

    WCF Web Service

    Defines the data contracts and service contracts for the Web Service, it also implements the concrete service and opens for listening in the WCF subsystem, it then delegates the requests to the COM Event Dispatcher component.

    COM Event Dispatcher

    This component provides the hookups for Dynamics NAV, both to activate the service and to register event sinks. It defines 2 IDispatch interfaces the IServiceEvents and the IWebServiceListner, as well as the concrete implementation of the IWebServiceListner in the WebServiceListner class that provides the actual code for hooking up the WCF Web Service to Dynamics NAV.

    .NET

    We are using the CLR runtime for writing our Web Service component and our COM plugin. Some of this blog entry is about interop between Dynamics NAV and .NET through COM.

    Codeunit Event Handler

    Is responsible for starting up the WCF Web Service through the COM interface, it then registered for events coming from the WCF Web Service Component. The events routed to XMLPort for processing.

    XMLPorts for datastreams

    It deals with the actual business logic and data coming from or going to the Web Service.

    Implementation

    The implementation is in 2 programming languages: C# and C/AL.

    Please take a look at the provided code sample, for the rest of the information contained in the posting. It can be found here: http://code.msdn.microsoft.com/nav/Release/ProjectReleases.aspx?ReleaseId=896

    I have included comments in the code that should explain what is going on, if you feel something is missing, first look at the documentation for the WCF or post a comment to this post and I will try to answer it.

    Deployment of Sample

    To deploy the sample you will first have to download it, unpack it.

    Then open it up with Visual Studio and compile.

    Then import the codeunit.txt and xmlport.txt into your NAV installation and compile those objects, starting with the XMLPort

    To run the service simply open the Object Designer in NAV, find the Codeunit that you just imported and press run.

    There is no dependency on IIS or other external components. No further deployment steps should be needed.

    In the Visual studio solution is a ConsoleTestApp project. After you have followed the steps above you can run that project, it will test if your install was successful, as well as provide sample on how to use the web service.

    Special considerations

    In this sample I’m using XMLPort to handle the XML stream that is provided.

    You can take many different approaches to this, and still reuse large please of the code provided in the sample.

    To use the XMLPort as handler you will have to set the encoding property to UTF-8. This is due to a null termination bug in stream handler in NAV.

    image

    image

    With this approach you can already today, incorporate web services in your projects in straightforward way.

    The appropriate usage is whenever you need to give external application access to Dynamics NAV data or business process.

    For any questions or comments please feel free to ask them in the comment section of this blog post.  I will answer questions to best of my ability on this post in the comments section as well.

    One last thing:  This is a sample code.  It has not been tested, you should thoroughly test this code before usage.

    Best regards,
    Kris Rafnsson

  • Microsoft Dynamics NAV Team Blog

    Upgrade Toolkit for Upgrading Data from Microsoft Dynamics NAV 2009 R2 and Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2013 R2

    • 53 Comments

    The components needed to upgrade data directly from Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2 are now available for download.

    Download the upgrade toolkit from PartnerSource or from CustomerSource

    In order to use the toolkit to upgrade data from Microsoft Dynamics 2009 R2 or Microsoft Dynamics NAV 2009 SP1, you will also need the Microsoft Dynamics NAV 2013 development environment and Microsoft Dynamics NAV 2013 R2.

    At the Directions EMEA conference in Vienna and the Directions US conference in Nashville, we said that we have a team that is focusing on the Microsoft Dynamics NAV upgrade story. Going through the big transformation from the classic stack to the new product architecture in Microsoft Dynamics NAV 2009 was not easy and is challenging many customers and partners. So for our team, it is essential that the final outcome of our work is that each upgrade is easier, requires significantly less effort, and that it does not disrupt the daily business for our customers.

    In Microsoft Dynamics NAV 2013 R2, we introduced support for converting a Microsoft Dynamics NAV 2013 database so that you can run an application that was created in Microsoft Dynamics NAV 2013 on the Microsoft Dynamics NAV 2013 R2 platform, also known as a technical upgrade.

    Note: We strongly recommend that you upgrade the application objects as well so that your solution includes the important application fixes and new functionality that is introduced in Microsoft Dynamics NAV 2013 R2.

    Secondly, you can fully automate the data upgrade process using the Windows PowerShell scripts that we included in the Microsoft Dynamics NAV 2013 R2 product media. Historically, this process has been known for its tediousness and high risk of human error when multiple operations had to be executed consecutively in all companies of the database that you were upgrading. The scripts automate this work so that you can test and execute your data upgrades more reliably.

    Now with this delivery, we introduce an upgrade toolkit for upgrading the data from Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2. This significantly simplifies the upgrade process for those of you coming from Microsoft Dynamics NAV 2009 R2 – or Microsoft Dynamics NAV 2009 SP1.

    Included in the new upgrade toolkit are all known data upgrade-related application hotfixes that we are aware of, and we also addressed several platform issues that affected the upgrade scenario.

    Note: You must download the latest Microsoft Dynamics NAV platform hotfixes before you start using the upgrade toolkit. The required hotfixes for Microsoft Dynamics NAV 2013 are available in the latest hotfix rollup, which you can download from PartnerSource or CustomerSource. For Microsoft Dynamics NAV 2013 R2, you can download the required hotfixes from PartnerSource or CustomerSource.

    When you download the new upgrade toolkit from the link above, you can use it to simply your upgrade process. Here are the main steps in upgrading from Microsoft Dynamics NAV 2009 R2 (or Microsoft Dynamics NAV 2009 SP1) by using the new upgrade toolkit.

    Note

    Before you start, make sure that you use the latest platform binaries for all versions of Microsoft Dynamics NAV in this process. For more information, see the following pages for the latest updates to the versions of Microsoft Dynamics NAV:

    Microsoft Dynamics NAV 2009 R2: Overview of Released Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2.

    Microsoft Dynamics NAV 2013: Released Cumulative Updates for Microsoft Dynamics NAV 2013.

    Microsoft Dynamics NAV 2013 R2: Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2.

    To upgrade data from a Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database

    1. In the Microsoft Dynamics NAV  2009 R2 or Microsoft Dynamics NAV  2009 SP1 development environment (Classic client):
      1. Make sure that all table objects have compiled successfully. During compilation, Microsoft Dynamics NAV generates or regenerates the BLOB content in the Object Metadata table that is used in the later steps.  In the Tools menu, choose Build Server Application Objects.
        Note: You must also do this if you upgrade from Microsoft Dynamics NAV 2013 to Microsoft Dynamics NAV 2013 R2 . All tables must be compiled Microsoft Dynamics in NAV 2013 before you start upgrading to Microsoft Dynamics NAV 2013 R2.
      2. Create a copy or a backup of your old Microsoft Dynamics NAV 2009 R2 database, and open it in the Microsoft Dynamics NAV 2009 R2 development environment.
      3. Add your 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 from the License Information window.
      4. Open the Object Designer, and then import Upgrade601701.1.fob. If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
      5. For each company in the database, 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. Then, from the Object Designer, run form 104001, Upgrade - Old Version. Choose the Transfer Data button. Repeat this action for each company in the database. 
      Note
      We strongly recommend that you back up the database using SQL Server management tools after this step. 
    3. 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 2013 R2.
    4. Uninstall Microsoft Dynamics NAV 2009 R2, and then install Microsoft Dynamics NAV 2013.
      Note
      You do not have to install the full Microsoft Dynamics NAV 2013 product to perform the following database conversion. Instead, you can create a folder with the following files from the Microsoft Dynamics NAV 2013 product media:
      • Finsql.exe
      • Fin.stx
      • Fin.etx
      • CRONUS.FLF
      • Ndbcs.dll

      You can then use the Finsql.exe file to perform the conversion.  

    5. Change the compatibility level of your database. For SQL Server 2008 and SQL Server 2008 R2, verify that the compatibility level of the database is set to 100. For SQL Server 2012, set the compatibility level to 110.
    6. In the Microsoft Dynamics NAV 2013 development environment, open the Microsoft Dynamics NAV 2009 R2 database and agree to convert the database. The database has now been technically upgraded to Microsoft Dynamics NAV 2013.

      Note 
      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. If you converted your database using the RTM version of Microsoft Dynamics NAV 2013, you may be experiencing collation-related issues after this conversion. Therefore it is strongly recommended to use the latest available version of Microsoft Dynamics NAV 2013 development environment to perform this step.

      Note
      We strongly recommend that you take a full SQL Server backup at this stage when the database conversion has completed.

    7. Uninstall Microsoft Dynamics NAV 2013, and then install Microsoft Dynamics NAV 2013 R2.
    8. In the Microsoft Dynamics NAV 2013 R2 development environment:
      1. Open the Microsoft Dynamics NAV 2013 database and agree to convert the database.
      2. Compile the system tables. You can find the tables by setting a filter for table ID 2000000004..2000000130.
        In the following step, you will be connecting a Microsoft Dynamics NAV Server instance to the database. 
    9. Make sure that the service account that the Microsoft Dynamics NAV Server instance uses has the db_owner role for the database. 

      Note 
      If you are upgrading a large database, such as a database size of more than 20-25 GB, make sure that you increase the timeout value in the Microsoft Dynamics NAV Server configuration file. In the CustomSettings.config file, the value is defined in the SQL Command Timeout node. The default value is 10 minutes, which is sufficient in the normal day-to-day work. However, during an upgrade that can take several hours depending on the size of the tables, you must increase the timeout period.

    10. Connect the Microsoft Dynamics NAV Server instance to the database, and then start the service instance.
    11. In the Microsoft Dynamics NAV 2013 R2 development environment:
      1. On the Tools menu, open Options, and then, make sure that the Prevent data loss from table changes field is set to Yes.You must also make sure that the Server Name, Server Instance, and Server Port fields are filled in.
        This ensures that the Microsoft Dynamics NAV Server instance that is connected to the database will verify that no operation will cause loss of data in the development environment.
      2. Import all customized objects in .fob format into the upgraded database. If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
      3. Make sure all objects are compiled.

        Important
        It is very important that at least all table objects have successfully compiled before you proceed.

      4. Import Upgrade Step 2 objects from the Upgrade601701.2.fob file.If the .fob file contains objects with conflicting versions that are already in the database, the Import Worksheet window opens. Choose Replace All.
    12. Perform the database schema synchronization by running the Sync-NAVTenant Windows PowerShell cmdlet in the Microsoft Dynamics NAV 2013 R2 Administration Shell (run as administrator).

      Note
      When you upgrade from one version to another, this typically involves numerous changes in the structure of the Microsoft Dynamics NAV tables (the object metadata). Database synchronization ensures that these changes in the metadata are applied to the corresponding tables in SQL Server. For example, it changes columns dimensions and data types, drops and create indices, drops and creates indexed views (V-SIFT), creates new tables, columns, and so on.

      When the number of changes is high, such as comparing Microsoft Dynamics NAV 2009 R2 to Microsoft Dynamics NAV 2013 R2, this synchronization can become a lengthy and resource consuming task.

      Database synchronization is triggered upon any request to Microsoft Dynamics NAV Server, such as when you start a client or run a windows Powershell cmdlet. Microsoft Dynamics NAV Server checks if metadata has changed since the last synchronization by comparing the content of the Object Metadata and Object Metadata Snapshot system tables. If a change is found, the synchronization procedure is initiated.  

      When the synchronization process has started, it is essential that you wait for it to complete or rollback (in case of an error or time-out). If you’re running a client, do not confirm or click anything client side. If you’re running the Sync-NAVTenant cmdlet, wait for it to complete and return control to the Windows PowerShell command prompt.

      Do NOT stop the Microsoft Dynamics NAV Server service at this point since there are high chances that the database synchronization transaction is still running.

      1. Run Microsoft Dynamics NAV 2013 R2 Administration Shell as Administrator. This opens a PowerShell prompt where the Microsoft Dynamics NAV cmdlets are available.

      2. Run the synchronization command against your upgrade database as follows: 

        Sync-NAVTenant –ServerInstance <MyNAVServerInstance> 

      3. Wait until the cmdlet returns control to the Windows PowerShell window.

    13. Open the Microsoft Dynamics NAV 2013 R2 Windows client to verify that you are connected to the Microsoft Dynamics NAV Server instance that is connected to the database being upgraded. 
      Next, you will run Upgrade Step 2 in each company in the upgraded database. You can get a list of all existing companies in the database by running the Get-NAVCompany cmdlet and passing as an argument the Microsoft Dynamics NAV Server instance which is connected to the upgraded database. 
      You will run objects directly from the development environment, so you must specify the company that the objects must run in in the Options window.
    14. In the Microsoft Dynamics NAV 2013 R2 development environment:
      1. On the Tools menu, open Options, and then, in the Company field, specify the name of the first company.
      2. In the Object Designer, find page 104002 Upgrade - New Version, and then choose Run.
      3. In the Upgrade - New Version window, choose Test Database Connection to make sure that the C/AL code that is triggered by the actions on the page has access to the database.
        If your database is on a named SQL Server instance, you must specify the full name in the SQL Server Name field.  
      4. Choose Transfer Data.
        If the process is successful, and you don't have to revisit the upgrade logs, you can clean the content of the Upgrade Time Log  table. 
      5. On the Navigate tab, choose Time Log, and then, in the Upgrade Time Log page, delete all records.
      6. Close the Upgrade - New Version window, and then close the Microsoft Dynamics NAV Windows client.
    15. Repeat step 14 for each remaining company in the database.
    16. When you have successfully transferred data in the last company, you must upgrade data that is common to all companies in the database, such as permissions, permission sets, web services, profiles and control add-ins.
    17. Upgrade data common to all companies such as permissions, permission sets, web services, profiles and control add-ins.
      If the customer has changed the Read/Write/Modify/Delete/Execute settings for any of the standard permissions, or customized default permission sets in any way, you must merge these changes into the default permissions sets and permissions that are included in Microsoft Dynamics NAV 2013 R2. You can use XMLport 104001 Import/Export Roles and XMLport 104002 Import/Export Permissions to export the new default roles and permissions from the CRONUS International Ltd. demonstration database in Microsoft Dynamics NAV 2013 R2. 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. For example, the following client control add-ins are available from the Microsoft Dynamics NAV 2013 R2 product media:
      • Microsoft.Dynamics.Nav.Client.BusinessChart
      • Microsoft.Dynamics.Nav.Client.PageReady
      • Microsoft.Dynamics.Nav.Client.PingPong
      • Microsoft.Dynamics.Nav.Client.VideoPlayer
    18. Delete the upgrade toolkit objects.
      1. In the Upgrade - New Version window, choose Mark/Delete Upgrade Toolkit.
        This deletes all upgrade toolkit objects, except tables
      2. In the Microsoft Dynamics NAV development environment, delete the upgrade tables by setting a field filter for objects where the Version List contains Upgrade Toolkit Table - marked for deletion.

    The database has now been through a data upgrade to Microsoft Dynamics NAV 2013 R2.

    Best regards,

    The Microsoft Dynamics NAV Service Experience team

    -----Blog post updated in May 2014-----

     

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV/SQL Server Configuration Recommendations

    • 12 Comments

    Michael De Voe, a Senior Premier Field Engineer at Microsoft, has compiled a set of recommendations for SQL Server configuration to improve performance when running Microsoft Dynamics NAV 5.0 and later versions with one of the following versions of SQL Server:

    • Microsoft SQL Server 2005 SP3 x64
    • Microsoft SQL Server 2008 SP1 x64
    • Microsoft SQL Server 2008 R2 x64

     The attached document contains Michael's recommendations, including the following options and parameters:

    • Max Server Memory
    • Auto-Create Statistics
    • Auto-Update Statistics
    • Auto-Grow
    • Database Compatibility Level
    • Trace Flag 4136
    • Trace Flag 4119
    • Data files for TempDB
    • Disk Alignment
    • Read Committed Snapshot Isolation (RCSI)
    • Max Degree of Parallelism
    • Dynamics NAV Default Isolation Level
    • Dynamics NAV "Lock Timeout"
    • Dynamics NAV "Always Rowlock"
    • Maintenance Jobs
    • Instant File Initialization
    • Optimize for Ad Hoc Workloads
    • Page Verify
    • Lock Pages in Memory

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

  • Microsoft Dynamics NAV Team Blog

    Visual Studio 2010 and SSRS (RDLC) reports in NAV 2009

    • 5 Comments

    A couple of weeks ago Microsoft Visual Studio 2010 was released. Unfortunately we will not be able to support Visual Studio 2010 in Dynamics NAV 2009 when developing SSRS (RDLC) reports.

    When you are designing RDLC reports in NAV 2009 you are creating reports in a format known as the “RDL 2005” format.
    Both Visual Studio 2005 and 2008 supports this format, but not Visual Studio 2010. Visual Studio 2010 is able to open the “RDL 2005” format but will immediately convert the report to “RDL 2008” format. This conversion gives us the some challenges:

    1. To view an “RDL 2008” format report you will need to have to have the “Microsoft Report Viewer 2010” installed on all Role Tailored client (RTC) machines, currently the “Microsoft Report Viewer 2008” is installed by the NAV 2009 installation program.
    2. If we opened up for Visual Studio 2010 you will convert the reports you design to “RDL 2008” format, and leave the unopened reports in “RDL 2005” format. So i.e. are developing an add on and share this with other partners you will need to inform these partners that Report Viewer 2010 is a requirement for all RTC machines. And if these partners are to modify any of your reports, Visual Studio 2010 is a requirement as well.
    3. We need change our code to now compile to “RDL 2008” format when importing the RDLC layout back to NAV.

    So to avoid this confusion we will not open up for Visual Studio 2010 support before our next major version of Dynamics NAV.

    If you for other reasons want to use Visual Studio 2010 you can easily have Visual Studio 2008 and Visual Studio 2010 installed on the same machine. We will just open the Visual Studio 2008 version when you select “View / Layout” in the Object Designer.

    If you only have Visual Studio 2010 installed you will see this message:

    An error occurred when opening Report Designer. A supported version of Visual Studio could not be found.

    This is the same message you get when you have no Visual Studio installed, because we search for the following Visual Studio versions and in prioritized order. So in case you have both Visual Studio 2005 and 2008 installed, we will use the 2008 version:

    1. Microsoft Visual Studio 2008
    2. Microsoft Visual Web Developer 2008 Express edition with SP1.
    3. Microsoft Visual Studio 2005 with SP1
    4. Microsoft Visual Web Developer 2005 Express edition with SP1.

    In a previous blog post I outlined the Visual Studio options you have for designing RDLC reports for NAV 2009, and this list is still valid.

    Hint: If you are using Windows 7 and want to use “Microsoft SQL Server 2008 Express with Advanced Services ” you need to install “Microsoft SQL Server 2008 R2 Express with Advanced Services” to have a successful installation.

    Thanks,

    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Creating a web service manually, the importance of the name you give it, and a few small things to remember

    • 14 Comments

    When you use the SC command line command to create a new NAV 2009 Service, how does the new service know whether it is a middle tier for RTC to connect to, or whether it is supposed to handle web service calls?

    In other words, what decides whether the new service will be "Microsoft Dynamics NAV Server" or "Microsoft Dynamics NAV Business Web Services"?

     

    It depends on the name. If it starts with "MicrosoftDynamicsNavWS", then it will be for Web Services. If the name starts with anything else, then it will be for middle tier for RTC clients.

     

    To keep things simple, just give your NAV Servers names beginning with MicrosoftDynamicsNAV / MicrosoftDynamicsNAVWS. Then if you need a second, third, etc server, add a unique name, seperated by a $-sign, for example:

    MicrosoftDynamicsNAV$Svr2

    MicrosoftDynamicsNAVWS$Svr2

     

    Here are the simple steps for how to create a new web service service, and a few more things to be aware of. Let's say that we want to start a second set of NAV Servers.

     

    First create the normal service from a command prompt:

    SC CREATE "MicrosoftDynamicsNAV$Svr2" binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service2\Microsoft.Dynamics.Nav.Server.exe" DisplayName= "MSSvr2"

    Then create the service for Web Services: 

    SC CREATE "MicrosoftDynamicsNAVWS$Svr2" binpath= "C:\Program Files\Microsoft Dynamics NAV\60\Service2\Microsoft.Dynamics.Nav.Server.exe $Svr2" DisplayName= "MSWSSvr2" type= share

     

    The additional settings you must provide as marked in bold above, and a few things that you must remember are:

     

    1)  The Name

    As described, the name must begin with MicrosoftDynamicsNAVWS if you want it to be for web services

     

    2)  Include the last part of the name in BinPath

    After the .exe in the binpath parameter you must specify the part of the name ($Svr2 in this case) that comes after MicrosoftDynamicsNAVWS. If you forget this step, you might get this error when you try to start the service:

    Windows could not start the MSWSSvr2 service on Local Computer.Error 1083: The executable program that this service is configured to run in does not implement the service.


     

    3)  Type must be share

    For the service that handles web services, add the parameter type= share. Otherwise the service will still try to start up as a middle tier (not for web services).

     

    4)  Spaces after =

    You must remember the space after each = in the command, as in for example "type= share". This is just the syntax of the SC-command.

     

    5)  DisplayName

    It doesn't matter what display name you give - this is just to find it in Services.

     

    These are just some of the small things to keep in mind. For many more details on web services go to Freddys blog, especially this post:

    Multiple Service Tiers - SP1

     

    // Lars Lohndorf-Larsen

     

  • Microsoft Dynamics NAV Team Blog

    Send email with PDF attachment in NAV 2009

    • 13 Comments

    In this post I would like to explore the possibilities to create an email from the Role Tailored client and attach an invoice as a PDF file to the email, unfortunately we have do not have this functional build into our Demo application, but let me show you how this can be do with little effort.

    First I suggest you download the fob file which contains the 5 different options I will go through here.

    When downloaded the fob file you will see that I have added 5 new actions

    image

    1. SendAsPDF(Use of codeunit to rename) recommended solution
    2. SendAsPDF(Access to Server needed)
    3. SendAsPDF(With Temp file name)
    4. SendAsPDF(User prompted to save)
    5. SaveAsPDFrecommended solution(if you just want the PDF file)

    Let me go through the different options starting from the bottom, since I recommend option 1, but I would also like to share other options for doing this, since these might be valuable for you.

    Option 5: SaveAsPDF
    In this option you will get prompted if you want to open or save the PDF. The PDF file created will be based on the select Invoice in the Posted Sales Invoices List Place

    image 

    In this option all I do is to have the server create the PDF file for me and use the new download function in NAV 2009 to retrieve the PDF file created on the server.

    Option 4: SendAsPDF(User prompted to save)
    In this option, you will first be prompted to save the file.
    Here it is important to select to “”SAVE” the PDF file on the disk, to have the correct name of the PDF file. If you select to “OPEN” the PDF filename will be given a temp name.

    After you have saved the PDF we now create the email message you will get 3 messages similar to this when this happens:

    image

    You get these message because we connect to an external component(Outlook) to the Role Tailored client. It is of course up to you if you want to set this to “Always allow”, but this would remove these messages, the next time you open the Role Tailored client.

    When you have allowed these to run, email will be created with the PDF file attached.

    image

    In this option all I do is to download the PDF to the client and then use the Mail codeunit to create the email

    Option 3: SendAsPDF(With Temp file name)
    In this option, you will not be prompted to save the PDF file.
    And the email will be created immediately. This would probably be the preferred compared to downloading this to the user disk, but we will use the PDF file created on the server, and since this file get a TEMP name, like this: “__TEMP__570eb0279b9d4b1fa837caf3a14acbf7” this option is not really good.

    Let us look at the option 1 and 2 where this issue is solved.

    Option 2: SendAsPDF(Access to Server needed)
    In this option you will not be prompted to save the file either, but here the end user will need to have access the server folder where the PDF is stored on the server. In some situation you might want this, but for security reasons you might also not want to give this access to the user.

    Option 1: SendAsPDF(Use of codeunit to rename) recommended solution
    Again in this option you will not be prompted to save the PDF file, but the PDF file will be automatically added to the email. In this option we have build a codeunit to rename the TEMP file created on the server, and end user will not need to have access to any folders on the server.

    So all in all I recommend option 1 for attaching PDF file to an email. And once again I have made all the code available here, so feel to be explore how I build this. If you feel there is an option that I missed, feel free to leave a commit or use the contact form Email.

    Thanks,
    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Using XMLports With Web Services

    • 11 Comments

    As a follow-up on my recent webcast (found HERE), here is the general walkthrough of how to create an XMLport and use it for sending data to NAV.

    First, what we want to do is create our XMLport and make sure it has the elements and values that we want.

    XMLPort

    For the root element, I have set maxOccurs = 1 to avoid any confusion.
    For the general XMLport, the UseDefaultNamespace and the DefaultNamespace values have been edited as seen below.

    SS02

    Other than that, I have no code on my XMLport, but naturally, anything goes that would work on a regular XMLport. Now to the Codeunit:


    ImportDim(VAR DimImport : XMLport DimImport) Return : Text[30]
    DimImport.IMPORT;
    EXIT('Import Run');

    So basically we’re telling the XMLport to run an import and we’re returning to the Web Service that we’ve run. All we need to do now is expose the Web Service using Form 810:

    SS03

    Remember that the actual name of the codeunit does not have to match that of the service name here.

    So now we move over to Visual Studio and start working with what we have. The first thing we’ll notice is that the WSDL matches our XMLport.

    SS04

    What we see is both the RootDimensions element which consists of multiple Dimension elements. From there, we can see the definition of the Dimension element the fields we’ve chosen to expose.

    When creating a new project, we will go with a Windows Forms project this time.

    SS05

    And from there we will start off by adding a web reference to http://localhost:7047/DynamicsNAV/WS/Codeunit/DimensionImport .

    The details on how to add a web reference can be found in the Developer and IT Pro Documentation.

    On my new form, I have created two input boxes for the Code and Name of the dimension and a Create button.

    SS06

    And then we have the code on the Create button, along with helpful comments:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms; 

    namespace
    NAV2009SP1WSDemo

        using WSDI; 
        public partial class Form1 : Form 
        {    

     

     

     

            public Form1() 
           
                InitializeComponent(); 
           
     
            private void button1_Click(object sender, EventArgs e) 
           
                //Make sure the ResultLabel doesn't have any text on multiple runs 
                ResultLabel.Text = ""

               
    //Create a WS reference instance, set credentials and define the company by specifying the URL. 
                DimensionImport NAVImport = new DimensionImport(); 
                NAVImport.UseDefaultCredentials = true
                NAVImport.Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20International%20Ltd/Codeunit/DimensionImport"
                
                //First we create our root element 
                RootDimensions RootDim = new RootDimensions();

     

     

     

                //Then we create a List to handle our (possible) multiple dimensions 
                List<Dimension> DimList = new List<Dimension>();

     

     

     

                //And then we create a single dimension 
                Dimension Dim = new Dimension();  

     

                if (dimInputCode.Text != "" && dimInputName.Text != ""
               
                    //We assign the values from our textboxes to the single dimension 
                    Dim.DimensionCode = "AREA"
                    Dim.Code = dimInputCode.Text; 
                    Dim.Name = dimInputName.Text;

     

     

     

                    //Then we add the single dimension to our list 
                    DimList.Add(Dim);  

     

                    //To get the list of dimensions "attached" to the root element, we use the following
                    RootDim.Dimension = DimList.ToArray(); 
                    try 
                   
                        //Then we send to NAV and show our result 
                        ResultLabel.Text = NAVImport.ImportDim(ref RootDim);

     

     

     

                   
                    catch (Exception ex) 
                   
                        //Show a possible exception 
                        ResultLabel.Text = ex.ToString(); 
                   
               
                else 
               
                    //Make sure there are values 
                    ResultLabel.Text = "Both values must be filled"
               
            
        }
    }

    Our wonderful application is now ready to run and all we have to do is press F5:

    SS07

    We have now created our XMLport, exposed it using a codeunit and set data into it from a form based application.

    Lars Thomsen

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Microsoft Visual Studio 2013 Now Supported for RDLC Report Design

    • 2 Comments

    We are happy to announce that we have added support for using Microsoft Visual Studio 2013 (Professional, Premium, and Ultimate editions) to create and modify RDLC reports. This lets you use the latest version of Microsoft Visual Studio when designing reports for Microsoft Dynamics NAV 2013 R2.

    To enable Microsoft Visual Studio 2013 support, you must install the hotfix from the Knowledge Base article 2907585. After the hotfix is installed, when you design reports from the Microsoft Dynamics NAV Development Environment, Microsoft Visual Studio 2013 is automatically used if it is available on the computer running the development environment; otherwise, Microsoft Visual Studio 2012 is used if it is available. There is no option to choose which version of Microsoft Visual Studio to use if both Microsoft Visual Studio 2013 and Microsoft Visual Studio 2012 are installed on the development environment computer. With regard to editing RDLC reports for Microsoft Dynamics 2013 R2, there is no functional difference between using Microsoft Visual Studio 2012 or Microsoft Visual Studio 2013, and RDLC reports that are edited with Microsoft Visual Studio 2013 can be edited later with Microsoft Visual Studio 2012 if needed.

     

    Best regards,

    the Microsoft Dynamics NAV team

  • Microsoft Dynamics NAV Team Blog

    Updated: How to log report usage

    • 28 Comments

    Updated: Now with links to both NAV 5.0 and NAV 2009 hotfixes

     /Claus Lundstrøm

    ******

    In several partners meetings I have heard this requests over and over again.

    Claus, when we are planning an upgrade of Classic reports to RDLC reports, we do not always know which reports are actually being used at the customer site. It would be great to be able to log which reports are used so we know exactly which reports we need to upgrade to RDLC.

    Well, if you download below hotfix you will now have the capability to log report usage at a customer site.

    Dynamics NAV 5.0: KB2575296

    Dynamics NAV 2009: KB2558650
    Find links to all NAV 2009 Platform Hotfixes here:
    CustomerSource: Overview of Released Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2
    PartnerSource: Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2

     

    In the following steps I have outlined which steps you need to do to get this log file. Remember above hotfix is required to perform the following steps.

    1. Create new table to be used for Log report usage:

    clip_image002

    OBJECT Table 50000 Report Log
    {
    OBJECT-PROPERTIES
    {
    Date=22-06-11;
    Time=14:19:58;
    Modified=Yes;
    Version List=CLAUSL;
    }
    PROPERTIES
    {
    }
    FIELDS
    {
    { 1 ; ;No. ;Integer ;AutoIncrement=Yes;
    MinValue=1 }
    { 2 ; ;User ID ;Code50 ;TableRelation="User Role"."Role ID";
    CaptionML=ENU=User ID }
    { 3 ; ;Report ID ;Integer ;CaptionML=ENU=Report ID }
    { 4 ; ;Report Name ;Text249 ;FieldClass=FlowField;
    CalcFormula=Lookup(AllObjWithCaption."Object Caption" WHERE (Object Type=CONST(Report),
    Object ID=FIELD(Report ID)));
    CaptionML=ENU=Report Name }
    { 5 ; ;Date Time ;DateTime }
    }
    KEYS
    {
    { ;No. ;Clustered=Yes }
    }
    FIELDGROUPS
    {
    }
    CODE
    {
    BEGIN
    END.
    }
    }

    2. Now with the table created for our Report Usage log please open Codeunit 1

    3. Open “C/AL Globals”

    4. Navigate to ”Functions”

    clip_image004

    5. Create new function with Name=”OnReportRun”

    clip_image006

    6. Open Properties and change ID to 120

    clip_image008

    7. Now open “Locals” and create parameter= ReportId with Type=Integer

    clip_image010

    8. Select Variables tab and create ReportLog

    clip_image012

    9. Now all we need is to write the code for this new trigger. Open C/AL Editor and navigate to the end.

    10. In OnReportRun write the following code:

    ReportLog."User ID" := USERID;

    ReportLog."Report ID" := ReportId;

    ReportLog."Date Time" := CURRENTDATETIME;

    ReportLog.INSERT;

    clip_image014

    11. Now Restart Classic Client

    12. Run a couple of reports

    13. And then at last run the Report Log table to see the result:

    clip_image016

    Yes I’m aware that this solution only works for Classic Reports, and yes I also would like a feature so it’s possible for you to log all objects being used at a customer site. For now we do not get this, but let’s see what the future brings.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    The New Table Synchronization Paradigm in Microsoft Dynamics NAV 2013 R2

    • 34 Comments

    Microsoft Dynamics NAV 2013 R2 was dispatched with a brand new feature that introduces big challenges to all of the Microsoft Dynamics NAV channel: Multitenancy. In simple words, multitenancy allows partners to deal with hosting scenarios on-premises and in cloud services in an easier and more flexible way than in the past.

    Before Microsoft Dynamics NAV 2013 R2, partners and customers could only use a single-tenant scenario (here also called Legacy mode).

    Below a short explanation how table synchronization used to work in earlier versions.

    Microsoft Dynamics NAV 2009 / Microsoft Dynamics NAV 2013

    1. At object import/compile, C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made.
    2. Schema changes are DIRECTLY APPLIED to the SQL Server database by C/SIDE if there is no breaking schema change, otherwise an error will be thrown by C/SIDE depending on SQL Server error catch.
    3. Object Change Listener is checking for changes in metadata, then updating Microsoft Dynamics NAV Server Cache with data from Object Metadata table if the change was detected.

    A synchronization failure would typically be reported with an error like “The Object Metadata does not exist. Identification and values … “ when running the Microsoft Dynamics NAV Windows client.

    The multitenancy feature has also changed the design how Microsoft Dynamics NAV developers has to deal with object changes, overall related to table objects. Multitenancy implies that the table structure definition has to be stored in the application database and this needs to be applied on one or more separate storage databases called Tenants. From a development perspective, this means that any modification that are made to a table object in C/SIDE are NOT DIRECTLY applied to the SQL Server structure but there is a need of performing a secondary action to apply and made persistent these modification at SQL Server side: this process is called Synchronization. Microsoft Dynamics NAV 2013 R2, then, comes with a time decoupling between table metadata creation (C/SIDE) and data structure changes (SQL Server).

    In order to simplify the current design, the Microsoft Dynamics NAV development team decided to handle single- and multitenant scenarios in the same way (roughly speaking a single-tenant / Legacy mode is handled as a multitenant scenario with a single tenant database constantly mounted against an application database).

    Below a short explanation how this is working in practice.

    Microsoft Dynamics NAV 2013 R2

    SCENARIO 1:

    • Single-tenancy / Legacy mode
    • “Prevent data loss from table changes” = Yes (default):

     

    1. At object import/compile, C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made. (Same as in Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2013)
    2. C/SIDE then CALLS THE Microsoft Dynamics NAV Server to check for breaking schema changes in SQL Server structure.
      If C/SIDE is unable to call the Microsoft Dynamics NAV Server or if a breaking schema change is attempted (action that cannot performed due to the current SQL Server structure such as deleting a field containing data): a C/SIDE error is reported accordingly and changes to Object Metadata table will not be committed.
      If it is evaluated as not attempting a breaking schema change in SQL Server then metadata from C/SIDE working memory is saved and committed to Object Metadata table.
      PLEASE NOTE: at this stage NO CHANGES ARE MADE TO THE SQL SERVER DATA STRUCTURE.
    3. When prompting for SYNCHRONIZATION, Microsoft Dynamics NAV Server then compares Object Metadata table with Object Metadata Snapshot table content. Any difference in the value for the “Hash” field is a flag to Microsoft Dynamics NAV Server that a change exists and should be subsequently applied physically SQL Server side as structural changes.

    Prompting for Synchronization happens when

    -         Performing ANY Microsoft Dynamics NAV client action.

    For example, if a user opens a Microsoft Dynamics  NAV Windows client, then Microsoft Dynamics NAV Server is starting applying the relevant structure changes to SQL Server, and the Microsoft Dynamics NAV Windows client is not shown until all the changes are done on SQL Server side.

    OR

    -         Running the Sync-NAVTenant Windows PowerShell cmdlet.

    SCENARIO 2 (DEPRECATED):

    • Single-tenancy / Legacy mode
    • “Prevent data loss from table changes” = No (Manually opted, not persistent)

    IMPORTANT NOTICE:

    Setting the “Prevent data loss from table changes” C/SIDE switch to “No” has been intended to be used as last resource in a pure multitenancy scenario and in Test or Staging environments when partners does not have any business data database mounted against the application database. All other usages that deviate from this statement might lead to unpredictable results and even undesired data loss scenarios in upgrades or, even worse, production environments.

    Never change for any reason this parameter to “No” when developing against a single-tenant / Legacy mode database.

    1. At object import/compile: C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made. (Same as in Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2013)
    2. C/SIDE DOES NOT CHECK FOR ANY BREAKING SCHEMA CHANGES IN SQL SERVER but simply FORCES COMMIT of metadata from C/SIDE cache TO the Object Metadata table.
    3. When prompting for SYNCHRONIZATION, Microsoft Dynamics NAV Server then compares Object Metadata table with Object Metadata Snapshot table content. Any difference in the value for the “Hash” field is a flag to Microsoft Dynamics NAV Server that a change exists and should be subsequently applied physically SQL Server side as structural changes.

    Since no validation is made against SQL Server (“Prevent data loss from table changes” was set to “No”) there might be chances that this will result in:

    • Data Loss
      There are few specific cases where data is dropped in this scenario:
      • The primary key is detected as being no longer unique
      • Data per Company is changed from Yes to No and more than one company contains data
      • One or more fields are deleted
      • One or more field data type is/are changed
    • Missing Synchronization
      Activities cannot be completed since SQL Server prevents these actions that would break the data structure and therefore no Microsoft Dynamics NAV Windows client or Web client can connect to the database. The partner or customer has to resolve these missing synchronization issues before moving forward or fall back to a backup where these issues does no longer exists

    SCENARIO 3:

    • Multitenancy
    • “Prevent data loss from table changes” = Yes (default):

    Same as Scenario 1 for point 1. and point 2.

    When prompting for SYNCHRONIZATION, changes will be triggered and applied to the SQL Server data structure.

    Prompting for synchronization in a pure multitenant deployment happens when

    -         Performing ANY Microsoft Dynamics NAV client action

    OR

    -         Running the Sync-NAVTenant Windows PowerShell cmdlet

    OR

    -         Mounting a tenant database

     

    Based on the scenario depicted above, there might be risks of data loss and/or missing synchronization issues if handling C/SIDE development (namely dealing with Table objects) in a way that deviate by the prospected paradigm.

    Data loss issues:

    These might arise typically in one of the following scenarios:

    • Direct removal of rows from the Object Metadata table in SQL Server
    • Stretched / Borderline scenarios that implement platform files with a Build No. lower than 36281 KB 2934571 as described in this blog post.

     

    Synchronization issues:

    These might arise typically in one of the following scenarios:

    • The Microsoft Dynamics NAV Server service account has insufficient permissions
      The service account must be added to “db owner” SQL Server role for the Microsoft Dynamics NAV tenant Database.
    • Stretched / Borderline scenarios that implement platform files with a Build No. lower than 36281 KB 2934571 as described in this blog post.
      With a lower build number, you might get into one of the following scenarios:
      • When several developers commit changes at the same time in the same database / tenant while synchronization is running, this might lead to metadata corruption. (Object Metadata table now is locked for committing  changes).
      • Doing actions like FOB Import > Replace > SaveAs  and then Import again the saved FOB was causing a metadata corruption.
    • SQL Connection Timeout meanwhile performing an operation, such as when SQL Server schema changes require drop and build of indexes on large tables.
      To resolve this issue it is necessary to increment the following parameter in the Microsoft Dynamics NAV Server CustomSettings.config file
       <add key="SqlCommandTimeout" value="10:00:00" />

    Development Environment best practice

    thinking about potential data loss and synchronization issues is a brand new big challenge in the development environment, and so some consideration and following best practice might be advisable. These applies to developing solutions for both single- and multitenant deployments.

    1. Do not use Build No. lower than than 36310 KB 2934572
      As a partner, you take this as the "RTM Build No." starting point for NAV 2013 R2 and deploy this platform hotfix in the future projects while you also convert existing installations.
      NOTE: As per common best practice, we recommend that you download / request / test and deploy the latest platform hotfix for Microsoft Dynamics NAV 2013 R2. This will contain correction for minor issues not directly or just slightly related to synchronization scenarios.
    2. Never-ever change “Prevent data loss from table changes” to “No”.
      This have been noticed as one of the major source of potential data loss and missing synchronization for NAV 2013 R2 databases.
    3. Make sure that the Microsoft Dynamics NAV Server service account has been granted the “db owner” role in SQL Server.
    4. Increment the SQL Server Command Timeout parameter in the Microsoft Dynamics NAV Server configuration file that you use in development to a very high value (such as 10:00:00)
    5. For large Microsoft Dynamics NAV objects OR a high number of table modifications, do NOT use a Microsoft Dynamics NAV client action to prompt for synchronization but it is warmly preferable to use the Sync-NAVTenant Windows PowerShell cmdlet. (This is a typical scenario related to upgrades).
    6. For big batch of FOB files that are making a high number of table modifications, be sure to have this tested on a safe staging environment and import, where possible, the Table Objects in smaller chunks and synchronize them after importing every single chunk of Microsoft Dynamics NAV objects.
    7. For important changes in several table structures, such as when upgrading from previous version, it would be good to run a SQL Server Profiler trace after prompting for synchronization to check what is running on the SQL Server side and keep the synchronization monitored until it ends.

    Recommended Events:

    • SP:StmtCompleted
    • SQL:StmtCompleted

    Recommended Column Filters:

    • DatabaseName   Like <DatabaseName>
    • TextData       Not Like  SELECT %

    Bottom line. Worth mentioning that if a Microsoft Dynamics NAV Client hang / disconnect happens due to a missing synchronization issue or there were a synchronization transaction running behind the transaction rollback SQL Server side will take a higher amount of time in comparison with the same committed transaction, depending on the type of changes, resources available, etc.

    Just in case you fall back in this situation, it is warmly advisable to do not stop nor restart Microsoft Dynamics NAV Server and check through a SQL Server Profiler trace and/or via SQL Server Management Studio if the transaction has successfully rollback.

    Another blog post will follow this one, related to synchronization challenges and best practice while upgrading to Microsoft Dynamics NAV 2013 R2 from previous versions.

     

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

     

    Gerard Conroy - Microsoft Dynamics UK

    Abdelrahman Erlebach - Microsoft Dynamics Germany

    Duilio Tacconi - Microsoft Dynamics Italy

    Jasminka Thunes - Microsoft Dynamics Norway                   

    Microsoft Customer Service and Support (CSS) EMEA

     

    A special thanks to Jorge Alberto Torres & Jesper Falkebo from the Microsoft Dynamics NAV development team

  • Microsoft Dynamics NAV Team Blog

    Error 1935 when installing Dynamics NAV 5.0 SP1

    • 5 Comments

    When I tried to install the NAV 5.0 SP1 client I ran into an error 1935. This may happen on Vista and Windows 2008.

    Error 1935; an error occurred during the installation of
    assembly component {98CB24AD-52FB-DB5F-A01F-C8B3B9A1E18E}. HRESULT:0×800736CC.

    The solution is simpler then it seems. You need to install Microsoft Visual C++ 20005 SP1 Redistributable pack. Remember if you are running on a 64 bit system you need to install both x86 and x64 package.

    x86
    x64

  • Microsoft Dynamics NAV Team Blog

    Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

    • 11 Comments

    In one of our previous blog post we discussed the possibility to do Transfooter and Transheader functionality in RDLC(SSRS) reports and describes a viable solution for this in RDLC.

    In this blog post we would like to suggest an alternative, a bit more economical and easier to implement solution for the same problem.

    For the demo we use the same table and the same report and will strive to achieve the same results as in the mentioned in our previous blog post.

    1. Create new report blank report with table 18

    clip_image002

    2. Create DataItem ”Customer”

    3. Go to Section Designer and add the following fields:

    • No.
    • Name
    • Debit Amount

    4. Save the report as ID 50000 – Transfooter / Transheader

    5. Now go to Visual Studio (View / Layout)

    6. Create table and add the fields No, Name and Debit Amount

    7. Give this table the name "MainTable"

    8. Now we have added the basic for this report. But I would also like to have a Grand total of the Debit Amount so I add this as well. I add this in the Footer of the table

    ="GrandTotal: " & sum(Fields!Customer__Debit_Amount_.Value)

    image

    9. Now if my report is printed I get a list of my all my customer with Debit Amount displayed and with GrandTotal in the end of the report:

    10. Now I create a small block of VBS code in order to perform some calculations and store intermediate data

    Open “Report->Report Properties” dialog and select “Code” tab, enter the following VBS code:

    11. Define a hashtable for storing running accumulated sums for each page of the report

    Shared RunningTotals As New System.Collections.Hashtable

    12. Define two public functions, which populate and query the hashtable from above

    Public Function GetRunningTotal(ByVal CurrentPageNumber)

    Return IIF(CurrentPageNumber > 0, RunningTotals(CurrentPageNumber), 0)

    End Function

     

    Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)

    RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber - 1)

    Return RunningTotals(CurrentPageNumber)

    End Function 

    image

    13. Ok, it’s now time to add a Transfooter and Transheader.

    Enable Page Header and Page Footer in the report (click “Report->Page Header” and “Report->Page Footer”).

    14. In the Page Footer I place a text box with the following expression:

    ="Transfooter subtotal = " & Code.SetRunningTotal( Sum(ReportItems!Customer__Debit_Amount_.Value), Globals!PageNumber)

    image

    This code actually performs the following actions:

    - calculate the sum of all “Debit Amount” values on the current page (sic)

    - adds this value to the running total, which has been already calculated for the previous page

    - returns this value as the actual running total for the current page

    15. In the Page header I place a text box with the following expression:

    ="Transheader subtotal = " & Code.GetRunningTotal(Globals!PageNumber-1)

    This code fetches the running total, calculated up to the previous page

    image

    16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report

    image

    17. Now I’m almost done but I would like to not see the Transheader on the first page and not to see the Transfooter on the last page.

    So I set the following expressions for the “Visibilty->Hidden” properties of the page header:

    =IIF(Globals!PageNumber > 1, False, True)

    And for the page footer:

    =IIF(Globals!PageNumber < Globals!TotalPages, False, True)

    18. Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print

    Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print:

    clip_image027

    clip_image029

    Question: Would this also work in the example of having a list of sales order lines per sales header and the sales order lines goes to multiple pages?

    Answer: The report above is a bit simplified in order to illustrate the point. It can be easily extended to support your scenario. I.e. the key for the hash should include page number AND header no to accomplish this.

    You can download the report object here, thanks to Nickolay Belofastow.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    Importing and Exporting Data in Microsoft Dynamics NAV 2013 R2 CU 8

    • 28 Comments

    In earlier versions of Microsoft Dynamics NAV, you could move or copy all or part of the data in a database by using the Microsoft Dynamics NAV backup functionality. In Microsoft Dynamics NAV 2013 R2, the support for the .fbk files was removed, but with Cumulative Update 8 for Microsoft Dynamics NAV 2013 R2, we introduce Windows PowerShell cmdlets so you can export data from a Microsoft Dynamics NAV database and import it into another Microsoft Dynamics NAV database. You can also export and import data in the Microsoft Dynamics NAV Windows client.

    You can export and import a single company or all companies in a database, and you can export and import other types of data such as global data, application data, and application objects. As part of Cumulative Update 8, we include a whitepaper that provides examples of the types of data and how to export and import data using Windows PowerShell cmdlets as well as in the Microsoft Dynamics NAV Windows client.

    When you export data from a Microsoft Dynamics NAV database, the data is stored in a file with the extension .navdata, which is a new file format that is proprietary to Microsoft Dynamics NAV data. You cannot edit the .navdata files in other tools.

    The data that you export is not deleted from the original database. So that means that you can use the functionality to essentially take a copy of your customer’s live data, leave them to continue working, while you import the data into an offline database back at your office for further debugging or other investigation. You can also use the .navdata files to move data to a new database, such as moving a company to a new database when you want to deprecate a database, for example.

    Exporting and Importing Data in the Microsoft Dynamics NAV Windows Client

    To export or import data, in the Search box, enter Data File, and then choose the related link.

    To export data, you specify the type of data that you want to export, and when you choose the OK button, you specify where you want to save the file.

    To import data, you specify the .navdata file to import data from, but you can't import an application if the .navdata file contains an application. This is because you can't overwrite the application that is currently open in the Microsoft Dynamics NAV Windows client. So the window has one less type of data that you can choose to import:

    If you want to import an application into a Microsoft Dynamics NAV database, you must use the Import-NAVData Windows PowerShell cmdlet.

    Windows PowerShell Cmdlets

    The following table describes the Windows PowerShell cmdlets that are new in Microsoft Dynamics NAV 2013 R2 Cumulative Update 8.

    Name

    Description

    Export-NAVData

    Exports data from a Microsoft Dynamics NAV database. You can export company-specific data, and you can choose to include global data, application data, or application objects.

    Import-NAVData

    Imports data into a Microsoft Dynamics NAV database from a file. You can import all data in the file, or you can choose to include specific companies, global data, application data, or application objects.

    You can only import an application into an empty database.

    Get-NAVDataFile

    Gets information from a file that has been exported from a Microsoft Dynamics NAV database.

    The extracted information includes the types of data that the file contains and any company names.

    The cmdlets take different parameter sets depending on how you connect to the database that you want to export data from or import data into. You can access the database through the Microsoft Dynamics NAV Server instance, or you can access the database directly as described in the following table.

    Access

    Description

    Through the Microsoft Dynamics NAV Server instance.

    Use parameter sets that include –ServerInstance when the database that you want to access is mounted against a Microsoft Dynamics NAV Server instance.

    The user account for the Microsoft Dynamics NAV Server instance must have access to write to the location that is specified by the –FileName parameter.

    Through a direct connection to the database.

    Use parameter sets that include –DatabaseServer and –DatabaseName when the Microsoft Dynamics NAV Server instance is stopped or not available. For example, if you want to import an updated application into a database, you stop the service so that users cannot access the database.

    You must have access to write to the location that is specified by the –FileName parameter.

    The following table describes the Windows PowerShell cmdlets that are modified in Microsoft Dynamics NAV 2013 R2 Cumulative Update 8.

    Name

    Description

    Get-NAVCompany

    Gets a list of the Microsoft Dynamics NAV companies in the specified tenant database or exported Microsoft Dynamics NAV data file.

    The cmdlet has been updated to be able to get information from a Microsoft Dynamics NAV data file.

     

    However, the Help for the Export-NAVData and Import-NAVData Windows PowerShell cmdlets does not show the correct syntax when you run a command such as the following:.

    PS C:\WINDOWS\system32> Get-Help Export-NAVData

     

    Refer to the following syntax for the Export-NAVData cmdlet:

    1. Export-NAVData -DatabaseName <string> -FileName <string> [-DatabaseServer <string>] [-ApplicationDatabaseServer <string>] [-ApplicationDatabaseName <string>] [-Description <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-CompanyName <string[]>] [-Force]  [<CommonParameters>]

    2. Export-NAVData [-ServerInstance] <string> [[-Tenant]< TenantId>] -FileName<string> [-Description <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-CompanyName <string[]>] [-Force]  [<CommonParameters>]

    3. Export-NAVData [-ServerInstance] <string> [[-Tenant] <TenantId>] -FileName<string> -AllCompanies [-Description <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-Force]  [<CommonParameters>]

    4. Export-NAVData -DatabaseName <string> -FileName <string> -AllCompanies [-DatabaseServer <string>] [-ApplicationDatabaseServer <string>] [-ApplicationDatabaseName <string>] [-Description <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-Force]  [<CommonParameters>]

     

    Refer to the following syntax for the Import-NAVData cmdlet:

    1. Import-NAVData [-FileName] <string> -DatabaseName <string> [-DatabaseServer <string>] [-ApplicationDatabaseServer <string>] [-ApplicationDatabaseName <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-CompanyName <string[]>] [-Force] [-WhatIf] [-Confirm]  [<CommonParameters>]

    2. Import-NAVData [-ServerInstance]< string> [[-Tenant] <TenantId>] [-FileName] <string> [-IncludeApplicationData] [-IncludeGlobalData] [-CompanyName <string[]>] [-Force] [-WhatIf] [-Confirm]  [<CommonParameters>]

    3. Import-NAVData [-ServerInstance]< string> [[-Tenant] <TenantId>] [-FileName] <string> -AllCompanies [-IncludeApplicationData] [-IncludeGlobalData] [-Force] [-WhatIf] [-Confirm]  [<CommonParameters>]

    4. Import-NAVData [-FileName] <string> -DatabaseName <string> -AllCompanies [-DatabaseServer <string>] [-ApplicationDatabaseServer <string>] [-ApplicationDatabaseName <string>] [-IncludeApplication] [-IncludeApplicationData] [-IncludeGlobalData] [-Force] [-WhatIf] [-Confirm]  [<CommonParameters>]

     

    You can find more information about this functionality, and the new or changed objects, in the following documents on the W1 version of the Microsoft Dynamics NAV 2013 R2 CU8 download media:

    • MicrosoftDynamicsNAV2013R2CU8_ExportImportDataUsingNavDataFiles.pptx
    • MicrosoftDynamicsNAV2013R2CU8_ImportExportData.pdf

    In subsequent cumulative updates, the documents will be available in the country-specific downloads as well.

  • Microsoft Dynamics NAV Team Blog

    Manually restoring the NAV 2013 Demo Database

    • 4 Comments

    With the release of Microsoft Dynamics NAV 2013, there are some new procedures for restoring the demo database manually.  I have outlined the necessary steps below.

    1)    From within the Microsoft SQL Server Management Studio, restore the Microsoft Dynamics NAV 2013 demo database from the installation media (..\SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\70\Database).

    2)    To restore the database from the Microsoft SQL Server Management Studio, open the Object Explorer and click on Databases.  With Databases highlighted, right click and select Restore Database.

    3)    This will open up the Restore Database window.  Select the Device option in the Source grouping. 

    4)    Now click on the assist edit button () and this will launch the Select backup devices window.

    5)    Next, click on the Add button to launch the Locate Backup File window and select the demo database backup file.

    6)    Once you have selected your file, click OK to exit the Locate Backup File window.  Once you return to the Select backup devices window, click on OK again.

    7)    You should now be on the Restore Database - Demo Database NAV (7-0) window - unless you have changed the value of the Database field then the window title would differ.  Make sure that the file listed in the Backup sets to restore section has a check mark next to it.  If you want to change where the database files are created, you can specify this on the Files page.  If you want to specify any Restore options this can be done on the Options page.  For this blog, it is assume that you will take the default settings.

    8)    Click OK to restore the database.

    9)    After the restore finishes, go to the Object Explorer and expand the Databases object.  The list of database objects should now have one named Demo Database NAV (7-0).

    10)  Next, expand the Demo Database NAV (7-0) object and select the Security\Schemas object.  Expand this object and make sure the $ndo$listener schema doesn't already exist.  If you had already installed NAV 2009 on this SQL Server,  then this schema may already exist.  If it does, go to step 12.  If it doesn't, then right click on the Schemas object and select New Schema

    11)   With the Schemas Properties window open, enter $ndo$navlistener in the Schema Name field.  

    Note: You will not be able to state a schema owner until the next step.  There are no values to fill in on either the Permissions or Extended Properties tabs.

    12)   Create a user in the Demo Database NAV (7-0) named NT AUTHORITY\NETWORK SERVICE or whatever user that will be used to login the NAV Server Service.

    13)   Enter $ndo$listener in the Default schema field on the General page.

    14)   On the Owned Schema page, make sure the $ndo$listener is marked.

    15)   On the Membership page, make sure that the following Role Members are selected

      • db_datareader
      • db_datawriter
      • db_ddadmin

    16)   On the Securables or Extended Properties pages, there is no information to fill in.

    17)   The last step before starting your new NAV Server, locate your new database and right-click and select the Properties option.  Select the Permissions Page.  You should see NT AUTHORITY\NETWORK SERVICE or whatever user that will be used to login the NAV Server Service on the right side of the page.

    Below that you will see a tab for Explicit Permissions for NT AUTHORITY\NETWORK SERVICE or whatever user that will be used to login the NAV Server Service.  Make sure that is a check in the following permissions 

      • Connect
      • View database state

    Once those are selected then click OK.

    18)   Use the Microsoft Dynamics NAV Administration tool to update the NAV Server settings with the new demo database name and restart the NAV Server.

     

     

    NOTE: If you were to use the Microsoft Dynamics NAV 2013 Installer to install the demo database, it would have run a script that would have executed each of these steps for you.

    Now, enjoy using Microsoft Dynamics NAV 2013!!!

     

  • Microsoft Dynamics NAV Team Blog

    Export to Excel on Dynamics NAV: RTC

    • 1 Comments

    Due to many and frequent questions on the topic of performance when exporting to MS Excel on RTC, I've summarized some of the answers in this blog to try and explain the impact of running MS Office automation objects on RTC .

    Let's start with Stefan's excellent blog on automation objects and considerations running server/client side:

    http://blogs.msdn.com/b/nav/archive/2008/12/03/automation-objects-in-microsoft-dynamics-nav-2009.aspx.

    As you can see, it means that office automation must run client side, and to reiterate recommendation given:

    General
    • It is good practice to design your Automation interfaces to get as much work done in a single method invocation. And when consuming a component, try to use it in way that minimizes chattiness. This is utmost importance for the client side automation scenario.

    Currently (and traditionally), some export to excel functions use office automation and table 370 (Excel Buffer), where export is run on row-by-row basis, meaning each row is exported and formatted in a separate call. If you are exporting hundreds or thousands of row, you're in effect maximizing 'chattiness', which will result in considerably slower performance when exporting data to excel (using standard application functionality and especially when running on RTC). This applies to functions like reports Aged Accounts Recevaibles (120) (export to excel), Customer/Item Sales (export to excel), then Budget: Export to Excel, Account Schedules: Export to Excel, Trial Balance: Export to Excel, RIM: Export to excel...  these will all run slower on RTC, as office automation is invoked for each row exported.

    This does not apply to export using Send-to option (Send-to Excel), as this uses msxml6.0. This also doesn't include export of Analysis by Dimensions, as here all data are exported to a flat file (then downloaded to client), which is then formatted in few calls using office automation. This will not strain performance and will run about as quickly from RTC as from classic.

    So what to do about functions that do see significant drop in performance (specially for larger data amount)? Well, there has to be several ways around this, here are some:

    • If you are going to export large amounts of data to excel, why not try Jet Reports for your own reporting?
    • Then, MSXML can run serverside (and thus runs fast) and you can create spreadsheet format file for excel. Example:  RIM functionality offers export to xml or Excel.  Both (by design) run client side as RIM is really intended for setup data, and that normally means a small amount of data and should run fine client side. But should you want to, it takes only few lines of code to switch to serverside running for xml, and then you can export large amounts of data to xml in same time classic client would run, then download and open generated xml file with excel (and import the same way back).
    • You can potentialy use SAVEASEXCEL, and it is maybe the simplest solution, but this will have a limitation in number of records you can export (65K), so you can't use it for larger export. Also you'll need to modify each report separately and also imoprt if you want to use it to export budgets.
    • Follow the example from Analysis by dimensions (codeunit 424), which exports data to a flat file, that is then afterwards downloaded to client and opened with excel. Excel file formatting is done in only few additional calls, so the performance on RTC is similar to that on classic client. Also, as a flat file size will only increase slowly with amount of data, you can process and export large amount of records quite quickly, created file is downloaded to client and office automation invoked only for file formatting and opening.
    • ...
    I've chosen here to illustrate the workaround from the last point (follow example from Analysis by Dimensions codeunit) in bit more details below. It might sound like a lot of work but it really is down to few lines of code only. If you in addition invest bit more time into this, you can add pivot functionality for much more flexible result. Keep in mind the below illustration is just the very simple basic code conversion to flat file, it is hardly the most elegant way to do this :-).
     
    There are several ways to go about even with this workaround, and you can choose to mimic exactly current export (so you won't have to change anything about import functionality, cause some of the functions, like Budget,  also offer import of modified data back into nav). This results in bit more tedious formatting of data when exporting, but minimum, if any, change of the code importing data.
    Or you can simplify the current export (this only takes few code lines) and then rather invest a bit more code into adding pivot functionality (just as in codeunit 424), to get far more powerfull resulting export, but then you also have to modify import for the functions that handle importing (Importing is used for ex. in Budget).
     
    I've done here the first option of the two. The following is illustration of how you could rewrite export to excel, so data are exported in same way they are today, only to a flat file, which is afterwards formatted and handled further by Excel.
    The only drawback here is one looses row-by-row formatting (like bold totalling) which is expensive for performance. But again, if formatting is important - go for pivot for simplicity and maximum effect. On the upside, the effect of this workaround on performance is proportional to number of records, so with larger amount of records and even on RTC, this will run quicker then today's export on classic.
     
    Changes apply to : Table 370 Excel Buffer
     
    1. Copy trigger InitCharTables from codeunit 424 Export Analysis View, along with code and variables, to table 370.
    2. Copy trigger CreateSheet (with code and variables) from table 370 to table 370. Rename copied trigger to CreateCSV, keep the rest as it is.
    3. In the very beginning of CreateSheet trigger, add the following lines:
     
      IF ISSERVICETIER THEN
      BEGIN
        CreateCSV(SheetName,ReportHeader,CompanyName,USERID);     
        EXIT;
     END;
     
     This would really give you the option to stick to old classic export on classic, and workaround on RTC, though workaround runs faster on classic too, specially  with larger files. If you want to run the workaround on both classic and RTC, then instead of the 5 lines above, add the following two:
      CreateCSV(SheetName,ReportHeader,CompanyName,USERID);
      EXIT;
     
    4. Replace following code of CreateCSV trigger : 
     

    Window.OPEN(

      Text005 +

      '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');

    Window.UPDATE(1,0);

     

    XlEdgeBottom := 9;

    XlContinuous := 1;

    XlLineStyleNone := -4142;

    XlLandscape := 2;

    CRLF := 10;

    RecNo := 1;

    TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;

    RecNo := 0;

     

    XlWrkSht.Name := SheetName;

    IF ReportHeader <> '' THEN

      XlWrkSht.PageSetup.LeftHeader :=

        STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);

    XlWrkSht.PageSetup.RightHeader :=

      STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);

    XlWrkSht.PageSetup.Orientation := XlLandscape;

    IF FIND('-') THEN BEGIN

      REPEAT

        RecNo := RecNo + 1;

        Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));

        IF NumberFormat <> '' THEN

          XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;

        IF Formula = '' THEN

          XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"

        ELSE

          XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;

        IF Comment <> '' THEN

          XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;

        IF Bold THEN

          XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;

        IF Italic THEN

          XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;

        XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;

        IF Underline THEN

          XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;

      UNTIL NEXT = 0;

      XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;

    END;

     ...
     
     with the code below:
     

     

    Window.OPEN(

      Text005 +

      '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');

    Window.UPDATE(1,0);

     

    XlApp.ActiveWorkbook.Close(FALSE);

    c := 9;

    Tab[1] := c;

    XlContinuous := 1;

    XLLandscape := 2;

    XLLinestyleNone := -4142;

    XlEdgeBottom := 9;

    CRLF := 10;

    TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;

    NoOfRows := 0;

     

    InitCharTables;

    TextFile.CREATETEMPFILE;

    FileName := TextFile.NAME + '.txt';

    TextFile.CLOSE;

     

    TextFile.CREATE(FileName);

    TextFile.TEXTMODE(TRUE);

     

    IF FINDFIRST THEN BEGIN

      FOR i := 1 TO STRLEN(xlColID) DO

        CurrAnsiCode := CurrAnsiCode + xlColID[i];

      RecNo := xlRowID;

    END;

    IF FIND('-') THEN BEGIN

      REPEAT

        IF xlRowID <> RecNo THEN BEGIN

          EVALUATE(CurrXlRow,RecNo);

          EVALUATE(NextXlRow,xlRowID);

          FOR i := 1 TO (NextXlRow -CurrXlRow) DO

          BEGIN

            Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);

            TextFile.WRITE(Line[1]);

            Line[1] := '';

          END;

          CurrAnsiCode := 65;

          AnsiCode := 0;

          NewLine := TRUE;

        END;

        Window.UPDATE(1,ROUND(NoOfRows / TotalRecNo * 10000,1));

        FOR i := 1 TO STRLEN(xlColID) DO

          AnsiCode := AnsiCode + xlColID[i];

        IF ((AnsiCode - CurrAnsiCode) > 1) OR (((AnsiCode - CurrAnsiCode) > 0) AND NewLine) THEN

          FOR i := 1 TO  (AnsiCode - CurrAnsiCode) DO

            Line[1] := Line[1] + Tab;

        IF NumberFormat = '' THEN BEGIN

          IF EVALUATE(DecVal,"Cell Value as Text") THEN

            Line[1] := Line[1] + FORMAT(DecVal) + Tab

          ELSE

            Line[1] := Line[1] + "Cell Value as Text" + Tab

        END ELSE

          Line[1] := Line[1] + "Cell Value as Text" + Tab;

        IF Comment <> '' THEN

          Line[1] := Line[1] + Comment + Tab;

        NoOfRows := NoOfRows + 1;

        RecNo := xlRowID;

        CurrAnsiCode := 0;

        AnsiCode := 0;

        FOR i := 1 TO STRLEN(xlColID) DO

          CurrAnsiCode := CurrAnsiCode + xlColID[i];

        NewLine := FALSE;

      UNTIL NEXT = 0;

    END;

     

    TextFile.CLOSE;

     

    FileName := ThreeTierMngt.DownloadTempFile(FileName);

    XlApp.Workbooks.OpenText(FileName);

    XlWrkSht := XlApp.ActiveSheet;

    xlSheetName := SheetName;

    xlSheetName := CONVERTSTR(xlSheetName,' -+','___');

    XlWrkSht.Name := xlSheetName;

    ...

    where following are local variables:

    Name                                                     DataType             Subtype                Length

    Tab                                                         Text                                                      1

    c                                                             Char                      

    CRLF                                                       Char                      

    Window                                                  Dialog                   

    TextFile                                                   File                        

    FileName                                                Text                                                      1024

    Line                                                        Text                                                      1024

    Line2                                                      Text                                                      1024

    ThreeTierMngt Codeunit                       3-Tier Automation Mgt.               

    xlSheetName                                          Text                                                      100

    i                                                               Integer                

    CurrXlRow                                               Integer                

    DecVal                                                    Decimal               

    NextXlRow                                              Integer                

    CurrXlCol                                                Integer                

    AnsiCode                                                Integer                

    CurrAnsiCode                                         Integer                

    NewLine                                                 Boolean              

    And that should be it really. Now all the export functionalities mentioned above (and the other exports using this table), should export to flat file (that will in the end result in an Excel export file), and relatively fast. Keep in mind this is just illustrating example, tested on W1 and only on the exports that are mentioned above.

    One note thoguh, countries with 'space' as digit grouping symbol (some of  these are Norway, France, and more....) might experience the old issue of numbers being exported to text (as digit grouping symbol is not a proper space and ANSI sign for that is different then ASCII, so the resulting export is treated as text). If your regional settings have this digit grouping symbol (looks like a space), and you're using something similar to the code above, add the following lines to the above code to resolve that: (in same trigger, CreateCSV), add following lines:

    ...

    TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX; 

    NoOfRows := 0; 

    ansispace[1] := 32;  //ADDED LINE

    asciispace[1] := 160;  //ADDED LINE

    ...

       BEGIN

            Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);

            Line[1] := CONVERTSTR(Line[1],asciispace,ansispace);  //ADDED LINE

            TextFile.WRITE(Line[1]);

            Line[1] := '';

            ...

    where ansispace and asciispace are local variables Type Text, Length 1.

    Now while fiddling with this I also run accross couple of things i'd like to comment on in std application, i'm just adding them here to be thorough.

    • Filename : Creating new file when exporting, regardless of the file name selected (and you get the option to select), your file will get the temp file name. This is funcitonality as it is today, but change it easily in the same trigger, by renaming the file after export (or download), or copying to the file with given filename (after export or download).
    • Account schedule: export to excel. In classic client, if you select to create a new book, you will not have an option of selecting file nor sheet name. These are available only when updating existing workbook. In RTC, as aftermath of page transformation, selecting filename is available for both options, and if users are not familiar wiht this fucntionality and try selecting file name when creating workbook (which you're not supposed to do), an automation error might occur on RTC. To align request page functionality with classic request from, add a group to request page of report 29, and indent fields FileName and SheetName under that group. Set Editable and Enabled  properties of the group to Show, where Show is a global boolean variable with IncludeInDatatset = TRUE; Then replace the code in trigger PageUpdateRequestForm wiht following code:


    IF Option = Option::"Update Workbook" THEN BEGIN
      IF NOT ISSERVICETIER THEN BEGIN
        FileNameEnable := TRUE;
        SheetNameEnable := TRUE;
      END ELSE BEGIN   //MODIFIED  LINE
      IF Option = 1 THEN show := TRUE ELSE  //ADDED LINE
        show := FALSE;  //ADDED LINE
      END;  //ADDED LINE
    END ELSE BEGIN //MODIFIED  LINE
      FileName := '';
      UploadedFileName := '';
      SheetName := '';
      IF NOT ISSERVICETIER THEN BEGIN
        FileNameEnable := FALSE;
        SheetNameEnable := FALSE;
      END ELSE //MODIFIED  LINE
      BEGIN  //ADDED LINE
        IF Option = 1 THEN show := TRUE ELSE //ADDED LINE
        show := FALSE;  //ADDED LINE
      END; //ADDED LINE
    END;

                        and request page should work as request from does on classic, avoiding automation errors if you're not familiar with this report's usage.

     

    // Copyright © Microsoft Corporation. All Rights Reserved.

    // This code released under the terms of the 

    // Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

     

     

     

     

     

     

     

     

Page 1 of 52 (770 items) 12345»