How to: Create an automation for usage in Navision

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 code
using 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

Convert Tab delimited strings

I had an issue to process a csv file with TAB delimited text lines. The existing code expected text lines with ; as delimiter.

Character TAB has ASCII Code 9.

So i wrote following code:

ConvertTabString(line : Text[250]) : Text[250]
ch := 9; // of type Char
line := CONVERTSTR(line,FORMAT(ch),';');
exit(line);

test code:

// set a line value or load the line from a text file using file.open
line := 'ab cd  ef  cd'; 
line := ConvertTabString(line);
MESSAGE(line);

result: 'ab;cd;ef;cd'

an interesting thing is, that this code does not work for older nav 2009 builds. maybe a problem with the encoding. for that case i developed a second version.

// text file test.txt contains 1 text line 'ab cd  ef  cd'. with TAB as delimiter.
// file : FILE
// instr : InStream
file.OPEN('c:\temp\test.txt');
file.CREATEINSTREAM(instr);
instr.readtext(line,maxstrlen(line));
line := CONVERTSTR(line,FORMAT(ch),';');
message(line);
file.close;

This can also be used to export the converted lines to a new text file, which can then be imported via dataport/xmlport.

cheers

How to display sum value in factbox on base of multiple line selection

Typical issue is to show a calculated value in a factbox on base of the currently selected line in a list page or the lines in a subpage. Is it possible to get a calculated value on base of more than one selected line? Let’s check out.

Let’s say we want to display the sum of field “Credit Limit” in the customer list when selecting multiple lines. So first we edit page 9082 “Customer Statistics FactBox”. There we add a global variable SumCredLimit (decimal) to the page, then we add a new line in page with sourceexpr = SumCredLimit.

Now we add a new global function SetSumCredLimit.

Save and close the page. Now we edit page 21 customer list. There we rename page part “Customer Statistics FactBox” to CustStatFbx. That’s needed, so that we can reach the custom functions in page “Customer Statistics FactBox” from page “customer list”.

After that edit the page code (F9). Goto trigger OnAfterGetCurrRecord, there add local variable Cust (record|Customer). Add following code to trigger:

Save and close the page. Now run the Nav windows client and goto customer list.
The result:

You see, the new value in the factbox shows the sum of the Credit Limit values of the 3 selected lines.

cheers

Simple Read/Write Excel Data

In a Nav forum there was a question, how to read data from an excel document, change the data in Nav, write data back to the excel document without changing the existing cell formatting. With table ExcelBuffer and the helper assemblies delivered with Nav this cannot be done, because the Write functions e.g. SetCellValueText work with decorators.

So i developed assembly XlsLib. With that assembly read and write actions from an excel document are done very easy. The provided methods are very simple. You can read the cell values, the basic number formats and save it to table Excel Buffer. When writing only the value as is is written back to the cell. No further formatting is read or written.That can also be used, when you work with templates.

Main functions:

XlsWorksheet.GetValue(rowId, colId, value, type):
– Get a value from cell rowId/colId,
– given value is of type text,
– type is of datatype integer, can be 0-3 according option field NumberFormat in table ExcelBuffer, Type=4 means Unknown

XlsWorksheet.SetValue(rowId, colId, value):
– Sets a value in cell rowId/colId. value can be of any type.

Here is a sample code:

TestXlsLib()
// variables
//  XlsWorkbook DotNet XlsLib.Workbook.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
// XlsWorksheet DotNet XlsLib.Worksheet.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
// value Text
// type Integer

// open excel workbook
XlsWorkbook := XlsWorkbook.Workbook('c:\temp\test.xlsx');
// select sheet 'Sheet2'
XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,'Sheet2');

// read cell values from row=1, column=1-3
XlsWorksheet.GetValue(1, 1, value, type); // e.g. 1234
XlsWorksheet.GetValue(1, 2, value, type); // e.g. 10.10.1990
XlsWorksheet.GetValue(1, 3, value, type); // e.g. 99.12

// Write some values
XlsWorksheet.SetValue(1, 1, 5432);  // an integer value again
XlsWorksheet.SetValue(1, 2, 010175D); // again a date value
XlsWorksheet.SetValue(1, 3, 34.45); // a decimal value

// read values again to check changes
XlsWorksheet.GetValue(1, 1, value, type);
XlsWorksheet.GetValue(1, 2, value, type);
XlsWorksheet.GetValue(1, 3, value, type);

// Save: Save to loaded file filename
//XlsWorkbook.Save();
// SaveAs: create a copy, save result to new file
XlsWorkbook.SaveAs('c:\temp\test2.xlsx');
// Close Excel App !!
XlsWorkbook.Close();

// if an error occurs ...
IF XlsWorkbook.Error <> '' THEN
  MESSAGE(XlsWorkbook.Error);

// release memory
clear(XlsWorkbook);

You can download XlsLib here.

Hint: When downloading that file, it can be, that you have troubles using it because of security issues, e.g. dll is not visible in list of assemblies, you get an error message like “Cannot open xlslib, because one or more dependencies are not met”, methods are not visible or something else. then start windows explorer, goto folder were you copied the lib files, view file properties. if you see a notice at the bottom of the file properties window like “file is blocked because downloaded from internet, grant access?”, then click on “grant access”.

cheers

Options in Report Request Page

This posting shows how to add option fields to a report’s request page.

I developed a simple report on base of table Sales Header.

To filter by field Posting Date you can simply add that field to report data item “Sales Header”‘s property ReqFilterFields, but in this sample we check out how to handle complex filter processing.

Add 2 global variables of type Date: StartDate, EndDate.

Now edit the request page (Menu View/Request Page), add a container line, a group line and 2 field lines to the request page, set the SourceExpr in line 1 to StartDate, line 2 to EndDate.

To apply the filter fields add following code to trigger Sales Header – OnPreDataItem.

If both filter fields are filled, the data is filtered: StartDate<=Posting Date<=End Date.
if only StartDate has a value: StartDate<=Posting Date
if only EndDate has a value: Posting Date<=EndDate

When running the report, we get:

cheers

 

Check License state of Objects

In Nav forums it’s quite often asked, how to check, if a object is within the current loaded license. There are some solutions for Nav 2009. I did not find a satisfying solution for newer Nav versions. So i’ve developed a “License Permission” Page, which lists Objects and their Permissions. Object IDs, which are not in the current loaded license, means there is no read or execute permission for that Object ID, are displayed red. When running the page the list of all nav objects in the database is loaded.

The buttons in the page:

Reload Base Objects: List all current database Object IDs
Swap Read Filter: Switch between 3 Read Permission Filter settings ” “, Yes|Inherited, No Filter
Swap Execute Filter: same as button above for Execute Permission
Custom Objects Filter: List objects between Object ID 50.000 and 99.999.

The object range buttons:

View Table Range: List all table Object IDs according the filter you set.
View Page Range: List all page Object IDs according the filter you set.
View Report Range: List all report Object IDs according the filter you set.
View CU Range: List all codeunit Object IDs according the filter you set.

You can download the page source code here.

The license information is saved in table “License Permission”. With a page on base of that table the windows client freezes, because the data load is really huge, especially, when filtering. So i developed that kind of solution.

cheers

Dotnet Events are not shown

There are 2 scenarios, where embedded events of .net (dotnet) assemblies can/should be displayed in the c/al code … or maybe not.

Scenario 1:
You use a control-addin like the PingPong Control-Addin (Microsoft.Dynamics.Nav.Client.PingPong). For usage it’s needed that a control-addin is listed in the Control-Addin Page. If so, you can select it from the list, wenn you set a value in property ControlAddin of Page’s field. If the control-addin has embedded events, they should be displayed immediately at the end of the fields triggers in the c/al code.

If not, then

• the assembly has no events
• the eventhandling in the assembly was developed not correctly
• the from the Control-Addin referenced assembly Microsoft.Dynamics.Framework.UI.Extensibility has the wrong version! This assembly is referenced by every Control-Addin. Microsoft ships with each version (build, CU) mostly (always?) new build versions of all the Nav assemblies.

 

Scenario 2:

You declare a global variable of type dotnet and use a .net assembly, which has embedded events. These events are only shown, if you set the variable’s property “WithEvents” to Yes.

If the property is set to No (default), the events are not shown in the c/al code.
if you have set it to true, after that to false, the events stay … for the moment. Compile, re-edit the object and the events in the c/al code are gone.

P.S.: The rumour that the WithEvents property is obsolete, is wrong! It is used in global variables of type dotnet  and is mandatory for the usage of embedded windows events. It was also used in old COM/OCX Components. That one is obsolete in newer Nav versions.

Working with Excel Documents in Nav

Dynamics Nav is shipped with the opportunity to export every report as Excel document. Additional there is Table ExcelBuffer for exporting each kind of data. An example for this table’s usage is the action “Export to Excel” in Page “G/L Budget”.

In table ExcelBuffer there are used some very helpful .net assemblies shipped with Dynamics Nav to cover the interaction with excel.

In Nav forums there are quite often questions about importing or exporting data from/to Excel. So i wrote a sample, where you can see, how to work with these assemblies.

• An excel file is opened
• Adding a new sheet
• Write a value to the new sheet
• Close the WorkbookWriter Object to close the current Excel session
• Open the Excel document in Excel with standard Excel Interop assembly

The variables: this is the Nav 2013 version, so here is used Microsoft.Dynamics.Nav.OpenXml vs. 7.0. In Nav 2015 use vs. 8.0 instead.

 

DECSTR – Decrease integer value in string

INCSTR is a C/AL command, which is often used to increase document numbers, which usually start with some letters followed by a number, e.g. AB-00010.

With INCSTR(‘AB-00010’)  you get then ‘AB-00011’.

In a Nav forum a member asked, if there is a simple way for decreasing such a document no. don’t know, why he needs that, but it’s a nice idea.

So, after a little research and finding some typical looooong c/al code solutions, i developed my own small, very cool solution using .net class Regex:

OnRun()
// loc. variables
//DocNo : Code 20

DocNo := DecStr(‘AB-00010’);
MESSAGE(DocNo);

LOCAL DecStr(DocNo : Code[20]) : Code[20]
// loc. variables
//Prefix: Text
//NoString: Text
//Number: Integer
//Regex: DotNet System.Text.RegularExpressions.Regex.’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
//DotNetInt: DotNet System.Int32.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
//PadLength: Integer

Prefix := Regex.Replace(DocNo,’\d+’,”);
NoString := Regex.Match(DocNo,’\d+’).Value;
Number := DotNetInt.Parse(NoString);
IF Number > 0 THEN BEGIN
Number := Number – 1;
PadLength := STRLEN(DocNo) – STRLEN(Prefix) – STRLEN(FORMAT(Number));
EXIT(Prefix + PADSTR(”,PadLength,’0′) + FORMAT(Number));
END;

ERROR(‘Resulting number would be negative.’);

---

Results in: AB-00009

the resulting number has the same length.

---

Additional the links i found:

• http://dinukak-navmate.blogspot.co.at/2015/01/decstr-like-incstr-but-decreasing.html
• http://forum.mibuso.com/discussion/17478/decstr-like-incstr-but-decreasing

 

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

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