Rashed Amini

The ara3n weblog

Compressing Warehouse Entry

15th May 2012

There have been many topics on compression/deleting subledger/ ledger in NAV. The table that I would like to talk about is the Warehouse Entry. If a location is setup with bins or zones, you can see entries recording every activity that has been performed for an item/bin/zone/unit of measure/Lot/Serial in warehouse entry. The table assigns consecutive numbers to all entries, and the entries appear by entry number order. The activities include every removal and placement of items in the bins and every adjustment registered to the bins. Dynamics NAV creates the warehouse entries whenever you post or register a warehouse document or journal. As you can already guess, this table can quickly grow very large as users register transactions. Standard NAV provides a method to compress warehouse entries. This process is running in CAL is very slow and time consuming process. I’ve built the following solution that deletes all the warehouse entries and recreates them with a SQL script and a NAV processing report.
The process I followed is as follows.
In Object Designer, find Warehouse Entry table design and saved it as a new table. I called it “Warehouse Entry Compressed” with Table ID 50085. The purpose is that if you have done some customization and added some custom fields they all exist in the new table.
Afterwards run the SQL script that will populate this new table. If you have custom fields, you will need to include them in the SQL script.
INSERT INTO [dbo].[Cronus$Warehouse Entry Compressed]
([Entry No_]
,[Journal Batch Name]
,[Line No_]
,[Registering Date]
,[Location Code]
,[Zone Code]
,[Bin Code]
,[Description]
,[Item No_]
,[Quantity]
,[Qty_ (Base)]
,[Source Type]
,[Source Subtype]
,[Source No_]
,[Source Line No_]
,[Source Subline No_]
,[Source Document]
,[Source Code]
,[Reason Code]
,[No_ Series]
,[Bin Type Code]
,[Cubage]
,[Weight]
,[Journal Template Name]
,[Whse_ Document No_]
,[Whse_ Document Type]
,[Whse_ Document Line No_]
,[Entry Type]
,[Reference Document]
,[Reference No_]
,[User ID]
,[Variant Code]
,[Qty_ per Unit of Measure]
,[Unit of Measure Code]
,[Serial No_]
,[Lot No_]
,[Warranty Date]
,[Expiration Date]
,[Phys Invt Counting Period Code]
,[Phys Invt Counting Period Type]
)

Select
[Entry No_]
,[Journal Batch Name]
,[Line No_]
,[Registering Date]
,LocationCode
,[Zone Code]
,BinCode
,[Description]
,ItemNo
,SQuantity
,QtyBase
,[Source Type]
,[Source Subtype]
,[Source No_]
,[Source Line No_]
,[Source Subline No_]
,[Source Document]
,[Source Code]
,[Reason Code]
,[No_ Series]
,[Bin Type Code]
,[Cubage]
,[Weight]
,[Journal Template Name]
,[Whse_ Document No_]
,[Whse_ Document Type]
,[Whse_ Document Line No_]
,[Entry Type]
,[Reference Document]
,[Reference No_]
,[User ID]
,[Variant Code]
,[Qty_ per Unit of Measure]
,UOM
,SerialNo
,LotNo
,[Warranty Date]
,[Expiration Date]
,[Phys Invt Counting Period Code]
,[Phys Invt Counting Period Type]

From
(
Select *
From
(Select [Item No_] as ItemNo,[Location Code] as LocationCode,Sum([Quantity]) as SQuantity, SUM([Qty_ (Base)]) as QtyBase,
[Bin Code] as BinCode,[Lot No_] as LotNo,[Serial No_] as SerialNo, [Unit of Measure Code] as UOM,
(SELECT Top 1 [Entry No_]
FROM [dbo].[Cronus$Warehouse Entry] as WEntry
where WE.[Item No_] = WEntry.[Item No_] and
WE.[Bin Code]= WEntry.[Bin Code] and
WE.[Lot No_]= WEntry.[Lot No_] and
WE.[Location Code] = WEntry.[Location Code] and
WE.[Unit of Measure Code] = WEntry.[Unit of Measure Code] and
WE.[Serial No_] = WEntry.[Serial No_]
order by [Entry No_] desc) AS EntryNo
from [dbo].[Cronus$Warehouse Entry] as WE
group by [Item No_],[Location Code],[Bin Code],[Lot No_],[Serial No_],[Unit of Measure Code]
) as Summary
where QtyBase 0) as Summary2

Left Join

[dbo].[Cronus$Warehouse Entry]
on (Summary2.EntryNo = [Entry No_])

Then run report 50085 Compressed Warehouse Entry. This report deletes all the warehouse entries and recreates them based on compressed entries.
There are many advantages to cleaning up this table. It improves performance, concurrency and saves space.

Here is the link for the text object and sql script.

Posted in Dynamics NAV | Comments Off

NAV 2013 Beta Released

14th May 2012

Microsoft Today released on PartnerSource the beta release of NAV 2013. Here is the URL. It is released for the first 14 countries.
I suggest to download it and start studying it.

Posted in Dynamics NAV | Comments Off

Role Tailored Client Localization

24th October 2011

Recently I was asked to help with localization of Dynamics NAV for Korea. I was involved a couple of years ago with another project that we implemented in Korea and so I knew the process on how to localize NAV classic client. The project was on version 4.0 sp3. Microsoft has not localized NAV for any country in Asia, except India. On partner source you can find the download for all localized countries / regions. Instead local partners in those countries localize it themselves. The process involves creating caption for all the fields, errors, messages, and reports. To localize the executable, you have to translate fin.stx. In addition Korea uses double-byte character sets (DBCS), which you need to enable in fin.stx. The following link provides steps on how to enable DBCS on you computer

Once you are done with translation, you need to send fin.stx to Microsoft to “close” the file. It basically creates a checksum in the file.
This is how classic client looks in Korean.
NAV Korean

With Role Tailored client for NAV 2009 R2 fin.stx is no longer used. Instead you need to download a template project provided by Microsoft. Here is the link

Once you open the project, you will need rename resources.en-CA.resx to your language codes. On MSDN you will find a list of all the country codes. The Download link above contains a word document with links inside them. Once you have renamed all the files, you will need to go through each project and sign the assembly. These steps are explained in details in the download above. This is a screenshot of solution in Visual Studio.

Visual Studio NAV Korean

You will need to translate all the resx files and compile/build the project. Visual Studio will create in debug folder all the necessary DLL files. If you see a folder en-CA in the output folder when compiling the project/solution, it means you have missed renaming all the resx files. Since I don’t know Korean I left all the translation as is. A native speaking person will do a much better job translating this.
Once the DLL were created, you will need to create a folder (ko-KR) under the RTC for the language and put the dll files as well as all the help files. Under the service tier you will need two folders. (KOR and ko-KR) KOR olds all the help files and ko-KR contains the dll files.

The Role Tailored client only runs dll files that are signed and in order to use them you will to run the Client Add-in and specify all the dll files and their public Key Token. This is how it should look like.

NAV addin table

Once you have done all the steps, restart the service tier. Start the RTC and you should see your language in the select Language option. If you don’t see it, it means you’ve made a mistake somewhere in your process. Here is a screenshot of RTC in Korean.

RTC NAV Korean

You’ll notice that a lot of stuff is still in English and the reason is that I have not translated the resx files. But existing translation for the existing caption translated is still present. Thus it will require much less effort to finish the translation.
The new method to translate the RTC makes it very easy for partners to localize the RTC for countries that MS is not releasing NAV. It no longer requires MS involvement with fin.stx. Hopefully in future RTC will be Unicode compliant and you wouldn’t need to do anything special with DBCS or unchecking Validate Collation setting.

Posted in Dynamics NAV, Role Tailored Client | Comments Off

Integrating with MSMQ using dotNET data types in NAV 2009 R2

2nd April 2011

With the release of NAV 2009 R2, we can now use dotNET data types. This allows CAL programmers to access .NET framework within CAL. There are many benefits on using dotNET data types as opposed to Automation data types. First is distribution of your CAL code. You simply send the fob to the client. If you are referencing any .NET framework class, then by default it’s installed on the box with NAV RTC. Second reason is performance. dotNET data types will perform better than COM Automations. Third, the code is available to see and edit in CAL.

Over the past several years I’ve integrated with many other systems using Microsoft Message Queue (MSMQ). The other systems were for example; BizTalk, websites, 3rd party Manufacturing systems, or handheld units. I’ve used the Automation provided by MS “Navision MS-Message Queue Bus Adapter”. It had many limitations and in certain integration I had to use “Microsoft Message Queue 3.0 Object Library”. I’ve decided to rewrite the solution using dotNET.

The solution below allows sending and receiving MSMQ messages using dotNET variables. It consists of two code units. Code unit 50010 “Send MSMQ” sends an xml file. Code unit 50011 “Receive MSMQ” reads the xml files and stores them on c drive. You can off course change this based on your requirement.

Here is a screenshot of Send MSMQ
MSMQ1

Here is a screenshot of Receive MSMQ
MSMQ2

The code is simple, but it took me a while to get it to work. The main reason is the MessageFormatter. In dotNET you need to specify the formatter for your messages. By default MSMQ uses XmlMessageFormatter but I could not make it work in CAL.
The C# code looks like this.
q.Formatter = new XmlMessageFormatter(new Type[] { typeof(string) });

So instead I am using
Q.Formatter := QActiveXFormatter.ActiveXMessageFormatter;

ActiveXFormatter is used for backward compatibility. You could also send Binary message format. You code would look like this.

Q.Formatter := QBinaryFormatter.BinaryMessageFormatter;

The reason is that there is no equivalent of typeof in CAL. If you find a workaround, post a response. If you have any solution using Automation, it is worth to take a look at implementing it in dotNET. If you are still using classic client, you can run this code using web service on service tier. Here is the link the objects in fob and text format MSMQ.zip.

Posted in DotNet, Dynamics NAV | No Comments »

The Query returned no rows for the data set

11th February 2011

If you are using RTC and previewing certain reports you probably will run into this error. “An error occurred during local report processing.” The Hidden expression for the textbox ‘……’ contains an error: The query returned no rows for the data set. The expression therefore evaluates to null.

RDLC print preview

I searched around and didn’t find any info on this and started researching with trial and error and found that it is simply not an error from NAV perspective. It simply means that there is not data to print. The way RTC reports work is that the reports are generated as XML files on service tier and sent to the client which uses it as a data source to render the RDL.
This simply means based service tier returned an empty xml file. So why would service tier return an empty xml file. After looking at different reports, I’ve concluded that if PrintOnlyIfDetail property on a data item is set, then Service tier removes parent records if there are no children.
I suggest telling your customers/ end users that there is no data based on filters. If they are not ok with it, then you have to dummy integer data item at the end of the report and set it to print one blank line if there are no Parents data item to print.

Posted in Dynamics NAV | No Comments »

Using ADO on RTC in NAV

10th January 2011

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';

ConnectionString := 'Data Source='+ServerName+';'
+ 'Initial Catalog='+NavDb+';'
+ 'Trusted_Connection=True;';

SQLConnection := SQLConnection.SqlConnection(ConnectionString);

SQLConnection.Open;
SQLCommand := SQLConnection.CreateCommand();
SQLCommand.CommandText := 'select * From Session';
SQLReader := SQLCommand.ExecuteReader;

WHILE SQLReader.Read() DO BEGIN
MESSAGE( 'Reading %1 , %2 ',SQLReader.GetInt32(0), SQLReader.GetString(1));
END;

SQLConnection.Close;

CLEAR(SQLReader);
CLEAR(SQLCommand);
CLEAR(SQLConnection);

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.

Posted in DotNet, Dynamics NAV | 1 Comment »

String Implementation in NAV

25th November 2010

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');

MyVariant := xmlDom.xml;
BText.ADDTEXT(FORMAT(MyVariant));
BText.GETSUBTEXT(mytext,1,1024);
MESSAGE(mytext);
CLEAR(xmlDom);

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

Posted in Dynamics NAV | 2 Comments »

Downgrade 2009 sp1 objects into 5.x or older databases

9th April 2010

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.
Downgrade NAV objects

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.

Posted in Dynamics NAV | 10 Comments »

Dynamics Add-in with WPF

5th January 2010

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.

Dynamics Map Screen shot

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.

Posted in Dynamics NAV | 21 Comments »

Dynamics NAV Addin Example Large Button

11th December 2009

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.

Button Screenshot Addin

The code for CreateControl is as follows


protected override Control CreateControl()
{

Button button1 = new Button();
button1.Location = new System.Drawing.Point(39, 25);
button1.Name = "button1";
button1.Size = new System.Drawing.Size(75, 23);
button1.TabIndex = 0;
button1.Text = "button1";
button1.UseVisualStyleBackColor = true;
button1.Click += new System.EventHandler(button1_Click);

Button button2 = new Button();
button2.Location = new System.Drawing.Point(59, 55);
button2.Name = "Largebutton2";
button2.Size = new System.Drawing.Size(175, 123);
button2.TabIndex = 0;
button2.Text = "Largebutton2";
button2.BackColor = System.Drawing.Color.Tomato;
button2.UseVisualStyleBackColor = true;
button2.Click += new System.EventHandler(button2_Click);

Panel buttonpanel = new Panel();
buttonpanel.Controls.Add(button1);
buttonpanel.Controls.Add(button2);
buttonpanel.Location = new System.Drawing.Point(12, 12);
buttonpanel.Name = "outlookGroupBox1";
buttonpanel.Padding = new System.Windows.Forms.Padding(4, 22, 4, 4);
buttonpanel.Size = new System.Drawing.Size(376, 219);
buttonpanel.TabIndex = 0;
buttonpanel.Text = "Many Buttons";

return buttonpanel;
}

Here is the Visual Studio Project Source

Posted in Dynamics NAV | 2 Comments »