Reason


  • Data synchronization between NAV and other applications (whose back end is SQL)

Applications:


  • eCommerce
  • Integrations

Who can do it?


  • Knowledge on NAV Development
  • Knowledge on NAV Table objects
  • Basic knowledge on database

Pre-requisites:


  • NAV 2016 or above
  • Access to NAV Development environment and NAV developer’s license
  • SQL Server (credentials required for both the cases)
  • Table Name and field data type required for external database

Steps:


  1. IDENTIFICATION-
    1. External database can be in same / different database server.
    2. Suppose we have ‘TestData’ as SQL database on database server ‘ABC\XYZ001’ (not NAV database)Fig1
    3. With two tables ‘dbo.Customers’ and ‘dbo.Products_Staging’
    4. Now, what do you think is required to connect this external database with NAV?
      1. May be same table structure? (yes)
      2. Connectivity between NAV and external database? (yes)
      3. LAN wire? (off course silly!!)
  2. TABLE MAPPING-
    1. In NAV, we can either use existing table or create new table (suggested)
    2. Now open new table which you’ve created in NAV
    3. Go to Table properties and find something called ‘TableType’ (value of this property varies, depending on NAV’s version)
    4. Change ‘TableType’ to ‘ExternalSQL’ and see new properties getting visible below
    5. Identify table name of SQL which you’ll connect with and put its name in ‘ExternalName’.Fig2
    6. Make sure that table name in SQL and ‘ExternalName’ should be same.
    7. Fig3
    8. ‘ExternalSchema’ (in my case) is said here as ‘dbo’
    9. TABLE MAPPING DONE surprise
  3. FIELD MAPPING-
    1. SQL fields are shown below with its datatype:
    2. Fig4
    3. Make fields which you require from external SQL in NAV.
    4. Fig5
    5. Mapping between two table’s field are done on Field properties of each field.
    6. Fig6
    7. Some notes:
      1. ‘Name’ -> can be different from ‘ExternalName’
      2. ‘Data Type’ -> for this you need to understand basic datatypes in NAV as compared to SQL data types
    8. Complete field mapping process for all required fields in NAV
    9. Is field mapping done? (Nope)
    10. You need to export NAV table in .txt format / .xml format (Why? Ans: Because of hidden property called ‘ExternalType’. You’ll not find this even after NAV Table object is exported in .txt / .xml. You need to manually add this property in exported object file.)
    11. Fig7
    12. This has been made easier for CRM Integrated tables. You can generate NAV Table’s .txt file from PowerShell command (Anxious? Let’s see it later)
    13. After manual addition of ‘ExternalType’ in .txt / .xml file, save it and import it in NAV and compile.
    14. Voila! FIELD MAPPING done! laugh
  4. CONNECTION
    1. Connection between NAV Table and SQL Table is not direct and should be registered. NAV Service needs to know this registration before connection is made.
    2. We’ll create a Codeunit in NAV which does this work. (same is readily available for CRM Integration. You may change the codeunit according to your requirement. Never hard code anything. :D
    3. Fig8
    4. Fig9
    5. Question: Where are the credentials?
      1. Ans: For Windows Credentials -> Database credentials can be waved off.
      2. Ans: For User Credentials -> You can add two more keywords and values on DatabaseConnectionString. (DatabaseConnectionString:= ‘Data Source=\;Initial Catalog=TestData;UserName=;Password=)
      3. Make sure you encrypt / decrypt password properly in NAV.
    6. Save, Compile and execute the Codeunit.
    7. If unsuccessful, you can Google the error message.
    8. You may face issue due to wrong Credentials
      1. Wrong datasource
      2. ExternalType mismatch or incorrect in step 3. g.
      3. ExternalName mismatch or incorrect in step 3. d.
    9. If successful, you can Run the new table created in step 2. b.
    10. Realtime data synchronization happens between NAV and external SQL.
    11. SUCCESS yes

enlightenedNote:

*- Not tried yet

# - These steps don’t guarantee 100% that it’ll work in your environment or you’re not doing anything wrong


mail Feel free to mail me on manish.kutar@ecanarys.com if you need any further information on this

Related

NAV – Connecting Microsoft Dynamics CRM customized entities

This blog is intended to see how Microsoft Dynamics NAV (here onwards NAV) can be connected to exter...

Read More >

What's New in Microsoft Dynamics NAV 2017

We were hearing about Dynamics NAV 2017 since long time that it will be releasing by the end of 2016...

Read More >

How to run NAV 2015 and NAV 2016 Administration tool side by side

Everyone would have installed NAV 2016 RTM and would have faced the same problem, i.e, you couldn...

Read More >

What’s new: Extension for Microsoft Dynamics NAV 2016

We all have seen the situation where we need to put in lot of efforts to modify the standard NAV obj...

Read More >

Identify and stop idle Dynamics NAV sessions using User Session Control Addon

Since the advent of RTC clients and 3-tier architecture of Dynamics NAV, it’s been a question ...

Read More >

Autofill RecVariable Name in DynamicsNAV 2015

I discovered something quite interesting couple of week back and I thought it was worth sharing.When...

Read More >

Phenomenal growth of Microsoft Dynamics NAV

There has been phenomenal growth in terms of organizations adopting Microsoft Dynamics NAV in the la...

Read More >

How to Remove the Departments Action Button in NAV 2015

Some customers may not want to use Departments menu available. We need to have some option to remove...

Read More >

How to print Header and Details (Lines) in different pages of a RDLC Report in NAV 2013 R2

We usually print header and details section of a document on same page, what if we want to print on ...

Read More >

How to print TransHeader and TransFooter in Microsoft Dynamics NAV RDLC reports

We know that TransHeader and TransFooter section types were available in NAV 2009 version but there ...

Read More >

Share

Post a Comment

  • Recent
  • Popular
  • Tag
Monthly Archive
Subscribe
Name

Contact Us
  • *
  • *