Tag Archives: NAV2013

My New Book | Microsoft Dynamics NAV 2013 (R2) Application Design

Finally! It has (almost) arrived.

On September 18th my new book Microsoft Dynamics NAV 2013 Application Design is scheduled to be shipped.

MicrosoftDynamicsNAV2013ApplicationDesign.jpg

The book is packed with design patterns and walk throughs of both standard NAV and a ton of customisations.

It covers the application from Bookkeeping to Manufacturing and Sales & Purchasing to Jobs.

Inside the book I have designed two complete add-on product with application objects, design patterns and much more.

Ever wondered what the validation flow of the sales and puchase lines are? Did you know there are (at least) five ways to setup Warehouse Management? It’s all described in my book, which makes it perfect reading for both functional oriented people and technical folks.

You can pre-order the book from this link:

https://www.packtpub.com/application-development/microsoft-dynamics-nav-2013-application-design

If you like the book, please also like the facebook page about the book here:

https://www.facebook.com/MicrosoftDynamicsNAV2013ApplicationDesign

What You Will Learn

  • Set up and customize the Dynamics NAV ERP suite for various industries
  • Study dozens of design patterns used in standard applications
  • Customize Microsoft’s application features and extend them safely
  • Use blueprints, design patterns, and application objects for Equipment Reservations and Transport Management
  • Master the fundamentals of application design and learn about B2B and B2C interfacing
  • Design applications that strike a balance between total cost of ownership and functionality
  • Extend your core applications using interfaces with Flatfile, CSV, XMLPorts, ADO, EDIFACT, and Webservices

Forewords by Michael Nielsen, Director of Engineering for NAV at Microsoft and David Studebaker, co-author of Programming Microsoft Dynamics NAV 2013

Michael Nielsen

Most books about Microsoft Dynamics NAV are about the tooling and the platform – but this is different and describes the building blocks and the code and metadata patterns that the application is made up of. The foundation for the patterns was created many years ago when we developed  the first versions of Microsoft Dynamics NAV, and since then they have be reused over and over again by us and every developer in the partner channel through copy-paste. However, it was not until Marije and her friends in PRS, Gary and Waldo, re-discovered the patterns and started to write books. blog, teach and speak at conferences that they became broader known.

Over time, this has evolved into an larger effort in collaboration with Microsoft the Microsoft Dynamics NAV developer community that has improved the overall quality of customization projects and reduced the implementation time. This book is important because it describes the most used patterns and how to leverage them when you modify Microsoft Dynamics NAV to suit industry-specific needs. It also contains examples on how to create add-ons and other enhancements that are easily upgraded.

Everything is based on Marijes huge experience of designing Microsoft Dynamics NAV implementations over almost two decades and expresses Marijes dedication to the Microsoft Dynamics NAV product and the many passionate people in the Microsoft Dynamics NAV community.

David Studebaker

Marije Brummel is the person other Microsoft Dynamics NAV experts go to when they have a really hard problem to solve.  In this book, Marije shares knowledge she has gained over the past two decades.  In my own work as a Microsoft Dynamics NAV consultant/developer, and as an author (writing three texts on programming in different versions of Microsoft Dynamics NAV), I’ve had the opportunity to draw on Marijes expertise many times.  By reading this book, you now have a chance to do the same.

I’ve known Marije since we met at Tech-Ed in Boston in 2006.  Marije was already an accomplished analyst and developer, an expert in Microsoft Dynamics NAV and an MVP.  Marije had been invited to that conference by Microsoft to help represent Microsoft Dynamics NAV to the other technically expert attendees.  Since then, Marije has gained considerable experience and broadened her knowledge, particularly in the areas of business application problem solving using Microsoft Dynamics NAV.

Microsoft Dynamics NAV is a very special business software product.  Included in the product are a set of IDE tools designed for business applications development and tailored to Microsoft Dynamics NAV. This makes it easier to create enhancements to fit the needs of specific businesses.  As an integrated, full featured ERP system, Microsoft Dynamics NAV includes functionalities for accounting, order processing, inventory control, manufacturing, distribution, service management, materials planning and asset management.  This book provides examples of how to choose from and apply these in a variety of business situations.

Each new version of Microsoft Dynamics NAV has delivered both new application functionality and new technical capability.  Upcoming versions are becoming more compatible with “the Cloud”, mobile users and various user interface devices.  Each of these new capabilities expands the types of business applications to which Microsoft Dynamics NAV can appropriately be applied.  With a worldwide installed base already of well over 1,000,000 users, we know the uses of Microsoft Dynamics NAV are only limited by how creatively we apply our knowledge.  Read on, let Marije expand your knowledge, then use your own creativity to apply Microsoft Dynamics NAV to the needs of your business.

NAV 2013 R2 | Debugger Watches

I totaly love the concept of pages. They are easier to merge and upgrade and they way they render is awesome. However, they come with a few drawbacks. Some of them we already had in the classic client with forms, some are new.

One of the drawbacks is the lack of drag and drop. This was true for the classic client too.

A place in Dynamics NAV where it is very obvious what would be the value of drag and drop is the debugger. The debugger in NAV2013(R2) is designed as a page.

Like this:

Dynamics-NAV-Debugger-2013-1

In general it works pretty good. It is easier to break, toggle breakpoints and the fact that you can debug any user on the servicetier is just straigt from heaven.

But one of the things that was a 1000 times easier in the old debugger was watches. It is no longer possible to just drag and drop them.

However… I only recently found a neat trick and I figured “how is it that I did not notice this earlier”.

The AL codepart is developped as a client add-in which allows to send events to the page. One of the events is adding a watch while hovering over a variable like this:

Dynamics-NAV-Debugger-2013-2

Now take a close look at the icon

Dynamics-NAV-Debugger-2013-3

If you push the + sign the variable is added to the watchlist.

Personally I find myself pretty foolish not noticing it for more than like 2 or 3 years but hey, I might not be the only one.

Here is the event in AL:

Dynamics-NAV-Debugger-2013-4

Enjoy

NAV2013 | Excel Buffer

Editors Note : This article was moved from my old bolg.

The Excel Buffer table. Loved by many, unkown to most.

Here is the description on MSDN.

If you still don’t have a clue what I’m talking about, then allow me to explain. Else skip this paragraph and continue to the end of this blog post.

Excel is the most used application to use in combination with ERP but it was not always in Dynamics NAV (Navision).

The interface to Excel that we know as the Excel Buffer table was intruced in the same timeframe as Automation Control using that technology to provide the interface.

Currently the interface looks like this

The reason I call it an interface rather than a table is because that’s what it is. There is never data stored in the table on SQL Server. It is always used as a temporary table.

I’ve written small example application on Mibuso that demonstrates how this is used. With almost 7000 downloads it is by far the most popular one of my downloads on Mibuso. Probably because Google likes “Excel Buffer” and people download it who don’t have a clue about NAV.

So to create an interface with NAV the excel buffer is your friend. It allowes both reading from and writing to excel which most of us know best from the Bugetting functionality in NAV. See : How to: Import or Export a Budget (MSDN).

And now what. Now we know what to use Excel buffer is for. But the Excel buffer has it’s limmitations. Here are the ones I find missing:

  • Multiple Sheets
  • Font
  • Size
  • Backgroundcolor (preferably HTML coded)
  • Foregroundcolor
  • Hyperlinks

Now before NAV2013 you could easily add this functionality yourself. Each cell is represented by a value of the list in the interface (let’s use dotnet terminology) and by adding members to the list which you then map to the properties of the cell.

Like this:

That was possible because NAV was using the Office Automation libraries directly. However in NAV2013 they are using a wrapper dll to accomodate Office Interop.

And the wrapper DLL simply does not have the functions.

And this has been recognised by both the channel and Microsoft as an issue.

Here is a link to a discussion on Mibuso

And  here are solutions provided by the NAV Team. (one and two)

But the workarounds by the NAV team are difficult and not as neat as we had in the old days.

Now there are three different ways you can handle this.

First one is to move back to Automation Control. Simply import the Excel Buffer from NAV2009 and you’re done. But that won’t work on the service tier and is dreadfully slow.

Second one if to implement ClosedXML as hinted by Arend Jan Kauffmann in his blog.

I can really recommend this solution. The ClosedXML is a wrapper around OpenXML (see humor here, laugh please) and basically provides the same interface we had with Automation Control to DotNet. It’s extremely fast and very easy to work with.

It allowes you to use the colors and hyperlink parameters and using of extra sheets and tons and tons more.

What I did is renumber the excel buffer table to something outside the customer number range (it does not have to be in the license, it’s an interface, not a table) and implement ClosedXML.

If enough comments are left on this blog I will make it available as download on Mibuso.

But the real solution would be to have the NAV team implement the things everyone creates on projects. I think my list is a good start.

Last week I was in Vedbaek and they asked me to provide a list of the things I need. Here it is. Do you have any other request? Please leave them as a comment of mail them to me.

Thanks for reading.

 

NAV 2013 | No more Indexhinting

By Marije Brummel, founder of NAV-Skills

UPDATE: I’ve heard this was fixed in 2016. Dit not test this myself.

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the past.

http://mibuso.com/forum/viewtopic.php?f=34&t=13154&hilit=hinting

http://www.mibuso.com/dlinfo.asp?FileID=896

With NAV 2013 it is no longer possible to setup index hints using the $ndo$dbconfig table.

Does it mean it’s no longer nescesairy.

Unfortunately not.

I found out about this during an upgrade of one of my customers from NAV2009 RTC to NAV2013.

We experienced extremely slow performance in an area where NAV2009 was much faster than NAV2013. And here I promised better performance… DAMN. Stick out tongue

Off course with our perfect documentation (not) it took some time to figure out it might be that index hinting was used in NAV2009 to force SQL using an index it would not use normally.

The process is selecting shipments to be planned in a trip for a taxicompany. We use a common method to select a shipment using the user id, just like you would when applying customer and vendor ledger entries. When selected it’s an extremely selective field (only one or two record of a million have this value) but SQL does not pick it up because… because… shoot me, I don’t know why not. DAMN 2. Stick out tongue

So with index hinting no longer possible (confirmed by Microsoft, done on purpose, by redesign) I needed to be creative.

The solution is to save a pointer to a shipment in a second table called “Selected Shipment”. All shipment that need to be planned in a trip are temporarily stored there and picked up and deleted afterwards. Depending on the architecture of your transaction this could even be done in a temporary table and/or a single instance codeunit.

So recap: no more indexhinting, be carefull with upgrades and creative with solutions.

And PS: The general performance of NAV 2013 is AWESOME!!!

NAV 2013 | SETAUTOCALCFIELDS

NAV 2013 | SETAUTOCALCFIELDS

In NAV 2013 we have a new command SETAUTOCALCFIELDS.

What does this command do.

It is used in C/AL like this:

Cust.SETAUTOCALCFIELDS(Balance)

IF Cust.FINDSET THEN REPEAT

Some Code

UNTIL Cust.NEXT = 0;

This leads to this query in SQL Server:

SELECT

ISNULL(“Customer”.”timestamp”,@0) AS ISNULL(“Customer”.”No_”,@1) AS “No_”,…,

ISNULL(“SUB$Balance”.”Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”,@77) AS “Balance”

FROM “CRONUS Nederland BV$Customer” AS “Customer” WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Balance$Detailed Cust_ Ledg_ Entry”.”Amount”),@76) AS “Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”

FROM “CRONUS Nederland BV$Detailed Cust_ Ledg_ Entry” AS “Balance$Detailed Cust_ Ledg_ Entry” WITH(READUNCOMMITTED)

WHERE (“Balance$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Balance”

Hence, a single SQL Statement that joins two tables.

This dramatically changes the teaching that has been done about T-SQL generation in NAV from the last few years.

Also, notice that the SIFT view is not used.

BE AWARE

Like a filter,  SETAUTOCALCFIELDS keeps being active on a variable until it is reset.

To reset this command Cust.SETAUTOCALCFIELDS should be used. So without any fields, just like resetting a filter with SETRANGE.

Thanks to Luc van Vugt to bring this to my attention.

TIP #35 | Using Queries in Pages & Reports

TIP #35 | Using Queries in Pages & Reports

This post is an extention of the post I did yesterday about Queries and performance.

As I said Queries deserve more attention. Unfortunately this is another post about what Queries cannot be used for, but with a workaround.

Whenever you want to make a report or page (or form in the old days) show data from two or more tables in one view you run into a challenge. Even though reports allow complex datasets they are easier to design if your dataset is single layer.

Technically it has always been possible to link a table object in NAV to a SQL Server view allowing you to join tables on SQL Server and have a single view in NAV. I never bloged about this cause I’m not a huge fan of this but more information can be found here on MSDN.

The first question that I can remember being in a session at MDCC about queries few years ago after the applause was: “Can we use this as a source for a page or a report?”. The answer was, “That would be great but is not within the scope of the current project”. And today after the release (soon) of R2 this is still the case, it is out of scope and my guess is it will be for a while.

The single and most important reason queries are here is to avoid the loopy-loopy code patterns.

But what if we want to use the query as a source for pages or reports. Well, that solution was presented during that same session at MDCC within minutes: “Then you use the integer table”.

And yes, that would be the solution. Or at least one solution, there is a second more elegant solution.

Let’s discuss the integer solution first.

An example of using the integer table for reporting has always been report 111 Customer – Top 10 List. This report allows you to show the top X customers by sales in your system. X defines the number of loops over the integer table.

As you might (or should) know the integer table is a system table (2000000026) that has been there forever allowing you to build repeat code Patterns. A similar table is the Date table (2000000007).

Writing this I realise that these system tables deserve their own blog article too. Stick out tongue

So let’s start with a cool query that we will use in our page.

The query combines sales information from the Value entries per customer. Now how do we show this in a page.

First step is to create a new listpage with the wizard based on the Integer table like this:

When we are in the page designer the next step is to add the query as a global variable and add a counter variable. Then we add all the query fields to the page and write some code as displayed

You can immediately see that this is not a real nice solution cause we loop at least twice trough the query. This is to get the number of rows to filter on the integer table.

When we run this page on Cronus we see this result:

And yes, we are happy and dancing.

But,

Try page up and down a little now..

Ups…

Now you might have a solution but I could not find one. And there are more issues with this way of showing a query in a page, we cannot filter, we cannot sort.

So what is the solution?

SourceTableTemporary

Quite a few years ago I have blogged about this property. And the power still counts.

But Mark, are you saying we should create a table now? Yes you should. But, that is an extra object! I know but it is free. Tables that are only used temporary do not need to be in a customers license.

So let’s continue.

First we create the table, with Entry No. as primairy key

Remember this table is free of charge, no license required. We only use it as buffer.

Step 2 is to create a new page on this table and toggle SourceTableTemporary to Yes.

And we add a very small amount of code

And this code we run from the OnOpenPage trigger and voila: this is the result:

All the gizmo’s work like filtering and the new interactive sort on all columns that is introduced in NAV 2013 R2

Hope this post was not to long.

Enjoy!

Advertisement: I do Tips & Tricks workshops and What’s new training! Send me an email if you are interested or fill out this contact form!

Table vs. Query | Performance Battle

Table vs. Query | Performance Battle

NAV2013 introduced the Query object. Frankly I think that this new object type deserves more attention from bloggers. We have been nagging Microsoft for years to deliver this and now it is there and what happens? Quietness.

I have at least two blogs around Queries. This one is the first. It is an important one because I want to blowup a myth even before it get’s the chance of becoming one.

Queries have two advantages when it comes to reading performance that tables don’t have. First you can limit the amount of data you retrieve from the server by defining only the specific columns you need and second you can join multiple tables into one query saving noumerous roundtrips to the server with our good old loopy-loopy code pattern.

But Queries can’t do what Tables can, they cannot write to the database.

And there is more that Queries cannot do. Let me explain with an example.

In my example I’ve created a realy simple query that contains a few fields from the Item Ledger Entry table

I use this Query in a Codeunit along with the Table variable itsself.

This is a very stupid piece of code that reads the same Item Ledger Entry four times. Twice from the table and twice with the query.

And yes, we get four messageboxes with the Document No.

So what is the clue?

Let’s look at what NAV Sends to SQL Server with Profiler…

Please note that NAV only issues three SQL Statements, not four. And I can tell you a Little secret, if you run this again, it will only issue two.

The two statements that are the same are the Query statements. True, they are more lightweight than the Table Query but unless you have a covering index it will read the entire record in SQL anyway.

Caching

Another thing that NAV2013 introduces is Service Tier caching. All the users on the same service tier share the same reading cache. So if I read customer 10000 and another user wants this data it is not read from SQL Server but from cache. NAV Cache.

Unfortunately this only works for Tables, not for Queries.

So remember this when you design your solution. If you read the same data over and over again, a Query might be overkill.

If you want my opinion you should only use queries when joining multiple tables. Never ever use queries in a single table scenario. The only thing you do is adding an extra layer of complexity to your solution and add extra objectcosts for your customer.

Thanks Waldo for the tip!