Microsoft Dynamics NAV: Upload with FTP using dotNET Interop

One of the biggest additions to the development environment with the advent of the RoleTailored Client Microsoft and Dynamics NAV Server was the availability of the Microsoft .NET Framework assemblies. This allows Microsoft Dynamics NAV objects can interact with .NET Framework objects. You can easily use assemblies from the .NET Framework class library, your own custom assemblies; or third-party assemblies. This allows you to extend your solution and not be limited to only the NAV or COM objects.

One such example of using dotNET Interoperability is the ability to use the dotNET framework to FTP files to a remote server. Without access to the dotNET framework it was necessary to create a third party COM control, call a batch file, or schedule some third party software to transfer a file. Now, you can generate and send files all from within NAV. This can centralize the task of generating and transmitting files to one application.

A function that can be used (and easily extended) to upload a file to a remote FTP server via dotNET Interop in NAV is listed below.

Local Variables

Name			DataType	Subtype
FtpWebRequest		DotNet		System.Net.FtpWebRequest.'System'
FtpWebResponse		DotNet		System.Net.FtpWebResponse.'System'
NetworkCredential	DotNet		System.Net.NetworkCredential.'System'
Stream			DotNet		System.IO.Stream.'mscorlib'
SIOFile			DotNet		System.IO.File.'mscorlib'
UploadFile(siteaddress : Text[250];username : Text[80];password : Text[80];filename : Text[250];path : Text[250])

FtpWebRequest := FtpWebRequest.Create(siteaddress + '/' + filename);

FtpWebRequest.Credentials := NetworkCredential.NetworkCredential(username, password);
FtpWebRequest.Method := 'STOR';
FtpWebRequest.KeepAlive := TRUE;
FtpWebRequest.UseBinary := TRUE;
//FtpWebRequest.UsePassive := FALSE;
filename := path + filename;

FtpWebRequest.ContentLength := SIOFile.ReadAllBytes(filename).Length;
Stream := FtpWebRequest.GetRequestStream;
Stream.Write(SIOFile.ReadAllBytes(filename), 0, FtpWebRequest.ContentLength);
Stream.Close;

FtpWebResponse := FtpWebRequest.GetResponse;

IF GUIALLOWED THEN BEGIN
    MESSAGE('%1 %2', FtpWebResponse.StatusCode, FtpWebResponse.StatusDescription);
END;

Microsoft Dynamics NAV: Copy Table Data from one Company to another Company

In Microsoft Dynamics NAV you can setup separate distinct companies. Unless a table is setup to share data across all companies, by setting the DataPerCompany property to No, each company’s data is separate and distinct from the other. In many cases it may be desirable to have tables that contain the same values across companies. Due to validation and performance considerations the setting of the DataPerCompany property may not be an option.

Fortunately in C/SIDE we can use the CHANGECOMPANY function to specify the company that a record variable references. Using this method we can easily copy data from one company to the other. It is simple enough to explicitly define two variables for a particular record and write a few lines of code to copy all of the data from one company to the other. I am all about reuse and prefer not modifying code for each table or scenario. Fortunately in C/SIDE we can make use of the RecordRef variable type. This allows us to dynamically specify the tables to reference and create a neat little function:

CopyTableFromTo(FromCompanyName : Text[30];ToCompanyName : Text[30];TableNo : Integer)

IF GUIALLOWED THEN BEGIN
  Window.OPEN('#1###############\' +
              '@2@@@@@@@@@@@@@@@');
END;

// Open source table
FromTableRef.OPEN(TableNo, FALSE, FromCompanyName);
IF GUIALLOWED THEN BEGIN
  Window.UPDATE(1, FromTableRef.NAME);
END;
// Open destination table
ToTableRef.OPEN(TableNo, FALSE, ToCompanyName);
RecCount := FromTableRef.COUNT;

// Loop through the fields
IF FromTableRef.FINDFIRST THEN REPEAT
  RecNo += 1;
  IF GUIALLOWED THEN BEGIN
        Window.UPDATE(2,ROUND(RecNo/RecCount * 10000, 1));
  END;
  ToTableRef.INIT;
  FOR i := 1 TO FromTableRef.FIELDCOUNT DO BEGIN
    FromFieldRef := FromTableRef.FIELDINDEX(i);
    ToFieldRef := ToTableRef.FIELDINDEX(i);
    //TODO: Check field types
    ToFieldRef.VALUE := FromFieldRef.VALUE;
  END;
  IF NOT ToTableRef.INSERT THEN ToTableRef.MODIFY;
UNTIL FromTableRef.NEXT = 0;

ToTableRef.CLOSE;
FromTableRef.CLOSE;

IF GUIALLOWED THEN BEGIN
  Window.CLOSE;
END;

With the variables:

 

This function can be use in ways similar to:

//This is intended to be called in a function:
//CopyFromSource(CompanyName : Text[30];TableNo : Integer)

// Verify company is valid
Company.GET('CRONUS USA, Inc.');
// Set the table number; in this case Country/Region
TableNo := 9;

Company.SETFILTER(Name, '<>%1', 'CRONUS USA, Inc.');
IF Company.FINDSET(FALSE, FALSE) THEN REPEAT
  CopyTableFromTo(CompanyName, Company.Name, TableNo);
UNTIL Company.NEXT = 0;

Note: This function does not trigger and code (or data) validation. When using the CHANGECOMPANY function if you run validation code on a variable for Company B in Company A, it will modify the record in Company B, but it will run the trigger in Company A.

Microsoft Dynamics Nav: Export objects License Range

One of the more tedious tasks when working on development using Microsoft Dynamics NAV is the exporting of Objects. You may need to export the objects to manage them in some sort of source control or to perform a code compare.

Often I have had to export all of the objects from a database (where the license has permission to export to text) and get held up having to work around objects that aren’t in the license range. There is the option is to filter those objects out before exporting the objects. This can be a difficult task as you may need to set different ranges for different object types.

The task of exporting object became easier with the addition of the EXPORTOBJECTS Function. This function allows you to export Dynamics NAV objects as either text or xml files. With this function you can automate the exporting of objects while comparing the object to the license permission information. The following snippet (codeunit attached to this post) demonstrates exporting objects that are contained within the license range:

 

PROCEDURE ObjectExport@1000000001(ObjectType@1000000000 : 'Table Data,Table,Form,Report,Dataport,Codeunit,XMLport,MenuSuite,Page';FilePath@1000000003 : Text[250]);
    VAR
      Object@1000000001 : Record 2000000001;
      Object2@1000000006 : Record 2000000001;
      LicensePermission@1000000002 : Record 2000000043;
      Window@1000000007 : Dialog;
      RecNo@1000000008 : Integer;
      RecCount@1000000009 : Integer;
      prefix@1000000004 : Text[30];
      filename@1000000005 : Text[1024];
    BEGIN

      IF GUIALLOWED THEN BEGIN
        Window.OPEN('#1#################################\' +
                    '@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@');
      END;
      Object.SETRANGE(Type, ObjectType);
      IF Object.FIND('-') THEN BEGIN
        RecCount := Object.COUNT;
        REPEAT
          IF GUIALLOWED THEN BEGIN
            RecNo += 1;
            Window.UPDATE(1, STRSUBSTNO('%1 %2 %3', FORMAT(Object.Type), Object.ID, Object.Name));
            Window.UPDATE(2, ROUND(RecNo / RecCount * 10000,1));
          END;
          LicensePermission.SETRANGE("Object Type", Object.Type);
          LicensePermission.SETRANGE("Object Number", Object.ID);
          //LicensePermission.SETRANGE("Read Permission",1,2);
          LicensePermission.SETRANGE("Execute Permission",1,2);
          IF LicensePermission.FINDFIRST THEN BEGIN
            CASE ObjectType OF
              ObjectType::Table: prefix := 'Tab';
              ObjectType::Form:  prefix := 'For';
              ObjectType::Report: prefix := 'Rep';
              ObjectType::Dataport: prefix := 'Dat';
              ObjectType::Codeunit: prefix := 'Cod';
              ObjectType::XMLport: prefix := 'XML';
              ObjectType::MenuSuite: prefix := 'Men';
              ObjectType::Page: prefix := 'Pag';
            ELSE
              ERROR('Unsupported Object Type');
            END;
            Object2.SETRANGE(Type, Object.Type);
            Object2.SETRANGE(ID, Object.ID);
            filename := STRSUBSTNO('%1%2%3.txt', FilePath, prefix, FORMAT(Object.ID));
            EXPORTOBJECTS(filename, Object2);
          END;
        UNTIL (Object.NEXT = 0);
      END;
      IF GUIALLOWED THEN BEGIN
        Window.CLOSE;
      END;
    END;

 

Note: This code is for NAV2009. NAV2013 does not support Forms and Dataports so the code will need to be slightly modified. Also, you can adapt the code to adjust the Object Filter and grab a specific set of objects by date, version list or any of the other object fields.

Cod50085.txt (2.89 kb)

Microsoft Dynamics NAV: Permission Error SET IDENTITY_INSERT

In your travel as a Dynamics NAV professional you make come across a permission error on a table like this:

“1088,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot find the object … because it does not exist or you do not have permissions. SQL: SET IDENTITY_INSERT…”

Error SQL SET INDENTITY_INSERT

Initially, this may seem easy enough to correct by ensuring that the user has the necessary NAV security permissions set for the table listed. However, after setting the NAV security permission you will realize that the user still receives the error. The permission error is not generated from NAV, rather it is generated from SQL. The error is due to the user not having the permission to alter the structure of the table. Before you bang your head and do something silly like give the user permission to the object let’s take a look the cause the error.

Error SQL SET INDENTITY_INSERT

The SET IDENTITY_INSERT permission error occurs when a user tries to enter data in a field that is set to be a SQL Identity Column (a column is set to be an identity column when the AutoIncrement property of the field is set to yes in the NAV table designer; generally on “Entry No.” fields). An identity column is a column that has its value generated by the database (the identity column is often used as a primary key; however it is not required to be the primary key). Although, the identity column has a value generated by the database when a record is inserted, it is possible to edit the value if you have permission to alter the database object; the database internally tracks the last value entered to generate the next value.

Error SQL SET INDENTITY_INSERT

You should review any code that modifies or inserts records into the table is identified in the error. Verify that the any code is not trying to insert a value in the AutoIncrementing field. I have seen several cases where code was written to increment an “Entry No.” field that was set to AutoIncrement.

Error SQL SET INDENTITY_INSERT

You can properly correct the issue by setting the AutoIncrement property of the table to No or altering the code to not enter a value in the field. Obviously you should not edit any code or table design unless you know what you are doing and properly test the changes in a non-production environment.

Microsoft Dynamics NAV: Excel Automation AddPicture

The ability to use Automation Objects has added to the power and flexibility of Microsoft Dynamics NAV.  Automation can be used to integrate NAV business logic with external processes and extend the functionality of the product, such Microsoft Office.  This is easily demonstrated through the use of Table 370, Excel Buffer.  Using the “Excel Buffer” table you can easily create Excel Workbooks with Dynamics NAV data.

One thing that is not included in the “Excel Buffer” table, which could be useful, is the option to insert a picture into a worksheet.  Fortunately, the Excel Automation control exposes the Shapes.AddPicture method than can be easily extended into the Dynamics NAV “Excel Buffer” table by creating a function for XlWrkSht.Shapes.AddPicture(filename,1,1,Left,Top,Width,Height);

 

This method can be implemented several ways and it is easy enough to add a field ImageName to the table.  The CreateSheet code can then be altered to add an image to the current sheet based on the value entered in the field.