- Data synchronization between NAV and other applications (whose back end is SQL)
Who can do it?
- 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
- External database can be in same / different database server.
- Suppose we have ‘TestData’ as SQL database on database server ‘ABCXYZ001’ (not NAV database)
- With two tables ‘dbo.Customers’ and ‘dbo.Products_Staging’
- Now, what do you think is required to connect this external database with NAV?
- May be same table structure? (yes)
- Connectivity between NAV and external database? (yes)
- LAN wire? (off course silly!!)
- TABLE MAPPING-
- In NAV, we can either use existing table or create new table (suggested)
- Now open new table which you’ve created in NAV
- Go to Table properties and find something called ‘TableType’ (value of this property varies, depending on NAV’s version)
- Change ‘TableType’ to ‘ExternalSQL’ and see new properties getting visible below
- Identify table name of SQL which you’ll connect with and put its name in ‘ExternalName’.
- Make sure that table name in SQL and ‘ExternalName’ should be same.
- ‘ExternalSchema’ (in my case) is said here as ‘dbo’
- TABLE MAPPING DONE
- FIELD MAPPING-
- SQL fields are shown below with its datatype:
- Make fields which you require from external SQL in NAV.
- Mapping between two table’s field are done on Field properties of each field.
- Some notes:
- ‘Name’ -> can be different from ‘ExternalName’
- ‘Data Type’ -> for this you need to understand basic datatypes in NAV as compared to SQL data types
- Complete field mapping process for all required fields in NAV
- Is field mapping done? (Nope)
- 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.)
- 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)
- After manual addition of ‘ExternalType’ in .txt / .xml file, save it and import it in NAV and compile.
- Voila! FIELD MAPPING done!
- 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.
- 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. 😀
- Question: Where are the credentials?
- Ans: For Windows Credentials -> Database credentials can be waved off.
- Ans: For User Credentials -> You can add two more keywords and values on DatabaseConnectionString. (DatabaseConnectionString:= ‘Data Source=<DBServer><DBInstance>;Initial Catalog=TestData;UserName=<User Name>;Password=<Password>’)
- Make sure you encrypt / decrypt password properly in NAV.
- Save, Compile and execute the Codeunit.
- If unsuccessful, you can Google the error message.
- You may face issue due to wrong Credentials
- Wrong datasource
- ExternalType mismatch or incorrect in step 3. g.
- ExternalName mismatch or incorrect in step 3. d.
- If successful, you can Run the new table created in step 2. b.
- Realtime data synchronization happens between NAV and external SQL.
*- 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 firstname.lastname@example.org if you need any further information on this