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”

Add Namespaces to outgoing XML

The tax authority in Iceland are using Soap web services.  I have built XML Ports in NAV to create the request XML and another one to read the response XML.

I already wrote about using stylesheet to strip namespaces from the incoming XML before passing it to the XML Port.  I previously just added namespaces to the outgoing XML with attributes in the request XML Port.  This does not work in Role Tailored Client.

The solution was to remove all the namespaces attributes from the request XML Ports and create a function to add namespaces to the XML before passing it to the web server.  This work both in Classic Client and in Role Tailored Client.

AddNameSpaces source code

Transaction No. error when UnApplying

When unapplying a customer or a vendor ledger entry you sometimes get the error “The latest Transaction No. must be an application in Vendor Ledger Entry No. ######”.  I have found that this can be triggered if “Adjust Exchage Rates” batch has been executed after the prior application posting.

This can be avoided by changing the function FindLastTransactioNo in Codeunit 227.

[code]FindLastTransactionNo(VendLedgEntryNo : Integer) : Integer
DtldVendLedgEntry.SETCURRENTKEY(
"Vendor Ledger Entry No.","Entry Type");
DtldVendLedgEntry.SETRANGE(
"Vendor Ledger Entry No.",VendLedgEntryNo);
LastTransactionNo := 0;
IF DtldVendLedgEntry.FIND(‘-‘) THEN
REPEAT
//# Changes Start
IF SourceCodeSetup."Exchange Rate Adjmt." = ” THEN
SourceCodeSetup.GET;
IF DtldVendLedgEntry."Source Code" <>
SourceCodeSetup."Exchange Rate Adjmt."
THEN
//# Changes End
IF (DtldVendLedgEntry."Transaction No." > LastTransactionNo) AND
NOT DtldVendLedgEntry.Unapplied
THEN
LastTransactionNo := DtldVendLedgEntry."Transaction No.";
UNTIL DtldVendLedgEntry.NEXT = 0;
EXIT(LastTransactionNo);[/code]

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) &lt;&gt; ” 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