Rashed Amini

On one of my recent projects I had to build a solution to update a NAV table with quantity on hand by location. Writing a processing report to maintain update and maintain the table would have taken less than an hour. But for this project performance of a NAV report would have not worked. The client has about 1000 locations and about 80,000 Items. Worst case scenario, you could end up with 80 million records in this table. The solution I provided uses ADO to connect to SQL server and issue a SQL statement on Item Ledger Entry Indexed view.
My solution was based on Waldo’s post

This worked fine on classic client, but when I tried to run this on Role Tailored Client (RTC), I was getting the following error.

The expression Variant cannot be type-converted to a String value.

The error was from the following line.
lADOCommand.ActiveConnection := lvarActiveConnection;

This basically means that you cannot use ‘Microsoft ActiveX Data Objects 2.8 Library’ in RTC. So what other option are available? You could build an external dll file that wraps the active x DLL file. But that creates many headaches with registering and maintaining the dll file on every workstation. The other solution that I’m going to talk about is that in NAV 2009 R2, Microsoft released a new data type called DotNet. With this data type, you can access the .Net framework and reference the classes in NAV. So the code for accessing NAV using DotNet datatype looks like this.


ServerName := 'VIRTUALXP-51168';
NavDb := 'Demo Database NAV 6R2';

In the example above I’m simply reading the session table and printing the “Connection ID” and “User ID”. In addition you can assign to SQLCommand.CommandText sql statement that are greater than 1024 characters.
Here are the DotNet data types for the above code.

Name DataType Subtype
SQLConnection DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection
SQLCommand DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand
SQLReader DotNet ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlDataReader

If you are on older version of NAV, you have to do executable upgrade to Dynamics NAV 2009 R2 to be able to use DotNet object types.

If you have developed with any OCX or COM objects in Dynamics NAV, you have probably run into the following error. “The length of the text string exceeds the size of the string buffer.” The reason you would get this error is because you were passing a string from COM object to NAV. NAV limits COM object string length to 1024 characters. In older version it was 250 characters but they increased it to 1024 characters. The workaround this limitation you had to build a COM wrapper for the DLL file and split the string into 1024 or smaller sizes and pass it to NAV. This solution was cumbersome with rolling it out on every PC and maintaining it, as well compiling the objects if you didn’t have the wrapper. I had suggested to MS to remove this limit and they are working on it. Freddy who was in one of those meetings with MS suggested that you could use Variant type to get around the problem. I didn’t try to look for a solution until recently I received an email from a client that they were running into this problem. Here is the solution. This only works on Role Tailored client and on classic you will still receive the error. If you are running on classic, you could use NAV web service and allow it to run the NAV code.
In the code below, xmlDom.xml returns a string of the whole xml as string, which is more than 1024 characters.


IF ISCLEAR(xmlDom) THEN
CREATE(xmlDom);
xmlDom.async(FALSE);
xmlDom.load('C:\Temp\NAVSetupPORT.xml');

Name DataType
MyVariant Variant
xmlDom Automation 'Microsoft XML, v6.0'.DOMDocument
mytext Text 1024
BText BigText


NAV 2009 R2 which is being released on December 15th 2010 will allow developer in NAV to use the .NET framework in NAV. NAV has introduced a new object type DotNet. The solution will work with DotNet objects as well. Here is an example code.

xmlDom := xmlDom.XmlDocument;
xmlDom.Load('C:\Temp\NAVSetupPORT.xml');
MyVariant := xmlDom.InnerXml;
BText.ADDTEXT(FORMAT(MyVariant));
BText.GETSUBTEXT(mytext,1,1024);
MESSAGE(mytext);

CLEAR(xmlDom);

The above code is very similar to the COM example except xmlDom is DotNet object type with subtype Systeml.xml.XmlDocument .
‘System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.XmlDocument

Most of modification I do, I try to reuse them in other projects. I have built this generic integration objects that can be used in 80 % of the integration scenarios that NAV integrates with other systems. I’ve built it in 2009 sp1 and one of our clients was running on 5.0. As you probably know, loading fob from 2009 into older version is not possible. It crashes older NAV clients. The workaround is to export them from 2009 as text and then removing 2009 specific areas out of the text file and then importing them into 5.0 or older executables. I’m sure many partners and VARS are in same scenarios. They do their development on latest version of NAV and back port it to older version if required.
I’ve built this tool that takes a text file and automatically removes 2009 sp1 properties and allows it to import the text file into 5.0 or older version.

I have testing this all nav standard objects by loading them from 2009 into 5.0 sp1.
Remember you do not need to the tool for forms, dataports, and codeunits. This works for tables and xmlports and reports.

Here is the link.

I wrote previously about using WPF textbox for automatic spellchecking. In this blog, I will actually talk about a real WPF Visualization that I have been playing with for a while. I didn’t want to build a visualization from scratch, so I searched for an existing WPF solution with source code that I could use for my Add-in. I found WPF Dynamic Data Display built by Microsoft Research that is used for many solutions.

My goal was to display sales data from Dynamics NAV in an interactive map that you could zoom in and out and it would be a nice demo tool. The data for Longitude and longitude on map would come from customer card, and the sale data would come from transactions. I had previously blogged about how to use geo codes for address verifications; you could use that to update all the customers’ coordinates. You would need to add two new fields to the customer table. In my example, I didn’t want to modify existing objects so I’m simply loading a CSV file with data.
Here is a screenshot of how it looks in NAV.

The user can interact with the map and zoom in and out of the map, change attenuation and color of the data.. I hope you’ll find this example interesting. I’ve attached the Visual Studio solution with 4 projects inside. MapSample is setup as starting project and it uses the DynamicsDisplay.Control to display the same information. The “DynamicsNAV WPF AddIn2” creates the NAV Add-in WPFDynamicMap.dll file. The solution uses 3 external DLL file in addition to the WPFDynamicMap.dll. In order for NAV reference these DLL files, they need to be put in the Role Tailored client folder, instead of the Add-ins folder. If you forget to do this the page will not open up and you’ll see the error in event log. When compiling the project, make sure example_for_visualization.csv is in C drive, you can change in the code as well.

Here is another example on how to create custom buttons in NAV and control events when they are pressed. In this example I’m actually using the WinForm Button controls and displaying them on a panel. When the button are pressed Nav code is triggered and you can add your own custom logic in it.

Here is a screenshot below. As you can see, there are two button with different sizes and colors and when you press then a nav message is displayed that you pressed it.

The code for CreateControl is as follows


protected override Control CreateControl()
{

Here is the Visual Studio Project Source

Dynamics NAV client has a feature to backup and restore company specific. This process creates an (.fbk) file that contains just the data without indexes. The (.fbk) file can be used to restore in another database. For large database this is a slow process, and not feasible to do. In addition Dynamics Developer license has recently changed in that partners no longer can delete standard NAV fields or fields added by Add-ons by an ISV. So if a client wants to remove an Add-on from its database, they won’t be able to and will be stuck with the fields in the table, unless they call the ISV to delete them for them with their license.
The following solution is a method to remove these fields, but this can also be used to Backup Company specific Data through SQL. This lowers the time required to do Company specific backups, but it also allows a faster way to transfer setup data from one company to another company. For example, customers want to periodically refresh their test database with production data.

The solution consists of a processing report that generates a sql script. It only works with 5.0 sp1 and higher. This is because it doesn’t includ the SIFT tables. I’m sure with some modification you could run it for older versions. As shown in the picture above the user selects a file name, destination database name, and company name. Once the user clicks OK, the report generates a sql script that copies the data from source table to a destination table. Below is a screenshot of the SQL script.

The steps for the whole process of copying a specific company through sql
1. Export all object from Source Database.
2. Create a new database and import the objects from step 1.
3. Create a blank company.
4. In Source Company, run this new report. Specify the destination Database and Company.
5. Open the sql script and execute it. (It should run without any errors)
6. Use the new company.

I mentioned above that you can also use this method to remove unwanted fields from a given table. The change that you would need to do in step 2 is not to load object with the unwanted fields and instead load new object. Then in step 5 find the fields in scrip file and remove them from the INSERT and SELECT statement. This way only the fields that you want to copy are transferred to the destination database.

I’ve included the ProcessingReportObjects.fob.

I saw some post on mibuso about asking for a spellchecker in Dynamics NAV. I thought it would be an interesting project to implement automatic spellchecking in NAV. I decided to implement it using Add-ins. After some googling, I found that I could use MS Office Word interop classes, but as I started implementing it, I found out that it does do real-time automatic spellchecking on a textbox. The user would have to click on a button and then the code would spell check the textbox. I kept searching and found out that WPF RichTextBox control has built in spell checker. Nav addin controls are based on winform classes, and I couldn’t use WPF RichTextBoxes. Then I saw Christian’s Blog with NAV templates and it included a WPF template for data visualization. Well displaying a textbox is a visualization of data. So I built a solution using the data visualization template. The results are better than I expected.
Here is a screenshot of Item card and the description field uses my add-in and underlines misspelled descriptions. You can right click on a word and it will suggest a list of option you can select.

I believe many users will find this useful for data entry and be able to see their typos as they type. I didn’t test other languages, but I’m sure WPF RichTextBox will suggest based on windows local language settings.
I’ve attached Visual Studio Project with Source Code.

To install the add-in, you need to insert in Client Add-in table
Control Add-in Name: Dynamics.NAV.SpellChecker
Public Key Token: 48f3911b65e24838
And put the DynamicsNAV Add-In Project.dll in the addin folder.
Design the Item Card page and change the Description ControlAddIn property as shown in the image below

Many companies, running Dynamics NAV, use NAS (Navision Application Server) to automate certain processes. Some companies use NAS to schedule to run many jobs at night. Others use it for integration where NAS periodically, using timer automation, checks a folder to process certain files throughout the day. Others use NAS to monitor message queue, (MSMQUEUE), for integration with web or 3rd party system.
The solution I will be describing here can replace NAS in the first two scenarios. Scheduling Jobs at night and periodically running on a timer. There are many advantages to using SQL Jobs with web service than NAS. The first advantage is reliability. SQL Jobs is more reliable and stable. Clients already use SQL Jobs to do nightly backups, rebuild indexes, update statistic. I’ve seen many instances where NAS stops working and had to be scheduled to be rebooted every night. A second advantage is intuitive/Familiar Scheduling user interface. Most SQL Administrators are familiar with sql and how to schedule jobs. A third advantage is that you can schedule jobs for multiple companies, where as for NAS you need one instance for each company. A forth advantage is parallel scheduling. You can schedule to run two processes that do not lock each other out at the same time. For example you can run a report and run Adjust Cost at the same time. A Fifth advantage is sequential scheduling with other SQL Jobs. For example clients run at night adjust cost routine, and would like afterwards to backup the file and rebuild the indexes. With current NAS solution you cannot schedule SQL Backup to start automatically after Adjust cost has been run and guess how long it usually takes and setup the time for nightly backups. If a process takes longer than usual and two jobs overlap, sql kills one of the jobs. Another advantage is notification of jobs through mail for example.
This solution can also be used for integration with 3rd party systems, where the 3rd party system connects to sql directly inserts some data into a staging table and calls Navision web service through a stored procedure. Basically the solution allows you to connect to NAV web service using TSQL. The Solution is built in SQLCLR as stored procedure.
Initially I started creating a SQL CRL Project in Visual Studio and added web reference to Navision Web service. I wrote the following code


NavJobScheduler.NavWebService.RunObject MyService = new NavJobScheduler.NavWebService.RunObject();
MyService.UseDefaultCredentials = true ;
MyService.Url = WebServiceURL;
bool Success = false;
Success = MyService.RunJob("Codeunit", 50000);

But quickly found out that sql does not allow dynamic XML Serialization is not allowed in SQL Server. You have to build the dll file outside of visual studio in command prompt using
csc /t:library StoredProc.cs WebService.cs
sgen /a:StoredProc.dll

After following the process and trying to run the stored Process in SQL Server Management Studio, I ran into another problem with web service: authentication. SQL Server Agent running the job do not pass the windows users to SQLCLR stored Procedure. I had to create new credentials and assign it to web Service.


System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("UserID", "password","domain");
myCredentials.Add(new Uri(MyService.Url), "NTLM" , netCred);
MyService.Credentials = myCredentials;


Notice above that I created a NTLM credentials. I could create a Kerberos credentials, but unfortunately it doesn’t work under SQLCLR. I had to change the Dynamics NAV config file and enable NTLM. In addition, since this stored proc was access an external system, I had to change the Permission Level on the Project property to External.
After making all these changes, I was finally able to connect to NAV web service and execute Navision Business logic.
Using web service as reference is nice but it makes it cumbersome to work with. You have to compile your code manually and deal with two dll files. So I decided to use lower level classes and build the xml file manually and connect to the web service.
Here is the code.

using System;
using System.Net;
using System.IO;
using System.Xml;

}
};


Here is how it’s called from SQL Server Management Studio in TSQL.
EXEC NavJobScheduler 'codeunit',50010,'USERID','password','Domain','http://localhost:7047/DynamicsNAV/WS/KRONUS2009SP1/Codeunit/RunObject'

You can schedule a SQL job as shown below and create a schedule for it.

In Dynamics NAV I’ve created a new codeunit 50000 and published it as web service.

With the solution above many clients can now skip using NAS altogether or use this in conjunction with NAS.
I’ve attached the Visual Studio Project with source Code.

When Service Pack 1 was released for Dynamics NAV, it introduced a new feature: Add-ins. I had read the walk-through and was thinking of something useful to build that would utilize the Add-ins feature. Last week a salesperson emailed me and asked me if anybody had migrated the Graphical Process Flow Chart Tool that Microsoft had released. Here is the URL.

I imported the fob and loaded the objects into a 5.0 database and looked at how the demo was built. The Visio diagrams are saved as pictures and a form with Visio picture at the back and NAV buttons displayed on the form.
Here is a screen shot. The background window show the form when you run it, and the front window is the form in design form. It’s a very simple demo.

I created a new visual studio class project following the walk-through in the following link. It was useful in getting me started with building a working Add-in that rendered a text box.
There are many solutions on how to render the Visio diagram in Role Tailed client. I tried to use the Visio control but couldn’t get it to work. I saw in the Demo VPC the Visio diagram, and it would be nice if they blog about it. The other solution is to save the visio as html file and render it in a browser. For demo purposes I thought staying with bitmap solution would be sufficient, so I decided to stay with the Bitmap solution.
I created a PictureBox control and display a blank image on opening of the page. After the Page is opened, I saved the image on the RTC temp directory and send the Image location to the add-in be loaded on the Page. My demo solution is implemented in the following steps: Display an Empty PictureBox. Load the Image from a blob field from NAV. Handle and send mouse event coordinates to NAV. Handle mouse click event and send it to NAV. Communication from C/AL is done through modifying the SourceExp assignment which calls Value Set function. The Add-in is used 3 times to load 3 different images on same page, and user can click on any areas and specific Dynamics NAV page will load.
For example; if they click on Purchaser icon, it will open The Salesperson/Purchase Card.
Here is a screen shot of the results. While moving the mouse over boxes, the mouse icon changes to a hand letting the user know that it’s click-able.

The handling of Coordinates and business logic are done in NAV. In My example I’ve hardcoded the locations in code, but could be moved to a setup table and made more dynamic so that you could build a variety of solutions. For example build a touch button calculator.

Or display a restaurant diagram that the user can click on and open the specific table. Or have a warehouse Image that loads the specific bin.
The mouse position/ coordinates are sent to NAV, thus you can have a setup table to do specific tasks based on the coordinates.

I’ve attached the demo.zip file that you can download and use it for demos or for future solutions.
I’ve also attached the Visual Studio Project with source code.

One of the issues customers run into as their database gets bigger and bigger is that their reports take longer and longer to run. One of those reports is the Inventory Valuation report. I’ve always suggested customers to look at SQL reporting services to write these reports. Inventory valuation report is a little complex, mainly because it can be run as of date. A user can run the report today and enter a date a month ago and find out what the value of his or her inventory was at that date.

Below is a screenshot of the report.

I have written the sql statement to run this on sql server and use it as datasource for your sql reporting

Here is a screenshot.

You can use this also in NAV report using ADO to execute the query using stored proc and get the result. I’ve implemented the above solution at a client where the original report took 40 minutes to run, and with using ADO, it took less than 5 minutes to run the report. That’s a huge performance gain.

Here is a screenshot of the data running in SSRS

I’ve attached the code sql code here Inventory Value script

In 2009 NAV released a new reporting rendering using reporting services for client. The service tier runs business logic and generates an xml file that is rendered at the client. Obviously if you have a large database the xml file can get huge. This takes up resources and memory on the service tier. In 2009 Sp1 they have added a new feature to offload the xml file in parts as it being generated. The problem is mitigated but really not solved. Until NAV introduces new objects type for querying the data we will have to live with this overhead.