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:

autom1    autom2

cheers

Advertisements

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.

ccl1.png-750x491

Now we add a new global function SetSumCredLimit.

ccl2.PNG-594x65

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”.

ccl3.png-750x571

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

ccl4.png-750x291

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

ccl5.png-750x485

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.

sh-list-1

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.

sh-list-2

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.

sh-list-3

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

sh-list-4

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:

sh-list-5

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.

licperm

The buttons in the page:

licperm2

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.

licperm3

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.

DisplayEvents3

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.

DisplayEvents2
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.

DisplayEvents1

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.