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

What’s in my record? Using the Zoom feature.

You are always looking at data through a form or a page. But what if you want to see all the data for this customer record even if it is not displayed on the card? For this you can use the zoom feature to see every field. Press Ctrl+F8 in classic and using the About This Page menu (Ctrl+Alt+F1) in RTC.

For example, the customer card looks like this with the form, then the page.

Screen Shot 2015-02-26 at 7.25.17 PM Screen Shot 2015-02-26 at 7.25.02 PM

The zoomed data will show everything recorded for this customer (RTC is shown first this time – just testing). This is a great way to diagnose issues and to report problems to your support staff. I use this a lot during testing

Screen Shot 2015-02-26 at 7.33.51 PM Screen Shot 2015-02-26 at 7.33.20 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;

Change Data in a Field in Multiple Records

Ever had a colleague enter or import a journal and found that the description has the same type in 1,000 lines?

How about importing 5,000 customers and then needing to change the terms code for every user in the state of Texas?

There are several ways to change data in  multiple records:

  1. change every record individually – STOP ALREADY!
  2. change one record, then cursor down and press F8 – this is nice but will get silly after about the 10th record
  3. CLASSIC CLIENT ONLY – use the Replace option (Ctrl-H) from your Edit menu.  Just like in Excel, you can replace a value in every row in a given column from one value to another.  Notes:
    1. you can do this in any form (Yes, even a Card form).
    2. if for a table, and you have access to Object Designer, you can run a table and do the same
  4. Write a Processing report to cycle through all the records and change the value from one to another.  See the cookbook for an example.