Tip #29 | Formatting in RDLC

This Blog Article was brought forward from my old blog

Ok, it has been a while since I blogged. One of the reasons for that was that we bought another house and moved last month. This has a huge impact on a family with young children and my wife is pregnant. Right now we are settled and everything is starting to be on its place.

Another reason is, I have to be honest, that after finishing the book my mind was not exactly on writing at all. The book has been publised now for quite a while and I had many nice reactions. I even signed a couple of books which feels very special.

Enough about me, what is this blog entry about. A tip, yes. Number 29 already. There has been a lot of talk about the changes that RDLC reporting brings to Dynamics NAV. A lot of good things, new possibilities, but also a new learning curve for developers. Also for me.

A while ago I was fiddling with formatting in RDLC. The syntax for formatting is different compared to the classic report designer. For example a decimal number can be formatted as #.##000. The format is saved in the Format propery of each field.

Since Dynamics NAV (Navision) can have dynamic decimal formatting depending on the Currency code this format is by default brought over in the DataSet, for example in the Sales Invoice (Report 206, my favorite) you have Sales_Invoice_Line__Line_Amount_Format.Value representing the format for the Line Amount field.

However, for many Navision end-users formatting is not such rocket science. They have formatting which is always the same.

Good news for these scenario’s is that RDLC has some formatting shorcuts that can be easily implemented.

Here is a list:

Format string Name
C or c Currency
D or d Decimal
E or e Scientific
F or f Fixed-point
G or g General
N or n Number
P or p Percentage
R or r Round-trip
X or x Hexadecimal

How easy can you have it…

So if you want a DateTime field to be Time formatted you just enter t in the format property. If you enter P0 your percentage will be formatted as 80%. How cool is that.

Formatting Dates is just as easy. The following table lists common .NET Framework date formatting strings.

Format string Name
d Short date
D Long date
t Short time
T Long time
f Full date/time (short time)
F Full date/time (long time)
g General date/time (short time)
G General date/time (long time)
M or m Month day
R or r RFC1123 pattern
Y or y Year month

So did I find this out myself? No… I used google. (not Bing, sorry Microsoft).

Here is the link.

Enjoy. I hope to blog more in the near future but no prommisses.

This blog ends here, I am going to prepare my presenatation for Directions USA in october.

Format string Name
C or c Currency
D or d Decimal
E or e Scientific
F or f Fixed-point
G or g General
N or n Number
P or p Percentage
R or r Round-trip
X or x Hexadecimal

Tip #28 – Count records in List Pages

This Blog Article was brought forward from my old blog

In the classic client it was a requirement from a lot of end-users to easily count filtered records in a listform. This can be solved by adding a control with SourceExpr COUNT on the form.

Off course we should watch for performance but with Master Data this should not be an issue.

But now we have pages, so how can we implement that? It’s not wysiwyg. Where do I put the control.

This is what I did:

In the List Page search for the end of the group repeater and add a new group group. In this group add a field with SourceExpr Count and add some captions.

This is the result:

This is just what I experimented and implemented, it is not supported by Microsoft!

Tip #25 – Update Header Page from the Line Page

This Blog Article was brought forward from my old blog

Update 2014 : With NAV2015 we now have the UpdatePropagation property that does this

Yes! It is possible!

This has always been a difficult trick, even in the good old classic client where youl’d use the dirty timer to do this, but in the RTC it is much, much easier.

So what do I mean… and why am I so entousiastic.

Let’s say you have added the total document amount as a flowfield to the purchase header…

And when you change the lines you want it to update but it doesn’t right…

So let’s make it happen. It’s really easy.

Go to the subpage (Purchase Order Subform, 54) and create a new function UpdateThePage.

And key in this code with the proper automation variable…

Yes, I see you smile. Big Smile Remember this blog?

So let’s call this function after we entered the Unit Cost.

The reason for two F5’s is that it does not update the main page on the first, only the fact boxes.

Be carefull with this hack since all flowfields are recalculated and there will be trafic to the SQL Box.


Tip #24 – The RunFormMode Property

This Blog Article was brought forward from my old blog

When you are on a Role Center you often see the New This and New That option…

Today I wanted to make one of my own and discovered a new property with a weird name.

Let’s create a New Vendor property on this page.

Let’s attach the Vendor Card page to this action…

And test…

You’ll see that it will just open on the first Vendor Record.

This is not the intended behavior, so how do we implement that? Yes, with the RunFormMode property.

I have no idea why this is called RunFormMode… dear Microsoft, it is a Page property…

But let’s change it to Create.

And retest,

And YES, it opens on a new, blank record.


Tip #22 – Open Page in New Window

This Blog Article was brought forward from my old blog

Again, this is a tip comming directly from one of the key users in my current RTC implementation.

When you open a list page from the Role Center you’ll get an embedded page. You can release this embedded page by clicking actions, Open in New Window.

And voila,

This way they can keep the page open and go somewhere else.

Good job Microsoft !! Cool

Tip #20 – Save Report Usage

This Blog Article was brought forward from my old blog

This is a hot subject for a lot of companies using Navision. When was a report last executed, by whom and how many times.

The classic solution for this is to add code to every report to store this information in a table.

But it can be solved more generic…

The solution is in Codeunit 1. Here is a trigger FindPrinter that is executed prior to each report being printed. So what about implementing the solution here.

In order for this to work, we have to implement a trick, because when this code is executed, Navision is not yet in transaction state.

The trick is to save the report usage in a Temporary table first inside a SingleInstance codeunit.

What? Stick out tongue

Ok, let me explain this.

SingleInstance codeunits were introduced years ago. The benefit of this type of codeunits is that all variables are stored in client memory during the lifetime of the session. This includes temporary tables.

To activate this propery, just go in the codeunit’s property window.

So what else do we need…

First of all the table to store data in. Off course you can make this as high tech as you want, but for this example we will store the Repord ID, the User ID, the number of runs and the last execution date.

The second thing we need is a management codeunit, the SingleInstance one.

This codeunit has two functions. One to populate/maintain the temporary table, and one to store the data in an actual table.

Remember to make the variable Temporary

Last change is to manage these functions from codeunit 1.

This is done in the FindPrinter and LogInEnd functions.

Remember that in both functions the RepUsageMgt codeunit is a LOCAL variable.


Oh, and yes, this works in the Role Tailored Client as well.

Tip #26 – Grouping in the HomeItems

Ever noticed the grouping in your homeitems?

I did, and today I decided to find out.

If you go to your Activities page of your role center (in my case the Purchase Agent Activities) you see the cues that are defined. Most cues are flowfields in the source. To open the correct page on clicking they have a DrillDownFormID.

This is where the grouping starts. Please notice the page.

Now let’s go to the Role Center. Mine is 9007, Purchasing Agent Role Center.

This page has a lot of Actions devided into different groups, together creating the user experience. One of these groups is the HomeItems creating the menu items we are looking for.

The first Action of the HomeItems is the Purchase Orders. Let’s look at the properties…

And notice the same page as in the Activities. This is what Dynamics NAV is using for the grouping. See what happens if I remove it?

The grouping is completely gone!


Tip #27 – Using Temp tables in Arrays

Tip #27 – Using Temp tables in Arrays

I’ll be honest, I found this out by accident lately but I want to share it anyway. Since it took me almost 15 years to find, it might not be know to some others.

As many of you know, using temporary tables in Microsoft Dynamics NAV is a very powerfull feature. We can use it to buffer data, to simulate SELECT DISTINCT etc. And they are free.

Using temporary tables becomes even more powerfull when adding an array to it. Let’s see this in an example.

We’ll create a new codeunit with a variable CustTemp that has two dimensions.

So now we can use CustTemp[1] and CustTemp[2].

Let’s write some C/AL Code with this.

We create a new record using CustTemp[1]. We then try a FINDFIRST on CustTemp[2]. Let’s see what message we get.

How cool is this! The contents of the CustTemp[2] is the same as that of CustTemp[1].

So what can we do with this. Let’s add some more code.

We add a new record to CustTemp[2] and filter on this value using CustTemp[1]. Then we show the value of Name and the number of records in CustTemp[2].

What does this give?

The CustTemp[1] now has the value of the Name we insterted in CustTemp[2]. CustTemp[2] ignores the filter we used in CustTemp[1].

Ok Mark, great stuff but what can I do with this?

Microsoft uses this in at least two places in the standard Navision product.

The fist place is the UpdInvPostingBuffer() function in Codeunits 80 and 90. This temporary buffer variable has two dimensions which are used to combine a select distinct with a total of the values.

The second place is the Inventory Profile Offsetting Codeunit (99000854) in Navision Manufacturing. In this codeunit supply and demand is created in the InventoryProfile table.




Tip #23 – The ‘Return’ Shortcut

Today I spent some time at a customer creating a new role center for Dynamics NAV CRM users.

One of the challenges we had was to create a My To-Do list on the Role Center that was based on the actual table. All went well until we started to test and compare to My Customers.

We could not get the double click on the lines to open the To-Do card.

So we started to ‘Find The Differences’ process. It is like playing the picture game with my kids.

Then, after trying out a few things we found this in the My Customer action properties…

Amazing isn’t it! Because return (hitting the Enter key) is the same as double clicking the line it works. [:P]

So now, let’s implement one of my favorite features.

Go to the General Ledger Entries page (20) and open the actions.

And make Navigate the default action!

So  now when I double click an entry it will Navigate.

Dear Microsoft, can you please implement this. [8-|]


Tip #21 – Implementing No. Series

Editors note: This tip was brought forward from my previous blog and is still as actual as it was back then. Meanwhile the Design Patterns initiative have posted a similar walkthrough on their site.

Number series is a great feature in Navision that allows you to use all kids of numbering methods easilty and also allows the application to work the same everythere,

So how do I implement Number Series in a new table. Let’s go through that step by step. For this example we will use the Item table to copy and paste from.

Step 1 – Create the table.

A table that has number series has a minimum of two fields. A primairy key field of type code length 20. But also a second field to store the number series. This is a code field as well with length 10.

Go to the object designer and create a new Table. The first field will be created manualy like this.

Please call it No. and not something else or otherwise our copy paste actions will fail.

Secondly we will open the item table in the designer as well. Find the No. Series field and copy/paste this.

Now our table looks like this.

Step 2 – Add the code

When you implement number series in your table, there are three required pieces of code. One in the OnInsert trigger, One in the OnValidate of the No. field and a new function that is always called OnAssistedit.

We will copy and paste this code from the item table like this.

When you are done with the copy/paste action your new table should look like this.

Step 3 – The variables

When you now compile your new table you will get an error message like this.

This is because we have not yet declared the nessesairy variables. We will need two of them, the Inventory Setup table and the NoSeriesManagement codeunit. We can also copy and paste this from the item table.

So our variables are like this.

Please notice that you will still receive the same error after this.

Step 4 – Code changes

In order to have the code compile we have to change it a little bit. The code we copied is reading the inventory setup in a function but that is not nessesariy in our case. We will just read it directly.

So go to the C/AL code and do a find and replace on GetInvtSetup by InvtSetup.GET like this.

Step 5 – Save the table (Twice).

Now we need to save the table, but when we try we’ll get this error.

The reason for this is simple to explain. We have created a new field (No. Series) and started using that field in code. But because the table was not saved in between, the compiler does not know that field yet. To solve this we need to save the table without compiling.


And then compile the table from the object designer.

Step 6 – Create your own number series.

If you look closely at the code we just created you’ll see that it uses the field Item Nos. from the inventory setup table. There is a high chance that you will want to use a number series of your own so let’s create one. We will do this in the inventory setup table as well but this can also be any other setup table in Navision.

Open the Inventory Setup table in the obect designer and copy the Item Nos. field like this

And paste it as number 50000. And rename to Test Nos.

We will also add this to the Inventory Setup Form or page

Notice that we create a new (fast) tab for our field. This is best practice.

Run the form or page and populate the number series.

Step 7 – Change the C/AL code

To use this new number series we need to go back to the code in our new table and do another find and replace action. This time replace Item Nos by Test Nos.

And save the table.

Step 8 – The User Interface

Last step in implementing the Number Series functionality is adding it to the user interface. This is done via the OnAssistedit trigger. We will demonstrate both forms and pages.

Create a new form with the form wizzard or page with the page wizzard.

Now go to the code behind the No. field by focussing on the field and press F9. You will see the four triggers, OnLookup, OnValidate, OnDrilldown and OnAssistedit like this.

Our code will go into the OnAssistedit trigger of both the form and page and is copied from form 30. The Item card.

Now when we run the page or form you’ll see that it works and how easy it was to implement.