Duplicating data across companies

Many customers with multiple companies will often have the same data repeated across all the companies.  For example, the chart of accounts (G/L Account table) may be exactly the same for all companies in the database.  This can also happen often with inventory (Item table) and other setup tables like payment terms, etc.

I see so many customers either re-entering data in every company or exporting and importing new and modified records.

PLEASE STOP!  This is a terrible waste of time and opportunity to mess things up.

NAV provides a property for tables called DataPerCompany.  If this property is set to No as shown below, then all data in this table will be shared between all companies in the database.  It’s best to set this property when first installing the system, but your table can be modified at any time to facilitate this enormous time saving step.   Just note the following cautions:

  1. the data MUST be exactly the same in all companies – meaning every field for the record is identical
  2. depending on the table, it will make sense to also set the property for child tables. For example, the Item Unit of Measure table should accompany the Item table.

Just call your expert NAV developer for help!

Screen Shot 2015-02-26 at 7.48.01 PM

Processing only report to change data

This is one of the techniques to mass change data in NAV.

  1. Create a new report, selecting the table you want to fix.
  2. Set the ProcessingOnly property as shown below

 

Screen Shot 2015-02-26 at 2.42.57 PM

 

CAUTION:  Make sure you set a filter on the dataitem to prevent accidental changes. 

// set a hard filter to prevent accidental changes
setrange(County,'TX');

*** TAKE A BACK UP OF THE TABLE in case you mess it up. Better still test in in a test environment, then run in your live environment***

Then write some code in the OnAfterGetRecord trigger

 


if "Payment Terms Code" = 'NET14' then
  "Payment Terms Code" := 'NET30';
modify;

Reading Data from Another SQL Database such as Oracle

If you want to see data from an external SQL-based application inside your NAV system, this can be done very easily without the messy process of exporting and importing data into NAV.

And when we say SQL, this means any database including SQL Server, Oracle, Access or MYSQL

The key requirement is that you are able to create a VIEW from Microsoft SQL Server Management Studio to this external database by creating a Linked Server, most likely using SQLOLEDB.  Of course, if the data is another SQL Server database, then just access that server directly from your list of servers.

Screen Shot 2015-02-26 at 2.00.42 PM

Once you have connected to the external database using a Linked Server, then create a view in SQL Server to this database to access as an example their employee table.


CREATE VIEW ExternalDataViewName
AS
SELECT FirstName, MiddleName, LastName, City
FROM LinkedServer.dbo.Employee
GO

Then create a new table in the NAV Object Designer referencing this view name and the columns selected in the view, making sure you set its Linked Object property to Yes.

Screen Shot 2015-02-26 at 2.20.45 PM

 

Once this is done, you can use this table just like any NAV table including the ability to add flowfields from other tables to this table.