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

Applications:

• eCommerce
• Integrations

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

• 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

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)
   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’.
   6. Make sure that table name in SQL and ‘ExternalName’ should be same.
   7. 
   7. ‘ExternalSchema’ (in my case) is said here as ‘dbo’
   8. TABLE MAPPING DONE 
3. FIELD MAPPING-
   1. SQL fields are shown below with its datatype:
   2. 
   3. Make fields which you require from external SQL in NAV.
   4. 
   5. Mapping between two table’s field are done on Field properties of each field.
   6. 
   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. 
   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! 
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. 
   4. 
   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 

Note:

*- Not tried yet

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

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