Add-in – Gunnar's Blog

October 24, 2014

My first dynamic Windows Client Add-in

I have a task where a user on a Windows Client wants to use his touch screen to select from a limited set of Items. In NAV 2009 R2 this was a form with buttons and I have been waiting to update this to NAV 2013 R2 until I had finished most of the other tasks for that client. That time is now.

I attended the session on Directions EMEA where Vjeko presented client extensions. I also saw a post by Rashed on his mibuso blog about buttons.

So I started the task. I have done several add-ins for NAV so I know enough about Visual Studio to feel comfortable using it. Of course, when ever one starts something like this there is always something new to learn and use. To search for help on C# on the internet is really helpful and we have a lot of good examples out there.

Like Rashed, I created a panel that hosted all the buttons and text boxes and that panel is the object added to the NAV page.

I wanted to be able to customize all the buttons and text boxes from NAV so I created default values and null able values for all my properties. Then I created a property for every value that I wanted NAV to have access to in the class.

After I created the panel control I wanted a trigger to fire in NAV to tell me that the add-in was ready. I followed the example Vjeko gave in last Directions but that simply did not work. I contacted him and that resulted in a new blog on Vjeko’s site. Instead of triggering an event when creating the control the trigger is now fired after the control is bound to the NAV page.

When I get the trigger fired in NAV I create the buttons with all the properties I need.

The test page I created will allow me to select any number of buttons and/or text boxes. I can customize background and foreground color, font family and font size. Some of these settings can be done for each button or text box individually, that is the ones included in the arrays used when calling the CreateButtons function with arrays create from the Item table using these settings.

In the add-in I have created two event handlers to handle the push of a button or a text change.

This is handled in the NAV page with this code.

So when I press a button or change a text I will get a message from NAV.

I also made sure that if I select zero as a size for either the buttons or the text boxes they will not show.

I have adapted a new method when sharing objects and information. I will publish on my blog how things are done if you want to create your own version and will also supply a version you can download and start using at Objects4NAV.com.

January 3, 2014

Add-ins Downloaded and Installed with NAV C/AL

I was watching Vjeko’s lecture in NAVTechDays 2013. He gave me an idea on the add-in installation. Thank you Vjeko.

It is now finished and time to share this with you all. The basic function Codeunit is used to handle the downloading and installing of the add-in. In the Add-on Setup Page I put an action to trigger the Add-ins validation.

This function will call the CheckAddins trigger on the Setup Table. There I have a simple code

[code]
IF NOT CANLOADTYPE(HardwareHubProxy) THEN
AddinMgt.DownloadAnInstallServerAddIn(
‘HardwareHubProxy.dll’,
‘https://dl.dropboxusercontent.com/u/33900811/NAV/Add-Ins/Server/Advania/HardwareHubProxy.dll’,
Text002);[/code]

That will check if the add-in is installed and if not call a trigger in the add-ins function Codeunit to install the add-in. This works both for the client and server.

In this example I am installing the HardwareHubProxy.dll that is required on the server to use the Hardware Hub. Text002 is ‘ENU=Connection to the Advania Hardware Hub;ISL=Tenging við vélbúnaðargátt Advania’.

When installing the add-in I begin by downloading it into a Base64 string. This is always done on the server. The add-in is then saved to a temporary file, on the server for a server add-in and on the client for a client add-in.

Next I create a command file that is used to copy the temporary file to the add-ins folder. This command file is then executed. Before execution there is a check to see if the current user is able to write to the add-in directly. If so, then the add-in is copied to the add-ins folder and the process finishes.

If the service user is not able to write to the server add-ins folder the user will get a save-as dialog and is asked to copy the file to the server add-ins folder. In all my tests the server has been able to save the add-in without problems.

By default the running client will not be able to write the add-in to the client add-ins folder. If this is the case then a confirmation dialog will be prompted as the copy function is running in an elevated access mode.

AddInFunctions

November 26, 2012

Using a .dll proxy for web services

I have now completed my first all-dotnet codeunit. The codeunit uses a dll file that I created from the web service WDSL. This makes the programming a lot easier.

This solution has a BLOB fields that stores both incoming and outgoing xml. When I use a proxy dll I don’t build a xml document and I never handle xml documents. Again dotnet has a solution. I use xml serializer from the system.xml object.

[code]LOCAL PROCEDURE SerializeToXMLStream@1100408008(VAR Object@1100408002 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Object" RUNONCLIENT;VAR NavOutstr@1100408004 : OutStream);
VAR
xmlSerializer@1100408000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.Serialization.XmlSerializer" RUNONCLIENT;
StreamWriter@1100408003 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.StreamWriter" RUNONCLIENT;
File@1100408001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
NavInstr@1100408005 : InStream;
BEGIN
IF ISNULL(Object) THEN EXIT;
StreamWriter := StreamWriter.StreamWriter(TempFileName);
xmlSerializer := xmlSerializer.XmlSerializer(Object.GetType);
xmlSerializer.Serialize(StreamWriter,Object);
StreamWriter.Close;
UPLOADINTOSTREAM(”,MagicPath,”,TempFileName,NavInstr);
COPYSTREAM(NavOutstr,NavInstr);
File.Delete(TempFileName);
END;[/code]

I can send the proxy object to this function and will get the xml into the BLOB field.

[code]CurrencyRates := StatementService.GetCurrencyRates(TypeRates,CREATEDATETIME(RatesDate,235959T));

WITH BankAccAction DO BEGIN
"Modified by User ID" := USERID;
"Modification Date and Time" := CURRENTDATETIME;
"Incoming Message".CREATEOUTSTREAM(OutStr);
SerializeToXMLStream(CurrencyRates,OutStr);
MODIFY;
COMMIT;
END;[/code]

This example is executed on the client since I am using certificates and web services security for the communication.

April 4, 2012

Geometric mean

In one of my recent projects I needed to calculate a geometric mean for a group of numbers. The Geometric mean formula is generally, if the numbers are $x_1,\ldots,x_n$ , the geometric mean $G$ satisfies $G = \sqrt[n]{x_1 x_2 \cdots x_n},$

The problem appeared when I needed to multiply numbers and got a overflow on the decimal data type in Dynamics NAV.

I produced two solutions to this problem; first for Classic Client using Excel and secondly for Role Tailored Client using Add-in. Using Excel for this job is slow but the Add-in works great.

In Excel I add values to a column and then use the built in function GEOMEAN(‘<Range>’) to calculate the value. This works for large values.

The GeoMean Class Add-in I created in Visual Studio in VB.NET
[code htmlscript=”false” lang=”vb”]Public Class GeoMeanClass
Dim TotalValue As Double
Dim NoOfValues As Integer
Public Sub ResetValue()
TotalValue = 1
NoOfValues = 0
End Sub
Public Sub AddValue(ByVal Value As Decimal)
If Value = 0 Then Exit Sub
TotalValue = TotalValue * Value
NoOfValues = NoOfValues + 1
End Sub
Public ReadOnly Property NoOfStoredValues() As Integer
Get
Return NoOfValues
End Get
End Property
Public ReadOnly Property GetGeoMean() As Decimal
Get
If NoOfValues = 0 Then
Return 0
Else
Return CDec(TotalValue ^ (1 / NoOfValues))
End If
End Get
End Property
End Class[/code]
Attached are the Add-in and the codeunits needed to calculate Geometric mean.

GeoMean

March 28, 2012

Using OpenXML to create an Excel Document

In one of my projects I needed to create a complex Excel document from Dynamics NAV. I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370. The problem is that this is dead slow. It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document. Great blog and a big help. The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”. I was inserting an invalid character into the XML. I did a quick search and found a solution, I needed to filter the data through a white list. Here is my AddCell function:

LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
VAR
XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
CellValueAsText@1200050007 : Text[1024];
CommentLine@1200050003 : Text[1024];
CommentPosition@1200050004 : Integer;
CommentLength@1200050005 : Integer;
BEGIN
IF CurrentRow = 0 THEN
GlobalRowNo := GlobalRowNo + 1
ELSE
GlobalRowNo := CurrentRow;
IF CurrentCol = 0 THEN
GlobalColumnNo := GlobalColumnNo + 1
ELSE
GlobalColumnNo := CurrentCol;

//Comments are not yet supported by ClosedXML
//CommentLength := CommentText.LENGTH;
//IF CommentLength > 0 THEN BEGIN
// CommentPosition := 1;
// WHILE CommentPosition < CommentLength DO BEGIN
// CommentPosition := CommentPosition + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
// XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
// END;
//END;

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
IF IsUnderline THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

IF Attention THEN BEGIN
XlColor := XlColor.FromName('Red');
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
END;

IF IsFormula THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
ELSE
CASE TRUE OF
Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
BEGIN
CellValueAsText := FORMAT(Value);
CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
END;
ELSE IF FORMAT(Value) <> '0' THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
END;

CASE Alignment OF
Alignment::General:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
Alignment::Center:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
Alignment::Right:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
Alignment::Left:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
END;
END;

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

is used to clean the string that is passed to the XML.

The GetEnumValue function is

LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
VAR
Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
BEGIN
ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
END;

When constructing the OpenXML dotnet object I also construct the white characther check object.

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in