When we create a new table in Navision , what is SQL doing?

cunny

Hi all,

Does anybody have a clue about what is sql doing when we create a new table in Navision.

I want to create a new table in SQL and view it in Navision! I think there must be a way to get it!

Regards,

cunny

deltoid

I can't say for sure but if you look at a SQL Navision database you will see the following.

Say your database has two companies, then you will have a different SQL table for each company.

ie.
company1$customer
company2$customer

The table also has an entry in the object table in Navision.

kriki

[Topic moved from Navision forum to SQL General forum]

cunny

hi deltoid,

Thanks for your reply.

I have create another table in sql which has the exactly format with a Navision table in SQL. But I still can not find it in Navision. And I also note that the diffreence between a typical sql table and a user table from Navision is there will be always a [timestamp] filed as the first filed in the user table from Navision and typical sql table does not have this field.

Further more, When we create a table in sql we can not specify the id and the name at one time and we can just specify one of them. And these are the requirements of creating table in Navision.

any idear?

kine

For NAV to be able to work with SQL table, you need to create table definition within NAV. Without this you cannot work with table on SQL (or you can just through ADO). If you want to create table on SQL first, than you can connect this into NAV through LinkedObject property on the table within NAV. It allows you to connect the NAV table definition to e.g. SQL View. Search this forum for more info about LinkedObject.

I do not recommend to create tables outside NAV and connecting them into NAV if you do not have strong requirement why to do that...

Marije_Brummel

Never create tables in SQL Directly.

Step 1; Open NAV and login as a Superuser
Step 2; Make sure you have a good license
Step 3; Open the object designer
Step 4; Push NEW
Step 5; Create some fields
Step 6; Save the table with an ID

Now look in SQL server and see that your table is created.

You can also connect NAV tabledefinitions to SQL Views but this is not recommended unless you are an expert in this field.

DenSter

You can't create a table on SQL Server that NAV will recognize as a table. Creating NAV tables should always be done through the NAV IDE.

Search this forum on 'linked tables' and you should find posts about how to create a view on SQL Server and link that to a NAV table.

PerJuhl

Hi

Take a look in Application Designers Guide page 105. (5.0)

The rule: Companyname@Customer only apply if the option
"DataprCompany" = Yes.

If "DataprCompany"=No SQL TabelName = NavisionTableName
unless you used special caracter like "ÆØÅ" this convert to "_"
eg. the "Company" Table.

BR Per

Waldo

The reason why you should do it through NAV IDE is because the IDE is also going to create a record (with BLOB) in the Object Table. There is the definition that NAV is going to use for its business logic and all... .

Try Denster's search. The same logic for linking Views can be applied on tables, created in SQL, but off course, you don't have any benefit of first creating the table in SQL, and then link it