Transcription
Extending Microsoft DynamicsNAV 2016 CookbookMake the most of your NAV deployment by extending andcustomizing it with a variety of expert toolsAlexander DroginBIRMINGHAM - MUMBAI
Extending Microsoft Dynamics NAV 2016CookbookCopyright 2017 Packt PublishingAll rights reserved. No part of this book may be reproduced, stored in a retrieval system, ortransmitted in any form or by any means, without the prior written permission of thepublisher, except in the case of brief quotations embedded in critical articles or reviews.Every effort has been made in the preparation of this book to ensure the accuracy of theinformation presented. However, the information contained in this book is sold withoutwarranty, either express or implied. Neither the author, nor Packt Publishing, and itsdealers and distributors will be held liable for any damages caused or alleged to be causeddirectly or indirectly by this book.Packt Publishing has endeavored to provide trademark information about all of thecompanies and products mentioned in this book by the appropriate use of capitals.However, Packt Publishing cannot guarantee the accuracy of this information.First published: January 2017Production reference: 1130117Published by Packt Publishing Ltd.Livery Place35 Livery StreetBirminghamB3 2PB, UK.ISBN 978-1-78646-060-8www.packtpub.com
CreditsAuthorCopy EditorAlexander DroginSafis EditingReviewerProject CoordinatorOleg RomashkovVaidehi SawantCommissioning EditorProofreaderAaron LazarSafis EditingAcquisition EditorIndexerSonali VernekarMariammal ChettiyarContent Development EditorProduction CoordinatorSiddhi ChavanArvindkumar GuptaTechnical EditorsDhiraj ChandanshiveBhavin Savalia
About the AuthorAlexander Drogin started working with Navision Attain version 3.01 in 2002 as a softwaredeveloper at a consulting company. After 7 years of development, he shifted his focus toend-user support. In 2012, he joined the Microsoft Russia development team as a softwareengineer in testing, and worked on NAV test automation. Currently, he leads the sustainedengineering team in the Supply Chain Management area at Microsoft.I would like to thank my colleagues who helped me in my work on this book--OlegRomashkov, for reviewing the book and testing the code samples, whose valuable feedbackon the content helped improve the quality of the book; and Sergey Iazovskiy, who helped mein designing numerous .NET examples and solving issues with .NET Interoperability.
About the ReviewerOleg Romashkov has been a NAV developer since 2001. He worked for eight years withMicrosoft by developing new features and country localizations for NAV 4.00 – NAV 2015.Also he spent 7 years working for a few MS partners where he implemented NAV for morethan 20 customer projects.
www.PacktPub.comFor support files and downloads related to your book, please visit www.PacktPub.com.Did you know that Packt offers eBook versions of every book published, with PDF andePub files available? You can upgrade to the eBook version at www.PacktPub.com and as aprint book customer, you are entitled to a discount on the eBook copy. Get in touch with usat service@packtpub.com for more details.At www.PacktPub.com, you can also read a collection of free technical articles, sign up for arange of free newsletters and receive exclusive discounts and offers on Packt books andeBooks.https://www.packtpub.com/maptGet the most in-demand software skills with Mapt. Mapt gives you full access to all Packtbooks and video courses, as well as industry-leading tools to help you plan your personaldevelopment and advance your career.Why subscribe?Fully searchable across every book published by PacktCopy and paste, print, and bookmark contentOn demand and accessible via a web browser
Customer FeedbackThank you for purchasing this Packt book. We take our commitment to improving ourcontent and products to meet your needs seriously—that's why your feedback is sovaluable. Whatever your feelings about your purchase, please consider leaving a review onthis book's Amazon page. Not only will this help us, more importantly it will also helpothers in the community to make an informed decision about the resources that they investin to learn.You can also review for us on a regular basis by joining our reviewers' club. If you'reinterested in joining, or would like to learn more about the benefits we offer, pleasecontact us: customerreviews@packtpub.com.
Table of ContentsPrefaceChapter 1: Writing Basic C/AL CodeIntroductionInstalling NAV Development EnvironmentGetting readyHow to do it How it works Application object triggersHow to do it How it works NAV Development Environment – C/SIDEHow to do it How it works Compiling objects and error handlingHow to do it How it works Importing and exporting application objectsHow to do it How it works Basic C/AL programmingHow to do it How it works Accessing the database in C/ALHow to do it How it works Configuring NAV ServerGetting readyHow to do it How it works Creating a NAV user accountGenerating a self-signed certificateObtaining the certificate thumbprintChanging the server configurationConfiguring web serverGetting 1313135353535363636
How to do it How it works There's more See also37414244Chapter 2: Advanced C/AL DevelopmentIntroductionCreating custom tablesHow to do it How it works Understanding database triggersHow to do it How it works Implementing a user interface with pagesHow to do it How it works Linking datasources in subpagesHow to do it How it works Working with page triggersHow to do it How it works Presenting related data in FactBoxesHow to do it How it works Designing reusable codeHow to do it How it works See alsoAccessing temporary tablesHow to do it How it works See alsoRole-Tailored client and role centersHow to do it How it works Assigning role centers to user profilesGetting readyHow to do it How it works [ ii 181838485859497979799
Simplifying data access with queriesHow to do it How it works Improving performance with indexesHow to do it How it works Linking datasources with advanced queriesHow to do it How it works Exchanging data with XMLPort objectsHow to do it How it works Designing the user menuHow to do it How it works Referencing records and fields via RecordRef and FieldRefHow to do it How it works Working with single instance codeunitsHow to do it How it works Running tasks in background sessionsHow to do it How it works See alsoChapter 3: Reporting and Data AnalysisIntroductionDesigning reports in Visual StudioHow to do it How it works Developing Word layout for RDLC reportsGetting readyHow to do it How it works There's more Writing C/AL code in a reportGetting readyHow to do it How it works [ iii 6146146146147153
Designing the report request pageGetting readyHow to do it How it works Writing RDLC code in a reportGetting readyHow to do it How it works Using built-in expressions in the report layout designerGetting readyHow to do it How it works Including user interaction in reportsGetting readyHow to do it How it works Updating NAV data with the Excel add-inGetting readyHow to do it How it works Retrieving data from NAV with Power QueryGetting readyHow to do it How it works Creating pivot tables with Power QueryGetting readyHow to do it Analyzing data with Power BIGetting readyHow to do it How it works Data hierarchies in Power BI reportsHow to do it Statistical data analysis in Power BI with RGetting readyHow to do it How it works Sales forecast in Power BI with RGetting ready[ iv 192192192196197197
How to do it How it works Designing server-side reports in SQL Server Reporting StudioGetting readyHow to do it How it works There's more Chapter 4: .NET Interoperability in C/ALIntroductionUsing .NET assemblies in C/ALHow to do it How it works Understanding static classes and methodsHow to do it How it works Developing server-side extensionsHow to do it How it works Developing client-side extensionsHow to do it How it works Working with generics in C/ALHow to do it How it works Working with interfacesHow to do it How it works Accessing collections with the FOREACH statementHow to do it How it works Working with .NET strings and arrays in C/ALHow to do it How it works Invoking .NET methods via reflectionHow to do it How it works Handling exceptions with try functionsHow to do it How it works 41243243246246247248
Handling .Net events in C/ALHow to do it How it works Developing your own .NET classHow to do it How it works Starting processes in separate threadsGetting readyHow to do it How it works Chapter 5: Extending C/AL with COM ComponentsIntroductionUsing COM type libraries in C/ALHow to do it How it works Mapping COM datatypes to C/ALHow to do it How it works Disposing of COM objectsHow to do it How it works Working with the Variant datatypeHow to do it How it works Sending data to the Excel automation serverHow to do it .How it works Creating a Word document using automation objectsHow to do it How it works Creating and registering a COM component that can be used in NAVHow to do it How it works Chapter 6: SharePoint 8289289292293IntroductionCreating a developer SharePoint siteHow to do it Developing NAV apps for SharePoint[ vi ]293294294296
Getting readyHow to do it How it works Embedding NAV page objects in site pagesGetting readyHow to do it How it works Publishing NAV reports in SharePointHow to do it Deploying applications on SharePointHow to do it How it works Configuring user permissionsHow to do it How it works Chapter 7: Control 15316IntroductionDeveloping a control add-inHow to do it How it works Signing the control add-in assemblyHow to do it How it works Registering and embedding a control add-inHow to do it How it works JavaScript in web, phone, and tablet clientsGetting readyHow to do it How it works Installing and using JavaScript control add-insHow to do it How it works Sending events from add-ins to NAV serverGetting readyHow to do it How it works Linking add-ins with the databaseHow to do it [ vii 36337337337340341342
How it works Exchanging data with add-insHow to do it How it works 344345345349Chapter 8: Web Services350IntroductionPublishing a SOAP web serviceHow to do it Calling object methods in SOAP web servicesHow to do it How it works Exposing and consuming OData web servicesHow to do it How it works Querying NAV OData web services with LINQHow to do it How it works Updating NAV data through web servicesGetting readyHow to do it How it works Securing access to web servicesGetting readyHow to do it How it works Consuming the NAV web service from JavaGetting readyHow to do it How it works Chapter 9: Events and Extension PackagesIntroductionSubscribing to business and integration eventsHow to do it How it works Subscribing to global eventsHow to do it How it works Using database trigger events[ viii 67367368371372372373377379379380380382383384386387
How to do it How it works Extending UI with page trigger eventsHow to do it How it works Creating custom eventsHow to do it How it works Subscribing to events in runtimeHow to do it Developing an extensionGetting readyHow to do it How it works Installing an extensionGetting readyHow to do it Chapter 10: 00401401402404IntroductionManaging the databaseHow to do it How it works Managing users and user permissionsHow to do it How it works Managing companiesHow to do it Administering NAV services with PowerShellHow to do it How it works Handling application objectsHow to do it How it works Merging application objectsHow to do it How it works 422422426427[ ix ]
PrefaceMicrosoft Dynamics NAV 2016 is a modern enterprise resource planning application thatcovers a wide range of user demands. The NAV technical platform gives end-users andapplications developers access to cutting-edge technologies, while the front-end businessapplication meets most of the daily needs of small and medium-sized companies. Still everybusiness is individual and must face unique challenges, and it is impossible to foresee alldemands from business users to the ERP software.To satisfy the requirements of each unique company, NAV offers a wide variety of tools toextend standard functionality. This toolset includes the internal development environmentthat enables developers to tailor the business application to individual needs. Besides, inthis book you will find multiple recipes guiding you through the integration of externallibraries developed in .NET languages, into NAV applications.With the recipes in this book, you will customize NAV applications, develop business logic,extend user interfaces, and organize solutions with custom role centers arrangedspecifically for each functional role in the organization.The book then proceeds to reporting tools, which are the essential part of any enterprisesoftware. You will build informative and interactive reports with built-in NAV tools, anduse Visual Studio reporting tools and SQL Server Reporting Services to design and publishreports. Readers not so skillful in application development, but proficient in Excel or PowerBI, will learn how to load data from NAV into their favorite reporting system and modelcomprehensive reports.System administrators will find useful recipes on different aspects of NAV serverconfiguration and security features.What this book coversChapter 1, Writing Basic C/AL Code, describes NAV 2016 server and client installation andconfiguration and introduces the NAV 2016 development environment along with thebasics of the NAV application language, C/AL.
PrefaceChapter 2, Advanced C/AL Development, delves deeper into C/AL development and NAVapplication objects. You will learn how to store your data in tables, present the data in UIwith pages, export, import, and structure the data in XMLPorts and queries, and writeefficient and reusable code.Chapter 3, Reporting and Data Analysis, introduces the reader to NAV reporting capabilities.The chapter begins with an in-depth description of C/SIDE Report objects, shifting tointegration with external tools in the second part. You will learn how to present NAV datain different reporting systems, including Power BI, SQL Server Reporting Services, and MSExcel.Chapter 4, .NET Interoperability in C/AL, covers the integration of .NET assemblies into theNAV client application language. Recipes in this chapter give an overview of the DotNetdata type in NAV, various extensions of C/AL aimed at supporting the .NETinteroperability and development of custom .Net assemblies and their integration intoNAV.Chapter 5, Extending C/AL with COM Components, walks the reader through developingCOM components and the integration of COM into the NAV application.Chapter 6, SharePoint Integration, provides an overview of corporate web portals on theSharePoint platform and various ways to integrate NAV elements into the corporate sites.Chapter 7, Control Add-ins, covers extending the NAV user interface with custom controlswritten in .NET languages, such as C#, or in JavaScript.Chapter 8, Web Services, covers publishing SOAP and OData web services from NAV toexpose data and functionality through standard communication interfaces over the Web.You will learn how to publish and consume NAV web services and protect web serviceendpoints from unauthorized access.Chapter 9, Events and Extension Packages, covers the events which have been introduced inNAV 2016 to allow application developers to extend NAV functionality withoutmodification to the base application code. We will go through subscribing to different typesof events to receive notifications of changes in database, user actions, and events in the appbusiness logic. The chapter then covers developing extension packages that utilize the ideaof events to bind third party extensions to NAV application code.Chapter 10, PowerShell, gives an overview of the reach set of PowerShell cmdlets availablein NAV shell.[2]
PrefaceWhat you need for this bookMicrosoft Dynamics NAV 2016Microsoft SQL Server 2012 Service Pack 2 Express, Standard, or EnterpriseMicrosoft Visual Studio 2012 Professional, Premium or Ultimate editionInstead of Visual Studio 2012, you can use its free version, Visual Studio Code, to develop.NET code in C#To design report layouts, Visual Studio is also required. VS report designer can be replacedwith the free Microsoft SQL Server 2012 Report BuilderMicrosoft Office 2016. The book covers different aspects of integration between NAV andOffice applications.Power BI is required for data analysis in Chapter 3, Reporting and Data Analysis.The R language environment. Two recipes in Chapter 3, Reporting and Data Analysis, use Rscripts for statistical analysis of NAV data. These will require R.SharePoint Server 2016 or SharePoint Online subscriptionJava SE Development Kit 8 and an IDE for Java development, for example, Eclipse.Windows Management Framework 3.0 must be installed to use features of PowerShell 3.0.Installation is required on Windows 7 or Windows Server 2008. In Windows 8, WindowsServer 2012 and higher versions, PowerShell 3.0 is integrated into the operating system.Who this book is forThis book is intended for NAV developers, administrators, and advanced users who wantto take the most from NAV implementation. The reader is expected to be familiar with basicprogramming and have a good knowledge of NAV business application. Understanding ofobject-oriented principles, as well as some experience of programming in C# and/or Javawould be helpful.[3]
PrefaceSectionsIn this book, you will find several headings that appear frequently (Getting ready, How todo it, How it works, There's more, and See also).To give clear instructions on how to complete a recipe, we use these sections as follows:Getting readyThis section tells you what to expect in the recipe, and describes how to set up any softwareor any preliminary settings required for the recipe.How to do it This section contains the steps required to follow the recipe.How it works This section usually consists of a detailed explanation of what happened in the previoussection.There's more This section consists of additional information about the recipe in order to make the readermore knowledgeable about the recipe.See alsoThis section provides helpful links to other useful information for the recipe.ConventionsIn this book, you will find a number of text styles that distinguish between different kindsof information. Here are some examples of these styles and an explanation of their meaning.[4]
PrefaceCode words in text, database table names, folder names, filenames, file extensions,pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "Open theClientUserSettings.config file located in the AppData directory."A block of code is set as follows:SalesLine.SETFILTER("Document Type",'%1 %2',SalesLine."Document Type"::Order,SalesLine."Document Type"::Invoice);Any command-line input or output is written as follows:Install-NAVApp -ServerInstance DynamicsNAV90 -Name "ILE Posting DateTime"New terms and important words are shown in bold. Words that you see on the screen, forexample, in menus or dialog boxes, appear in the text like this: "Click Next, then select theoption Do not export the private key."Warnings or important notes appear in a box like this.Tips and tricks appear like this.Reader feedbackFeedback from our readers is always welcome. Let us know what you think about thisbook-what you liked or disliked. Reader feedback is important for us as it helps us developtitles that you will really get the most out of.To send us general feedback, simply e-mail feedback@packtpub.com, and mention thebook's title in the subject of your message.If there is a topic that you have expertise in and you are interested in either writing orcontributing to a book, see our author guide at www.packtpub.com/authors .[5]
PrefaceCustomer supportNow that you are the proud owner of a Packt book, we have a number of things to help youto get the most from your purchase.Downloading the example codeYou can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/supportand register to have the files e-mailed directly to you.You can download the code files by following these steps:1.2.3.4.5.6.7.Log in or register to our website using your e-mail address and password.Hover the mouse pointer on the SUPPORT tab at the top.Click on Code Downloads & Errata.Enter the name of the book in the Search box.Select the book for which you're looking to download the code files.Choose from the drop-down menu where you purchased this book from.Click on Code Download.You can also download the code files by clicking on the Code Files button on the book'swebpage at the Packt Publishing website. This page can be accessed by entering the book'sname in the Search box. Please note that you need to be logged in to your Packt account.Once the file is downloaded, please make sure that you unzip or extract the folder using thelatest version of:WinRAR / 7-Zip for WindowsZipeg / iZip / UnRarX for Mac7-Zip / PeaZip for LinuxThe code bundle for the book is also hosted on GitHub at soft-Dynamics-NAV-2016-Cookbook. We also have other codebundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out![6]
PrefaceErrataAlthough we have taken every care to ensure the accuracy of our content, mistakes dohappen. If you find a mistake in one of our books-maybe a mistake in the text or the codewe would be grateful if you could report this to us. By doing so, you can save other readersfrom frustration and help us improve subsequent versions of this book. If you find anyerrata, please report them by visiting http://www.packtpub.com/submit-errata, selectingyour book, clicking on the Errata Submission Form link, and entering the details of yourerrata. Once your errata are verified, your submission will be accepted and the errata willbe uploaded to our website or added to any list of existing errata under the Errata section ofthat title.To view the previously submitted errata, go to https://www.packtpub.com/books/content/supportand enter the name of the book in the search field. The required information willappear under the Errata section.PiracyPiracy of copyrighted material on the Internet is an ongoing problem across all media. AtPackt, we take the protection of our copyright and licenses very seriously. If you comeacross any illegal copies of our works in any form on the Internet, please provide us withthe location address or website name immediately so that we can pursue a remedy.Please contact us at copyright@packtpub.com with a link to the suspected piratedmaterial.We appreciate your help in protecting our authors and our ability to bring you valuablecontent.QuestionsIf you have a problem with any aspect of this book, you can contact usat questions@packtpub.com, and we will do our best to address the problem.[7]
1Writing Basic C/AL CodeIn this chapter we will cover the following recipes:Installing NAV Development EnvironmentApplication object triggersNAV Development environment – C/SIDECompiling objects and error handlingImporting and exporting application objectsBasic C/AL programmingAccessing the databaseConfiguring NAV ServerConfiguring Web ServerIntroductionMicrosoft Dynamics NAV 2016 has a rich toolset for extending functionality. Additionally, awide number of external tools can be connected to the NAV database to enrich dataprocessing and analysis experience. But C/AL, the internal NAV application language, isstill the primary means of enhancing user experience when it comes to developing newfunctionality.This chapter will introduce you to the basics of NAV Client Application Languagedevelopment, from installing the development environment and configuring the server tothe fundamentals of data manipulation with C/AL.
Writing Basic C/AL CodeC/AL Development is framed around objects representing different kinds of functionalityand designers associated with each object type. While the details of design for each type ofobjects will be covered in the next chapter, this chapter will introduce readers to the conceptof objects and triggers and present the integrated development environment.In the last recipe we will concentrate on secure access to the NAV server from the web andmobile client.Installing NAV Development EnvironmentThis introductory recipe describes the basic steps of installing the C/SIDE – NAVdevelopment environment. This is the initial requirement for all recipes involving thedevelopment of NAV objects.Getting readyMicrosoft Dynamics NAV 2016 server and development environment can be installed on acomputer running Windows 7 Home edition or higher versions. But the recommendedminimum requirement is Windows 7 Service Pack 1 Professional edition. Some of thefeatures described in this book are supported only on Windows 7.1 Professional or higherversions.If you need detailed instructions on system requirements, refer to the MSDN article SystemRequirements for Microsoft Dynamics NAV 2016. Further in this book, we will assume that theminimum requirements described in this article are satisfied.How to do it 1. Run setup.exe from the installation media.2. After accepting the licensing terms, you will have two setup modes to choosefrom:1. Install Demo: This installs a preconfigured set of components for thedemonstration environment without manual configuration.2. Choose an installation option: You can choose which components toinstall and manually configure setup options.[9]
Writing Basic C/AL Code3. Click the Choose an installation option. Install Demo mode is convenient for aquick unattended setup, but it won't install all components required for therecipes in this book.4. In the list of installation options, choose Developer and click Customize. Optionsthat should be installed include:ClientDevelopment Environment (C/SIDE)Microsoft Office Excel Add-inAdministration ToolServerMicrosoft Office Outlook IntegrationSQL Server Database ComponentsDemo DatabaseMicrosoft Office Outlook Add-inWeb Server Components[ 10 ]
Writing Basic C/AL Code5. Click Next and review the installation parameters.6. If you have the Microsoft SQL Server installed on your development computer,all parameters can be left with their default values. Otherwise specify the SQLServer name and SQL Server instance name.7. Click Apply to run the installation.8. After installation completes, run the Dynamics NAV 2016 DevelopmentEnvironment from the Start menu. C/SIDE client will connect to the SQL Serverdatabase created during the installation process.9. The following is the Object Designer window that opens when you connect tothe database in the NAV Development Environment:If you don't see this window after connecting to the database, click ObjectDesigner in the Tools menu or press Shift F12.[ 11 ]
Writing Basic C/AL CodeHow it works We chose to install Dynamics NAV 2016 components required for the recipes in this book.Extended testability options, automated data capture system, and click Once installationare not covered in the book and remain optional.The help server must be installed if you intend to use local help files. If you prefer lookingfor information on MSDN or online communities, it can be skipped. All referencedocuments installed along with the help server are available online.Installation will create and start a service named DynamicsNAV90. You can find it in yourcomputer's services list as Microsoft Dynamics NAV Server [DynamicsNAV90].To verify the service installation, run the Dynamics NAV 2016 Client from the Start menu,click Select Server in the application menu, and enter the server address:localhost:7046/DynamicsNAV90The web server components installation creates a web site called DynamicsNAV90 in theInternet Information Services.Dynamics NAV can now be accessed in a web browser on the following tion object triggersTrigger is a piece of code that is executed in response to some external action. All objects inNAV, except Menu Suite, have a set of triggers that can be programmed to respond tocertain user's or system actions. For example, when a page with data is displayed on thescreen, a sequence of triggers are fired in the application.OnInit: Page object is initializedOnOpenPage: Page is displayedOnAfterGetRecord: Table record displayed on the page is read from thedatabaseOnAfterGetCurrRecord: Table record currently selected is read from thedatabase[ 12 ]
Writing Basic C/AL CodeThere are other triggers reacting to UI elements, data modifications or to external eventsfrom .NET components. We will delve deeper into different types of objects andcorresponding triggers later in the book. Now let's create a code module (called a codeunitin NAV) with a single trigger that fires when the object is executed.How to do it 1. In the left column of the object designer, click on the codeunit object.2. Click the New button in the bottom part of the Object Designer form. A newcodeunit object is created.3. Ea
Updating NAV data with the Excel add-in. 173. Getting ready. 173. How to do it 174. How it works 176. Retrieving data from NAV with Power Query. 176. Getting ready. 177. How to do it 177. How it works 181. Creating pivot tables with Power Query. 182. Getting ready. 182. How to do it 183. Analyzing data with Power BI. 185. Getting .
