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

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.

 

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

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

Renumbering tool for Dynamics NAV Objects

I needed to renumber objects for Skyrr Payroll addon and found on Mibuso an old renumbering tool.  I downloaded it and installed.  I corrected the bug and created an import function for Excel worksheet.

The Excel worksheet has the object type in column A, object ID in column B, object name in column C, object caption in column D and the object new ID in column E.  The first row is the header row.  I simply copied my object list from Object Designer and worked out my new ids in Excel.

After importing this Excel worksheet I have a list of new and old IDs in NAV.

If any of the new IDs is an old ID, you will need to renumber first from the old ID to temporary ID and then do another renumbering to the new ID.

I create two sheets in my Excel workbook to solve this.

This renumbering tool does not support Pages and I did not need that support in this case.

Renumbering Tool Source Code

File Handling and RTC

In the Role Tailored Client all file system commands used by the Classic Client have been removed.  For those who have been using commands such as EXISTS, RENAME and ERASE there is a code update to be done.  I created a codeunit that includes these functions and can be used to replace the commands in the old code.  These functions work both in the Role Tailored Client and in Classic Client.

The attached code includes the following functions;

  • SelectExcelFileName
  • ConfirmFileExists
  • FileExists
  • FileRename
  • FileErase
  • FileCopy

Code is attached here.

Data Transfer with XML Port

In my Payroll development I created a solution to import and export setup data from one company to another.  This is done via XMLPort.  I created the XMLPort code with a Report that is attached at the bottom of the post.

First I need a function to create the table list for the setup data
[code]SetupObjectNoList(VAR TempObject : Record Object)
TableIDArray[1] := DATABASE::"Payroll Setup";
TableIDArray[2] := DATABASE::"Payroll Tax Setup";

TableIDArray[57] := DATABASE::"Payroll Column Layout";

Object.SETRANGE(Type,Object.Type::Table);

FOR Index := 1 TO ARRAYLEN(TableIDArray) DO BEGIN
Object.SETRANGE(Object.ID,TableIDArray[Index]);
IF Object.FINDFIRST THEN BEGIN
TempObject := Object;
TempObject.INSERT;
END;
END;[/code]
And to be able to import new setup data I must clear all data from the setup tables
[code]DeleteSetupData()
IF NOT CONFIRM(Text005,FALSE) THEN EXIT;

SetupObjectNoList(TempObject);
DialogMgt.WindowOpen(‘@1@@@@@@@@@@@@@@@@@@@@@@’);
DialogMgt.WindowSetTotal(1,TempObject.COUNT);
TempObject.FINDSET;
REPEAT
RecRef.OPEN(TempObject.ID);
IF NOT RecRef.ISEMPTY THEN
RecRef.DELETEALL;
RecRef.CLOSE;
DefaultDim.SETRANGE("Table ID",TempObject.ID);
IF NOT DefaultDim.ISEMPTY THEN
DefaultDim.DELETEALL;
LineDim.SETRANGE("Table ID",TempObject.ID);
IF NOT LineDim.ISEMPTY THEN
LineDim.DELETEALL;
Translation.SETRANGE("Table ID",TempObject.ID);
IF NOT Translation.ISEMPTY THEN
Translation.DELETEALL;
DialogMgt.WindowProcess(1);
UNTIL TempObject.NEXT = 0;
DialogMgt.WindowClose;
COMMIT;[/code]
Then I have the Import and Export functions. In this code I am using a Log table BLOB field but could as well use the TempBlob table.
[code]ImportFile(FileName : Text[1024])
DialogMgt.WindowOpen(Text003);
Log.INIT;
Log."Entry No." := 0;
Log."Service Description" := Text001;
Log."Created by User ID" := USERID;
Log."Creation Date and Time" := CURRENTDATETIME;
IF Log.ImportIncomingXML(FileName,FALSE) <> ” THEN BEGIN
Log."Incoming Message".CREATEINSTREAM(InStr);
XML.SETSOURCE(InStr);
XML.IMPORT;
CLEAR(XML);
END;
Log.INSERT;
DialogMgt.WindowClose;

ExportFile(FileName : Text[1024])
DialogMgt.WindowOpen(Text004);
Log.INIT;
Log."Entry No." := 0;
Log."Service Description" := Text002;
Log."Created by User ID" := USERID;
Log."Creation Date and Time" := CURRENTDATETIME;
Log."Outgoing Message".CREATEOUTSTREAM(OutStr);
XML.SETDESTINATION(OutStr);
XML.EXPORT;
CLEAR(XML);
Log.INSERT;
Log.ExportOutgoingXML(FileName,FALSE);
DialogMgt.WindowClose;[/code]
Report to create XMLPort

Transferring small amount of data between databases

Most of us have needed to copy data from one Dynamics NAV database to another.  For example the posting setup tables, payment terms, currency, etc.

If the databases are not identical you will not be able to copy and paste the data and you have to solve this problem with dataports or XMLPorts.

I offer a solution to this problem.  I have created a form that is able to export and import data based on table and field numbers.  It will import data to a table even if some fields are missing in the destination database.

Here is the source code

Dynamics NAV and Soap Services

Soap Web Services return XML with name spaces.  You need to remove the name spaces before you can use XMLPort to read the answer.  Microsoft has supplied a solution to this problem by applying Stylesheet to the XML before it is read by the XMLPort.  I added to that Stylesheet a few lines to remove the Header section of the XML.

Attached is a working example that queries a bank for a given bank account and registration number.

Here is the source code.

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.

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.