I want to make a visual datamodel for documentation purposes. A model that has tables, fields, field types and relations between fields (something like Access). So, I am looking for a easy way to do this. It doesn’t have to be a fancy tool, but a quick way to generate a visual model of some custom made tables. This subject probably has been discussed before, but I’d like to know if anyone has had any solutions for this typical Navision problem.
We have used the Visio 2002 Reverse Engineer function on a Navi-SQL database. You can prepare model diagrams and listings but you don’t get the ERD stuff with visual links between table keys, etc. Lloyd Freiday Open Door Technology Calgary, AB, Canada
Navision has announced a feature in the next version of Attain (3.10) to document the table relations in the SQL Option. The relations can be read and used by for example external reporting tools but they are not enforced by the SQL Server. The Enterprise Manager contains a nice tool for displaying the table relations as diagrams.
The feature you mention can also be used nicely by the Visio Reverse Engineering diagramer, including the relationships (the standard SQL Server ODBC or OLEDB data source is used).
Jens, I don’t have any experience with the combination of Navision and SQL. The databases I work with are Navision. Do you mean I can see the diagrams in MS SQL, or do I have to wait for the next version (3.10). Robert, do you use Vision to make the datamodel and import this into SQL or into Navision. I heard that Navision on a SQL database has all business rules still in the objects in Navision and the tables in SQL are independent/loose tables only, without any table relations. Is this not true? Can I use Visio get a datamodel out of an existing Navision or SQL atabase?
The feature Jens is talking about, for Attain version 3.10, allows the creation of foreign key constraints, which are disabled but visible, in SQL Server for the TableRelation properties in Attain (i.e. those relations that are valid in SQL). Visio etc can then see these constraints and diagram them, as it would with an Access or other SQL datasource. They are not used for referential integrity or cascaded deletes/updates. The business logic is still handled by Attain as usual - there is no desire to turn all A/L programmers into T-SQL programmers.
If you need something quick 
, create a codeunit in navision that reads thru the ‘Fields’ system table,creates a access database & tables using automation(DAO). Using automation you can build link b/w tables using the ‘RelationTableNo’ column in the ‘Fields’ table. Ideal solution would be is to allow the user to select relevant tables in navision and it automatically starts up Visio, creates a diagram with relations (again using the Fields table). Visio has components that you can use for automation. Edited by - brians on 2002 Aug 06 03:28:14
forgot to mention, both solutions i mentioned will be one direction only. You cannot make table changes in Access/Visio and update Navision automatically.
HI, Has there been any advances made using the Jason Prickett Visio Data Modeler from www.mynavision.net. It really looks so promising then if you start to use more tables it becomes a little messy. If anybody can or has taken his idea further please let us know. Thanks
Has anyone used the Attain 3.10 feature mentioned above? Could you tell me how. Alternatively does anyone know how to reference a Table Relation that has Conditions in it? e.g. Sales Line.No. - the Field table does not hold this.
The 3.10 feature to create SQL Server foreign key constraints (relationships) from Attain table relations is enabled in the File/Database/Create and Alter window in the Integration tab. You can then build models from the relationships using Enterprise Manager, Visio or other tools able to read this meta data from the database.
If you have a impuls Workbench running, you can also “visualize” certain object-relations … Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP
Impuls Workbench is no longer supported - but replaced by Navision Attain Developers Toolkit (that basically just is an updated version of IW). Best regards, Erik P. Ernst, webmaster
As some already pointed out, when you can get your native database on SQL Server you can make a visual (access like) model from the enterprise manager using a diagram wizard.
Ok so I have loaded up the SQL relations and am making a SQL diagram - but it seems to be missing some of the relationships. These always seem to be ones where there is either an IF condition or a WHERE condition on the Table-Relation. Any suggestions? Will I have to manually add these relationships? (Would this affect Navision?)
quote:
Originally posted by wlgnles: Ok so I have loaded up the SQL relations and am making a SQL diagram - but it seems to be missing some of the relationships. These always seem to be ones where there is either an IF condition or a WHERE condition on the Table-Relation. Any suggestions? Will I have to manually add these relationships? (Would this affect Navision?)
Probably because native SQL does not know conditional relationship if I’m correct. So the diagram is not showing it.
SQL Server foreign keys constraints are fixed and cannot contain where conditions with a relationship. For IF TableRelations in Attain, all the relations are created as separate constraints in SQL Server, so these are possible.
Hi, I managed to create E/R model for Microsoft Navision Go to http://www.NaviTools.com I hope you find it useful.
Take a look at the following link… http://www.mibuso.com/dlinfo.asp?FileID=152 There you will find a Visio Data modeller specific for Navision [:D]
Hi, Jason’s report creates a very useful diagram. You only have to layout tables and you are done (there are 808 tables in Navision 4.0 [:o]). As posted in other posts this report doesn’t include table relations where IFs are used. I’ve managed to include these too. However Jason gave me an idea how to create ER diagram for Navision and I’ll be always grateful for this. It would be great if Jason and his colleagues from MS Visio department would get together and develop a Visio Add-In for Navision?