Tip #34 | RDLC Row background itteration

This Blog Article was brought forward from my old blog

As I described in my previous post, we will get report design guidlines.

One of these guidlines is to use itterated background changes in list reports. This looks realy cool, just like listplaces in the RTC. But how do you implement this. Wel, it is extremely easy.


This is a property that every contol has in RDLC and you can populate it with a name or hexnumber representing the color


In most RDLC properties you can write expressions. This is a very easy language which is unfortunately different from C/AL. For example the IF/ELSE statement is IIF(Condition, Yesresult, Noresult). But still easy to learn.

Built In Report variables

One thing I advice everyone to learn is that RDLC has built in variables that you can use, similar to CurrReport.PAGENO in the classic designer, but TONS and TONS more.

One of them is RowNumber.

If you combine this you can create an expression like this:



Nothing) mod 2, “LightGrey”, “White”)


The result is making reports easily look like this:

Tip #33 | Smart Debugging

This Blog Article was brought forward from my old blog

One of the “Real life” tips I showed at NavTechDays is how to use the classic debugger smartly.

What I see people do many times is turn on the debugger with Breakpoints on Triggers

This means that the debug process wil break on each trigger it finds, meaning form & table triggers, functions and objects.

Even if you step through this process with F5, debugging a posting process like Sales wil take forever as there are hundreds of triggers that you will pass.

So what is the alternative.

The alternative is to place your own Breakpoints; this can be done in two ways

1. Via the Object Desiger

If your license allows you to edit C/AL code you can use this icon to go to the code of each object.

If you do this you’ll enter the C/AL Editor that shows the code behind the application. But if you look closer there is a hidden “column” in this editor.

If you use the “Show Column” feature you’ll see that this column even has a name: Breakpoint Column

If you select a line of code and hit F9 you’ll see that a “Red Dot” appears in the column, meaning there is a breakpoint for the debugger.

Whenever you start the debugger now without the “Break on triggers” option, you’ll see that the debugger will stop at this point, allowing you to go directly to the “suspected” piece of code.

But wait, there is more.

2. Via the debugger

You cannot just put breakpoints in the C/AL code via the object designer, you can also set them in the debugger directly, while debugging.

This allows you to set a new breakpoint somewhere down the line. This avoids the issue of stepping into all the functions and losing time.

Be Careful: This only works in the active object! You cannot step back into the previous object and add a breakpoint there.

They communicate!

The great feature about these breakpoints is that the debugger and code editor remember each others breakpoints. So if you place a breakpoint in the debugger, the red dot wil appear in the C/AL editor. This is done using the system table Breakpoint that is not visible in the Object Designer but is from the Report/Form/Page designer.

So remember using this smartly and save a lot of time in your debugging processes.

What does the open “red dot” mean?

Was one of the questions on NavTechDays, and I have to admin that I did not knew. But fortunately Lars Hammer of Microsoft was in the room and he knew. (And he should).

This allows you to temporarily disable a breakpoint without throwing it completely away. So you can quickly reenable it.

Stop Debugging

Another great feature about the debugger is the possibilty to roll-back a transaction that you are debugging. This is often useful when you have a scenario that requires a long time to setup and you don’t want to backup and restore databases just to have the same scenario while finetuning your code.

Remember this when you need to fiddle with your code and you want to see the step-by-step results.

Call Stack

In Dynamics NAV you are likely to step into a loop of objects if you debug a complex process. Sometimes you hit an error somewhere in a tree and you want to see where this code was executed from. This is where you can use the Call Stack.

In this example, Codeunit 80 was called from codeunit 81 and before that from Form 42. You can double click on an object and see which line of code caused the execution of that object. This you can then use to stop the debug process, go to the object and place your breakpoint there. Remember: you cannot add breakpoints in the debugger in a tree of objects.

Dirty Reads

Sometimes you would like to see the data that your process creates without actually committing the transaction so you can roll-back and change your code. With the SQL Server option you can do that since NAV is using the ReadUncommitted isolation level. This allows users to see data in the database that is not committed yet.

NOTE: with the introduction of buffered inserts you might not see the inserted data since that is buffered to either a find command on that table or the commit of the transaction. If you do want to see the uncoommitted data and you know where to go, then put in a simple FIND statement on a copy variable of the record object.

That’s al for today folks.

Tip #32 | Flowfield Calculation in RTC

This Blog Article was brought forward from my old blog

Update 2014 : This is obsolete from version 2013 and onwards

In the classic client we are used to getting a runtime error when a flowfield cannot be calculated if the key metadata is missing:

This no longer is the case in the RTC, it will just show zeroes:

I’m not sure if this is a bug or a design decision.

Tip #31 | Print Report ID on Reports

When an end user asks you to change a report, they often show a paper print of the report with the changes drawn with a pen or marker.

They often (or always) don’t know the report number and if you’re unlucky the report is printed in some automatic process and you’ll have to dig into the code to find the report ID.

This can be easily solved by printing the report number in the header or footer like this:

When doing this on Internal Reports there should be no problem.

Take care,


PS: With thanks to Reinder van der Veen.

Tip #30 | Reading 1000 + Characters from a File

This Blog Article was brought forward from my old blog

UPDATE 2014 : In current versions with DotNet interop you would probably want to use that instead.

As many of you know there is a lenght limitation of text variables in Microsoft Dynamics NAV.

One of the real-life problems you can encounter due to this is when doing interfacing, especially when reading EDI files.

EDI files are often very long and without Cariage Return Line Feeds.

Solutions are using a dataport with a delimiter such as ‘+’ instead of NewLine or asking the EDI vendor to implement CRLF’s.

If both are not possible, for example if you want to read the EDI files though a NAS you have to use a codeunit with File variable.

Using the File variable with long textlines with result in overflow errors.

The solution to work around this is using a stream variable. This is how:

i := 1;

EDIFile.TEXTMODE(FALSE); EDIFile.OPEN(NewFileName); EDIFile.CREATEINSTREAM(EDIInStream); WHILE NOT EDIInStream.EOS DO BEGIN   EDIProcBufferTemp.”Entry No.” := i;   i := i + 1;   EDIInStream.READTEXT(EDIProcBufferTemp.”EDI Text”, 250);   EDIProcBufferTemp.INSERT; END;

EDIProcBufferTemp.FINDFIRST; EDIText :=  EDIProcBufferTemp.”EDI Text”;

WHILE NOT Finished DO BEGIN   IF STRPOS(EDIText, ‘+’) = 0 THEN BEGIN     EDIProcBufferTemp.DELETE;     IF EDIProcBufferTemp.FINDFIRST THEN       EDIText := EDIText + EDIProcBufferTemp.”EDI Text”     ELSE       Finished := TRUE;   END;     IF STRPOS(EDIText, ‘+’) <> 0 THEN BEGIN     EDITag := COPYSTR(EDIText, 1, STRPOS(EDIText, ‘+’) – 1);     EDIText := COPYSTR(EDIText, STRPOS(EDIText, ‘+’) + 1, 1000);     SplitInfo(EDITag);   END ELSE     SplitInfo(EDIText);  //* Last Tag END;

In this example I move the contents of the textfile to a InSteam variable. The Insteam variable supports the ReadText method that allows us to read the content in smaller chunks.

Then I move the lines into a buffer table for easier processing. (I love working with buffer tables, they make cleaner coding).

Since breaking the file into 250 characters might actualy split some of the EDI tags, I re-connect the lines into a larger text varible. I can easily merge two 250 character lines into one 1000 character variable.

Remember to also process the last tag when there are no more delimiters.

Here you are, good luck.

If you want to learn more about interfacing you can read chapter 9 of my book which is dedicated to this subject.

Tip #39 | Pipe Filter with Excel | Alternative

I just looked at the video by Kerry Rosvold on how to make a pipe filter in excel. It’s awesome. You can watch it here.

Now, I have an alternative way that I have been using for the last many many years and I don’t understand why I never shared it.

Blogging it now kinda make me a smart-ass. Maybe I am that. I apologize. 😉

So this is how I do it.

You copy a few lines to excel like this:


And in Excel you paste, and copy the number column


And you paste/transpose and copy the results again


Then you open a notepad, paste the rows and copy the tab character


Then you select find/replace, paste the tab character and replace with the pipe sign


There you have your filter.

Now you have two options. Maybe there are more.

Thanks again Kerry!


This week I got a question from one of my book readers about how to convert a SETSELECTIONFILTER to a SETFILTER command.

This is something that is implemented in the standard product on a few list pages. The Customer List page (22) is one of them.

The code is handled in codeunit 46, SelectionFilterManagement

This is the code:

 TempRecRefCount := TempRecRef.COUNT;
 IF TempRecRefCount > 0 THEN BEGIN
   WHILE TempRecRefCount > 0 DO BEGIN
     TempRecRefCount := TempRecRefCount - 1;
     FieldRef := RecRef.FIELD(SelectionFieldID);
     FirstRecRef := FORMAT(FieldRef.VALUE);
     LastRecRef := FirstRecRef;
     More := TempRecRefCount > 0;
     WHILE More DO
       IF RecRef.NEXT = 0 THEN
         More := FALSE
         SavePos := TempRecRef.GETPOSITION;
         IF NOT TempRecRef.FIND THEN BEGIN
           More := FALSE;
           FieldRef := RecRef.FIELD(SelectionFieldID);
           LastRecRef := FORMAT(FieldRef.VALUE);
           TempRecRefCount := TempRecRefCount - 1;
           IF TempRecRefCount = 0 THEN
             More := FALSE;
       IF SelectionFilter <> '' THEN
         SelectionFilter := SelectionFilter + '|';
       IF FirstRecRef = LastRecRef THEN
         SelectionFilter := SelectionFilter + AddQuotes(FirstRecRef)
         SelectionFilter := SelectionFilter + AddQuotes(FirstRecRef) + '..' + AddQuotes(LastRecRef);
       IF TempRecRefCount > 0 THEN

In older versions than 2013, this code is on the page/form object.


Tip #37 | Tracking Shipments from Dynamics NAV [Edited]

NOTE: This blog entry was edited after a comment from one of my readers.

Last week during one of my sessions for the NAVUG Summit one of the attendees asked a question about best practices to track shipments from NAV. The scenario is that you ship via UPS or Fedex and want to see the status. Answering the question, I obviously overdesigned the solution thinking about a factbox with a webservice calling out and displaying the status in the factbox.

Keith Owen came up with a much simpler solution that he already implemented and was willing to share. After publishing the first version of this post I edited it, since I was not aware that this was even a standard feature of NAV.


Instead of showing the actual status in Dynamics NAV, you run a hyperlink against a predefined URL.

This URL can be stored in the shipping agent table, and from the Sales Shipment you can call the Track Package action.


Thanks Keith! NAVUG

Tip #36 | Using Mark and MarkedOnly in the Role Tailored Client

I always wanted to blog about myself, but humble as I am I never got around to do that. 😉

But now I have an excuse.

When the Role Tailored Client was introduced in 2009 one of the things that is missing from the UI was the ability to Mark some records and filter on Marked Only records. This is usefull when you want a subset of your data with records that have no common value to filter on.

In the Classic Client, now Development Environment, you can find the functions here:

Mark1 Mark2

However, this feature was discontinued from the IU, but not from C/AL.

In the C/AL Symbol Menu it is still possible to Mark records and filter them.


So let’s see what happens if we use that in a Page step-by-step. We’ll use the Customer List (Page 22)

Step 1 – Add a new Action Category (Optional)

To make it easy for end users to find the options we’ll start by adding a category for the actions we are going to add later.


Note that in order to add a new Category you need to also add the default ones. In this case we will work with Category4.

Step 2 – Add Actions with the correct properties

We add three actions in the ActionItems category


And we assign a Name, Caption, Image and PromotedCategory. I also choose to make them big and promoted.


Step 3 – Add C/AL Code

We add the C/AL commands to the actions


Note that we use the reverse value to toggle the values on and off

Step 4 – Add a column indicator

To make is easy for users to see if a record is marked or not we add a new column to the page with Mark as the source expression


The Result

When we run the page you see that we can Mark records which will be indicated in the new column, filter on them and easily clear the Marks.


TIP: Copy/Paste

Since we did not use any variables or functions we can just copy/paste the actions around to any other page, as long as the ActionCategoryML does not conflict with existing values.


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.


Try page up and down a little now..


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?


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.


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!