If you haven’t already read part 3 you should do so here, before continuing to read this post.
By now you have seen how to create a WCF Service Proxy connected to NAV with an endpoint hosted on the Servicebus (Windows Azure AppFabric). By now, I haven’t written anything about security yet and the Proxy1 which is hosted on the Servicebus is available for everybody to connect to anonymously. Continue reading
If you haven’t already read part 2 you should do so here, before continuing to read this post.
In part 2 I talked about how to connect to my locally installed NAV Web Service Proxy from anywhere in the world and towards the end, I promised that I would explain how the proxy was build. Problem was, that while writing this post I ran into a bug in the Servicebus – which was really annoying. Continue reading
If you haven’t already read part 1 you should do so here, before continuing to read this post.
In part 1 I showed how a service reference plus two lines of code:
var client = new Proxy1.ProxyClassClient("NetTcpRelayBinding_IProxyClass");
Console.WriteLine(client.GetCustomerName("10000"));
could extract data from my locally installed NAV from anywhere in the world. Continue reading
It is time to collect the pieces.
The full Edit In Excel R2 solution looks like this
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.
Lets have a closer look at the Client Setup Program
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:
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:
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:
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 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.
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
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
Yes, it can be done!
No, it isn’t .net code – nor AL code.
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:
People might not assume that this actually is Invoice no. 10103 in PDF format. What you of course want to have is:
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?
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).
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
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.
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.
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:
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:
I don’t know whether that could come in handy, but maybe it can spawn off some good ideas.
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
As with the release of Microsoft Dynamics NAV 2009, I was also deeply involved in the TAP for Microsoft Dynamics NAV 2009 SP1. My primary role in the TAP is to assist ISVs and partners in getting a customer live on the new version before we ship the product.
During this project we file a lot of bugs and the development team in Copenhagen are very responsive and we actually get a lot of bugs fixed – but… not all – it happens that a bug is closed with “By Design”, “Not Repro” or “Priority too low”.
As annoying as this might seem, I would be even more annoyed if the development team would take every single bug, fix it, run new test passes and punt the releases into the unknown. Some of these bugs then become challenges for me and the ISV / Partner to solve, and during this – it happens that I write some code and hand off to my contact.
Whenever I do that, two things are very clear
and of course I send the code to the development team in Copenhagen, so that they can consider the fix for the next release.
One of the bugs we ran into this time around was the fact that when doing merge with Microsoft Word in a 3T environment, word would only accept 64 merge fields. Now in the base application WordManagement (codeunit 5054) only uses 48 fields, but the ISV i was working with actually extended that to 100+ fields.
The bug is in Microsoft Word, when merging with file source named .HTM – it only accepts 64 fields, very annoying.
We also found that by changing the filename to .HTML, then Word actually could see all the fields and merge seemed to work great (with one little very annoying aberdabei) – the following dialog would pop up every time you open Word:
Trying to figure out how to get rid of the dialog, I found the right parameters to send to Word.OpenDataSource, so that the dialog would disappear – but… – then we are right back with the 64 fields limitation.
The reason for the 64 field limitation is, that Word loads the HTML as a Word Document and use that word document to merge with and in a document, you cannot have more than 64 columns in a table (that’s at least what they told me).
I even talked to PM’s in Word and got confirmed that this behavior was in O11, O12 and would not be fixed in O14 – so no rescue in the near future.
Knowing that the behavior was connected to the merge format, I decided to try and change that – why not go with a good old fashion .csv file instead and in my quest to learn AL code and application development, this seemed like a good little exercise.
So I started to look at WordManagement and immediately found a couple of things I didn’t like
MergeFileName := RBAutoMgt.ClientTempFileName(Text029,’.HTM’);
IF ISCLEAR(wrdMergefile) THEN
CREATE(wrdMergefile,FALSE,TRUE);
// Create the header of the merge file
CreateHeader(wrdMergefile,FALSE,MergeFileName);
<find the first record>
REPEAT
// Add Values to mergefile – one AddField for each field for each record
wrdMergefile.AddField(<field value>);
// Terminate the line
wrdMergefile.WriteLine;
UNTIL <No more records>
// Close the file
wrdMergefile.CloseFile;
now wrdMergefile is a COM component of type ‘Navision Attain ApplicationHandler’.MergeHandler and as you can see, it is created Client side, meaning that for every field in every record we make a roundtrip to the Client (and one extra roundtrip for every record to terminate the line) – now we might not have a lot of records nor a lot of fields, but I think we can do better (said from a guy who used to think about clock cycles when doing assembly instructions on z80 processors back in the start 80’s – WOW I am getting old:-))
One fix for the performance would be to create the file serverside and send it to the Client in one go – but that wouldn’t solve our original 64 field limitation issue. I could also create a new COM component, which was compatible with MergeHandler and would write a .csv instead – but that wouldn’t solve my second issue about wanting to learn some AL code.
I decided to go with a model, where I create a server side temporary file for each record, create a line in a BigText and write it to the file. After completing the MergeFile, it needs to be downloaded to the Client and deleted from the service tier.
The above code would change into something like
MergeFileName := CreateMergeFile(wrdMergefile);
wrdMergefile.CREATEOUTSTREAM(OutStream);
CreateHeader(OutStream,FALSE); // Header without data
<find the first record>
REPEAT
CLEAR(mrgLine);
// Add Values to mergefile – one AddField for each field for each record
AddField(mrgCount, mrgLine, <field value>);
// Terminate the line
mrgLine.ADDTEXT(CRLF);
mrgLine.WRITE(OutStream);
CLEAR(mrgLine);
UNTIL <No more records>
// Close the file
wrdMergeFile.Close();
MergeFileName := WordManagement.DownloadAndDeleteTempFile(MergeFileName);
As you can see – no COM components, all server side. A couple of helper functions are used here, but no rocket science and not too different from the code that was.
CreateMergeFile creates a server side temporary file.
CreateMergeFile(VAR wrdMergefile : File) MergeFileName : Text[260]
wrdMergefile.CREATETEMPFILE;
MergeFileName := wrdMergefile.NAME + ‘.csv’;
wrdMergefile.CLOSE;
wrdMergefile.TEXTMODE := TRUE;
wrdMergefile.WRITEMODE := TRUE;
wrdMergefile.CREATE(MergeFileName);
AddField adds a field to the BigText. Using AddString, which again uses DupQuotes to ensure that “ inside of the merge field are doubled.
AddField(VAR count : Integer;VAR mrgLine : BigText;value : Text[1024])
IF mrgLine.LENGTH = 0 THEN
BEGIN
count := 1;
END ELSE
BEGIN
count := count + 1;
mrgLine.ADDTEXT(‘,’);
END;
mrgLine.ADDTEXT(‘”‘);
AddString(mrgLine, value);
mrgLine.ADDTEXT(‘”‘);
AddString(VAR mrgLine : BigText;str : Text[1024])
IF STRLEN(str) > 512 THEN
BEGIN
mrgLine.ADDTEXT(DupQuotes(COPYSTR(str,1,512)));
str := DELSTR(str,1,512);
END;
mrgLine.ADDTEXT(DupQuotes(str));
DupQuotes(str : Text[512]) result : Text[1024]
result := ”;
REPEAT
i := STRPOS(str, ‘”‘);
IF i <> 0 THEN
BEGIN
result := result + COPYSTR(str,1,i) + ‘”‘;
str := DELSTR(str,1,i);
END;
UNTIL i = 0;
result := result + str;
and a small function to return CRLF (line termination for a merge line)
CRLF() result : Text[2]
result[1] := 13;
result[2] := 10;
When doing this I did run into some strange errors when writing both BigTexts and normal Text variables to a stream – that is the reason for building everything into a BigText and writing once pr. line.
and last, but not least – a function to Download a file to the Client Tier and delete it from the Service Tier:
DownloadAndDeleteTempFile(ServerFileName : Text[1024]) : Text[1024]
IF NOT ISSERVICETIER THEN
EXIT(ServerFileName);
FileName := RBAutoMgt.DownloadTempFile(ServerFileName);
FILE.ERASE(ServerFileName);
EXIT(FileName);
It doesn’t take much more than that… (beside of course integrating this new method in the various functions in WordManagement). The fix doesn’t require anything else than just replacing codeunit 5054 and the new WordManagement can be downloaded here.
Question is now, whether there are localization issues with this. I tried changing all kinds of things on my machine and didn’t run into any problems – but if anybody out there does run into problems with this method – please let me know so.
So what if you install this codeunit into a system, where some of these merge files already have been created – and are indeed stored as HTML in blob fields?
Well – for that case, I created a function that was able to convert them – called
ConvertContentFromHTML(VAR MergeContent : BigText) : Boolean
It isn’t pretty – but it seems to work.
I realize that by posting this, I am entering a domain where I am the newbie and a lot of other people are experts. I do welcome feedback on ways to do coding, things I can do better or things I could have done differently.
Enjoy
Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV
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.
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.
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.
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:
Not very informative when you were expecting this:
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.
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:
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.
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:
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.
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.
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.
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.
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;
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.
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.
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.
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:
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
Right around the release of Microsoft Dynamics NAV 2009, I wrote a blog entry with some .bat files on how to create multiple Service Tiers when working with NAV 2009.
The blog post is here: http://blogs.msdn.com/freddyk/archive/2008/10/29/multiple-service-tiers.aspx
Now NAV 2009 SP1 is about to be released, it is time for a small update. One of the files of the package is a CustomSettings.template file, which really is just the CustomSettings.config with a few values replaced with variable names, so that we can replace those automagically.
Now in SP1, the CustomSettings.config has changed – new keys have been added and we also support named instances in the database.
SP1 will actually run with the old config file, so we could just ignore the entire thing and continue as if nothing happened – the .bat files will still work in SP1.
However – if we want to take advantage of the named instances in SQL Server or we want to have the additional keys available for modifying we need to change something.
I have created a new CustomSettings.template based on the SP1 config file – copy the config file and change the following keys:
<add key=”DatabaseServer” value=”#DBSERVER#”></add>
<add key=”DatabaseInstance” value=”#DBINSTANCE#”></add>
<add key=”DatabaseName” value=”#DATABASE#”></add>
<add key=”ServerInstance” value=”#INSTANCE#”></add>
and extended the createservice.bat file to also allow a database instance to be specified, meaning that the usage is now:
CreateService name [dbserver] [“dbinstance”] [“dbname”] [demand|auto|disabled] [both|servicetier|ws]
The new .zip file is available for download here.
Enjoy
Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV
The majority of installations of NAV 2009 will be in a network environment, you will have a domain server and an Active Directory with your users and your biggest worries will be how to setup a 3T environment on 3 different boxes, getting the delegation setup correctly all of that stuff.
But in a few cases we might run into situations, where there is only a LAN between a number of machines and no server.
In this post I will show what it takes to make one XP computer run the RoleTailored Client (or the Classic) on one XP computer and have another XP computer be the Database Tier, Service Tier – and of course run a client too.
First of all – I have installed two XP computers with XP SP3 and nothing else – no Office, no Visual Studio, no nothing – just plain good old XP.
In both XP’s I have used the Set up a home or small office network wizard to make the computers join a workgroup and be able to see each other
The computers are named WorkXP1 and WorkXP2 – and there is one user on each computer XP1 on WorkXP1 and XP2 on WorkXP2.
So what I want to do, is to install the full NAV 2009 on WorkXP1 and only the Clients on WorkXP2 – and make WorkXP2 run with WorkXP1 as the Service Tier and Database tier for the RTC or the Classic. I will also see that I can in fact run Web Services from WorkXP2 as well.
I did not try to install the database tier separate from the Service Tier, as I do not think it is relevant in a scenario like this.
Note, that I cannot just insert the DVD and press Install Demo, because I don’t have Microsoft Outlook installed, so I select Choose an installation option and then Customize under Server (or Client or Database Components) and select the following options
and then just accept the default setup values and install.
on the WorkXP2 we install both clients – Again Choose an Installation option and select customize under Client and Select to run Classic from My Computer.
After selecting Next you will need to Click the RoleTailored Client link and specify the Service Tier computer (which is WorkXP1).
If you wonder how – I am running this experiment as two virtual machines on a Windows 2008 Server running Hyper-V.
As you might have guessed – starting the clients on WorkXP1 just works, and trying the same on WorkXP2 doesn’t.
We need to do 3 things in order to make this work…
The way workgroups works is to authenticate the XP2 user on WorkXP1 using his own Username and Password, so you need to create all the users on the “server”, and note, then need to have the same password.
After this you of course need to create the user in NAV (using the Classic Client on WorkXP1) and give him the right permissions (In my world everybody is SUPER).
Having done this, we try to start the Classic Client on the WorkXP2 machine and get the following result:
This means that the firewall is blocking access to the SQL Server.
On WorkXP1 open the Control Panel and open Windows Firewall. Click Add Program, browse and locate SQLSERVR.EXE under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn add this to the list of exceptions:
At the top of the list of exceptions you will BTW. find DynamicsNAVServer – which means that the RoleTailored actually doesn’t need this setting.
Trying to connect from the Classic Client now will give a different error:
The reason for this is that XP by default is running Simple Sharing, meaning that everybody will login as Guest on a different computer and not trying to login with their own Username and Password.
On WorkXP1 in the Control Panel or any explorer window select Tools -> Folder Options, goto the View Tab and remove the checkmark from Use simple file sharing.
After having done this – you should be able to connect using the Classic Client and also the RoleTailored Client should now work.
It is probably not any different than what you would do with NAV 5.0 – but a number of people has asked me whether or not it is possible – and it is.
BTW – Web Services works in exactly the same way. If you start the Web Service listener on WorkXP1 – you can navigate to
http://WorkXP1:7047/DynamicsNAV/WS/CRONUS%20International%20Ltd/Services (this is the SP1 URL)
You might have to open the firewall for port 7047 if it doesn’t pick up that DynamicsNAVServer is listening on both ports.
Enjoy
Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV
Freddys blog 