nav 5

How to: Create an automation for usage in Navision

2 Comments

The development of a COM component with .Net for usage in Navision needs a special approach. Start with a new C# project, project type Class Library. Set the project to “Make assembly com-visible”. The simple automation provides a string property and a method to show a .net message box.

// a sample codeusing System;using System.Runtime.InteropServices;using System.Windows.Forms;namespace SampleAutom{ // the interface, so that the methods and properties are shown in Nav // needed attributes InterfaceType and Guid [InterfaceType(ComInterfaceType.InterfaceIsDual)] // create a new Guid [Guid("9304DD04-5EF0-498E-893E-CB644CD34656")]  interface IMyInterface {  // set a DispId for each method/property  [DispId(1)]  int MsgBox(string message);  [DispId(2)]  string Title { get; set; } } // class attributes [ClassInterface(ClassInterfaceType.AutoDual)] [Guid("D9E556F3-4D85-45C9-965A-DB3D918528CD")] // implement the interface public class TestCom : IMyInterface {  string _title = "Dynamics Nav";  public TestCom()  { }  // with property Title you can set/read the title of the msgbox  public string Title  {get{ return _title; }  set{ _title = value; }  }// method msgbox returns an integer value according the clicked button  // Yes = 6, No = 7, Cancel = 2  public int MsgBox(string message)  {var result = MessageBox.Show(message, Title,MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information);return (int)result;  } }}

To install the new automation run visual studio in admin mode. after restart and loading the project build/compile the project.

Now start Navision and create a new codeunit. In the OnRun trigger add following code:

// TestCom, Automation, 'SampleAutom'.TestCom // RetVal, Integer CREATE(TestCom);TestCom.Title := '.Net Msgbox run in Navision';RetVal := TestCom.MsgBox('Your message text ...');MESSAGE(FORMAT(RetVal));CLEAR(TestCom);

Now run the codeunit. It results in:

    

cheers

Mass data import

4 Comments

There was an issue with importing all UK post codes from a csv file, about 3m post codes. Importing using rapidstart services (excel import) can cause buffer overflow messages. excel itself has row/size limitations. Increasing MaxNoOfXMLRecordsToSend in config file ClientUserSettings.config from default value 5000 to e.g. 20000 is no problem and can help. Also changing MaxUploadSize in server config file CustomSettings.config is an option (also available via nav service admin console). Better choice for mass data import are dataports (older nav versions) and xmlports.

Another option is to develope a report, which imports the file contents and loops through the lines. quite simple, no memory issues.

create a new report, add following code to trigger OnPreReport():

OnPreReport()// variables PostCode, Record, Post Code file, File fileName, Text, 250 line, Text, 1024 dlg, Dialog idx, Integer txtValue, Text, 100// code PostCode.DELETEALL(FALSE); COMMIT; dlg.OPEN('#1###### #2######'); // show progress dialog idx := 1;// downloaded post code file from https://www.doogal.co.uk/PostcodeDownloads.php// as test file, size: 500k, 2.1m lines, some of them contain obsolete post codes fileName := 'c:\temp\England postcodes.csv'; file.WRITEMODE := FALSE; file.TEXTMODE := TRUE; file.OPEN(fileName); file.READ(line); // skip header line WHILE file.READ(line) > 0 DO BEGIN   // skip obsolete post codes: 2. value = No   IF SELECTSTR(2,line) = 'Yes' THEN BEGIN  PostCode.Code := SELECTSTR(1,line); PostCode.VALIDATE(City,GetValue(SELECTSTR(15,line))); PostCode.County := GetValue(SELECTSTR(8,line)); PostCode."Country/Region Code" := 'GB'; PostCode.INSERT; dlg.UPDATE(1,idx); dlg.UPDATE(2,PostCode.Code); idx += 1;   END; END; file.CLOSE; dlg.CLOSE; MESSAGE(FORMAT(idx) + ' post codes imported.');LOCAL GetValue(txtValue : Text[100]) : Text txtValue := DELCHR(txtValue,'<','"'); // remove leading "   txtValue := DELCHR(txtValue,'>','"'); // remove trailing " IF STRLEN(txtValue) > 30 THEN // fields City,County are Text[30]   txtValue := COPYSTR(txtValue,1,30); // cut text, leading 30 chars EXIT(txtValue);

report runs with 1.5m valid lines/records about 5 min.

cheers

Cannot create an instance of an Automation Server with CLSID = {GUID}

1 Comment

If you get that kind of error, in most cases that means the automation, which should be registered with that given GUID in your system is missing or misconfigured. In both cases the system does not know the common name of the system. That’s why you get that cryptic message.

Solution:

Search the web (e.g. google.com, microsoft.com) to get more information about the automation. The given GUID is unique worldwide! So you’ll get the name for the automation quite easy. The variable name, where the automation is used, is also a hint for the name, also comments in the code.
Then search for the file on the server using windows explorer, where the nav service is installed and where you want to run the nav object (report, table, codeunit, …), if that automation file (dll) is physically installed.

  • If you do not find it, then it’s missing. download it from the vendor or install the missing software, which includes that automation.
  • If you find the file, then it was not registered correctly. In that case you need to run the registry tool regsrv32. First uninstall, then re-install the automation:
    Regsvr32 [/u] [/n] [/i[:cmdline]] <full path to dllname>
    for more details and troubleshooting follow this.

Typical GUIDs according that issue:

  • 000208D5-0000-0000-C000-000000000046: Microsoft.Office.Interop.Excel
  • 248DD896-BB45-11CF-9ABC-0080C7E7B78D: Winsock Control 6.0
  • DB07BCE5-B131-11D3-9219-00002430F8E2: CFront Plus
  • F5078F18-C551-11D3-89B9-0000F81FE221: MS XML 4.0 (or 6.0)

If you can’t find the GUID on the web, then it is probably a custom automation developed by yourself or a vendor years ago.

cheers

Build or buy solutions

Leave a comment

i read an quite interesting, but obviously hidden commercial, posting about build or buy a solution for EDI. it’s right, that this is often not an easy decision, if you need a new module.

one point is missing in that posting: if you buy a so called ready-to-use solution, in most cases, it is not ready-to-use. you should reckon at least 30% of the budget for customization. The custom solution can be the cheaper solution, if there is no buy-solution on the market or the available solutions are only usable with a huge amount of customizations. For managing a custom project you could read this blog posting.

Reached 10.000 blog views

Leave a comment

Hi,

some days ago i recognized, that my blog on wordpress.com reached more than 10.000 views!

Additional i reached more than 58.000 views on the mirrored blog of the Dynamics Nav Community blog site and over 2.500 views on the mirrored blog of dynamicsuser.net!

Thanks to all have viewed my postings for your interest! I will continue writing posts.

cheers
jonathan

 

 

Compare Dynamics Nav Versions

1 Comment

Sometimes it’s needed to compare the features of different Nav versions, especially in scenarios, where there is a decision: Upgrade or not?

For that MS has debveleoped a very helpful site: The MS Dynamics Nav Feature Comparision Tool.

You can find it here.

You get relevant information not only about the main feature topics. You get also detailed info about modules and features, also about Licensing. Especially interesting is the View “Compare all”. This is the overview, which can be an important decision helper for questions:

  • Which nav version fits best for my company
  • Should i upgrade the current nav version? To which version?
  • Which features do i get with a special Nav version as against Dynamics AX, SAP or an other ERP System.

Where-Used-Tools for Dynamics Nav

1 Comment

Dynamics Nav does not ship with a Where-Used-Tool, although it is often needed. A Where-Used Tool is a tool, with which you can check, which nav objects use/reference a certain nav object or a certain table field.

Idyn has developed a widly used Toolset, the Object Manager Advanced (OMA). It is very helpful, contains that kind of tool, also a version/source control, rename tool, a.s.o. but it has it’s prize. 😦

Other Where-Used/Search tools:

  • “Statical Prism“: free community version available, cheapest full version you can get for $37.
  • “GDT Where Used“: This small free tool lists all Nav objects and has a “Where used” function. It supports Nav 5, Nav 2009, Nav 2013. . Looks very nice, it’s worth to test it.

02/2016, News about “GDT Where used”: There is a new version, which now supports also Nav 2015 and Nav 2016.

Error: Table 2000000009 has no name

1 Comment

If you get following error:

then the database is corrupted. In detail it means sql view “Session” is missing in the database! This can be a result of a crashed backup/restore process or a failed conversion process.

It can be, that View “Database File” is also missing. Although it is possible to restore the missing view e.g. from a (not changed) default cronus database, it won’t help, but you can give it a try. You can restore a new cronus database from the nav 2009 setup, subfolder SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\60\Database.

In most cases it’s needed to restore the database from an earlier backup. If you do not have a backup strategy for your databases – but you should have – then export all changed nav objects as text files from the current, crashed database and start with a new database on base of a cronus database. then import/migrate the changed nav objects. after that re-compile all nav objects and restart the nav service.

Helpful Links:
http://saurav-nav.blogspot.co.at/2012/02/metadata-for-object-of-type-table-with.html

cheers

Active Directory queries from C/AL

Leave a comment

I wanted to get a list of all NAV users with displayname and roles per user in a report. The data base is table “Windows Access Control”. One field should show the displayname of each user. The displayname can be read from hidden table “Windows Object”, field Name. To embed that value in the report i tried to import the field with different methods: by code and by a second, linked dataitem in the report. The result was the same in both cases: the read call (internally an AD query) of the displayname (field Name) is really very, very slow! Although AD queries are quite slow in general (the technique behind is slow), this simple report lasted over 20 minutes and longer. That is not slow, that is really a hoax. Maybe that’s a bug in Nav 2009, maybe the implementation is not very good. So i searched for an alternative solution.

In the end i developed a little automation dll to get the displayname of each user with a given domain account (field “Login ID”). With that automation the report rendering speed was quite acceptable.

This automation, i called it ActiveDirectoryLib, delivers 2 methods:
* GetDisplayName(string userName) : string
* GetUserName(string loginSID) : string

// the displayed report fieldsLogin SID | Username | Role ID | Role Name// global variablesUsername | Text | 100adLib | Automation | 'ActiveDirectoryLib'.AccountInfo// the codeWindows Access Control - OnPreDataItem()CREATE(adLib);Windows Access Control - OnAfterGetRecord()CALCFIELDS("Login ID","Role Name");Username := adLib.GetDisplayName("Login ID");IF Username = '' THEN  Username := "Login ID";Windows Access Control - OnPostDataItem()CLEAR(adLib);

You can download the file here.

cheers