Dynamic Connector – When new/custom tables won’t add web services

I have been working on a Dynamic Connector upgrade and one of the steps in the upgrade is you have to apply a hotfix.  These hotfix is for the Integration of Countries/Regions hotfix.

I think this was to fix a problem that occurred because in NAV you have a countries/region lookup and this basically just converted to a field in CRM.  sometimes this value was to big for the field in CRM.

So the hotfix meant changing the CodeUnit 5150 and adding in some lines to add the Countries/Regions.

I probably need to explain how (in my mind anyway) the Dynamic Connector works.  In NAV the Pages are used to create Web Services and these web services are used by the connector to read/write data to/from NAV.

When you tick the Enable Connector button in the Marketing Setup it kicks off the codeunit 5150 and this creates and publishes the web services   So when applying the new hotfix the code changes should have run when you ticked/unticked the enable connector button.  The changes should have added a new web service called Integration Countries/Regions.

You can see the web services by pasting the url from the adapter settings, with Services added onto the end

http://navserver:7047/customerName/WS/Services

or by opening NAV classic –>Administration–>IT ADministration–>General Setup–>Web Services

or by typing Web Services in NAV 2009/2013

Above you can see the Integration Countries/Regions has been added but the problem I was experiencing was after I made the changes to the code unit and unticked/ticked the enable connector button, it was adding in the new web service.

Something seemed to not be working because usually when you untick the enable connector button it clears all the web services.  One possible solution was that maybe someone else was logged onto NAV.

Anyway the way I got around this was to add the web services manually.

You can go to web services in NAV and add#

Object Type       Object ID       Service Name                                   Published
Page                     10                   Integration Countries/Regions      Yes

You will need to add the values above.  You will also need to add a row to the Integration Page Table (5150)

Service Name Source                     Table ID Page ID Published
Integration Countries/Regions      9             10     Yes 

If you then restart the NAV web services (just to make sure) You should be up and running.

You might sometimes experience a similar problem when adding in custom tables into the Connector

Dynamic Connector – Missing Icon

Sometimes you end up installing, uninstalling the Dynamic connector lots of times, especially in the bad old days where the connector would often forget your maps when you updated them.

Sometimes this can lead to the icon missing, I will also add they should create their own logo (BRAND IT)

I know it’s not a big deal and it’s really worthy of a blog post but it was really annoying me and it looked awful on a customer site.

if you click the change icon button it will pop up this path

%SystemRoot%\Installer\{99B04600-CB97-4A17-8175-AE7B17A52E82}\GPLogo

to find the icon you need to replace %SystemRoot% which is refers to the directory where you installed Windows.  Mine is C:\Windows but I guess you could be radical and named the drive something else (is that even possible!).  You should end up with something like this

C:\Windows\Installer\{99B04600-CB97-4A17-8175-AE7B17A52E82}\GPLogo

A word of warning you might not be able to just copy and paste the line above because your Dynamic Connector installation could have a different guid.  Although oddly I have two installations with the same guid (but on different machines).

[NAV Enumerated Values to Picklist] has encountered an error while processing key [Countries_Regions]. Sequence contains no matching element

If you get this error message in the NAV connector for the enumerated maps

[NAV Enumerated Values to Picklist] has encountered an error while processing key [Countries_Regions]. Sequence contains no matching element

This means you haven’t read the install documentation correctly (don’t ask how I know this!!) and you haven’t applied a hot fix.

Basically between version 1 and version 2 of the connector they added in a new map to the enumerated types, this was probably because I think the countries field used to cause problems when mapping, especially when a user would type in a massive long value.

You need to run through this hotfix

http://support.microsoft.com/kb/2721512

You basically add a few values to the integration code unit.

Dynamic Connector works with CRM 2011 and NAV 2013

Good news the Dynamic Connector or the Connector for Microsoft Dynamics team are keeping up the good work and development on the Dynamic Connector and already have it working with NAV 2013.

I will first add I think the Connector team need to use a better name than Connector for Microsoft Dynamics because that name makes it very difficult to search for help and we need all the help we can get when using the Dynamic Connector.

The way I search for things is type the name of the software and then the question but the name they are using makes it impossible and extremely difficult to find any help.

on a positive note I think the Dynamic Connector (I’m sticking with that name now) have put a lot more effort and development this year.

  • they have written more useful blog posts
  • put videos on the blog showing how to upgrade
  • improved the functionality (filtering)
  • fixed a load of bugs in the connector

but you still have some way to go because I upgraded the connector the other week and it totally fell apart and couldn’t read half of the maps and I still haven’t installed the connector without problems.

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 – State Code or status code is invalid

I was getting the error above when trying to add an order in CRM

the reason I was getting this error because I was using the service user to try and add records into CRM 2011.  This won’t work because the service user can only create/modify records using the SDK.

This is a new edition to the Connector for Microsoft Dynamics, so watch out.

Also once you have set the service user you cannot unset him easily.  You can change this but I’m afraid you are moving into the realms of unsupported changes.

if you try to change the service user by running the CRM configuration utility again with a different user you will set up two users who can’t create orders and yes I did find this out the hard way.

Basically there is a field on the SystemUserBase table called IsIntegrationUser .

When a user is used to run the CRM config utility it will set this value to true and assign the user the Dynamic Integration security role.

To unset you a user you need to set the IsIntegrationUser field to false

as an alternative and much safer method you could show the field on the user form and then set the value on there.

Once you have done this you can then sign in as that user and start using CRM again.

This is all well documented in the installation guide but for those of you who haven’t read it for a while it’s well worth remembering.

Don’t use your CRM user to run the CRM config utility in the connector

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 not working after Connector upgrade to V2

I was trying to set up a new connector configuration whilst updating an existing connector installation.

One of the things I haven’t worked out is to have the connector connecting multiple NAV databases that use different services.

When you setup the connector you can only point it at one service in the adapter settings, unfortunately I would like to link to multiple services but this would mean having lots of different connector installations.

So instead I was replacing one and I thought I would upgrade the connector whilst I was there.

After upgrading the connector, I then couldn’t connect to the integration services.  The first reason was I didn’t have the correct license, DOH.  The second reason was the code units hadn’t compiled?!

I was getting the error below along with quite a few other error messages

I’m not sure why but when I was connecting to the connector integration web services I was getting compiled errors.

I initially recompiled the codeunits but this didn’t resolve the problem, so I then googled the problem and found some instructions which didn’t just compile the code units but ALL. I found these instructions on information about upgrading instructions for NAV 2009 R2, so it might have been a problem with the database I was using but I thought it was R2 (but my knowledge of NAV is not extensive)

Install and Upgrade Instructions for Microsoft Dynamics NAV 2009 R2

Performing a Solution Upgrade

You perform the following steps to upgrade a solution from Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2009 R2:

  1. Back up your Microsoft Dynamics NAV 2009 SP1 database.
  2. Uninstall Microsoft Dynamics NAV 2009 SP1.When you uninstall Microsoft Dynamics NAV 2009 SP1, your database is not removed.
  3. Install Microsoft Dynamics NAV 2009 R2 components. For more information, see Install and Configure Microsoft Dynamics NAV 2009 SP1 in the MSDN Library.If you use SQL Server with Microsoft Dynamics NAV, then you must select SQL Server Database Components, which were removed when you uninstalled Microsoft Dynamics NAV 2009 SP1, and select the Demo Database subcomponent of the Database components as Not available. If you do not exclude the Demo Database subcomponent, then you may see the following error message:Fatal error during installation. A database with name Demo Database NAV (6-0) already exists.

    The message may also advise you to delete the database. If you see this error message, then you can ignore it.

  4. Open the Classic client, and then connect to your database.A dialog box opens and says that the database must be converted before you can use it with this version of Microsoft Dynamics NAV Classic. ClickOK to convert your database.
    Gg502497.Important(en-us,NAV.60).gifImportant
    If you have installed the Classic client and the database on the same computer and you are running Microsoft Dynamics NAV 2009 R2 on Windows 7, Windows Server 2008, or Windows Vista, then click Start, point to All Programs, click Microsoft Dynamics NAV 2009, and then right-click Microsoft Dynamics NAV 2009 with Microsoft SQL Server. On the shortcut menu, click Run as administrator.
  5. Recompile all objects in your database. To recompile the objects, in the Classic client, on the Tools menu, click Object Designer. Click All, press CTRL+A to select all objects, and then press F11.

After I did that then I could connect to the connector integration services without any problems and I was on to the next step of adding a non standard map.