Category Archives: Tips and Tricks

Tips and Tricks

Tip #55 | Add RDLC option to Visual Studio 2015 Community Edition

In Microsoft Dynamics NAV 2016 CU1, support for VS2015 Community Edition has been implemented.

However if you do a default install of VS2015CE, the RDLC editor is not included and your report will show up as XML instead of the visual designer.
Continue reading

Advertisements

Tip #54 | PowerShell Merge – A clustered key can appear only once in a table

If you upgrade your database to NAV2016 using the PowerShell Merge commandlets and have a change to table 49 you will run into this issue.

Table 49 is the Invoice Posting Buffer table that NAV uses to create G/L entries for each unique combination of values in the Sales and Purchase documents. With the NAV2016 release Microsoft have added the Deferral Code field to this table and the primary key.
Continue reading

Tip #53 – Locks, Blocks, Deadlocks and Timeouts | What are they

Recently I’ve been asked to do a code review of a vertical solution. Since these are always interesting projects I cleared my schedule and planned an interview to see what it was about. I’m curious by nature.

During the interview I was told that some of their customers were complaining about deadlocks. This was going on for a while now and they did their best to look at the problem and even created a chart with deadlocks per day.

This was honestly not that much so I figured something else must be wrong. After the intake we spent some time looking at one customers system and I started up some measurements in SQL Server Profiler. Nothing fancy, just some lock events.

2015-09-15_08-06-54

This showed that not deadlocks, but locktimeouts are causing the problem. From a user perspective they almost look the same.

Here is the deadlock message:

deadlock

And the locked message:

Lock

But the source of the problem can be entirely different.

Simulate a (b)lock timeout

This is realatively easy. Simply create one codeunit that reads a customer record and hold the transaction with a confirm. (Never do this in real life kids)

Lock Code

Now simply run this codeunit twice and the timeout message will appear.

Simulate a deadlock

This is a little harder. We need two codeunits this time.

deadlock code

In this scenario we read two different customer records with a confirm in between.

Start both codeunits and then quickly press the confirm message. If you do this within the locktimeout window (default 10 seconds) you will get a deadlock.

Locktable

This last example also proves the myth about locktable busted. This is probably the most ambiguous command in C/AL language. It does not what it implies. It does NOT lock the table, but only locks the specific rows you read in the database.

Microsoft, can you please rename this to something that makes more sense?

Tools

You can also measure blocks and deadlocks using the tools Jorg Stryk provides for free. http://dynamicsuser.net/blogs/stryk/archive/2014/10/10/directions-emea-2014-troubleshooting-nav-2013-r2.aspx

Tip #52 – Run MODAL pages after INSERT

One of the things I spend a lot of time on during my workshop is design for performance and using temporary tables. There are so many great things you can do here.

Let’s look at this code:

Buffer 1

Typically after an insert you would expect this error when trying a RUNMODAL

Buffer 2

But, not in this case. Here we have this:

Buffer 3

The reason why this works is the use of a temporary table. The record is not actually inserted into the SQL Server database.

Buffer 4

This is very useful for example when running a wizard. This allows you to roll back the transaction very easily when the wizard was cancelled.

Do you want to lean more?

During my workshop I spend a lot of time on these typical things in NAV that are poorly documented but very powerful.

The next workshop is in Copenhagen, close to the heart of NAV developement.

Register here. (QBS Partners can use vouchers)

Tip #51 – Scrolling Development Environment in Windows 10

If you install Windows 10 RTM and Dynamics NAV 2015 development environment you will notice that the scrolling on the development environment with your mouse wheel does not work.

Scrolling in Windows 10-1

I was pointed to this by the NAV Yammer group and found it hard to believe since I’ve been testing with Windows 10 for a while.

This is a bug and will be fixed by Microsoft.

Fortunately Mibuso has the answer.

http://mibuso.com/forum/viewtopic.php?f=32&t=64837

It seems to be a setting in Windows 10

Go To: Settings – Device – Mouse & Touchpad – Turn Off “Scroll inactive windows when I hover over them”

Scrolling in Windows 10

Thanks massicm!

Tip #50 – Implementing Addresses

Many, many years ago, in 2009 I’ve written a blog about how to implement No. Series in Microsoft Dynamics NAV. This is one of the oldest features in the product, first introduced in Navision Financials 1.1. When I acquired the product as an end user this was presented as a new feature.

Implementing features like this in your add-on products will make your software recognisable as NAV software and make it easier to use the product for customers.

Another example of a typical way of doing things in NAV is using addresses and formatting them to be printed. This is maybe older than Number Series.

So, since we are doing tip number fifty since I started the first one in 2009 lets focus on that feature and how to implement an address in NAV in such a way that people who are familiar with NAV will recognise it as NAV.

This does not mean that how we work with addresses in NAV cannot be improved, but this is the typical way to implement it in the product.

Description

This creates a standard address format, listing all necessary fields, attributes and methods to generate a basic version of the data entity.

It is being used for master data of the category Business and Document entities.
Examples of usages are:

Business Master Data:

  • Table 18: Customer
  • Table 23: Vendor
  • Table 270: Bank Account
  • Table 5050: Contact

Documents:

  • Table 36: Sales Header
  • Table 38: Purchase Header
  • Table 5900: Service Header
  • Table 5740: Transfer Header

When implemented, addresses have a standard set of fields
Address – Text 50
Address 2 – Text 50
City – Text 50 (Tablerelation: Post Code)
Contact – Text 50
Country/Region Code – Code 10 (Tablerelation: Country/Region)
Post Code – Code 20 (Tablerelation: Post Code)
County – Text 30

The Post Code field and City Code field have the following C/AL Code.

Post Code
PostCode.ValidatePostCode(City,"Post Code",County,"Country/Region Code",(CurrFieldNo <> 0) AND GUIALLOWED);
City
PostCode.ValidateCity(City,"Post Code",County,"Country/Region Code",(CurrFieldNo <> 0) AND GUIALLOWED);

PostCode is a global variable of type record, table Post Code.

Naming & Conventions

Master Data :

  • Standard Field Names

Documents :

  • If multiple addresses exist then : How Used + Field Names
    Example: Bill-to Address
  • If only one address exist then : Standard Field Names

Inheritance

When implemented on documents, data is inherited when the reference to the master data is populated.

"Bill-to Address" := Cust.Address;
"Bill-to Address 2" := Cust."Address 2";
"Bill-to City" := Cust.City;
"Bill-to Post Code" := Cust."Post Code";
"Bill-to County" := Cust.County;
"Bill-to Country/Region Code" := Cust."Country/Region Code";

Users can change the values after they are inherited. When the master data is changed, the documents are not updated.

Printing

The standard way of printing addresses on documents is done using an API, Codeunit 365. For each reference of the address this API has a function that takes the record as a parameter and returns an array of type Text, length 80 with 8 dimensions.
The formatting is done based on a set of business rules in the application that is out of the scope of the blog post.

Example

A document table has a reference to the Customer Master Data and inherits the Address Fields

The table contains a reference to the customer table using a field like

Customer No. Code 20

OnValidate
"Address" := Cust.Address;
"Address 2" := Cust."Address 2";
"City" := Cust.City;
"Post Code" := Cust."Post Code";
"County" := Cust.County;
"Country/Region Code" := Cust."Country/Region Code";

When populated the address fields get copied over from the master data. The address fields reference to the customer table.

Address – Text 50
Address 2 – Text 50
City – Text 50
Contact – Text 50
Country/Region Code – Code 10
Post Code – Code 20
County – Text 30

To format the address fields, a function in the API (Codeunit 365) exists

ExampleDocument(VAR AddrArray : ARRAY [8] OF Text[50];VAR ExampleDocument : Record "Example Document Header")
WITH ExampleDocument DO
  FormatAddr(
    AddrArray,"Ex-Name","Ex-Name 2",'',"Ex-Address","Ex-Address 2",
    "Ex-City","Ex-Post Code","Ex-County","Ex-Country/Region Code");

Specific Example

Addresses are used in Sales Documents like shown in this screenshot
Address Format 1Address Format 2The Values are inherited using C/AL Code
Address Format 3The code for post code and city is validated
Address Format 4We can print the address using functions in the API
Address Format 5On a report this code populates the array to be printed on the report.
Address Format 6

Usages

You can see this used in the following tables & pages:

Master Data: Company Information, Location, Customer, Vendor, Bank Account, Resource, Contact, Employee, Union, Responsibility Center, Work Center, Machine Center.

Other Tables: Customer Bank Account, Vendor Bank Account, Ship-to Address, Order Address, Contact Alt. Address, Alternative Address, Service Item
Processes Job

Documents: Sales Header, Purchase Header, Sales Shipment Header, Sales Invoice Header, Sales Cr.Memo Header, Purch. Rcpt. Header, Purch. Inv. Header, Purch. Cr. Memo Hdr., IC Outbox Sales Header, IC Outbox Purchase Header, Handled IC Outbox Sales Header, Handled IC Outbox Purch. Hdr, IC Inbox Sales Header, IC Inbox Purchase Header, Handled IC Inbox Sales Header, Handled IC Inbox Purch. Header, Reminder Header, Issued Reminder Header, Finance Charge Memo Header, Issued Fin. Charge Memo Header, Service Header, Service Shipment Header, Service Invoice Header, Service Cr.Memo Header, Sales Header Archive, Purchase Header Archive, Transfer Header, Transfer Shipment Header, Transfer Receipt Header, Service Contract Header, Filed Service Contract Header, Return Shipment Header, Return Receipt Header

Consequences

Adding an address reference adds 500 characters (Double Byte) to the table. Each table can only have 8000 characters.

Other applications when using interfaces might use different formatting. Using three address fields is very popular.

This might lead to conflicts in Dynamics NAV

On Master Data, when addresses are implemented often the following fields also exist, although they are not part of the address

Phone No. Text 30
Fax No. Text 30
E-Mail Text 80
Home Page Text 90

References

Elements that are typically used in connection with the Address elements could be the “No. Series”, “Master Data” and/or the “Documents”. Master Data are central to almost everything we do, so most elements connect in one way or another to the Master Data.

Tip #49 | PowerShell, break long lines into readable code

Unlike other languages PowerShell does not allow you to just continue a command on a new line.

You have to specifically tell it to do that by adding a “Backtick” character.

This one:

`

Example:

Split-NAVApplicationObjectFile -Source “C:\Users\Mark Brummel\Documents\Export.txt” `

                               -Destination “C:\Users\Mark Brummel\Documents\Files\” `

                               -Force