Tag Archives: COM

Open .net SDK command prompt here!

Posted on by
Reply

Barry Fitzgerald from Gumdrop books (a NAV 2009 customer) sent me a hint to another blog post, which in short talks about how to add a menu item to your folder menus like this:

image

http://www.theproblemsolver.nl/dotnet_faq_0016.htm

This would open a command prompt, starting in this directory ready to use Visual Studio tools like SN (which you need for Client extensibility) or RegAsm (which you need for COM development).

I found it pretty nice, so I thought I would share this.

On my machine, I have created the following registry key, which seems to work great.

image

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel R2 – Part 2 (out of 2) – the final pieces

Posted on by
Reply

It is time to collect the pieces.

The full Edit In Excel R2 solution looks like this

image

Slightly more complicated than the first version – but let me try to explain the pieces

NAVEditInExcel is the COM object, which we use from within NAV. This actually hasn’t changed a lot, the only small change is, that the EditInExcel method now takes a base URL, a company, a page and a view (compared to just a page and a view earlier).
NAVPageDynamicWebReference is the Dynamic Web Reference class and the NAVPageServiceHelper class – described here.
NAVPageFieldInfo contains the NAVFieldInfo class hierarchy for handling type weak pages, described here and used in the Conflict resolution dialog here.
NAVPageMergeForm is the conflict resolution dialog, described here.
NAVTemplate is the actual Excel Add-In which of course now makes use of Dynamic Page References and conflict resolution. It really haven’t changed a lot since the version described here – the major change is the pattern for handling conflict resolution.
EditInExcel Setup is the Client Setup program, this setup program needs to be run on all Clients
EditInExcelDemo is the Server Setup program, this setup program contains the Client Setup msi and places it in the ClientSetup folder for the ComponentHelper (which you can read about here) to autodeploy to clients. This setup also contains the .fob with the EditInExcel objects.

The Client Setup Program

Lets have a closer look at the Client Setup Program

image

This setup project includes primary output from the COM component and the Excel Add-in and calculated dependencies from that.

Note, that when deploying add-ins you have to add the .vsto and the .manifest files to the setup project yourself, the dependency finder doesn’t discover those. Also note, that all the vsto runtime dll’s etc are excluded from the install list, as we do not want to copy those DLL’s.

Instead I have built in a Launch condition for VSTO runtime 3.0, which is done in 2 steps:

image

First a Search on the Target Machine for component ID {AF68A0DE-C0CD-43E1-96DD-CBD9726079FD} (which is the component installation ID for VSTO 3.0 Runtime) and a launch condition stating that that search needs to return TRUE – else a message will appear with a URL for installing VSTO, which is:

http://www.microsoft.com/downloads/details.aspx?FamilyId=54EB3A5A-0E52-40F9-A2D1-EECD7A092DCB&displaylang=en

One more thing needed in the Client Setup program is to register the COM object. Now the Setup actually has a property you can set, indicating that the object should be registered as COM, but I couldn’t get that to work, so I added custom install actions to the NAVEditInExcel COM object:

image

and the code for the class, which is called by the installer looks like:

[RunInstaller(true)]
public partial class RegasmInstaller : Installer
{
public RegasmInstaller()
: base()
{
}

    public override void Commit(IDictionary savedState)
{
base.Commit(savedState);
Regasm(false);
}

    public override void Rollback(IDictionary savedState)
{
base.Rollback(savedState);
}

    public override void Uninstall(IDictionary savedState)
{
base.Rollback(savedState);
Regasm(true);
}

    private void Regasm(bool unregister)
{
string parameters = “/tlb /codebase”;
if (unregister)
parameters += ” /unregister”;
string regasmPath = RuntimeEnvironment.GetRuntimeDirectory() + @”regasm.exe”;
string dllPath = this.GetType().Assembly.Location;
if (!File.Exists(regasmPath))
throw new InstallException(“Registering assembly failed”);
if (!File.Exists(dllPath))
return;

        Process process = new Process();
process.StartInfo.CreateNoWindow = true;
process.StartInfo.UseShellExecute = false; // Hides console window
process.StartInfo.FileName = regasmPath;
process.StartInfo.Arguments = string.Format(“”{0}” {1}”, dllPath, parameters);
process.Start();

        // When uninstalling we need to wait for the regasm to finish,
// before continuing and deleting the file we are unregistering
if (unregister)
{
process.WaitForExit(10000);
try
{
System.IO.File.Delete(System.IO.Path.ChangeExtension(dllPath, “tlb”));
}
catch
{
}
}
}
}

All of the above is captured in the NAVEditInExcelR2.msi – which is the output from the Edit In Excel Setup project. Running this .msi on a client will check pre-requisites, install the right DLL’s, register the COM and you should be good to go.

The Server Setup Program

The Server Setup program actually just needs to place the Client Setup Program in a ClientSetup folder and the .fob (NAV Objects) in the ServerSetup folder.

There are no pre-requisites, no actions no nothing – just copy the files.

After Copying the files on the Server – you need to import the .fob, run the setup code unit and you should be good to go.

Note, that this requires ComponentHelper1.03 (which you can read about here and download here) to run.

Wrapping up…

So, what started out as being a small garage project, ended up being somewhat more complicated and way more powerful. It runs with Office 2007 and Office 2010 (even though you cannot modify the project when Office 2010 beta2 is installed) and even though you might not need the actual Edit In Excel functionality – there are pieces of this that can be used for other purposes.

The source for the entire thing can be downloaded here and the EditInExcel Demo msi can be downloaded here.

 

Happy holidays

 

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Auto Deployment of Client Side Components – take 2

Posted on by
Reply

Updated the link to the ComponentHelper msi on 12/11/2009

Please read my first post about auto deployment of Client side components here before reading this.

As you know, my first auto deployment project contained a couple of methods for automatically adding actions to pages, but as one of my colleagues in Germany (Carsten Scholling) told me, it would also need to be able to add fields to tables programmatically in order to be really useful.

In fact, he didn’t just tell me that it should do so, he actually send me a couple of methods to perform that.

The method signatures are:

AddToTable(TableNo : Integer;FieldNo : Integer;VersionList : Text[30];FieldName : Text[30];FieldType : Integer;FieldLength : Integer;Properties : Text[800]) : Boolean

and

AddTheField(TableNo : Integer;FieldNo : Integer;FieldName : Text[30];FieldType : Integer;FieldLength : Integer) SearchLine : Text[150]

And it can be used like:

// Add the fields
SearchLineLat := ComponentHelper.AddTheField(DATABASE::Customer, 66030, ‘Latitude’,  FieldRec.Type::Decimal, 0);
SearchLineLong := ComponentHelper.AddTheField(DATABASE::Customer, 66031, ‘Longitude’,  FieldRec.Type::Decimal, 0);

which just add’s the fields without captions or

// Add Latitude to Customer table
ComponentHelper.AddToTable(DATABASE::Customer, 66030, ‘VirtualEarthDemo1.01’, ‘Latitude’, FieldRec.Type::Decimal, 0,
‘CaptionML=[DAN=Breddegrad;DEU=Breitengrad;ENU=Latitude;ESP=Latitud;FRA=Latitude;ITA=Latitudine;NLD=Breedte];DecimalPlaces=6:8’);

Remember, that the table will be left uncompiled after doing this.

AddToTable actually calls AddTheField and after that it modifies the metadata to set the caption on the field:

AddToTable(TableNo : Integer;FieldNo : Integer;VersionList : Text[30];FieldName : Text[30];FieldType : Integer;FieldLength : Integer;Properties : Text[800]) : Boolean
changed := FALSE;
SearchLine := AddTheField(TableNo, FieldNo, FieldName, FieldType, FieldLength);
IF SearchLine <> ” THEN
BEGIN
GetTableMetadata(TableNo, Metadata);
IF AddToMetadataEx(TableNo, Object.Type::Table, Metadata, SearchLine, ”, ‘;’ + Properties, TRUE, FALSE) THEN BEGIN
SetTableMetadata(TableNo, Metadata, VersionList);
changed := TRUE;
END;
END;

AddTheField is the actual “magic”:

AddTheField(TableNo : Integer;FieldNo : Integer;FieldName : Text[30];FieldType : Integer;FieldLength : Integer) SearchLine : Text[150]
Field.SETRANGE(TableNo, TableNo);
Field.SETRANGE(“No.”, FieldNo);
SearchLine := ”;

IF Field.ISEMPTY THEN BEGIN
Field.TableNo := TableNo;
Field.”No.” := FieldNo;
Field.FieldName := FieldName;
Field.Type := FieldType;
Field.Class := Field.Class::Normal;
Field.Len := FieldLength;
Field.Enabled := TRUE;
Field.INSERT;

  Field.FINDFIRST;

  Len[1] := 4;
Len[2] := 20;
Len[3] := 15;

  IF STRLEN(FORMAT(FieldNo))   > Len[1] THEN Len[1] := STRLEN(FORMAT(FieldNo));
IF STRLEN(FieldName)         > Len[2] THEN Len[2] := STRLEN(FieldName);
IF STRLEN(Field.”Type Name”) > Len[3] THEN Len[3] := STRLEN(Field.”Type Name”);

  SearchLine := ‘    { ‘ + PADSTR(FORMAT(FieldNo), Len[1]) + ‘;  ;’ +
PADSTR(FieldName, Len[2]) + ‘;’ + PADSTR(Field.”Type Name”, Len[3]);
END;

The new ComponentHelper 1.03 msi can be downloaded here and my upcoming posts (e.g. Edit In Excel R2) will require this. If you only want to download the objects you can do so here (there is no changes in the NAVAddInHelper source (compared to the first post – that can be downloaded here).

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Running Code on the Client without deploying COM objects

Posted on by
Reply

Yes, it can be done!

No, it isn’t .net code – nor AL code.

Why?

It started out as me being a little too fast when stating that you could easily download a file to the Client and attach it to Outlook without any user interaction – and as you might know that is true, but you might also know that if you go the recommended route:

FILE.DOWNLOAD(FileName, ”, ‘<TEMP>’,”, ToFile);
Mail.NewMessage(”,”,Name,”,ToFile,TRUE);
FILE.ERASE(FileName);

Then you will get an e-mail that looks like this:

image

People might not assume that this actually is Invoice no. 10103 in PDF format. What you of course want to have is:

image

So, how do we get there.

I actually did respond to a post on mibuso a while back (http://www.mibuso.com/forum/viewtopic.php?f=32&t=29806) about how this could be done, but that would involve a COM object deployed to all clients and not everybody wants that (although I have posted a method on how to do this automatically).

The problem here is, that file.download always places the file in a temporary directory with a temporary filename – and there is (to my knowledge) no other way to copy a file to the Client.

Assuming that this is correct, how do we then rename a file on the client without having to deploy COM objects?

I said without deploying COM objects, not without USING COM objects

As you know, we can run COM objects on the server or on the Client and one of the COM objects, which ships with Windows can come in handy here. The Windows Script Host – if we instantiate this COM object we can actually give the component a VB Script to execute in the context of the COM component (which would be either on the Server or on the Client).

Windows Script Host

Yes, WSH is legacy – but it is widely used and it is included on all Windows versions from Windows XP and up. I am not going to make this a tutorial on VBScript and WSH – for that you can find a number of good posts on the internet – or start by reading msdn

http://msdn.microsoft.com/en-us/library/ms950396.aspx

Creating a script function / method

The method AddCode on the Windows Script Host COM object is used to add sourcecode to the component.

Note, that you need to add an entire function / method in one call and note, that each line needs to be terminated by a CR.

You also need to specify what language you use, the control supports JScript and VBScript.

A VBScript function which returns Hello <name> could look like this:

function Hello(who)
Hello = “Hello “&who
end function

Creating this function in a Client side COM component could look like:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;
objScript.AddCode(
‘function Hello(who)’+CR+
‘  Hello = “Hello “&who’+CR+
‘end function’);
END;

The way I write this is, that I try to maintain the structure of the VBScript even though it is inside a string in NAV, maybe I am fooling myself, but I think it is more readable.

Invoking a script function / method

There are two ways of invoking a script method:

Eval – used to invoke a function, and get a return value back.

The above function could be called using

MESSAGE(FORMAT(objScript.Eval(‘Hello(“Freddy”)’)));

Note – when calling functions, VBScript wants your parameters embraced by parentheses.

ExecuteStatement – used to invoke a method which doesn’t return anything

Let’s rewrite the above function to a method and have the method show a MessageBox:

The VBScript could look like:

sub Hello(who)
MsgBox “Hello “&who, 0, “Title”
end sub

and creating this function in a COM object and calling the method could look like:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;
objScript.AddCode(
‘sub Hello(who)’+CR+
‘  MsgBox “Hello “&who, 0, “Test”‘+CR+
‘end sub’);
objScript.ExecuteStatement(‘Hello “Freddy”‘);
END;

Note – when calling methods (or sub’s) VBScript does NOT want the parameters embraced by parentheses.

Some sample scripts

Rename a temporary file

function RenameTempFile(fromFile, toFile)
set fso = createobject(“Scripting.FileSystemObject”)
set x = createobject(“Scriptlet.TypeLib”)
path = fso.getparentfoldername(fromFile)
toPath = path+””+left(x.GUID,38)
fso.CreateFolder toPath
fso.MoveFile fromFile, toPath+””+toFile
RenameTempFile = toPath
end function

As you can see, I am doing exactly what I responded on the mibuso thread here – just in VBScript instead – which then requires no client side install.

BTW this function is actually used in ClausL’s post about sending e-mail with PDF attachments, which proves that we do talk with our colleagues at Microsoft:-). Note that there is no good way of creating a GUID from VBScript – I (mis)use the fact that every instance of Scriptlet.TypeLib gets assigned a new GUID.

Get Machine name

function GetComputerName()
set net = createobject(“wscript.network”)
GetComputerName = net.ComputerName
end function

I know, that you also can read an environment variable – but this way you can actually get all kind of information on the network though this.

Launch an application

sub Notepad()
set shell = createobject(“WScript.Shell”)
shell.Run “notepad.exe”
end sub

Yes, you can do this by using the Shell object directly in NAV, like:

Shell       Automation       ‘Microsoft Shell Controls And Automation’.Shell

CREATE(objShell,True,true);
objShell.Open(‘c:\windows\system32\notepad.exe’);

I just wanted to show that you that stuff like this can be done in VBScript too, and note, that the Shell object in VBScript and in NAV is not the same.

Asking a simple question

function Input(question, title, default_answer)
Input = InputBox(question, title, default_answer)
end function

A couple of partners have told me, that they are unhappy with the discontinuation of INPUT from NAV and having to create pages for even the simplest questions. Running the following code:

IF CREATE(objScript,TRUE,TRUE) THEN
BEGIN
CR := ‘ ‘; CR[1] := 13;
objScript.Language := ‘VBScript’;

  objScript.AddCode(
‘function Input(question, title, default_answer)’+CR+
‘  Input = InputBox(question, title, default_answer)’+CR+
‘end function’);

  s := objScript.Eval(‘Input(“How old are you?”, “A simple question”, “”)’);
MESSAGE(s);
END;

Brings up this dialog on my machine:

image

Who knows, maybe somebody can use this as an alternative to INPUT.

Read the RoleTailored Client configuration file

function ReadConfigFile()
set shell = CreateObject(“WScript.Shell”)
folder = shell.ExpandEnvironmentStrings(“%LOCALAPPDATA%”)
if folder = “” then folder = shell.ExpandEnvironmentStrings(“%USERPROFILE%”)&”Local SettingsApplication Data”
  filename = folder&”MicrosoftMicrosoft Dynamics NAVClientUserSettings.config”
set fso = createobject(“Scripting.FileSystemObject”)
set file = fso.OpenTextFile(filename, 1)
ReadConfigFile = file.ReadAll()
end function

Note that I have NOT tested this function under Windows XP – I know that LOCALAPPDATA is not defined on Windows XP and I think the line:

  if folder = “” then folder = shell.ExpandEnvironmentStrings(“%USERPROFILE%”)&”Local SettingsApplication Data”

should take care of finding the right folder – if anybody can confirm that, then add that as a comment to this post.

Bringing up a MESSAGE with the outcome of this function on my machine gives me this dialog:

image

I don’t know whether that could come in handy, but maybe it can spawn off some good ideas.

Wrapping up

As you can see, you can do a lot of things in VB Script on the Client (or on the Server). There are a number of scripts you can find on the internet to work with the A/D (create, delete and enumerate users).

Of course there a limitations as to what you can do in VBScript and it isn’t a real alternative to writing a COM component, but for something it is easy and straightforward – and it doesn’t require any client side installation of components and this works in both Classic and RTC.

You can download the rename function from ClausL’s post about sending e-mail with PDF attachments. You will need to do copy, paste and maybe modify the other samples in order to use them.

 

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Auto deployment of Client Side Components

Posted on by
Reply

NOTE: There is an updated post on Auto deployment of Client Side Components here.

When you install the RoleTailored Client on a number of clients, you might need to install a number of Client side components as well. This might not sound as too much of a problem when you need to install the client anyway – but lets say you install an ISV Add-on with a live customer, who already have 100 clients install – and now you need to install the objects to the database – AND you need to run to 100 computers and install Client side components.

Yes, you can do this with system management policies, but not all customers are running SMS and it would just be way easier if everything could be handled from the ISV Add-On and the Client Components could be auto deployed.

When doing this – it is still important, that IF the customer is running SMS and decide to deploy the Client Side components through system policies – then the auto deployment should just pick this up and accept that things are Ok.

Two kinds of Client Side Components

NAV 2009 SP1 supports Add-Ins (Client Extensibility Controls) and Client side COM components (as NAV 2009) and the way these components are installed is very different.

Add-Ins needs to be placed in the Add-Ins folder under the RoleTailored Client folder on the Client and COM components can be installed wherever on the Client, but needs to registered in the registry with regasm.

Both Add-Ins and COM components might rely on other client side components, so it is important that we don’t just create a way of copying files to the Client – but we should instead create a way of launching a setup program on the client, which then installs the components. In my samples, I have one Setup program for every component, but an ISV could easily package all components together in one installation program and install them all in one go.

To install a client side component really isn’t that difficult – use FILE.DOWNLOAD with an MSI and that’s it. But how do we detect whether or not the component is installed already?

We cannot keep a list on the server side, since the computer might get re-installed or restored – we need a way of discovering whether a component is installed.

Detecting whether a Client side COM component is installed

I will start with the COM component (since it will take a COM component to check whether an Add-In is installed). The COM component needs a CREATE statement to be initialized and if you check the return value of the CREATE statement – you know whether or not the COM component is executable. If not we launch a FILE.DOWNLOAD

IF NOT CREATE(mycomponent, TRUE, TRUE) THEN
FILE.DOWNLOAD(mycomponentinstaller);

Almost too simple right?

Now – I know that some people will say – well, what if I have an updated version of the COM component and it needs to be deployed?

My answer to that would be to change the COM signature, in effect making it a different COM component and allow them to be installed side-by-side. This would in effect mean that you might have multiple versions of COM components installed on a client, but they typically don’t take up a lot of space, and they don’t run if nobody uses them.

You could also create a function for checking the version number of the component like:

IF NOT CREATE(mycomponent, TRUE, TRUE) THEN
FILE.DOWNLOAD(mycomponentinstaller)
ELSE IF NOT mycomponent.CheckVersion(100) THEN
FILE.DOWNLOAD(mycomponentinstaller);

problem with this approach is, that NAV keeps a lock on the Client side component (event if you CLEAR(mycomponent)) due to performance reasons and your mycomponentinstaller will have to close the NAV client in order to update the component.

I like the solution better, where you just create a new GUID and thus a new component – so that is what I will describe here.

Detecting whether an Add-In is installed on the Client

If you have installed the Server pieces of the Virtual Earth Integration (look here), but have a Client without the VEControl Add-In, this is how the FactBox will look:

image

Not very informative when you were expecting this:

image

But as you might know, we actually didn’t write any code to plugin the control and the Control Add-In error above is handled by the Client without actually notifying the Service Tier that anything is missing.

What we need to do, is to create one line of code in the INIT trigger of all pages, which uses an Add-In:

ComponentHelper.CheckAddInNameKey(‘FreddyK.LargeVEControl’,’1c9f7ad47dba024b’);

and then of course create a function that actually checks that the Add-In is there and does a FILE.DOWNLOAD(addininstaller) if it isn’t.

Problem here is that we need a COM component in order to check the existence of an Add-In, and this COM component will have to run Client side (how else could it inspect the Add-ins folder – doh).

The INIT trigger is executed before anything is sent off to the Client and thus we can install the component and continue opening the page after we have done that. BTW the FILE.DOWNLOAD is NOT going to wait until the user actually finishes the setup program, so we will have to bring up a modal dialog telling the user to confirm that he has completed the setup.

BTW as you probably have figured out by now, the above line requires a registration of Add-Ins like:

ComponentHelper.RegisterAddIn(‘FreddyK.LargeVEControl’,’1c9f7ad47dba024b’,’NAV Large Virtual Earth Control’, ‘NavVEControl.msi’);

In order to specify what file to download. Now I could have added this to the Check function to avoid a table – but I actually don’t think it belongs there.

The ComponentHelper

So, what I have done is to collect some functionality that I find I use all the time in various samples in a Component called the ComponentHelper.

The functions are:

  1. Installation of Client side COM components (used by the majority of samples)
  2. Installation of Client side Add-Ins (used by all samples with Add-ins)
  3. Ability to Escape and Unescape strings (the method Web Services uses for encoding of company name – used in the Virtual Earth Integration)
  4. Ability to register a codeunit or page as Web Service from code (used by all samples using Web Services)
  5. Global information about the URL to my IIS and Web Service tier (used in Edit In Excel and Virtual Earth Integration)
  6. Modify metadata programmatically (all samples)

In fact I am hoping that these basic pieces of functionality will find their way into the base product in the future, where they IMO belong.

Installation of Client side COM components

Every time you use a self built COM component (in this case the NAVAddInHelper), which you want to auto-deploy, you should create a function like this:

LoadAddInHelper(VAR NAVAddInHelper : Automation “‘NAVAddInHelper’.NAVAddInHelper”) Ok : Boolean
Ok := FALSE;
WHILE NOT CREATE(NAVAddInHelper,TRUE,TRUE) DO
BEGIN
IF NOT AskAndInstallCOMComponent(‘NAV AddIn Helper’, ‘NAVAddInHelper.msi’) THEN
EXIT;
END;
Ok := TRUE;

and always invoke this when you want to create an instance of the Component (instead of having CREATE(NAVAddInHelper,TRUE,TRUE) scattered around the code.

AskAndInstallCOMComponent(Description : Text[80];InstallableName : Text[80]) Retry : Boolean
Retry := FALSE;
IF CONFIRM(STRSUBSTNO(TXT_InstallCOMComponent, Description)) THEN
BEGIN
Retry := InstallComponent(InstallableName);
END;

InstallComponent(InstallableName : Text[80]) Retry : Boolean
Retry := TRUE;
toFile := InstallableName;
fromFile := APPLICATIONPATH + ‘ClientSetup’+InstallableName;
IF NOT FILE.EXISTS(fromFile) THEN
BEGIN
fromFile := APPLICATIONPATH + ‘..ClientSetup’+InstallableName;
END;
IF FILE.DOWNLOAD(fromFile, InstallableName, ”, ”, toFile) THEN
BEGIN
Retry := CONFIRM(TXT_PleaseConfirmComplete);
END;

as you can see from the code, the function will try to create the component until it succeeds or the user says No, I do not want to install the component. At this time I would like to mention a small bug in NAV 2009 SP1 – when you try to CREATE a COM component client side and it isn’t there, the Client will still ask you whether or not you want to run a client side component, but since the Control isn’t installed – it doesn’t know what to call it, meaning that you will get:

image

Now it is OK for the user to cancel this window because he doesn’t know what it is, but if he says Never Allow (silly choice to give the user:-)), he will have to delete personalization settings for automation objects to get this working again.

image

BTW If the user declines running a COM component – our code will see this as the component is not installed and ask him to install it.

Installation of Client side Add-Ins

To check whether an Add-in is installed, we first check whether it is registered in the Client’s add-in table.

CheckAddInNameKey(AddInName : Text[220];PublicKeyToken : Text[20]) Found : Boolean
Found := FALSE;
IF NOT AddIn.GET(AddInName,PublicKeyToken) THEN
BEGIN
MESSAGE(STRSUBSTNO(TXT_AddInNotRegisterd, AddInName, PublicKeyToken));
EXIT;
END;
Found := CheckAddIn(AddIn.”Control Add-in Name”, AddIn.”Public Key Token”, AddIn.Description);

Without anything here – nothing works. After this we check our own table (in which we have information about what executable to download to the client)

CheckAddIn(AddInName : Text[220];PublicKeyToken : Text[20];Description : Text[250]) Found : Boolean
IF Description = ” THEN
BEGIN
Description := AddInName;
END;
Found := FALSE;
IF LoadAddInHelper(NAVAddInHelper) THEN
BEGIN
WHILE NOT NAVAddInHelper.CheckAddIn(AddInName, PublicKeyToken) DO
BEGIN
IF NOT InstallableAddIn.GET(AddInName, PublicKeyToken) THEN
BEGIN
IF NOT CONFIRM(STRSUBSTNO(TXT_AddInNotFound, Description)) THEN
BEGIN
EXIT(FALSE);
END;
END
ELSE
EXIT(AskAndInstallAddIn(Description, InstallableAddIn.InstallableName));
END;
Found := TRUE;
END;

and last but not least – the method that installs the Add-In

AskAndInstallAddIn(Description : Text[80];InstallableName : Text[80]) Retry : Boolean
Retry := FALSE;
IF CONFIRM(STRSUBSTNO(TXT_InstallAddIn, Description)) THEN
BEGIN
Retry := InstallComponent(InstallableName);
END;

BTW, the method to register Add-Ins to this subsystem is

RegisterAddIn(“Control Name” : Text[220];”Public Key Token” : Text[20];Description : Text[128];InstallableName : Text[80])
IF NOT AddIn.GET(“Control Name”, “Public Key Token”) THEN
BEGIN
AddIn.INIT();
AddIn.”Control Add-in Name” := “Control Name”;
AddIn.”Public Key Token” := “Public Key Token”;
AddIn.Description := Description;
AddIn.INSERT(TRUE);
END;
IF NOT InstallableAddIn.GET(“Control Name”, “Public Key Token”) THEN
BEGIN
InstallableAddIn.INIT();
InstallableAddIn.”Control Add-in Name” := “Control Name”;
InstallableAddIn.”Public Key Token” := “Public Key Token”;
InstallableAddIn.InstallableName := InstallableName;
InstallableAddIn.INSERT(TRUE);
END;

As you can see I could have extended the AddIn table – but I decided to go for adding a table instead, it doesn’t really matter.

Ability to Escape and Unescape strings

In the Virtual Earth sample, I need to construct a URL, which contains the company name from NAV. Now with NAV 2009SP1 we use standard Escape and Unescape of strings in the URL, so I have added functions to ComponentHelper to do this. In fact, they just call a function in the C# COM component, which contains these functions.

Ability to register a codeunit or page as Web Service from code

Instead of having to ask partners and/or users to register web services in the Web Service table or form, I have created this small function in the ComponentHelper to do this.

RegisterWebService(isPage : Boolean;”Object ID” : Integer;”Service Name” : Text[80];Published : Boolean)
IF isPage THEN
BEGIN
ObjType := WebService.”Object Type”::Page;
END ELSE
BEGIN
ObjType := WebService.”Object Type”::Codeunit;
END;

IF NOT WebService.GET(ObjType, “Service Name”) THEN
BEGIN
WebService.INIT();
WebService.”Object Type” := ObjType;
WebService.”Object ID” := “Object ID”;
WebService.”Service Name” := “Service Name”;
WebService.Published := Published;
WebService.INSERT();
COMMIT;
END ELSE
BEGIN
IF (WebService.”Object ID” <> “Object ID”) OR (WebService.Published<>Published)  THEN
BEGIN
WebService.”Object ID” := “Object ID”;
WebService.Published := Published;
WebService.MODIFY();
COMMIT;
END;
END;

Global information about the URL to my IIS and Web Service tier

Again – a number of the samples I create will integrate from the RoleTailored Client to an application or a web site, which then again uses Web Services. I found out, that I needed a central way to find the URL of the right Web Service listener and the best way was to create a table in which I store the base URL (which would be ://WS/”>://WS/”>http://<server>:<port>/<instance>/WS/ (default http://localhost:7047/DynamicsNAV/WS/).

Also in the Virtual Earth I spawn up a browser (with HYPERLINK) and I need a location for the intranet server on which an application like the MAP would reside.

Modify Metadata programmatically

I found that all my samples worked fine in the W1 version of NAV 2009 SP1, but as soon as I started to install them on other localized version, the pages on which I added actions etc. had been modified by local functionality and since there is no auto merge of pages, people would have to merge page metadata or find themselves loosing local functionality when they installed my samples.

I have added 4 functions:

GetPageMetadata(Id : Integer;VAR Metadata : BigText)

SetPageMetadata(Id : Integer;Metadata : BigText)

AddToMetadata(Id : Integer;VAR Metadata : BigText;Before : Text[80];Identifier : Text[80];Properties : Text[800]) result : Boolean

AddToPage(Id : Integer;VersionList : Text[30];Before : Text[80];Identifier : Text[80];Properties : Text[800]

where the last function just call the three other (Get, Add, Set metadata).

I am not very proud of the way these functions are made – they just search for a line in the exported text file and inserts some metadata but they meet the needs.

As an example on how these functions are used you will find:

// Read Page Metadata
ComponentHelper.GetPageMetadata(PAGE::”Customer Card”, Metadata);

// Add Map Factbox
ComponentHelper.AddToMetadata(PAGE::”Customer Card”, Metadata, ‘    { 1900383207;1;Part   ;’,
‘    { 66031;1  ;Part      ;’,
‘ SubFormLink=No.=FIELD(No.); PagePartID=Page66030 }’)
OR

// Add View Area Map Action
ComponentHelper.AddToMetadata(PAGE::”Customer Card”, Metadata, ‘      { 82      ;1   ;ActionGroup;’, ‘      { 66030   ;2   ;Action    ;’,
‘ CaptionML=[ENU=View Area Map]; OnAction=VAR MAP : Codeunit 66032; BEGIN MAP.OpenCustomerMAPInBrowser(Rec); END; }’);

// Write Page Metadata back
ComponentHelper.SetPageMetadata(PAGE::”Customer Card”, Metadata);

So basically – it reads the metadata for the page, checks whether the action already has been added (the string ‘      { 66030   ;2   ;Action    ;’ exists already). If not it searches for the string ‘      { 82      ;1   ;ActionGroup;’ and inserts the action below that. Not pretty – but it works.

The Visual Studio piece

As mentioned earlier a couple of functions are needed in a client side COM component.

The Escape and Unescape functions really doesn’t do anything:

public string EscapeDataString(string str)
{
return Uri.EscapeDataString(str);
}

public string UnescapeDataString(string str)
{
return Uri.UnescapeDataString(str);
}

and the essence of the CheckAddIn is the code found in the LoadAddIn function of the AddIn class:

Assembly assembly = Assembly.LoadFrom(dll);

this.publicKey = “”;
foreach (byte b in assembly.GetName().GetPublicKeyToken())
{
this.publicKey += string.Format(“{0:x2}”, b);
}

Type[] types = assembly.GetTypes();
foreach (Type type in types)
{
foreach (System.Attribute att in System.Attribute.GetCustomAttributes(type))
{
ControlAddInExportAttribute expAtt = att as ControlAddInExportAttribute;
if (expAtt != null && !string.IsNullOrEmpty(expAtt.Name))
{
if (!isAddIn)
{
this.controlNames = new List<string>();
isAddIn = true;
}
this.controlNames.Add(expAtt.Name);
}
}
}

Which loads an Add-In, finds the public key token and the registered controls. The rest is really simple – check whether one of the Add-Ins in fact is the one we are looking for – else install it…

The Visual Studio solution also contains a setup project for generating the .msi file which needs to be placed in the ClientSetup folder.

Putting it all together

So, now we have a .fob file and an .msi file which we need to install on the Service Tier – so why don’t we create a Setup project, which contains this .fob (install that in a ServerSetup folder) and the .msi (install that in the ClientSetup folder).

Doing this makes installing the ComponentHelper a 3 step process:

  1. Install ComponentHelper.msi on the Service Tier
  2. Import a .fob from the ServerSetup folder
  3. Run a codeunit which registers the necessary stuff

In fact I am trying to make all the demos and samples installable like the ComponentHelper itself – so that anybody can download cool samples and get a sexy Microsoft Dynamics NAV 2009 SP1 – to work with.

ComponentHelper1.01.zip (which contains ComponentHelper1.01.msi) can be downloaded here.

If you don’t fancy downloading the .msi (for whatever reason) – the source to NAVAddHelper can be downloaded here and the ComponentHelper objects can be downloaded here.

 

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel R2 – Part 1 (out of 2)

Posted on by
Reply

This post assumes that you have read the 4 step walkthrough of how to build the Edit In Excel demo from November 2008. You can find the parts here: Part 1, Part 2, Part 3, Part 4 and the Bug Fix.

In this post I will talk about what is needed in order to be able to save an Excel spreadsheet on a local disc, edit it offline and then submit your changes later.

My first assumption was that this was just a few small changes, but I should learn more…

Goal

The success scenario is the one where a NAV user decides to take the customer table offline in Excel to do modifications. The Excel spreadsheet is saved to a local hard drive and edited while not connected to the network.

When the user reconnects to the network, he can submit his changes, which will be send to NAV through Web Services and validated according to business rules.

The location of NAVTemplate.vsto

Let’s just try to save the spreadsheet in our Documents folder and see what happens if we open the spreadsheet. Not surprisingly we get an error telling us that it was unable to locate NAVTemplate.vsto

image

An immediate solution is to save the spreadsheet next to the .vsto and then it seems to work better, but the spreadsheet is not attached to NAV anymore, we cannot save changes and reload crashes with an exception.

The .vsto file is our deployment manifest, which is our managed code extension to the spreadsheet and of course this is needed in order for the spreadsheet to work.

You can read more about the architecture of Document-Level Customizations here:

http://msdn.microsoft.com/en-us/library/zcfbd2sk.aspx

Looking at the ServerDocument interface,which we use in the NAVEditInExcel solution, it has a property called DeploymentManifestUri, which is the location of the .vsto file. Adding the following line to the NAVEditInExcel project

serverDoc.DeploymentManifestUrl = new Uri(System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), “NAVTemplate.vsto”));

will cause the document to be created with an absolute reference to the NAVTemplate.vsto, and this will solve the problem with the spreadsheet not being able to locate the .vsto. In fact if this is a network location, it should even be possible to send the Excel spreadsheet to somebody else who should be able to modify the document and post the changes.

When doing this I decided to make another change in the NAVEditInExcel project. As you know it creates a temporary template in the same location as the DLL and the VSTO – and then it deletes it again. This is really not the preferred location of temporary files – we should create temporary files in the Temp folder so we change the line setting the template name to:

string template = System.IO.Path.Combine(System.IO.Path.GetTempPath(), page + “.xltx”);

since we don’t need to have the .xltx next to the .vsto anymore.

What do we need to save?

Members of the Sheet1 class marked with [Cached] will be saved together with the document – we know that from the way we transfer the page name and the current view to the spreadsheet. Thinking about it, the only things we need to save together with the document is the dataTable (which at any time contains the changes made by the user) and the objects collection (which are the objects returned from the Web Service).

The DataTable class implements IXmlSerializable and the objects collection is (as we know) an array of objects returned from a web Service provider and since these objects where send over the wire from a Web Service they of course also implements IXmlSerializable.

The fields collection cannot be saved, since the NAVFieldInfo class uses the PropertyInfo class, which cannot be serialized. The Service connection of course cannot be serialized either – nor can the datalist class – as the matter of fact, the datalist class shouldn’t be a member at all, it should be moved to the AddDataToExcel method as a local variable.

Problem now is, that if we just mark the dataTable and objects members with [Cached] we need to initialize them in the NAVEditInExcel project and there is no way we can instantiate the objects collection at that time with the right type.

A little more on cached data objects in Office documents

http://msdn.microsoft.com/en-us/library/ms178808(VS.80).aspx

and then how to programmatically add members to the cached objects

http://msdn.microsoft.com/en-us/library/48b7eyf3(VS.80).aspx

using this knowledge gives us the following two lines, we want to add after we have instantiated the dataTable and the objects array.

// Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);

Now we just need to determine that the spreadsheet was loaded from a file (and not started from NAV) and then act differently.

Refactoring some code

We need to do a little refactoring of code in order to make things work. In the existing solution the PopulateFieldsCollection method creates the fields collection, but it also creates an empty dataTable class. Since we now store the dataTable class and not the fields collection we need as the first thing in the new spreadsheet to create the fields collection (a lot of things is depending on this). This is the new PopulateFieldsCollection (and AddField):

/// <summary>
/// Populate Fields Collection with NAVFieldInfo for all properties in the record
/// Should works with any NAV 2009 Page exposed as WebService
/// </summary>
/// <param name=”objType”>Type of Object (typeof(Customer), typeof(Vendor), …)</param>
/// <param name=”fieldsType”>Type of the Enum holding the property names</param>
private void PopulateFieldsCollection(Type objType, Type fieldsType)
{
this.fields = new List<NAVFieldInfo>();

    // Key property is not part of the Enum
// Add it manually as the first field
AddField(“Key”, objType);

    // Run through the enum and add all fields
foreach (string field in Enum.GetNames(fieldsType))
{
AddField(field, objType);
}
}

/// <summary>
/// Create a NAVFieldInfo for a field
/// </summary>
/// <param name=”field”>Field name</param>
/// <param name=”objType”>Type of Object in which the field is (typeof(Customer), typeof(Vendor), …)</param>
private void AddField(string field, Type objType)
{
field = NAVFilterHelper.VSName(field);
PropertyInfo pi = objType.GetProperty(field, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
if (pi != null)
{
NAVFieldInfo nfi = NAVFieldInfo.CreateNAVFieldInfo(field, pi, objType.Namespace);
if (nfi != null)
{
// If we encounter unknown Field Types, they are just ignored
this.fields.Add(nfi);
}
}
}

The Load function in the old spreadsheet does a number of things. It populates the Fields collection, loads the data, populates the dataTable and add the dataTable to the spreadsheet.

We will remove and create one function called LoadDataTable, which will create a dataTable (based on the fields collection), load the data from Web Services and populate the dataTable.

/// <summary>
/// Load Records from NAV via Web Services
/// </summary>
private void LoadDataTable()
{
// Create Data Table object based on fields collection
this.dataTable = new DataTable(this.page);
foreach (NAVFieldInfo nfi in this.fields)
{
this.dataTable.Columns.Add(new DataColumn(nfi.field, nfi.fieldType));
}

    SetFilters(this.view);
this.objects = this.service.ReadMultiple();
// Populate dataTable with data
foreach (object obj in this.objects)
{
DataRow dataRow = this.dataTable.NewRow();
foreach (NAVFieldInfo nfi in this.fields)
{
dataRow[nfi.field] = nfi.GetValue(obj);
}
this.dataTable.Rows.Add(dataRow);
}
this.dataTable.AcceptChanges();
}

As you can see, this is pieces of the PopulateFieldsCollection, Load and PopulateDataTable functions – as the matter of fact, you can delete the PopulateDataTable function as well. BTW the AcceptChanges call was moved from AddDataToExcel it needs to be together with the code populating the dataTable.

Right now my startup code in my spreadsheet has changed to

if (this.service != null)
{
this.service.UseDefaultCredentials = true;

    // Create Fields collection
this.PopulateFieldsCollection(this.service.GetObjectType(), this.service.GetFieldsType());

    Application.ScreenUpdating = false;

    bool loadData = this.ListObjects.Count == 0;
if (!loadData)
{
MessageBox.Show(“Spreadsheet loaded from disc”);
}
if (loadData)
{
// Load Data into dataTable from Web Services
this.LoadDataTable();
// Add dataTable to Excel Spreadsheet
this.AddDataTableToExcel();

        // Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);
}

    Application.ScreenUpdating = true;
}

from just invoking Load() before.

So we populate the fields collection and then we check whether or not there is a ListObject in the document (remember the Controls collection was empty). If this is the case we must do something (for now we just display a messagebox).

If we are called from NAV (loadData becomes true) we will load the data and call AddDataTableToExcel (renamed from AddDataToExcel) and that should work.

If we try to compile now, we will see that the Reload() method uses Load() as well. We need to change Reload to

/// <summary>
/// Reload data from NAV (delete old dataTable, and load new data)
/// </summary>
internal void Reload()
{
Application.ScreenUpdating = false;

    // Remove List Object
if (this.dataTable != null)
this.Controls.RemoveAt(0);
else
this.ListObjects[1].Delete();

    // Load Data into dataTable from Web Services
this.LoadDataTable();
// Add dataTable to Excel Spreadsheet
this.AddDataTableToExcel();

    // If this reload was in fact a reattach of the spreadsheet, start caching dataTable and objects again
if (!this.IsCached(“dataTable”))
{
// Add dataTable and objects to the Caching collection
this.StartCaching(“dataTable”);
this.StartCaching(“objects”);
}

    Application.ScreenUpdating = true;
}

Note that we remove the old listobject in two different ways based on whether or not dataTable is set. dataTable is null if the spreadsheet has been detached from NAV – I will touch more upon that later. This is also the reason why we restart Caching the dataTable and objects if in fact this was a reattach.

The solution should work as before now – the only major difference is, that if you save the spreadsheet on the disc and try to load it again it should not give an exception telling you, that you cannot overlap a table with another table, instead it should give you something like:

image

This is of course not the final solution, but it shows us that we are on the right track.

Restoring the “State” of the spreadsheet

To make a long story short, the lines we need in order to restore the managed list object are:

// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Create a new VSTO ListObject – data bound
this.AddDataTableToExcel();

Meaning that we remove the unmanaged ListObject and we add the managed ListObject to Excel, seems pretty easy. But what if the document is saved on disc and you add a field to the Customer Page (+ update your web reference and recompile your NAVTemplate) then the managed extension assembly doesn’t match the saved spreadsheet anymore and the above logic wouldn’t work.

In many cases we could just say that we don’t care – but given the ability to save spreadsheets that are connected to Web Services and reload data adds another dimension to the entire Excel thing. You can have spreadsheets that contain a lot of other things than your dataTable and you might not be pleased with the fact that you loose the NAV Web Service connection if this happens.

I decided to build in a way of determining this and give the user a couple of options:

// If the spreadsheet was detached already – just ignore
if (this.IsCached(“dataTable”))
{
// We have loaded a saved spreadsheet with data
// Check that the VSTO assembly (fields) matches the spreadsheet
bool fieldsOK = this.dataTable.Columns.Count == this.fields.Count;
if (fieldsOK)
{
for (int i = 0; i < this.fields.Count; i++)
if (this.dataTable.Columns[i].Caption != this.fields[i].field)
fieldsOK = false;
}
if (!fieldsOK)
{
// Schema mismatch – cannot link back to NAV
switch (MessageBox.Show(“Customer Card definition has changed since this spreadsheet was save. Do you want to re-establish link, reload data and loose changes?”, “Error”, MessageBoxButtons.YesNoCancel))
{
case DialogResult.Cancel:
// Quit
Application.Quit();
break;
case DialogResult.Yes:
// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Signal reload data and reestablish link
loadData = true;
                this.StopCaching(“dataTable”);
this.StopCaching(“objects”);
                break;
case DialogResult.No:
// Detach spreadsheet from NAV
              this.dataTable = null;
              this.objects = null;
              this.StopCaching(“dataTable”);
              this.StopCaching(“objects”);
                break;
}

    }
else
{
// Remove Non-VSTO List Object
this.ListObjects[1].Delete();
// Create a new VSTO ListObject – data bound
this.AddDataTableToExcel();
}
}

3 options – Cancel quits the spreadsheet open command and no harm has been done, Yes removes the old table and sets the loadData to true (meaning that the spreadsheet will reload data as if it was opened from NAV) and No will detatch the spreadsheet from NAV (setting the dataTable to null and stop caching dataTable and objects). Note that if you ever press Reload it will re-attach the spreadsheet and load data from NAV again (the reason for checking whether the dataTable was null in Reload).

Yes or No will of course not touch the original spreadsheet and you can always save in a new name.

BTW – we need one simple check in Save() as well

/// <summary>
/// Save Changes to NAV via Web Service
/// </summary>
internal void Save()
{
if (this.dataTable == null)
{
MessageBox.Show(“Spreadsheet was detached from NAV, cannot perform Save!”);
}
else if (DoSave())
{
Reload();
}
}

The only extra tihing you need is to add the following line right before the return statement in DoSave():

this.dataTable.AcceptChanges();

Without this line you cannot edit the data, save changes to NAV, edit more data and then save locally.

Done

We are done – we now have a template you can use for modifying NAV data. You can save the spreadsheet locally and modify it while you are offline and later, when you come back online you can push your changes to NAV and this is when validation happens.

As usual you can download the project here http://www.freddy.dk/NAVTemplateR2.zip – there are no changes to what’s needed in NAV, so you should use the objects from the original Edit In Excel walkthrough inside C/AL.

BTW. If you decide to use the source, you probably need to right-click the Web References and invoke Update Web Reference to make sure that the Web Reference matches your Customer, Item and Vendor Card Pages.

Part 2?

So why is this a part 1 out of  2 you might ask yourself right now?

Reason is that I want to create a better conflict resolution in the spreadsheet. With the solution we have build now, any change to a record by anybody will cause you to loose your changes in Excel. Wouldn’t it be nice if we were able to determine that we only changed the location code in Excel, so just because somebody changed the credit limit on a couple of customers, we should still be able to re-apply our changes without having to type them in again.

This is what R2 step 2 is all about – a better conflict resolution mechanism.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

NAV 2009 and Unicode!

Posted on by
Reply

The title might be a bit misleading, but I am writing this post as a response to a problem, which a partner ran into with NAV 2009 – and the problem is caused by Unicode. I am not a Unicode expert, so bare with me if I am naming some things wrong.

As you know, NAV 2009 is a 3T architecture and the Service Tier is 95% managed code (only the lower pieces of the data stack is still unmanaged code). You probably also know, that managed code supports Unicode natively – in fact a string in C# or VB.Net is by default a Unicode string.

In C# you use byte[] if you need to work with binary data. My earlier post about transferring binary data between the Service Tier and Client Side COM or Web Service consumers (you can find it here) I read the file content into a byte[] and use a base64 encoding to encode this content into a string, which is transferable between platforms, code pages etc.

Is NAV 2009 then Unicode compliant?

No, we cannot claim that. There are a lot of things, that stops us from having a fully Unicode compliant product – but we are heading in that direction. Some of the things are:

  • The development environment and the classic client is not Unicode. When you write strings in AL code they are in OEM.
  • The import/export formats are OEM format.
  • I actually don’t know what the format inside SQL is, but I assume it isn’t Unicode (again because we have mixed platforms).

Lets take an example:

In a codeunit I write the following line:

txt := ‘ÆØÅ are three Danish letters, ß is used in German and Greek.’;

Now, I export this as a .txt file, and view this in my favorite DOS text viewer:

image

As you can see – the Ø has changed and a quick look at my codepage reveals that I am running 437 – the codepage that doesn’t have an Ø.

Opening the exported file in Notepad looks different:

Txt := ‘’î are three Danish letters, á is used in German and Greek.’;

Primary reason for this is, that Notepad assumes Unicode and the .txt file is OEM. When I launch the RoleTailored Client, and take a look at that line in the generated C# source file in Notepad:

txt = new NavText(1024, @”ÆØÅ are three Danish letters, ß is used in German and Greek.”);

Nice – and we know that Notepad is Unicode compliant and C# uses Unicode strings, so the AL -> C# compiler converts the string to Unicode – how does this look in my favorite DOS text viewer:

image

Clearly my string has been encoded.

But wait…

If NAV 2009 converts my text constants to Unicode – what if I write this string to a file using my well known file commands in AL? – well, let’s try.

I added the following code to a Codeunit and ran it from both the Role Tailored Client and from the Classic Client.

file.CREATETEMPFILE();
filename := file.NAME;
file.CLOSE();
file.CREATE(filename);
file.CREATEOUTSTREAM(OutStr);
OutStr.WRITETEXT(Txt);
file.CLOSE();

and don’t worry, the results are identical – both platforms actually writes the file in OEM format (we might have preferred Unicode, but for compatibility reasons this is probably a good thing).

Another thing we should try, is to call a managed COM object – and take a look at the string we get there – and again we get the same string from the Classic and from the Role Tailored Client – but in this case, we do NOT get the string in OEM format – we get a Unicode string.

MSXML

Now if we call an unmanaged COM object (like MSXML2 – XMLHTTP) we actually get the OEM string when invoked from the Classic Client and a Unicode string when invoked from the Role Tailored Client. Typically XMLHTTP is used with ASCII only – but in some cases, they do have binary data – which might be in the 128-255 character range.

Our problem now is, that our binary data (which didn’t have any relation to any special characters) gets converted to Unicode – and the Web Service provider doesn’t stand a chance to guess what we mean with the data we send.

The next problem is that the Role Tailored Client doesn’t support a byte[] type (binary) – in which we could have build a command and send it over. I tried a number of things, but didn’t find a way to send any binary data (above 128) to the Send command of XMLHTTP.

The third problem with XMLHTTP is that the only way we can get a result back is by reading the ResponseText – and that is treated as a Unicode string and gets crumbled before we get it into NAV.

Remember that these problems will not occur if the web service provider uses XML to transfer data back and forth – since XML is ASCII compliant.

My first proposal if you are having problems with a Web Service provider, using a binary communication is to query the provider and ask for an XML version of the gateway. If this isn’t possible – you have a couple of options (which both include writing a new COM object).

Create a proxy

You could create a Web Service proxy as a COM component (probably server side) and have a higher level function you call. This would remove the XMLHTTP glue code from NAV and put that into the COM object.

Example – you have a Web Service provider who can verify credit card numbers – and normally you would build up a string in AL and send this to the Send command – and then parse the ResponseStream you get back to figure out whether everything was A OK for not.

Create a function in a new COM object which might be named:

int CheckCreditCard(string CreditCardNo, string NameOnCard, int ExpireMonth, int ExpireYear, int SecurityCode)

Then your business logic in AL would just call and check – without a lot of XML parsing and stuff.

This would be my preferred choice – but it does involve some refactoring and a new COM object that needs to be installed on the server.

Use temporary files to transfer data

As mentioned earlier – writing a file in NAV 2009 with binary data, creates a file in OEM format – which would be the same binary content as we are typing in the AL editor.

So, you could create the string you want to send to XMLHTTP in a temporary file, create a new COM object which contains a function which sends the content of a file to a XMLHTTP object and writes the response back into the same file for NAV to read.

The idea here is that files (and byte[]) are binary data – strings are not.

The function in the COM object could look like:

public int SendFileContent(object NAVxmlhttp, string filename)
{
MSXML2.ServerXMLHTTP xmlHttp = NAVxmlhttp as MSXML2.ServerXMLHTTP;
FileStream fs = File.OpenRead(filename);
BinaryReader br = new BinaryReader(fs);
int len = (int)fs.Length;
byte[] buffer = new byte[len];
br.Read(buffer, 0, len);
br.Close();
fs.Close();

    xmlHttp.send(buffer);

    buffer = xmlHttp.responseBody as byte[];
fs = File.Create(filename);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(buffer);
bw.Close();
fs.Close();

    return xmlHttp.status;
}

If you went for this approach your AL code would change from:

XmlHttp.Send(Txt);

(followed by opening the ResponseStream) to

file.CREATETEMPFILE();
filename := file.NAME;
file.CLOSE();
file.CREATE(filename);
file.CREATEOUTSTREAM(OutStr);
OutStr.WRITETEXT(Txt);
file.CLOSE();

myCOMobject.SendFileContent(XmlHttp, filename);

(followed by opening the file <filename> again – reading the result).

The second approach would also work from the Classic client – so you don’t have to use IF ISSERVICETIER THEN to do the one of the other.

In the Edit In Excel – Part 4, you can see how to create a COM object – should be pretty straightforward.

Build a binary string that doesn’t get encoded

You could create a function in a COM object, which returns a character based on a numeric value:

public string GetChar(int ch)
{
return “”+(char)ch;
}

Problem with this direction is, that this function should ONLY be used when running in the Role Tailored Client.

Calling this function from the Classic Client will case this string to be seen as Unicode and converted back into OEM – and that really wouldn’t make sense at all.

Convert to/from Unicode using strings instead of files

So what if the data you need to send is confidential and you cannot write that to a file?

Well – you can create a function, which converts the string back to OEM (making it the same binary image) – send it over the wire – and then convert the response to UniCode (so that when the string comes into NAV – it will be converted back to OEM again again).

Seems like a lot of conversion back and forth – but it would actually work from both the Classic and the Role Tailored Client, the code for that goes here:

public class MyCOMobject : IMyCOMobject
{
private static Byte[] oem2AnsiTable;
private static Byte[] ansi2OemTable;

    /// <summary>
/// Initialize COM object
/// </summary>
public MyCOMobject()
{
oem2AnsiTable = new Byte[256];
ansi2OemTable = new Byte[256];
for (Int32 i = 0; i < 256; i++)
{
oem2AnsiTable[i] = (Byte)i;
ansi2OemTable[i] = (Byte)i;
}
NativeMethods.OemToCharBuff(oem2AnsiTable, oem2AnsiTable, oem2AnsiTable.Length);
NativeMethods.CharToOemBuff(ansi2OemTable, ansi2OemTable, ansi2OemTable.Length);
// Remove “holes” in the convertion structure
Int32 ch1 = 255;
Int32 ch2 = 255;
for (;; ch1–, ch2–)
{
while (ansi2OemTable[oem2AnsiTable[ch1]] == ch1)
{
if (ch1 == 0)
break;
else
ch1–;
}
while (oem2AnsiTable[ansi2OemTable[ch2]] == ch2)
{
if (ch2 == 0)
break;
else
ch2–;
}
if (ch1 == 0)
break;
oem2AnsiTable[ch1] = (Byte)ch2;
ansi2OemTable[ch2] = (Byte)ch1;
}
}

    /// <summary>
/// Convert Unicode string to OEM string
/// </summary>
/// <param name=”str”>Unicode string</param>
/// <returns>OEM string</returns>
private byte[] UnicodeToOem(string str)
{
Byte[] buffer = Encoding.Default.GetBytes(str);
for (Int32 i = 0; i < buffer.Length; i++)
{
buffer[i] = ansi2OemTable[buffer[i]];
}
return buffer;
}

    /// <summary>
/// Convert OEM string to Unicode string
/// </summary>
/// <param name=”oem”>OEM string</param>
/// <returns>Unicode string</returns>
private string OemToUnicode(byte[] oem)
{
for (Int32 i = 0; i < oem.Length; i++)
{
oem[i] = oem2AnsiTable[oem[i]];
}
return Encoding.Default.GetString(oem);
}

    /// <summary>
/// Send data through XMLHTTP
/// </summary>
/// <param name=”NAVxmlhttp”>XmlHttp object</param>
/// <param name=”data”>string containing data (in Unicode)</param>
/// <returns>The response from the XMLHTTP Send</returns>
public string Send(object NAVxmlhttp, string data)
{
MSXML2.ServerXMLHTTP xmlHttp = NAVxmlhttp as MSXML2.ServerXMLHTTP;
byte[] oem = UnicodeToOem(data);
xmlHttp.send(oem);
        return OemToUnicode((byte[])xmlHttp.responseBody);
}
}

internal static partial class NativeMethods
{
#region Windows OemToChar/CharToOem imports

    [DllImport(“user32”, EntryPoint = “OemToCharBuffA”)]
internal static extern Int32 OemToCharBuff(Byte[] source, Byte[] dest, Int32 bytesize);

    [DllImport(“user32”, EntryPoint = “CharToOemBuffA”)]
internal static extern Int32 CharToOemBuff(Byte[] source, Byte[] dest, Int32 bytesize);

    #endregion
}

Unfortunately I have not found a way to do this without having a COM object in play.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 4 (out of 4)

Posted on by
Reply

If you haven’t read part 3, part 2 (and part 1), you should do so before continuing here.

We have seen how to put code inside Excel, using VSTO and connect to NAV 2009 Web Services. We have seen how to add this to a table inside Excel and how to write data back to NAV through Web Services. We can delete, add and modify records in Excel and we can even do so with both Customers, Vendors and Items. We have added support for NAV filters, error handling and the only thing we are missing to have a very good experience is integrating the whole thing into NAV.

So we better do that now!

Disclaimer

Most of the people reading this are probably superior AL coders compared to me. I really only started coding in AL last year and I am still struggling to understand how things works. So, if I do something stupid in AL – just tell me in a comment how to do it smarter – thanks.

Actions in NAV 2009

What we want to do is

image

Add an action to the menu, which launches Excel, reads the same data as we have in the list place and allows the user to modify that data.

But we need to start in a different area – we need a COM object, which our action can invoke.

Creating a COM object

First of all we will create a COM object, which contains one function.

public void EditInExcel(string page, string view)

I do think there are a number of tutorials that explains how to do this, so I will run over the steps very quickly.

  1. In the same solution as the NAVTemplate, create a new project – type Class Library and name the project NAVEditInExcel
  2. Rename class1.cs to NAVEditInExcel.cs – and say Yes to the question whether you want to rename the class as well.
  3. Select Properties on the project (not the solution)
    1. On the Build tab, set the output path to ..NAVTemplatebinDebug in order to share the output path the the Excel Spreadsheet
    2. On the Build events tab, we need to register the COM object to make it visible to NAV. Add the following Post Build Event: C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm NAVEditInExcel.dll /codebase /tlb
    3. On the Signing tab, check the Sign the Assembly checkbox and select New key in the combo box, name the key and protect it with a password if you fancy.
  4. Open the AssemblyInfo.cs (under Properties in the Solution Explorer)
    1. Add using system; to the using statements
    2. Add [assembly: CLSCompliant(true)] under the line with [assembly: ComVisible(false)].
  5. Open the source for the NavEditInExcel.cs
    1. Add using System.Runtime.InteropServices; to the using statements
    2. Create an Interface and change the class to be:

[ComVisible(true)]
[Guid(“A2C51FC8-671E-4135-AD27-48EDC491E76E”), InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface INAVEditInExcel
{
void EditInExcel(string page, string view);
}

[ComVisible(true)]
[Guid(“233E0C7F-2276-4142-929C-D6BA8725D7B4”), ClassInterface(ClassInterfaceType.None)]
public class NAVEditInExcel : INAVEditInExcel
{
public void EditInExcel(string page, string view)
{

        // Code goes here…
    }
}

Now you should be able to build the COM object and see it inside NAV when adding a variable of type automation.

Adding the Action in NAV

Open up the Classic Client and design the Customer List Place.

Insert an Action on the Customer List Place called Edit In Excel and edit the code for that (btw. the Image Name for the Excel icon is Excel)

In the code for that Action – create a local variable called NAVEditInExcel of type Automation and select the NAVEditInExcel.NAVEditInExcel COM object to use and add the following code:

CREATE(NAVEditInExcel, TRUE, TRUE);
NAVEditInExcel.EditInExcel(TABLENAME, GETVIEW(TRUE));

That’s it on the NAV side, but of course we didn’t make all the code necessary in the COM object yet.

If you try to launch the Action you will be met by the security dialog

image

Which you of course want to hit always allow to – else you will get this dialog every time you say Edit In Excel.

BTW – If you hit Never allow – you will never be allowed to Edit in Excel – not until you have deleted your PersonalizationStore.xml at least.

Completing the COM object

Having that hooked up we really just need to launch that damn spreadsheet with the right parameters.

We need to add 3 .NET references to the COM object:

  • System.Windows.Forms
  • Microsoft.Office.Interop.Excel
  • Microsoft.VisualStudio.Tools.Applications.ServerDocument.v9.0

and the following 3 using statements:

using Microsoft.VisualStudio.Tools.Applications;
using System.Windows.Forms;
using System.Reflection;

and last but not least, add the following EditInExcel method:

public void EditInExcel(string page, string view)
{
try
{
// Copy the original template to a new template using the page name as name!
string originalTemplate = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), “NAVTemplate.xltx”);
if (!System.IO.File.Exists(originalTemplate))
{
MessageBox.Show(string.Format(“The template: ‘{0}’ cannot be found!”, originalTemplate), “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string template = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), page + “.xltx”);
while (System.IO.File.Exists(template))
{
try
{
System.IO.File.Delete(template);
}
catch (System.IO.IOException)
{
if (MessageBox.Show(string.Format(“The template: ‘{0}’ is locked, cannot open spreadsheet”, template), “Error”, MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) != DialogResult.Retry)
{
return;
}
}
}
System.IO.File.Copy(originalTemplate, template);

        // Open the new template and set parameters
ServerDocument serverDoc = new ServerDocument(template);
CachedDataHostItem host = serverDoc.CachedData.HostItems[0];
host.CachedData[“page”].SerializeDataInstance(page);
host.CachedData[“view”].SerializeDataInstance(view);
serverDoc.Save();
serverDoc.Close();

        // Create a new spreadsheet based on the new template
Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
excelApp.Workbooks.Add(template);

        // Erase template
System.IO.File.Delete(template);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message, “Critical error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}

This method really does 4 things:

  1. Copy the NAVTemplate.xltx to a new template called Customer.xltx (that is if the page name is customer) which is a temporary template
  2. Open the template as a ServerDocument and set the parameters
  3. Ask Excel to create a new spreadsheet based on this template
  4. Erase the template

That was easy!

Oh – there is one things I forgot to say, you need to specify in the Excel Spreadsheet that the page and view variables are cached data (meaning their value are saved with Excel) – this is done by adding an attribute to the variables:

[Cached]
public string page;

[Cached]
public string view;

Having done this, you can open the spreadsheet as a Serverdocument, get and set the value of these parameters and save the document again, pretty sweet way of communicating parameters to Excel or Word – and this will definitely come in handy later.

Adding the action other pages

Having verified that we can edit customers in Excel we can now add the same action as above to the Vendor and the Item List Places.

You can either follow the same steps as above – or you can copy the action and paste it on the other List Places.

Note that you cannot build the Visual Studio solution while you have NAV 2009 open. When NAV loads the COM object, it keeps a reference to it until you close NAV.

Last but not least – this should work from the classic client as well – if you want to add the functionality there – I haven’t tried it.

That’s it folks

That completes the Edit In Excel in Part 1 through 4

As always, there is absolutely no warranty that this code works for the purpose you need it to, but these posts show how to do some things and feel free to use pieces of this or use it as a base to build your own solution using Excel – the code is free – a referral to my blog is always a good way of acknowledgement.

I hope you can make it work, that it is useful and you can download the final solution here: http://www.freddy.dk/NAVTemplate_Final.zip

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 3 (out of 4)

Posted on by
Reply

If you haven’t read part 2 (and part 1), you should do so before continuing here.

In Part 1 and 2, we have seen how easy it is to add a Web Service Reference inside Excel, and use it to get Data. In Part 2 we even had the ability to modify data and send this back to NAV. The original intend was that part 3 would be all about integrating this to NAV on the Client side and part 4 would be to make this loosely coupled – but I have changed my mind on this.

Part 3 will remove the direct dependency on the Customer Web Service from most of the code – and thus allowing us to modify both Customer, Vendor or Item data in Excel with very few tweaks to the code. Also I will add support for parsing a filter string and applying this to the list. I will also add error handling of the save process.

Part 4 will then be to add the Action in NAV and hook that up to set the parameters in Excel.

I will still post the source of the original loosely coupled XMLHTTP based Edit In Excel, but I will not use it for anything.

To prepare ourselves for part 4 we need the following variables:

/// <summary>
/// Page which is going to be used for Edit In Excel
/// Customer, Vendor, Item, etc…
/// The card page for this record needs to be exposed as webservice with that name
/// </summary>
string page;

/// <summary>
/// The filters to apply (format: GETVIEW(TRUE))
/// Sample: “SORTING(No.) WHERE(Balance (LCY)=FILTER(>10,000))”
/// </summary>
string view;

These are the parameters, which we in part 4 will transfer values to Excel in – for now we will build the Spreadsheet to use those.

BTW – I changed the Project name from CustomerTemplate to NAVTemplate (actually I created a new project and copied over some of the files and changed the namespace).

Then I have moved the service connection initialization away from Load – and into Sheet_Startup, the new Sheet1_Startup code looks like this

private void Sheet1_Startup(object sender, System.EventArgs e)
{
switch (this.page)
{
case “Customer”:
this.service = new CustomerRef.Customer_Service();
break;
case “Vendor”:
this.service = new VendorRef.Vendor_Service();
break;
case “Item”:
this.service = new ItemRef.Item_Service();
break;
default:
MessageBox.Show(string.Format(“Page {0} is not setup for Edit In Excel. Please contact your system administrator”, this.page), “Microsoft Dynamics NAV”, MessageBoxButtons.OK, MessageBoxIcon.Error);
break;
}
if (this.service != null)
{
this.service.UseDefaultCredentials = true;
Load();
}
}

and I have added references to all 3 services.

This is the only place I have a switch on the page – the rest of the code is made to work with all – but wait… – how is that possible?

Service Connection classes code generated from Visual Studio doesn’t implement any common interface and we cannot change the code generated proxy classes (or rather – we don’t want to). We can, however, add something to the existing service. Looking at the code generated proxy class we will notice that the Customer_Service class is defined as a partial class – meaning that we can actually write another part of the class just by creating a new class (with the keyword partial)

Looking through my code I really need the Customer_Service to implement an interface like this:

public interface INAVService
{
bool UseDefaultCredentials {get; set; }
System.Net.ICredentials Credentials {get; set; }

    object[] ReadMultiple();
void Update(object obj);
void Create(object obj);
bool Delete(string key);

    Type GetFieldsType();
Type GetObjectType();

    void ClearFilters();
void AddFilter(string field, string criteria);
}

Some of these methods are already implemented by all Service Proxy classes and I use this to allow my code to look at the Service Connection via this interface only and the service variable I have in the sheet is actually type INAVService, flip side of this idea is, that for every new Page I want to add – I need to create a class like this:

public partial class Customer_Service : INAVService
{
List<Customer_Filter> filters;

    #region INAVService Members

    public object[] ReadMultiple()
{
return this.ReadMultiple(this.filters.ToArray(), null, 0);
}

    public void Update(object obj)
{
Customer customer = (Customer)obj;
this.Update(ref customer);
}

    public void Create(object obj)
{
Customer customer = (Customer)obj;
this.Create(ref customer);
}

    public Type GetObjectType()
{
return typeof(Customer);
}

    public Type GetFieldsType()
{
return typeof(Customer_Fields);
}

    public void ClearFilters()
{
this.filters = new List<Customer_Filter>();
}

    public void AddFilter(string field, string criteria)
{
Customer_Filter filter = new Customer_Filter();
filter.Field = (Customer_Fields)Enum.Parse(typeof(Customer_Fields), field, true);
filter.Criteria = criteria;
this.filters.Add(filter);
}

    #endregion
}

Not really nice – but it beats having a series of switch statements scattered around in the source files.

So, whenever we want to add a record object type, which we want to be able to Edit In Excel – we add a source file like this (search and replace Customer with <newtype>), we add an extra outcome in the switch statement above and we expose the page to Web Services in NAV 2009.

BTW – In my solution, I have added the classes to the solution in a folder called Services.

Applying Filters and Load

The Load method now looks like this:

/// <summary>
/// Load Records from NAV via Web Services
/// </summary>
private void Load()
{
PopulateFieldsCollection(this.service.GetObjectType(), this.service.GetFieldsType());
SetFilters(this.view);
this.objects = this.service.ReadMultiple();
PopulateDataTable();
AddDataToExcel();
}

Note that we ask the Service connection class for the Object Type, the Fields Enum Type and we call the ReadMultiple on the Service Connection (all through the interface we just implemented).

After generating fields collection and the DataTable we call SetFilters – which in effect just parses the view variable (sample: “SORTING(No.) WHERE(Balance (LCY)=FILTER(>10,000))” – without the quotes) and calls AddFilter a number of times (in the sample only once) on the Service Connection Interface.

I added a NAVFilterHelper static class with 3 static helper methods – GetBlock, WSName and VSName.

GetBlock parses the string for a block (a keyword followed by a parentheses with stuff in it) – SORTING(No.) is one and the WHERE clause is another. The FILTER  clause is another block inside the WHERE block.

WSName takes a name like “Balance (LCY)” and puts it through name mangling to get the Visual Studio generated identifier name (this is the name used in Enum – Balance_LCY)

VSName takes the enum identifier and removes special characters to get the Property name of the record object (there are no special characters in Balance_LCY)

Confused? – well look at this:

test  &&//(())==??++–**test – is a perfectly good (maybe stupid) field name in NAV

test__x0026__x0026___x003D__x003D__x003F__x003F__x002B__x002B___x002A__x002A_test is the same identifier in the xx_Fields enum (from the WSDL)

test___test is the same identifier as property in Visual Studio (the code generated proxy class)

and yes – you can generate fields, which will cause Web Services to fail. In fact, CTP4 (the US version) has an Option field in Customer and Vendor (Check Seperator), where the options causes Customer and Vendor to fail when exposed to Web Services. This special case is fixed for RTM – and the WSName in my sample contains the same name mangling as NAV 2009 RTM, but you can still create field names, which will end up having identical names in VS – and then your WebService proxy won’t work.

WSName and VSName works for my usage – they might not work for all purposes.

There is really nothing fancy about the SetFilters code, but it works for the purpose:

/// <summary>
/// Parse the view and apply these filters to the Service Connection
/// </summary>
/// <param name=”view”>View to parse (from AL: GETVIEW(TRUE))</param>
private void SetFilters(string view)
{
this.service.ClearFilters();
if (string.IsNullOrEmpty(view))
return;
string sorting = NAVFilterHelper.GetBlock(“SORTING”, ref view);
string where = NAVFilterHelper.GetBlock(“WHERE”, ref view);
do
{
int e = where.IndexOf(“=FILTER”);
if (e < 0)
break;
string field = NAVFilterHelper.WSName(where.Substring(0, e));
string criteria = NAVFilterHelper.GetBlock(“FILTER”, ref where);
this.service.AddFilter(field, criteria);

        if (where.StartsWith(“,”))
where.Remove(0, 1);
}
while (true);
}

Yes, yes – as you of course immediately spotted – this code doesn’t work if you have a field with =FILTER in the field name – so don’t!

The PopulateDataTable and AddDataToExcel methods haven’t changed.

Save

One thing we didn’t get done in part 2 was error handling. If anybody tried to modify a Location Code to an illegal Location Code and save it back to Excel – you will have noticed that Excel just ignored your request.

Reason for this is, that Excel swallows the Exception, and just ignores it.

So – I have changed the Save() method to:

/// <summary>
/// Save Changes to NAV via Web Service
/// </summary>
internal void Save()
{
if (DoSave())
{
Reload();
}
}

and then created the DoSave() – with most of the content from Save() – but refactored inside one loop with error handling (Abort, Retry, Ignore).

/// <summary>
/// Delete, Add and Update Records
/// </summary>
internal bool DoSave()
{
// Run through records marked for delete, create or modify
DataView dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Deleted | DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
foreach (DataRowView drv in dv)
{
bool retry;
do
{
retry = false;
try
{
if (drv.Row.RowState == DataRowState.Deleted)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
if (!service.Delete((string)drv[0]))
{
throw new Exception(string.Format(“Unable to delete record”));
}
}
}
else if (drv.Row.RowState == DataRowState.Added)
{
object obj = Activator.CreateInstance(this.service.GetObjectType());
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv.Row[nfi.field]);
}
}
this.service.Create(obj);
}
else
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv[nfi.field]);
}
}
this.service.Update(obj);
}
}
}
catch (Exception e)
{
DialogResult reply = MessageBox.Show(string.Format(“{0} {1} {2}nn{3}”, this.dataTable.TableName, this.dataTable.Columns[1].Caption, drv[1].ToString(), e.Message), “Microsoft Dynamics NAV”, MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Error);
if (reply == DialogResult.Abort)
return false;
if (reply == DialogResult.Retry)
retry = true;
}
} while (retry);
}
return true;
}

oh yes, and beside that – you can see that I now use the methods on the Service Connection Interface directly and I do not use the type safe references to Customer – but instead just object. A couple of comments:

The DataView is the only way (I know of) that we can see which records have been deleted in the DataTable.

The Line

object obj = Activator.CreateInstance(this.service.GetObjectType());

does the same as using

object obj = new CustomerRef.Customer();

if the object type of the Service Connection is Customer.

So if you change the location code on a customer to something stupid you now get this error:

image

Of course it doesn’t make much sense to Retry this one. Abort aborts the save – and doesn’t reload. Ignore continues the Save and ends up reloading and you loose the changes, we couldn’t save. Note that Abort is a little strange – it cannot abort the changes that has happened up until you abort – and since it doesn’t reload, it leaves the spreadsheet in a bad state.

Maybe we should just remove the abort option – it just seems like a bad thing only to be able to retry or press ignore on every single record. If I come up with a better way of handling Abort, I will post that.

Can’t wait for part 4?

Part 4 is where we integrate this into NAV and create Actions to call out to open this Excel Spreadsheet on the Client and that includes changes in NAV, a Client Side COM object and a mechanism to transfer parameters to an Excel spreadsheet – stay tuned.

In my original Edit In Excel sample, I use XMLHTTP to retrieve page information and look through that – I will still post the original Edit In Excel source code with part 4 – but note, that there are a LOT of pitfalls in that – and, it doesn’t support Adding records or deleting records, and I have stopped working on that code, even though the need for using XMLHTTP might still be relevant.

The safer way is to use the sample solutions posted with this walk through.

BTW – the NAVTemplate solution can be downloaded here: http://www.freddy.dk/NAVTemplate.zip

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Edit In Excel – Part 2 (out of 4)

Posted on by
Reply

If you haven’t read Part 1, you should do so before continuing here.

In Part 1 we saw how easy it is to white .net code inside Excel, and get it executed based on an event in Excel, and how easy it is to fill values into cells. But in order to make this really useful we need to go a different way around.

First of all, we need to know more about the record we are working with. We could of course hard code everything – but that is not the preferred way to go. It needs to be flexible.

When referencing the Customer Page in Visual Studio, Visual Studio creates a proxy class (a wrapper for the Customer).

This Proxy class contains members for all fields on the Page and properties to access them, like:

public partial class Customer
{
private string keyField;
private string noField;
private string nameField;
… etc …


public string Key
{
get { return this.keyField; }
set { this.keyField = value; }
}

public string No
{
get { return this.noField; }
set { this.noField = value; }
}

    public string Name
{
get { return this.nameField; }
set { this.nameField = value; }
}

… etc …
}

Meaning that if we have a variable of type Customer, we can get the value of the fields by accessing cust.Name etc., as we saw in part 1. There is no collection of fields, field types and getters/setters we can call – all we have is a class with a number of properties and an Enum with all the field names.

So, in order to be able to build a list of fields, look at their types and get their values without having to hard code everything, we need to use reflection.

Setting up definitions

I wont go into details about reflection here – but basically we need a method like this:

/// <summary>
/// Populate Fields Collection with NAVFieldInfo for all properties in the record
/// Should works with any NAV 2009 Page exposed as WebService
/// </summary>
/// <param name=”objType”>Type of Object (typeof(Customer), typeof(Vendor), …)</param>
/// <param name=”fieldsType”>Type of the Enum holding the property names</param>
private void PopulateFieldsCollection(Type objType, Type fieldsType)
{
// Create columns in Datatable
this.dataTable = new DataTable(objType.Name);
this.fields = new List<NAVFieldInfo>();

    // Key property is not part of the Enum
// Add it manually as the first field
AddField(“Key”, objType);

    // Run through the enum and add all fields
foreach (string field in Enum.GetNames(fieldsType))
{
AddField(field, objType);
}
}

This method enumerates all field names in the fieldsType Enum and call AddField for every field in the record object.

AddField then looks like this:

/// <summary>
/// Add a Column to the DataTable
/// And create a corresponding NAVFieldInfo
/// </summary>
/// <param name=”field”>Field name</param>
/// <param name=”objType”>Type of Object in which the field is (typeof(Customer), typeof(Vendor), …)</param>
private void AddField(string field, Type objType)
{
PropertyInfo pi = objType.GetProperty(field, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
NAVFieldInfo nfi = NAVFieldInfo.CreateNAVFieldInfo(field, pi, objType.Namespace);
// If we encounter unknown Field Types, they are just ignored
if (nfi != null)
{
this.fields.Add(nfi);
this.dataTable.Columns.Add(new DataColumn(field, nfi.fieldType));
}
}

It uses reflection to get the information about the property on the record object (the PropertyInfo).

After calling the PopulateFIeldsCollection, we will have a collection of fields in the this.fields variable and we will have the corresponding columns in the this.dataTable variable, all created with the right names and types on the columns.

For every different field type there is a different class which all derives from NAVFieldInfo. This class holds the field name, the field type and the PropertyInfo class (which is used for invoking the getter and setter on the record object later).

The static CreateNAVFieldInfo which is called from AddField then creates the right NAVFieldInfo class as shown below:

/// <summary>
/// Create a NAVFieldInfo object for a specific field
/// </summary>
/// <param name=”field”>Name of the property</param>
/// <param name=”pi”>PropertyInfo for the property on the record object</param>
/// <param name=”ns”>Namespace for the record object (namespace for the added WebServices proxy class)</param>
/// <returns>NAVFieldInfo or null if the type isn’t supported</returns>
internal static NAVFieldInfo CreateNAVFieldInfo(string field, System.Reflection.PropertyInfo pi, string ns)
{
if (pi.PropertyType == typeof(string))
{
// String Property – is it the KeyField
if (field == “Key”)
return new KeyFieldInfo(field, pi);
else
return new StringFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(decimal))
{
// Decimal Property
return new DecimalFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(int))
{
// Integer Property
return new IntFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(bool))
{
// Boolean Property
return new BooleanFieldInfo(field, pi);
}
if (pi.PropertyType == typeof(DateTime))
{
// DateTime Property
return new DateTimeFieldInfo(field, pi);
}
if (pi.PropertyType.Namespace == ns)
{
// Other Property Types, in the same namespace as the object
// These are enum’s – set up restrictions on OptionFields
return new OptionFieldInfo(field, pi, Enum.GetNames(pi.PropertyType));
}
// Unsupported – ignore
return null;
}

Meaning that there are classes for KeyFieldInfo, StringFieldInfo, DecimalFieldInfo, IntFieldInfo, BooleanFieldInfo, DateTimeFieldInfo and OptionFieldInfo.

On these classes we have 3 virtual methods:

  1. Get the value of the property from a Record Object (GetValue)
  2. Set the value of the property on the Record Object (SetValue)
  3. Format the Excel column used to show this type (AdjustColumn)

Example of the AdjustColumn on the StringFieldInfo is:

/// <summary>
/// Adjust formatting and properties of an Excel column
/// </summary>
/// <param name=”column”>Excel Range of cells to set formatting on</param>
internal override void AdjustColumn(Microsoft.Office.Interop.Excel.Range column)
{
column.EntireColumn.NumberFormat = “@”;
column.EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft;
column.EntireColumn.AutoFit();
}

Which will set the format, the horizontal alignment and make the column with adjust to the size of the content.

The GetValue is defined as

/// <summary>
/// Get the value from the record object by calling the property getter on the object
/// </summary>
/// <param name=”obj”>The record object</param>
/// <returns>The value in the type specified in fieldType</returns>
internal virtual object GetValue(object obj)
{
return this.pi.GetValue(obj, null);
}

in the base class – and is only overwritten in the BooleanFieldInfo class. The SetValue is defined in the base class is:

/// <summary>
/// Set the value to the record object by calling the property setter on the object
/// </summary>
/// <param name=”obj”>The record object</param>
/// <param name=”value”>The new value for the field</param>
internal virtual void SetValue(object obj, object value)
{
    if (value == DBNull.Value)
{
if (!string.IsNullOrEmpty(this.pi.GetValue(obj, null) as string))
{
this.pi.SetValue(obj, “”, null);
}
}
else
{
this.pi.SetValue(obj, value, null);
}
}

has is overwritten in OptionFieldInfo and BooleanFieldInfo.

The reason for overwriting these functions is, that the property type in the Record Object is different from the type we set in the DataTable. For Boolean – we want to have a Yes/No option in Excel – but the type in the Record Object is a Boolean – not a string.

For Option Fields – the Property Type of the Record Object is an Enumeration defined in the same namespace as the Record Object and the OptionFieldInfo class uses reflection to enumerate the enumeration names.

Loading the data

After having information about all fields ready on our fingertips and the DataTable initialized and ready to recieve data – we just need to read the data and add it to the datatable (and then of course add the datatable to the Spreadsheet).

I have created a method called Load() – which does all of these things:

/// <summary>
/// Load Customers from NAV via Web Services
/// </summary>
private void Load()
{
PopulateFieldsCollection(typeof(CustomerRef.Customer), typeof(CustomerRef.Customer_Fields));
CustomerRef.Customer_Service service = new CustomerRef.Customer_Service();
service.UseDefaultCredentials = true;
this.objects = service.ReadMultiple(null, “”, 0);
PopulateDataTable();
    AddDataToExcel();
}

First of all, call the PopulateFieldsCollection method. Then create a Web Service Connection and read all record objects in an array of objects (note that I am NOT using strongly typed class references, as I want this code to be reusable when I work with other pages via Web Services).

The PopulateDataTable method became amazingly simple:

/// <summary>
/// Populate DataTable based on array of objects
/// </summary>
private void PopulateDataTable()
{
// Populate DataTable with data
foreach (object obj in this.objects)
{
DataRow dataRow = this.dataTable.NewRow();
foreach (NAVFieldInfo nfi in this.fields)
{
dataRow[nfi.field] = nfi.GetValue(obj);
}
this.dataTable.Rows.Add(dataRow);
}
}

Run through all rows and for all rows – run through all columns and get the value from the Record Object into the Data Table.

and the AddDataToExcel really isn’t that hard either:

/// <summary>
/// Add a DataList to Excel
/// This function should work with any Page exposed as Web Service
/// </summary>
/// <param name=”objects”>Array of records to add</param>
private void AddDataToExcel()
{
Application.ScreenUpdating = false;

    // Populate Excel Spreadsheet with data
this.dataList = this.Controls.AddListObject(this.Range[this.Cells[1, 1], this.Cells[this.dataTable.Rows.Count + 1, this.dataTable.Columns.Count + 1]], this.dataTable.TableName);
this.dataList.AutoSetDataBoundColumnHeaders = true;
this.dataList.DataSource = this.dataTable;
this.dataTable.AcceptChanges();

    // Adjust columns in excel with the right formatting based on Field Info
int col = 1;
foreach (NAVFieldInfo nfi in this.fields)
{
nfi.AdjustColumn(this.dataList.Range[1, col++] as Microsoft.Office.Interop.Excel.Range);
}

    Application.ScreenUpdating = true;
}

First, we disable ScreenUpdating, setup a ListObject with the created DataTable, call AdjustColumn on the correct NAVFieldInfo class for the corresponding column and enable screenupdating again.

Running the project with these things gives us:

image

Now – that’s more like it. We have data in a table, formatting on fields and even options in option fields:

image

and… Saving the changes!

The only thing we need to do more is to be able to write data back into NAV, if we change something. But wait – we would of course also like to be able to create records and delete records in the spreadsheet – lets see what is needed for that….

We use DataView to look at a subset of a DataTable (deleted, added or modified) and then we need to do react accordingly.

First we run through the deleted records and delete them from NAV – if you want to add a warning, you should do so before calling service.Delete.

// Run through records marked for deletion and delete those
DataView dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Deleted);
foreach (DataRowView drv in dv)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
service.Delete(((CustomerRef.Customer)obj).Key);
}
}

Then we run through the added records and add them to NAV:

// Run through added records and add them
dv = new DataView(this.dataTable, “”, “”, DataViewRowState.Added);
foreach (DataRowView drv in dv)
{
CustomerRef.Customer customer = new CustomerTemplate.CustomerRef.Customer();
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(customer, drv[nfi.field]);
}
}
service.Create(ref customer);
}

and finally we run through the modified records and update them in NAV:

// Run through modified records and update them
dv = new DataView(this.dataTable, “”, “”, DataViewRowState.ModifiedCurrent);
foreach (DataRowView drv in dv)
{
object obj = GetRecordObject((string)drv[0]);
if (obj != null)
{
foreach (NAVFieldInfo nfi in this.fields)
{
if (nfi.field != “Key”)
{
nfi.SetValue(obj, drv[nfi.field]);
}
}
CustomerRef.Customer customer = (CustomerRef.Customer)obj;
service.Update(ref customer);
}
}

and after this – we reload the data.

so… – that was a LOT of code and some explaining. I hope you are with me so far.

I didn’t explain how to add a Ribbon to Excel, nor did I list all the NAVFieldInfo classes in this post, but you can download the solution as source from http://www.freddy.dk/CustomerTemplate.zip and see how these things are done. You can play around with things and/or use pieces of the code in your own solution.

The code shown in this post comes with no warranty – and is only intended for showing how to do things. The code can be reused, changed and incorporated in any project without any further notice.

So that’s all good, but what’s next?

We now have a spreadsheet, which can read customers via Web Services, allow you to modify, add or delete and save the changes through Web Services, which is pretty impressive – but we have 2 more posts on the topic:-)

Next, we want to integrate this into the Customer List Place and get the filter from the List Place to the spreadsheet. To do this we need a Client side COM object, which transfers parameters to the Excel Spreadsheet and launches Excel.

And last, we will make the Edit In Excel work without the Web Reference – meaning that we can just add an action to the pages on which we want the Edit In Excel to work.

Enjoy

Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV