Sending data to NAV through a XMLPort

One of the things I showed at Directions US 2009 was how to send data to NAV via a XMLPort, and I promised to blog about that as well. In the meantime I found out, that Lars actually already wrote about this (and he also made a WebCast).

Please have a look at the NAV Team Blog for more info:

The only reason for this post is, that people who attended my session at Directions might look for stuff like this on my blog – so, here you are.


Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

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

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.

The Client Setup Program

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:

public partial class RegasmInstaller : Installer
public RegasmInstaller()
: base()

    public override void Commit(IDictionary savedState)

    public override void Rollback(IDictionary savedState)

    public override void Uninstall(IDictionary savedState)

    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))

        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);

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

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



Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Auto Deployment of Client Side Components – take 2

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


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,

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
GetTableMetadata(TableNo, Metadata);
IF AddToMetadataEx(TableNo, Object.Type::Table, Metadata, SearchLine, ”, ‘;’ + Properties, TRUE, FALSE) THEN BEGIN
SetTableMetadata(TableNo, Metadata, VersionList);
changed := TRUE;

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 := ”;

Field.TableNo := TableNo;
Field.”No.” := FieldNo;
Field.FieldName := FieldName;
Field.Type := FieldType;
Field.Class := Field.Class::Normal;
Field.Len := FieldLength;
Field.Enabled := TRUE;


  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]);

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


Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Conflict resolution when working with Web Services

It’s time to wrap up on Edit In Excel R2 – but before I do that, I will explain about another important feature in Edit In Excel R2 – conflict resolution.

In my last post on Edit In Excel R2 (found here) – I explained how to make Edit In Excel capable of taking data offline for editing. It doesn’t make much sense to do that unless you also have some sort of conflict resolution when you then try to save data.

Another important post, which you should read before continuing is this post, explaining about how to use dynamic web references and explains a little about some helper classes for enumerating fields and stuff.

This post is all about creating a conflict resolution dialog like this


which can be used in Edit In Excel – or in other scenarios, where you want the user to do conflict resolution.

Type weak

This dialog of course needs to be type weak – we are not about to write a conflict resolution dialog for each page we use as web services.

For this, I will use the NAVFieldInfo class hierarchy from this post and produce the following static method in a Windows Forms Form called NAVPageMergeForm:

/// <summary>
/// Resolve conflicts in a record
/// </summary>
/// <param name=”fields”>NAVFields collection</param>
/// <param name=”orgObj”>Original record</param>
/// <param name=”obj”>Record with your changes</param>
/// <param name=”theirObj”>Record with changes made by other users</param>
/// <param name=”mergedObj”>This parameter receives the merged object if the user presses accept</param>
/// <returns>DialogResult.Retry if the user pressed Accept Merged Values
/// DialogResult.Ignore if the user chose to ignore this record
/// DialogResult.Abort if the user chose to abort</returns>
public static DialogResult ResolveConflict(NAVFields fields, object orgObj, object myObj, object theirObj, out object mergedObj)
NAVPageMergeForm form = new NAVPageMergeForm();
// And set the values
form.SetValues(fields, orgObj, myObj, theirObj);
DialogResult response = form.ShowDialog();
if (response == DialogResult.Retry)
mergedObj = form.GetMergedObj();
mergedObj = null;
return response;

The method takes a Field Collection and three objects. The object as it looked before i started to make my changes (orgObj), the object with my changes (myObj) and the “new” object including the changes somebody else made in the database (theirObj). The last parameter is an out parameter meaning that this is where the method returns the merged object.

The return value of the dialog is a DialogResult, which can be Abort, Ignore og Retry.

Why Retry?

Why not OK?

Well, if you think of it, when you have merged the record you now have a new record which you have constructed – but while you were merging this record, somebody else might have changed the record again – meaning that we have to retry the entire thing and this might result in a new conflict resolution dialog popping up for the same record.

How to use the ResolveConflict method

As you can imagine, the usage of the method needs to follow a certain pattern and in my small sample app I have done this like:

bool retry;
retry = false;
service.Update(ref myChanges);
catch (Exception e)
string message = e.InnerException != null ? e.InnerException.Message : e.Message;
object theirChanges = null;
if (service.IsUpdated(customer2.Key))
theirChanges = service.Read(customer2.No);
catch { } // Ignore errors when re-reading
if (theirChanges != null)
object merged;
retry = (NAVPageMergeForm.ResolveConflict(fields, customer2, myChanges, theirChanges, out merged) == DialogResult.Retry);
if (retry)
myChanges = (Customer)merged;
retry = (MessageBox.Show(string.Format(“{0}nn{3}”, “Customer ” + customer2.No, message), “Unable to Modify Record”, MessageBoxButtons.RetryCancel, MessageBoxIcon.Error) == DialogResult.Retry);
} while (retry);

Compared to just saying service.Update(ref myChanges) – this is of course more complicated, but it adds huge value.

In Edit-In-Excel, this is of course captured i a method called UpdateRecord.

What happens in SetValues?

SetValues basically enumerates the field collection and adds values to a grid as you see in the image above, comparing the changes made by the various people and automatically merging values only changed by one user – displaying conflicts if the same field was modified by both.

/// <summary>
/// Set records in merge form
/// </summary>
/// <param name=”fields”>Field collection</param>
/// <param name=”orgObj”>Original record</param>
/// <param name=”obj”>Modified record</param>
/// <param name=”theirObj”>Changed record from WS</param>
internal void SetValues(NAVFields fields, object orgObj, object myObj, object theirObj)
this.mergedObj = theirObj;
this.fields = fields;

    foreach (NAVPageFieldInfo field in fields)
if (field.field != “Key”)
object orgValue = field.GetValue(orgObj);
if (orgValue == null) orgValue = “”;
object myValue = field.GetValue(myObj);
if (myValue == null) myValue = “”;
object theirValue = field.GetValue(theirObj);
if (theirValue == null) theirValue = “”;
object mergedValue;

            DataGridViewCellStyle myStyle = this.normalStyle;
DataGridViewCellStyle theirStyle = this.normalStyle;
DataGridViewCellStyle mergedStyle = this.normalStyle;

            bool iChanged = !orgValue.Equals(myValue);
bool theyChanged = !orgValue.Equals(theirValue);

            if (iChanged && theyChanged)
// Both parties changed this field
myStyle = modifiedStyle;
theirStyle = modifiedStyle;
if (myValue.Equals(theirValue))
mergedValue = myValue;
mergedStyle = this.modifiedStyle;
mergedValue = “”;
mergedStyle = this.conflictStyle;

else if (theyChanged)
// “They” changed something – I didn’t
mergedValue = theirValue;
theirStyle = this.modifiedStyle;
mergedStyle = this.modifiedStyle;
else if (iChanged)
// I changed something – “they” didn’t
mergedValue = myValue;
myStyle = this.modifiedStyle;
mergedStyle = this.modifiedStyle;
// Nobody changed anything – merged value is ok
mergedValue = orgValue;
int rowno = this.mergeGridView.Rows.Add(field.field, orgValue, myValue, theirValue, mergedValue);
this.mergeGridView[2, rowno].ValueType = field.fieldType;
this.mergeGridView[3, rowno].ValueType = field.fieldType;
this.mergeGridView[4, rowno].ValueType = field.fieldType;

            this.mergeGridView[2, rowno].Style = myStyle;
this.mergeGridView[3, rowno].Style = theirStyle;
this.mergeGridView[4, rowno].Style = mergedStyle;
if (mergedStyle == this.conflictStyle)
if (this.mergeGridView.CurrentCell == null)
this.mergeGridView.CurrentCell = this.mergeGridView[0, rowno];

The rest is really manipulating button status depending on selection, setting values if you press My Changes, Their Changes or Original and in the end when the user pressed Accept changes we just return Retry and the caller will call and get the Merged Object, which basically just is

/// <summary>
/// Get Merged object
/// </summary>
/// <returns>the Merged record</returns>
internal object GetMergedObj()
int rowno = 0;
foreach (NAVPageFieldInfo field in fields)
if (field.field != “Key”)
field.SetValue(this.mergedObj, this.mergeGridView[4, rowno].Value, field.GetValue(this.mergedObj));
return this.mergedObj;

A small Test App

Here is a small test app, demonstrating how the conflict resolution can be provoked

Console.WriteLine(“Initialize Service…”);
Customer_Service service = new Customer_Service();
service.UseDefaultCredentials = true;

Console.WriteLine(“Read Customer 10000… – twice (two users)”);
// Read customer twice
Customer customer1 = service.Read(“10000”);
Customer customer2 = service.Read(“10000”);

Console.WriteLine(“One user changes Customer 10000…”);
// Change customer 1
customer1.Phone_No = “111-222-3333”;
customer1.Address_2 = “an address”;
service.Update(ref customer1);

Console.WriteLine(“Other user tries to change Customer 10000”);
NAVFields fields  = new NAVFields(typeof(Customer), typeof(Customer_Fields));
Customer myChanges = (Customer)GetCopy(customer2);

myChanges.Phone_No = “222-333-4444”;
myChanges.Name = “The Cannon Group, Inc.”;

Write the data according to the pattern shown above – using conflict resolution and all (and after that – clean up the data, so that we can run the test app again)

Console.WriteLine(“Reset Data…”);

// Clear any updated data
Customer customer = service.Read(“10000”);
customer.Phone_No = “”;
customer.Address_2 = “”;
customer.Name = “The Cannon Group PLC”;
service.Update(ref customer);

Console.WriteLine(“nTHE END”);

BTW – the GetCopy method looks like this:

private static object GetCopy(object obj)
Type typ = obj.GetType();
MethodInfo mi = typ.GetMethod(“MemberwiseClone”, BindingFlags.Instance | BindingFlags.NonPublic);
return mi.Invoke(obj, null);

The ConflictResolution solution can be downloaded here.


Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV

Dynamic references to NAV Page Web Services in C# – take 2

In this post from April, I explained how to make dynamic references to page based Web Services, but the post really left the developer with a lot of manual work to do using reflection.

So – I thought – why not create a couple of helper classes which makes it easier.

Basically I have created a generic NAVPageServiceHelper class, which encapsulates all the heavy lifting of reflection and leaves the developer with a set of higher level classes he can use.

The service helper will have a collection of classes explaining various information about the fields and has methods for getting or setting the value (and setting the corresponding _Specified automatically as well).

The primary reason for making this is of course to make Edit In Excel bind to any page without changing anything, but the method can be used in a lot of other scenarios.

2 projects: NAVPageFieldInfo and NAVPageDynamicWebReference

I split the PageServiceHelper and the PageFieldInfo into two seperate projects. NAVPageFieldInfo just contains the FieldInfo classes for all the supported field types and a collection class.

NAVPageFieldInfo is the abstract base class
BooleanFieldInfo is the field info class for a boolean field
OptionFieldInfo is the field info class for an option field
IntFieldInfo is the field info for…

You get it – all in all, the following types are supported:

String, Decimal, DateTime, Int, Option, Boolean

Furthermore, there is a class called NAVFields, which derives from List<NAVFieldInfo>, for keeping a collection of the fields.

NAVFields has a method called PopulateFieldsCollection, which takes an object type and a fields enum type and based on this, instantiates all the NAVFieldInfo classes – let’s look at the code.

/// <summary>
/// Populate Fields Collection with NAVPageFieldInfo 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)
// 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>
/// Add a NAVPageFieldInfo for a field to the fields collection
/// </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 = VSName(field);
PropertyInfo pi = objType.GetProperty(field, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
if (pi != null)
NAVPageFieldInfo nfi = NAVPageFieldInfo.CreateNAVFieldInfo(objType, field, pi, objType.Namespace);
if (nfi != null)
// If we encounter unknown Field Types, they are just ignored

As you can see, the AddField method calls a static method on NAVPageFieldInfo to get a FieldInfo class of the right type created. That method looks like:

/// <summary>
/// Create a NAVPageFieldInfo 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>NAVPageFieldInfo or null if the type isn’t supported</returns>
public static NAVPageFieldInfo CreateNAVFieldInfo(Type objType, 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, null);
return new StringFieldInfo(field, pi, null);
PropertyInfo piSpecified = objType.GetProperty(field + “Specified”, System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
if (pi.PropertyType == typeof(decimal))
// Decimal Property
return new DecimalFieldInfo(field, pi, piSpecified);
if (pi.PropertyType == typeof(int))
// Integer Property
return new IntFieldInfo(field, pi, piSpecified);
if (pi.PropertyType == typeof(bool))
// Boolean Property
return new BooleanFieldInfo(field, pi, piSpecified);
if (pi.PropertyType == typeof(DateTime))
// DateTime Property
return new DateTimeFieldInfo(field, pi, piSpecified);
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, piSpecified, Enum.GetNames(pi.PropertyType));
return null;

No more magic!

And actually the constructor for NAVFields – takes the object type and the field type as parameters for the constructor:

public NAVFields(Type objType, Type fieldsType)
: base()
this.PopulateFieldsCollection(objType, fieldsType);

Meaning that all it takes to utilize the NAVFieldInfo subsystem is instantiating the NAVFields class, which doesn’t necessarily need a dynamic web reference helper, but could also be instantiated through:

NAVFields fields = new NAVFields(typeof(Customer), typeof(Customer_Fields));

If you have some code, which needs to access data loosely coupled, NAVFields is a great way to get going.

The other project is the NAVDynamicPageWebReference – which really is a combination of the Dynamic Web References post from April and a Page Service Helper class.

The way you get a reference to the Dynamic Web Reference is much like in the post from April:

Assembly customerPageRef = NAVPageDynamicWebReference.BuildAssemblyFromWSDL(
new Uri(“
http://localhost:7047/DynamicsNAV/WS/CRONUS%20International%20Ltd./Page/Customer”), 5000);

Based on this, you now instantiate the Service Helper with the Assembly and the name of the Page:

NAVPageServiceHelper serviceHelper = new NAVPageServiceHelper(customerPageRef, “Customer”);

Using the Page Service Helper

The Page Service Helper then uses NAVFields so that you can do stuff like:

foreach (NAVPageFieldInfo fi in serviceHelper.Fields)
Console.WriteLine(fi.field + ” ” + fi.fieldType.Name);

The properties currently in the Service Helper are:

Fields is a NAVFields (a list of NAVFieldInfo derived classes)
PrimaryKeyFields is an array NAVFieldInfo classes (from Fields) which makes out the primary key of the record
GetFieldsType returns the type of the Field enumeration
GetObjectType returns the type of the records handles through this Service
ReadMultiple reads the records matching an array of filters (calls the ReadMultiple on the Service)
CreateFilter creates a filter spec based on a field and a criteria
Read reads a record based on a primary key (creates a filter spec for the primary key and calls ReadMultiple)
Update updates a record (calls the Update method on the Service)
Create creates a record (calls the Create method on the Service)
Delete deletes a record matching a key
ReRead reads an updated instance of a record (calls the Read method on the Service with the key fields)
IsUpdated checks whether the record is updated (calls the IsUpdated method on the Service)
GetFiltersFromView creates an array of filter specs based on a view (from GETVIEW in AL Code)

An example of how to read customer 10000 and print the name would be:

object cust = serviceHelper.Read(“10000”);

Note, that you find the Field – and on the field, you call GetValue and specify the record instance.

If you need to Display the name of all customers with location code yellow you would write

ArrayList filters = new ArrayList();
filters.Add(serviceHelper.CreateFilter(“Location_Code”, “Yellow”));
object[] customers = serviceHelper.ReadMultiple(filters);
foreach (object customer in customers)

Or you could create a Customer by writing

object newcust = System.Activator.CreateInstance(serviceHelper.GetObjectType());
serviceHelper.Fields[“Name”].SetValue(newcust, “Freddy Kristiansen”, DBNull.Value);
newcust = serviceHelper.Create(newcust);

As mentioned before, the Page Service Helper was primarily created for making Edit In Excel and other projects, where you are using loosely coupled Page Web Service Access.

For a lot of other usages, this is overkill and you should rather use Web References in Visual Studio and have a strongly typed contract with the Web Service.

You can download the projects and the small test program here.


Freddy Kristiansen
PM Architect
Microsoft Dynamics NAV