CRM 2011 – Dynamic Connector Filtering is a great new feature

Version 2 of the Connector for Microsoft Dynamics (I’m still not convinced about this name, it seems to long to me).

has a great new piece of functionality. Filtering makes upgrading worthwhile alone.

There are some other good things in V2 like the fact it installs the CRM solution for you, which means all you have
to do now is run the installer.

Filtering

What is filtering.

When you create a map in the Dynamic Connector (much better name) there is a new section called Filter. In this area
you can specify lots of filters using AND functionality. This means you can now only sync records which pass your
filter criteria.

An example I recently implemented was a user wanted to only sync orders of a certain type. So I added a new dropdown
and the users would set the dropdown to a certain value – Orders in this case.

I then set a filter to only sync orders with the field value set to Orders.
This is powerful functionality and it’s easy to setup and use in the connector.

There are also a number of bug fixes in the connector so I would recommend upgrading.

The connector team have done a good job making the upgrade very easy, all you have to do is run the Connector.MSI
and it will upgrade legacy maps and running connectors.

when you open the connector which has been upgraded you will see there are two sections, CRM and CRM 2011.

The maps should work without you having to make any changes

Dynamic Connector – Excellent Dynamic Connector blog

This is a quick blog post with two aims regarding the Dynamic Connector.

The fist aim is to point you to an excellent resource for the connector.  The blog written by Konstantin is one of the best blogs on the Dynamic Connector (CRM/NAV connector as I still sometimes call it)

http://www.nav-connector.com/

He has some great blog posts

The Most useful tool for troubleshooting the connector

How to include in integration custom NAV tables – Step by Step instruction

How to update Production Dynamics Connector from Development environment

Talking about good Connector resources, I should also mention the Microsoft Dynamic Connector blog, which has recently had some good blog posts and videos.

http://blogs.msdn.com/b/dynamicsconnector/

Konstantin also left a comment on one of my connector blog posts which explained something I had wondered about.  When you tick/untick the Synchronise Connector tickbox in NAV (in Marketing Setup)

Codeunit 5150 has property “Single Instance”=Yes, which mean if you run it, it will sit in memory until you close Client.
That is why “Re-activate” Connector function not working from first time properly. Every time, after you modify this particular Codeunit, you need to reopen Client to apply modifications in YOUR client (WebServices should pickup modifications immediately).

Dynamic Connector – Stop, Step back and start again logically

I had a very frustrating last few hours, I was having a date format error with the connector.

I was mapping Job to Contract and Job Planning Line to Contract Line.

The Job to contract was working perfectly and the dates coming across without any problems, unless I had forgotten to a put a date in.

Then when I was doing the planning line to Contract line I was getting odd date format errors.  I couldn’t understand why the dates were not always working.

I thought it might be the fact that CRM was in a US date format and NAV was in UK.

I then tried to hard code the date but even this was working, now I was really stumped.

I remember reading about a development team who had a card board cut out called Alan.  When they were having problems and were stuck or extremely frustrated they would get Alan the cardboard cut out and then talk and walk him through the problem they were having.  This action of explaining the problem to someone would often help the developer to understand the problem and find out they were doing something stupid.

unfortunately or perhaps fortunately I had a colleague working with me who said add that contract line in CRM.

When I tried to do this I then found out that CRM complained about the date, hmmm maybe this isn’t a date format problem.

I then realised that I had been putting in developer date e.g.  a load of random key presses to get rough values into boxes.

What I was basically trying to do was put in a start and end date outside of the Contract start and end dates.

So the problem was nothing to do with the connector and down to me putting in random dates.

It can be tricky to think problems are always because of the connector but when you have data which isn’t syncing then you should check you can put the date in CRM or NAV to see that it’s actually valid.

CRM/NAV Connector – Crm Exception: Message: entity doesn’t contain attribute with Name = ‘name’

I have had two very frustrating days wrestling with the connector or to call it by its proper name the Connector for Microsoft Dynamics.

I was trying to map Jobs to Contracts, this worked fine, a bit of tinkering to work out what values I needed to put into Contract template lookups but this is to be expected.

I then wanted to map Job Planning Lines to Contract lines.

initially when I put in Job Planning Lines the form in NAV had a header record.  This meant that I couldn’t generate a 1 to 1 mapping between Job Planning Lines and Contract lines because the header would be one record and the individual planning lines would be child records.

Eventually to get round this I put in the form as

Job Planning Line Subform

This is the child form on the Job Planning Line form, so it excluded the header.

I then ran the Connector config utility for NAV but because I had run it before it now doubled up and I had Integration Job Planning Lines and Integration Job Planning Lines Subform.

I also did something similar in CRM and ended up with duplicates for Contracts and Contract lines.

I’m not entirely sure why this happens but its very annoying.

In the end I had to delete the maps which are held in here and will be held with the name of the organisation/company you are mapping

NAV

C:\Program Files (x86)\Microsoft Dynamics\Microsoft Dynamics Adapter\Adapters\Microsoft.Dynamics.Integration.Adapters.Nav2009\ObjectConfig

CRM

C:\Program Files (x86)\Microsoft Dynamics\Microsoft Dynamics Adapter\Adapters\Microsoft.Dynamics.Integration.Adapters.Crm2011\ObjectConfig

I then ran the config utilities again and the duplicates were removed.  I have no idea why this happens but it happens a lot and is quite annoying.

I then tried to map the Job planning lines and the contract lines now I had a one to one relationship.

I was getting this odd error

>Crm Exception: Message: ‘Contract’ entity doesn’t contain attribute with Name = ‘name’., ErrorCode: -2147217149

The reason I was getting this error is because I think the connector defaults it’s fetchXML to assume their is always a field called name.  Contract unlikely for me changed this to be title.

This just didn’t work and I could find any information on the subject until I finally found this forum

http://social.microsoft.com/Forums/en-US/crm/thread/6cf88569-3283-4902-b28b-f327ee1b27d6/

Some excellent person found a solution, I also got some help from Konstantin Levchenko who has an excellent CRM/NAV Connector blog, have a look http://nav-connector.com/

to get this to work I had to create a plugin which intercepts the fetchXML by catching RequestMultiples and then change the FetchXml to not use Name but to use DynamicsIntegrationKey and then it works.  This seems a crazy situation and why haven’t Microsoft written this as a plugin within instructions on how to do it.

       public void Execute(IServiceProvider serviceProvider)
        {
            // Obtain the execution context from the service provider.
            Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
                serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

           if (context.PrimaryEntityName.Contains("<prefixforCustomEntities>")) //Replace with your prefix
            {

                tracingService.Trace("in the if statement");
                if (context.InputParameters.Contains(("Query")))
                {

                    QueryExpression query = (QueryExpression)context.InputParameters["Query"]; ;
                    FilterExpression fexp = query.Criteria;
                    foreach (ConditionExpression condition in fexp.Conditions)
                    {
                        if (condition.AttributeName == "name")
                            condition.AttributeName = "dynamics_integrationkey";

                    }

                }
            }
        }

I also had another odd thing was the owninguser field had to be filled in and I had to put in a guid.
I had to go and look up the guid for my user
=CreateGuid(“16A37447-9242-E111-8094-00155D6A3700”)
I’m not sure why it makes me do this whilst other entities allow you to use a lookup.

CRM/NAV Connector – How to map a custom table not included in the standard maps

If you use the connector regularly you will eventually want to map a custom table which isn’t covered by the standard maps.  To do this in CRM side is easy, you run the configuration utility and add the new entity but to do this in NAV requires a bit more work.

The connector works (as I understand it) by publishing tables/pages using a web service which is used by the connector to pass information from and to NAV.

The connector catches these web service calls in the code unit 5150 – intergration management.  If you were to open this up you would see the tables used by the standard maps mentioned.

So I am going to run through the process of adding Job to the custom maps.

First I open NAV and the correct database and company.  Then

Tools –> Object Designer  –> Codeunit –> 5150 –> Design

The first function you need to change is called

EnableConnector

You can see from the screen shot below I have also added in Site Address but that was just because I needed to do that, it’s not related to adding the Job table.

You need to copy one of the lines above,  paste it into the list and then change the details so you are referencing the Job table e.g.

If you are not sure what to add and you are adding something other than a Job then the first value is the Form Name and the second value is the table name, so if you were adding Job Planning lines then it would be

  SetupWebServicePages(FORM::”Job Planning Lines”,DATABASE::”Job Planning Line”);

 

 

The next function you have to change is called SetupIntegrationTables, you will need to add a new line for each table.

You might wonder why Job is not in double quotes.  Double quotes are needed if the table name has a space in it, otherwise you can just put the table name in.

The next function we need to change is IsIntegrationRecord, you add a reference to the table you want to add.  This function basically tells the connector you want the table to be added to the connector.

Be careful about adding  a comma if the row isn’t the last value

You have finished with the code changes, close the codeunit and press yes to save and make sure the compiled checkbox is ticked.

You now need to refresh the integration webservices so your new table is added and you do this by unchecking and checking the Enable Connector checkbox which is held in Marketing Setup.

You access the Marketing Setup by going to

Administration –> Application Setup –> Sales & Marketing –> Marketing Setup

 

Untick the box, check the tickbox.  After a bit of time you should see a new integration Web Service added to the list.

You can check this by going to

Administration –> IT Administration –> General Setup –> Web Services

You can see that the page is published and the new Integration Job Card Web Service is running.

Don’t panic if the Job Web Service doesn’t appear, first I would give it a bit of time.  I would try a good old close the NAV client down and open it again (this doesn’t really do anything but it will keep you busy for 30 seconds).

Finally if you don’t see the Web Service go back and check the code changes you have made, look for spelling mistakes, putting in the form name instead of the table name, missing comma.  The problem is going to be a syntax problem.

The final part of the puzzle is too run the NAV configuration utility in the Connector and tick the new Job Card entity

So

Open the Connector for Microsoft Dynamics

Adapter Settings –> Configure Microsoft Dynamics NAV –> Next –> fill in login details –> select Company –> tick Job Card

I think you might want to just enable the Job Card or the custom entity of your choice only on a specific integration.

hopefully the walk through above should help people add custom entities

CRM/NAV Connector – Video – New features of V2 of the Connector

Here is an excellent video from msdyncomm which runs through the new features in V2 of the Connector for Microsoft Dynamics.

This is an indepth video which is 23 minutes long and goes through some of the features in detail like the new filtering functionality (which is excellent by the way)

CRM/NAV Connector – Video – Connector for Microsoft Dynamics Installation

A very useful video to help anyone who is trying to install the CRM/NAV connector

The connector installation has to be done in a certain order and if you turn the maps on in an incorrect order then you could receive hundreds or thousands of errors when maps try to run without the prerequisites maps having been setup and run first.

CRM/NAV Connector suddenly stopped working

The CRM/NAV connector always likes to keep me on my toes and today it achieved the feet but suddenly stopping working.

One minute it’s working and the next minute none of the maps are running.

So what happened.

Looking at the logs (a NAV/CRM connector users best friend) I noticed there were some errors about a field I had deleted  on the CRM entity but was still mentioned in one of the maps.

Oddly it wasn’t a map that was running and the field didn’t have a value being mapped.

I restarted the CRM/NAV Connector and then looked at the event viewer applications log.  When you start and stop the connector it writes a value to the log.

Here I found an error

An unexpected error occurred while loading maps for site [Metaphorix \ Metaphorix]. Object reference not set to an instance of an object.

Now if you read my blog or have been searching for CRM/NAV connector errors then you will know I have seen this error before

https://crmbusiness.wordpress.com/2012/01/19/crmnav-connector-object-reference-not-set-to-an-instance-of-an-object/

This highlights the confusion you can have when using the connector the same error can mean something different has broken and the error messages are not easy to understand.

What I think this error was complaining about  a field which was mapped which no longer existed.

To resolve this problem you have to run the configuration tool again to remap all the fields correctly.

I will add a word of warning, the connector configuration tool might forget if you had added entities to the mapping, so check to make sure they are ticked.

So there we go another error and another solution.  hopefully if the Dynamic community can keep blogging solutions to the CRM/NAV connector problems then it will make using it easier.

CRM/NAV Connector – Object reference not set to an instance of an object

Another day working with the CRM/NAV connector and another error.

One of the frustrating aspects of the CRM/NAV connector is transferring the maps from one server to another (e.g. test to live).

Almost every time I have tried to do this it hasn’t worked and I have had to copy all the maps by hand.

Today I was trying it and then when I tried to open the CRM/NAV connector I was getting the error

“Object reference not set to an instance of an object”

The problem with the error above is it doesn’t really give you an indication what exactly is wrong.

If you search for the problem you will find a microsoft page – Trouble: integrating the connector

Resolving issues when reinstalling Microsoft Dynamics NAV and starting the Connector for Microsoft Dynamics

When you reinstall Microsoft Dynamics NAV and then try to start the Connector for Microsoft Dynamics, you may see the following message:

Object reference not set to an instance of an object.

To resolve this error, enable the Connector in Microsoft Dynamics NAV, verify that Microsoft Dynamics NAV web services are turned on, and then start the Connector for Microsoft Dynamics again.

This didn’t solve the problem because the web service was running.

The solution to the problem this time I found on this page

http://nav-connector.com/?p=88

Basically the problem was caused when I created a map from file, it had created rows on the MSDI database (the database the connector uses) with nulls in.

Thank fully the blog page above supplies the solution

  1. Open SQL Management Studio for server where NAV Connector database was installed
  2. Find Maps table in MSDI database
  3. Open Maps table in Edit mode
  4. Delete all records where ReaderBinder and WriterBinder is NULL

Another problem I had after this was one CRM/NAV connector integration could import maps from file and another one couldn’t.

so I had to import them into one and then copy them to the other but this didn’t actually work because it was creating nulls in the MSDI database, which means I have to type them out manually again.

I have to say I have spent days trying to configure the connector and searching for solutions to mysterious problems.

The connector might be free to download but it costs you a lot in time trying to work out how to configure it and why it’s not working.

NAV/CRM Dynamics Connector – Error – Object reference not set to an instance of an object

I got this error today

NAV/CRM Dynamics Connector – Object reference not set to an instance of an object

this was very frustrating because I hadn’t changed anything, literately tried to turn on the connector to make some changes.

I’m not sure what had happened but the connector had got itself into a right mess.  I tried to repair it and it couldn’t write any values to the MSDI database.

It was complaining about the user privileges but the user had admin rights on the machine and the database.

I tried uninstalling and reinstalling but this didn’t work at all because then the service was appearing in the list of services, the database was still dodgy.

In the end to resolve this problem I had to delete the MSDI database in SQL Server management studio.

What I surmise was the problem was the database had for some reason become corrupted and if you don’t delete the database I think it still leaves it there to use, so when I reinstalled the connector it was still using (or trying to) the corrupted database.

I did find a couple of other blog posts which put forward different answers but they seemed like more effort than deleting and starting again (make sure you back up those maps)

but I will paste them below if you need to try them

this one seems quite promising but it didn’t work for me

http://nav-connector.com/?p=88

 

http://community.dynamics.com/product/ax/axtechnical/b/axsupport/archive/2011/05/24/object-reference-not-set-to-an-instance-of-an-object-while-testing-the-settings-or-running-maps-in-the-ax-crm-connector.aspx