Rashed Amini

The ara3n weblog

Archive for November, 2009

Automatic Spell Checker in Dynamics NAV

27th November 2009

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.
Dynamcis NAV Spellcheck

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

Item Card page Dynamics NAV

Posted in Dynamics NAV | 7 Comments »

Replacing NAS with SQL Jobs and NAV Web service

14th November 2009

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;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void NavJobScheduler(string ObjectType, int ObjectID, string Login, string Password,string Domain,string WebServiceURL)
{
string Body = @"" +
"" +
"" + ObjectType + "" +
"" + ObjectID + "" +
"";

WebRequest request = HttpWebRequest.Create(WebServiceURL);
request.Headers.Add("SOAPAction", @"""urn:microsoft-dynamics-schemas/codeunit/RunObject:RunJob""");
request.ContentType = "application/xml; charset=utf-8";
request.ContentLength = Body.Length;
request.Method = "POST";
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential(Login, Password, Domain);
myCredentials.Add(new Uri(WebServiceURL), "NTLM", netCred);
request.Credentials = myCredentials;

Stream strWrite = request.GetRequestStream();
StreamWriter sw = new StreamWriter(strWrite);
sw.Write(Body.ToString());
sw.Close();

WebResponse wr = request.GetResponse();
HttpWebResponse httpRes = (HttpWebResponse)wr;
Stream s = httpRes.GetResponseStream();
StreamReader sr = new StreamReader(s);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(sr);

if (xmlDoc.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Value != "SUCCESS")
{
throw new Exception("ObjectType " + ObjectType + " ObjectID " + ObjectID.ToString() + " failed with Error: "
+ xmlDoc.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Value);
}

}
};

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.
SQL Job Nav Scheduler

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

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.

Posted in Dynamics NAV, webservice | 18 Comments »

Dynamics NAV 2009 Sp1 Client Add-ins

9th November 2009

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.
Screenshot

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.

Screenshot

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.

Screenshot

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.

Screenshot

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.

Posted in Dynamics NAV | 5 Comments »