How-To’s – Page 12 – Gunnar's Blog

March 7, 2011

Unzip Files

By using the Automation “‘Microsoft Shell Controls And Automation’.Shell” you can unzip a file within Dynamics NAV.

Create a Global

Name	DataType	Subtype
SystemShellControl	Automation	‘Microsoft Shell Controls And Automation’.Shell
SystemShellItem	Automation	‘Microsoft Shell Controls And Automation’.FolderItem
SystemShellItems	Automation	‘Microsoft Shell Controls And Automation’.FolderItems
FileName	Text
ZipFileName	Text
DestFolderName	Text
Index	Integer
Pos	Integer

And then simply
[code]ZipFileName := ‘C:\TEMP\ZipFile.Zip’;
DestFolderName := ‘C:\TEMP\’;
SystemShellItems := SystemShellControl.NameSpace(ZipFileName).Items;
SystemShellControl.NameSpace(DestFolderName).CopyHere(SystemShellItems);
FOR Index := 1 TO SystemShellItems.Count DO BEGIN
SystemShellItem := SystemShellItems.Item(Index – 1);
IF ISSERVICETIER THEN
FileName := SystemShellItem.Path
ELSE
FOR Pos := 1 TO STRLEN(SystemShellItem.Path) DO
IF COPYSTR(SystemShellItem.Path,Pos,1) = ‘\’ THEN
FileName := COPYSTR(SystemShellItem.Path,Pos + 1);
// Do what ever you whant to DestFolderName + FileName
END;[/code]

March 7, 2011

Download a File

In Dynamic NAV it is possible to use the Automation “‘Microsoft XML, v6.0’.XMLHTTP” to download files. The code would be
[code]IF ISCLEAR(WinHTTP) THEN
CREATE(WinHTTP,TRUE,FALSE);

WinHTTP.open(‘GET’,URL,FALSE);
WinHTTP.send(”);

IF WinHTTP.status <> 200 THEN
ERROR(Text003,WinHTTP.status,WinHTTP.statusText);

TempFile.CREATE(TempFileName);
TempFile.CREATEOUTSTREAM(OutStr);
InStr := WinHTTP.responseStream;
COPYSTREAM(OutStr,InStr);
TempFile.CLOSE;[/code]
Where Text003 is “Status error %1 %2”

The URL will be downloaded to the filename “TempFileName”

February 15, 2011

Data Upgrade Method and Code

I am in the process of upgrading a 5.0 SP1 database to 2009 R2. That includes updated addon solutions from Skýrr. To help me with this process I created a table, form and a codeunit to handle the upgrade and provide the user a visual process information.

This code will store information on processed tables in the log table and make sure that if you execute the upgrade process only the remaining processes will be executed. You can simply add another processing codeunit and execute the process again and again.

UPGRADE TOOL

January 6, 2011

Forms with Line No. and Filter

Some forms in Dynamics NAV use the property “AutoSplitKey”. That means that the “Line No.” field will be automatically assigned the next line number or a number between lines if the user tries to insert a line between two lines.

If the user adds a filter to the form then this will fail. I created a code that will solve this problem. First you need to reset the “AutoSplitKey” property. Then you create two functions.

NextLineNo() : Integer
CommentLine.RESET;
CommentLine.SETRANGE("Table Name","Table Name");
CommentLine.SETRANGE("No.","No.");
IF CommentLine.FINDLAST THEN
EXIT(CommentLine."Line No." + 10000)
ELSE
EXIT(10000);

BeforeLineNo(BeforeCommentLine : Record "Comment Line") : Integer
CommentLine.RESET;
CommentLine.SETRANGE("Table Name","Table Name");
CommentLine.SETRANGE("No.","No.");
CommentLine := BeforeCommentLine;
IF CommentLine.FIND('<') THEN
EXIT(CommentLine."Line No." +
ROUND((BeforeCommentLine."Line No." -
CommentLine."Line No.") / 2,1))
ELSE
EXIT(ROUND(BeforeCommentLine."Line No." / 2,1));

And then insert this code on the “Form – OnNewRecord(BelowxRec : Boolean)” trigger.

SetUpNewLine;

IF BelowxRec THEN
"Line No." := NextLineNo
ELSE
"Line No." := BeforeLineNo(xRec);

This will create automatically assign a “Line No.” to your line even if you are using filter.

October 26, 2010

Multiple NAS Services on a single license

Most Dynamics NAV databases have more than one companies. When setting up Dynamics NAV Application Server as a service, you have to install a service for each company in your database. Your license might not have enough user licenses to be able to run all services at once. My solution is the following.

I install a NAS service for each company. One of them set to automatic startup type, all others are set to manual. I then schedule a task every 30 minutes and execute a script that I save as NAS-Rotate.vbs.
[code lang=”vb”]strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & _
"\root\cimv2")

CurrentServiceName = ""
RunNextService "NAS-1","NAS-2"
RunNextService "NAS-2","NAS-3"
RunNextService "NAS-3","NAS-4"
RunNextService "NAS-4","NAS-5"
RunNextService "NAS-5","NAS-6"
RunNextService "NAS-6","NAS-1"
If CurrentServiceName = "" Then
StartService "NAS-1"
End If

Sub RunNextService(ServiceName,NextServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Running" Then
‘* WScript.Echo "Stopping: " & objService.DisplayName & "," & _
‘* objService.StartName & "," & objService.State
objService.StopService
StartService(NextServiceName)
CurrentServiceName = NextServiceName
‘* WScript.Quit
End if
Next
End Sub

Sub StartService(ServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Stopped" Then
‘* WScript.Echo "Starting: " & objService.DisplayName & "," & _
‘*objService.StartName & "," & objService.State
objService.StartService
End if
Next
End Sub[/code]
In this excample I have installed six services.

October 14, 2010

Forms and Pages are editable in lookupmode

Some forms and pages in Dynamics NAV are used both for setup and look up. For example form and page “Payment Terms” with ID 4. I like to add a code to forms and pages to prevent this.

On forms:
[code]OnOpenForm=BEGIN
CurrForm.EDITABLE := NOT CurrForm.LOOKUPMODE;
END;[/code]
On pages:
[code]OnOpenPage=BEGIN
CurrPage.EDITABLE := NOT CurrPage.LOOKUPMODE;
END;[/code]
This causes the form or page to be write protected if they are opened as a look up from the “Payment Terms Code” fields.

October 12, 2010

Import from ODBC into Dynamics NAV

Importing data from ODBC database into Dynamics NAV is a regular task. The most common problem is where the external database has data types and null values that are not supported by Dynamics NAV.

I have solved this with a function that builds the query command. The function selects all fields from the table and then loops through them, checks the data type and builds an new query command using both “ISNULL” and “CONVERT” functions.

The attached example is where I import customers and vendors from Pervasive database with DK business data. The create function requires the table name and the primary key. If the external table has to many fields to fit into one query command more queries will be created for remaining fields along with the primary key.

Here you can download a ZIP file with the example codeunit.

September 14, 2010

Detailed presentation of Skyrr BI Solution

This is a presentation where I use the Skyrr BI Solution as a basis of an OLAP Cube. The presentation is in my native language, Icelandic.