Accessing tables created out of Navision.

zulq

Is there any way to access tables created outside Nav? When I create tables using SQL management studio or something else I don't see them in Nav.
Any information please!!!

garak

basicly: If you create a table in NAV, NAV will create this table as sql table (Company$Tablename) and stores also this information in the Object table.
So, when you have a table designed outside of NAV (but in the same database or a other) you can also access to this table in NAV. How to?

Open the Objectdesigner in NAV.
Create a new table with all the fields in your NavisionOutTable.
Then set the tableproperty to LinkedObject -> YES.
Store this table.

to learn more -> read the onlinehelp of this property, search the forum for LinkedObject or read the
"Application Designer's Guide" pdf.

Regards

jlandeen

A few additional pointers:
1) If you set DataPerCompany to NO on a table then it will not have the format of <CompanyName>$<TableName>. This can cut down on the administration of a table in Navision/SQL if you have multiple companies in the same database.

2) you can also hook views up in a similar manner. I've found that this works really well if you want to allow Navision to read/write information to another SQL table. Writes can be handled with updatable views.

bbrown

Don't create table in the NAV database from outside the Object Designer. Even if you intend to access them from outside of NAV.

jlandeen

That's a very good point - simplest way to make sure any staging table or view in SQL is built properly is to define it in Navision first and then make sure that anything built matches the table defined by Navision.

kine

There are some exceptions, like views connected to tables on linked servers, with calculated fields etc... ;-)

bbrown

kine wrote:

There are some exceptions, like views connected to tables on linked servers, with calculated fields etc... ;-)

I was not referring to views. Those you need to create in SQL then link to a NAV table object. I was only referring to actual tables.

kine

Sorry, just my minor "out of sync" (what I read, what I understand and what I am writing)

jlandeen

...no wait. I do think that even when you're building a view in SQL. Start by creating the table in Navision. Then build a view that exactly matches the table signature that Navision created. I find this helps ensure that you have names and datatypes that are guaranteed to match (like "No." in Navision being stored as "No_" in SQL View).

bbrown

jlandeen wrote:

...no wait. I do think that even when you're building a view in SQL. Start by creating the table in Navision. Then build a view that exactly matches the table signature that Navision created. I find this helps ensure that you have names and datatypes that are guaranteed to match (like "No." in Navision being stored as "No_" in SQL View).

How do you address the object name conflict that will arise? If you first create the table object it will create a SQL table CompanyName$NewTable. Now when you go to create the view you will get an error because you can't have two objects with the same name. How are you avoiding this?

jlandeen

Ok so here's the steps I've gone about in the past to build a Table based on a SQL View, note that I used DataperCompany = NO.

1) Create and design new table in Navision called "IntegrationStagingTable" that has all of the fields defined that I need
2) Create and design a new SQL View called "View_1" (via SQL Management studio or any other tool) that exactly matches the SQL data types (eg. Nav boolean = SQL tinyint, Nav text = SQL varchar, etc.), field names and order match exactly to the SQL version of "IntegrationStagingTable". You can copy field names, types, etc. from the SQL table definition of "IntegrationStagingTable".
3) Redesign "IntegrationStagingTable" in Navision and set the LinkedObject Property to YES.
4) Via SQL Manager (or other SQL Tool) delete "IntegrationStagingTable" table and rename "View_1" to "IntegrationStagingTable".

Remember depending on what and how you work with the view (i.e. if you're writing to it or it is connected to remote data tables via linked servers) you may need to changed the LinkedInTransaction property accordingly (check the Application Designers Guide for more details on that as I don't remember the specifics).

I find this approach is pretty simple to follow and I like to use it when building views as having the initial table helps give me a template to write my view against. Normally I just start by scripting out the table that is created by SQL and then change the script so it builds a view instead of a table. Helps save on typing and reduce coding errors.