Transfer data from NAV to Other Database

casanova

hi all
I'm using NAV 2013 and I have a question
can NAV send data to other database?
let's say i want to post sales order become posted sales invoice, and then this posted invoiced should be inserted in other database...
can we do that without using web service?

thx

uwe

Hi casanova,

you can link a NAV table definition to an external database table and then use INSERT in NAV.

Define a new table in NAV to represent the table in your external database. In this table's definition the property "LinkedObject" must be "yes".

You need an appropriate SQL TABLE in a database your SQL Server can access and an appropriate SQL VIEW in the NAV database.

HTH

uwe

casanova

Hi Uwe,
let me confirm with you,correct me if I'm wrong
Let's say in Other DB, I have Table ABC

so what you mean is...
I create table ABC (the structure must exactly the same??) in NAV DB
and then In this table's definition the property "LinkedObject" I change become "yes".
by then i can insert data to other database

is that correct?
thanks

uwe

Hi casanova,

quite close.

The way I doit is:

1. in NAV: create the table ABC in NAV as an ordinary NAV table. LinkedObject is "no".This creates a SQL table in NAV DB. Save the table and close the table editor.
2. now start SQL Server Management Console
3. find the SQL table that belongs to ABC in [NAV DB] and create a table [Copy of ABC] with exactly the same definition in [Other DB]. If you want your data stored per company you will need several tables in [Other DB]. Maybe you mangle the Company Name into the table name like NAV does.
I's a good idea to keep a copy of the script that you use to create the SQL table until you are sure that everything works fine.
4. in [NAV DB] create a VIEW into Other DB in which you select all fields of [Other DB].[Copy of ABC].
Important: the name of this view must be exactly the same as the name of the table that Navision created for your ABC table. This includes the Company Name formerly mangled into the table name if you want your data stored per company. You will Need one view per Company.

5. Got the copy of the script mentionned above in a safe place? Ok. Now delete in [NAV DB] the original SQL table in SQL Server Management Console.

Now you should have a SQL VIEW in [NAV DB] that behaves exactly like the SQL table that Navision had formerly created.

6. return to NAV designer and edit the Definition of ABC. Set LinkedObject to "yes". Save and there you are.

I hope I got the sequence of steps right in memory.

casanova

uwe wrote:

Hi casanova,

quite close.

The way I doit is:

1. in NAV: create the table ABC in NAV as an ordinary NAV table. LinkedObject is "no".This creates a SQL table in NAV DB. Save the table and close the table editor.
2. now start SQL Server Management Console
3. find the SQL table that belongs to ABC in [NAV DB] and create a table [Copy of ABC] with exactly the same definition in [Other DB]. If you want your data stored per company you will need several tables in [Other DB]. Maybe you mangle the Company Name into the table name like NAV does.
I's a good idea to keep a copy of the script that you use to create the SQL table until you are sure that everything works fine.
4. in [NAV DB] create a VIEW into Other DB in which you select all fields of [Other DB].[Copy of ABC].
Important: the name of this view must be exactly the same as the name of the table that Navision created for your ABC table. This includes the Company Name formerly mangled into the table name if you want your data stored per company. You will Need one view per Company.

5. Got the copy of the script mentionned above in a safe place? Ok. Now delete in [NAV DB] the original SQL table in SQL Server Management Console.

Now you should have a SQL VIEW in [NAV DB] that behaves exactly like the SQL table that Navision had formerly created.

6. return to NAV designer and edit the Definition of ABC. Set LinkedObject to "yes". Save and there you are.

I hope I got the sequence of steps right in memory.

Hi Uwe
wow ! very clear instruction
now i get it
thanks a lot