Crucial Info for Integrating Dynamics NAV CRM with NAV 2016

Dynamics NAV Integrates with Dynamics CRM

Integration is the new buzzword in business. We want every piece of software to talk to each other like we do over our cubicle walls. In NAV 2016, Microsoft built a true integration which can talk to Dynamics CRM as easily as you can talk to your cubemate.

But the default configuration (in my opinion) is really just for demonstration purposes. But it is a starting point, and provides a basis upon which we can build.

Before I get into the limitations and bugs, we first need to cover the basics of how the Dynamics CRM connector works.

1. CRM Data is NOT stored in the NAV database.

• Those “CRM” tables you see in Object Designer are not standard NAV tables. They are simply a framework or reference to the corresponding CRM Table. You can use these tables in C/AL code but you cannot access them via the SQL. The “CRM Integration Record” table is the only table which holds CRM data and is in the NAV db.

2. Integration Table Basics

• Integration Record – Lists every record from NAV tables marked for integration.
  • In Codeunit 5150 you define which NAV tables will be included in the Integration. Every record that is one of those integrated tables will have a corresponding record in the “Integration Record” table.
  • The “Integration Record” table assigned each record an “Integration ID”.

• CRM Integration Record – Lists the GUID for every record in CRM that is/was coupled to a NAV record. This table also has an “Integration ID” field which is how records are coupled to records in the “Integration Record” table.

• Integration Table Mapping – Details and filters for each NAV<-->CRM table relationship
  • Beware! There is stock code which expects only 1 table mapping per table. However, with a fair amount of customization, you can have the same table in multiple mappings in order to accomplish 1:N or N:N table mappings. (For example, we mapped the NAV “Contact” table to CRM “Accounts”, “Contacts”, and “Leads”.)
  • There is a stock NAV page for this table but it hides a LOT of fields that you will likely want to know about.
• Integration Field Mapping – List of fields to sync for each Table Mapping relationship
  • 1:N mappings are not a problem here.
  • There is not a stock page for this. I created one and made it accessible from the Table Mapping. I also added “Field Name” & “Integration Field Name” fields to the table/page because I didn’t feel like memorizing field numbers. Silly, I know.

3. Key Events for Customization

• OnQueryPostFilterIgnoreRecord: Allows you to ignore records in either system from being synced/coupled based on code.
• OnBeforeTransferFields: before transferring data based on Field Mappings
• OnAfterTransferFields: after transferring data based on Field Mappings
• OnBeforeInsertRecord: before TryInsert statement
• OnAfterInsertRecord: after TryInsert statement
• OnBeforeModify: before TryModify statement
• OnAfterModify: after TryModify statement

4. There are 3 different ways to sync/couple records

• Record level: Manually on a list or card page: On all tables which are included in the stock integration, you can open the card or list page and there are new action buttons in the “Navigate” action tab. These buttons can be used to manually couple/uncouple/sync individual records with CRM.
  • WARNING**
    • Does not respect any filters set in the Table Mapping 
    • Skips the OnQueryPostFilter event
    • Executed as Current User

• Table level: Manually on Table Mapping page: On the Table Mapping page, you can use “Synchronize Modified Records” to couple/sync all records that match the filters/settings in the Table mapping(s) selected.
  • Executed as Current User
  • There is also a “Run Full Synchronization” button but this should only be used for demonstration purposes. (Click HERE for full explanation—read carefully!)

• Table level: Job Queue: A “Job Queue Entry” can be made for each table mapping and linked to the Table Mapping via the “Record ID to Process” field. (For example, I created an event for OnInsertTableMapping which creates the Job Queue Entry and sets it OnHold using code copied from CodeUnit 5334 function: “RecreateJobQueueEntry”.)
  • Executed as User specified in the NAV 2016 Administration console

Creating a New CRM Table in NAV & Syncing it with CRM

• You should always include all CRM tables when using the Development Shell to create a new CRM table in NAV (This allows the DevShell to create the proper relationships between the tables.)

• The Microsoft documentation for the DevShell is a little poor. I recommend using this command:
  • New-NAVCRMTable -CRMServer YourURL.crm.Dynamics.Com -Credential (Get-Credential -UserName youremail@yourcompany.com -Message "Enter Password") -EntityLogicalName systemuser,transactioncurrency,
    businessunit,pricelevel,team,
    nav_accountstatistics,account,
    contact,lead,email,phonecall,
    task,annotation -ObjectId 5340,5345,5364,
    5346,5359,5367,5341,5342,
    50030,50032,50033,50034,
    50036 -Name "CRM Systemuser","CRM Transactioncurrency",”CRM Businessunit”,”CRM Pricelevel”,”CRM Team”,”CRM Account Statistics”,"CRM Account","CRM Contact","CRM Lead",”CRM Email”,”CRM Phone Call”,”CRM Task”,”CRM Note” -OutputPath c:\CRMObjects

• For full instructions: Follow this: MSDN Article

• Here’s a short-hand To-Do list:
  • Import table using Development Shell
  • Create List Page (ALWAYS Start with list page for another CRM entity and do a SAVEAS)
  • Create Coupling Page (ALWAYS Start with coupling page for another entity and do a SAVEAS)
  • Create actions on existing NAV record list/card pages for managing one-off coupling/syncing
  • Requires Partner License
    • CU 5150 – IsIntegrationRecord
    • CU 5150 – CreateIntegrationPageList
  • Restart Server tier service
  • CU 5331 – (new function) CreateOrUpdateCoupling_______
  • CU 5331 – CreateOrUpdateCoupling (modify to reference new local function above)
  • CU 5330 – GetIntegrationTableMapping – Handle 1:N table mappings
  • CU 5330 – GetCRMEntityUrlFromRecordID – Handle 1:N table mappings
  • CU 5330 – OpenCoupledNAVRecordPage – Open NAV Record from CRM
  • CU 5330 – OpenRecordCardPage – Open NAV Record from CRM
  • CU 5330 – GetSelectedSyncDirection – Handle 1:N table mappings
  • CU 5334 – GetTableIDCRMEntityNameMapping
  • Create Table Mapping
  • Create Field Mapping
  • Add code for complex mapping issues—using the integration events published by Codeunit 5335
  • Run “Generate Integration ID’s” from CRM Connection Setup page

BUGS, GOTCHA’S, & TIPS

• NAV “Modified By” does not exist.
  • When a user changes a CRM record, this results in a sync first TO NAV. And then a sync of the same changes back to CRM! If the user is still editing the CRM record when NAV tries to sync the changes back to CRM, a sync error is created.
  • My fix:
    • Add a “Modified By” field in Table 5151 and code in the OnModify Event to update this field.
    • Add a “Modified By User filter” field in the Table mapping and code in CU 5340 to exclude the user which runs the Job Queue. (MS even comments in code where this should be done.)
    • Add code to Table 5331 functions
      • IsModifiedAfterLastSynchonizedCRMRecord
      • IsModifiedAfterLastSynchronizedRecord

-

• There are 4 Modified On fields!
  • Integration Record “Modified On” = Last date/time when record was modified in NAV
  • On every CRM Record there is a “Modified On” field that is used to determine the last date that the record was modified
  • CRM Integration Record::”Last Synch Modified On”
    • When NAV record was last synced to CRM
  • CRM Integration Record::”Last Synch CRM Modified On”
    • When CRM record was last synced to NAV

• Table Changes/Updates
  • If you make changes to a table in CRM that is synced to NAV, you’ll likely need to update the NAV table with the new definition. If you don’t you could get odd errors—sometimes even NAV permission errors.
  • Never overwrite an existing NAV “CRM” Table object with a new one from the Development Shell without first merging.
    • Fields get re-numbered based on alphabetical order
• Syncing Option fields:
  • NAV initial value of ‘’ is assumed to be the same as “Null” in CRM. In NAV the blank should be the first option. Do not add an option for this in CRM.
  • The first option in CRM should be numbered 1. The 2nd is 2, etc if you want the fields to map correctly through field mapping.
  • If you can’t set up your fields this way, then you’ll need to handle the mapping with code in the AfterTransferFields event.

• Copying Table Mapping filter fields from your Test db to Live db
  • Copy/Paste rows works great for all fields except the filters. Use SQL for this or else manually enter it.

• Integration Errors table (TAB 5339)
  • Add “Resolved” checkbox.
    • After every record sync, set code to check for matching record in error log, and set it to “Resolved”. You’ll thank me later.
  • Add fields (based on variables) to page to show more data
    • I added all 4 “Modified On” fields as well as the “names” of records
  • The “Delete All Entries” button is limited to current filters set on the page
    • This is helpful to know in combination with the “Resolved” checkbox

• Job Log – Integration Sync
  • Direction field is not to be trusted
    • I still haven’t worked out the “why!” of this (or else I’d have a fix for you)
    • Just know that the 1st job is ALWAYS “To CRM”, the 2nd is ALWAYS “From CRM” simply because that’s the order that the code is written.

• Manual Record Sync: Default Direction of sync
  • When resolving a sync error, you’ll likely do a manual sync of an individual record. In CU 5330 the function “GetSelectedSyncDirection” sets the default direction to be FromNAV. If CRM is modified since last sync and NAV is not, then this will get switched. If both systems have been modified, then it will expect you to use the default.
  • Choosing something other than the default results in additional error log record, and another message to click through.
  • My fix:
    • Create custom function to determine the default direction based on which record was updated more recently.

• Why does the Job Queue take FOREVER to sync FROM CRM?
  • If the CRM table you are syncing has more than a few records, you’ll likely notice this. It’s because the connector essentially has a “cleanup routine” in CU 5340 which scans the “CRM Integration Record” table and removes any records which have been deleted from the CRM database. It runs after every scheduled sync FROM CRM.
  • My fix:
    • I added code to 5340 so that this cleanup routine gets skipped unless current time is between midnight and 6am.

• Role Center Button “CRM Connector Mgmt.” – you’ll want quick access to each of these

• You can use codeunits to manipulate CRM data!
  • Backdoors are dangerous, but helpful. If you have CRM Online, you don’t have much of a backdoor. Until now.
• You can’t use SQL to touch CRM tables directly, but you can write C/AL code in a NAV Codeunit to work with the data your CRM tables to your heart’s content. Run your codeunit, and watch the magic happen. (hopefully!)

Here is a list of the primary objects I customized during this process

*All code and tips are provided as-is and are by no means guaranteed to work on your system.
*Before doing this with your live database, connect your NAV Test database to a CRM Sandbox and work out all the kinks. I’ve not tried to do this with different companies in the same database, but I do have both my Sandbox and my Production environments syncing data simultaneously with no issues (and both NAV dbs are on the same server).

*I highly recommend reading through the following documentation very carefully BEFORE you begin:

• NAV side: Setting Up Dynamics CRM Integration in Dynamics NAV
  There are many links in this document to other documents. Read ALL of them.
  Beware that this documentation jumps very quickly into having users perform a "Full Synchronization". As discussed above, this is dangerous—and should only be used for demonstration purposes.

• CRM side: How to: Set Up Microsoft Dynamics CRM for Integration with Dynamics NAV

• Connector Customization: Walkthrough: Customizing Microsoft Dynamics CRM Integration in Dynamics NAV

*When searching for MSDN documentation, BEWARE the documentation for the OLD Nav connector is easy to stumble on, and it will confuse you. Stick to the articles above and the embedded links within those articles.