Mergetool – Love it!

Still my all time favorite tool for working with Microsoft Dynamics NAV is MergeTool.

Long known as a tool for comparing customized objects in NAV, then providing the ability to merge changes into a new version, there are some aspects which I feel are overlooked.

  1. Scoping tool – no easier way to evaluate the impact of customizations on a customer database.  The Contrasts and Compare Logs provide an instant perspective on how much work has been done to modify and add objects in a database.
  2. Documentation aid – many developers remove the Modified flag and sadly some fail to update the version tag.  The Contrast will provide a true reading on whether an object has changed and allow one to update version tags and documentation triggers properly.
  3. Analysis tool – as I do not sell NAV anymore, my customers are all new to me when I start a project.  Sadly, most customizations done to NAV lack design documentation to help understand why a mod. was done.  Using the Where-Used features quickly reveals all the objects affected by a contrast.  Then some simple detective work will reveal why the object in question was added or modified.
  4. NAV Version coverage – while newer versions of NAV have some cool commands for merging object changes, Mergetool is still the only effective way of evaluating any NAV version, so still provides maximum ROI for developers who work with many customers.

 

CRM to NAV integration alternative

Most users will use Scribe adapters to connect data between CRM and NAV.  These adapters are very useful, especially with CRM’s restrictions on how data may be written into its database.

Coming back from CRM to NAV, the adapters also do a great job, except for when NAS is needed to support the process.

FULL DISCLOSURE – I DO NOT LIKE NAS AND NEVER HAVE.  If you are happy with NAS, you can stop right here.

In general I do not like how NAS requires multiple instances for companies, is prone to crash and is prone to do so without the user knowing something went wrong.  Hopefully with the use of web services, the Scribe adapter will be more effective.

Nonetheless for CRM/NAV integration there are other options which should be considered:

  1. use a SQL Server integration table to pass data between CRM and NAV, especially when you have CRM programmer resources at hand.  This completely eliminates the need for Scribe to pass the data between applications and eliminates the need for several Scribe scripts.  You will need an NAV and a CRM programmer to update and process the data from the integration table into each respective application.
  2. use an industry standard application like WINAUTOMATION to process batch jobs instead of the NAV scheduler (which is NAS-dependent).  I have only used this in the Classic client, but it works exceptionally well for automating nightly processes, especially when you have to start and stop processes in multiple companies.

With web services, I would look to use something like Winautomation to process jobs, then execute scripts which are not NAS dependent to fire off functions inside NAV to process data.

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.

 

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.

SQL SELECT Statements for NAV tables

Yikes!  It’s wonderful to have NAV data in SQL Server tables, but getting to this data using a SELECT statement can be enormously challenging because of the NAV data “nuances”.  Let’s check our major table with critical data, the G/L Entry table in the Cronus Canada company.  or CRONUS Canada, Inc_$G_L Entry.

LOVELY!

  • every table is prefixed by the company name.  Company names with spaces between words will cause further problems generating a SELECT statement
  • Names in NAV are not “SQL friendly”.  In NAV the table is “G/L Entry”.  In SQL it’s “G_L Entry”

Then when we actually start examining columns, we see the same naming issues plus a few additional nuances:

  • option fields such as Document Type in NAV are shown as “Payment” or “Invoice”, but in SQL are integer values
  • Boolean fields in NAV are Yes or No, but 0 or 1 in SQL
  • Flowfields are not real fields at all as their data is in an underlying detail table and requires a nested SELECT statement to see the value.

So a fairly simple SELECT statement becomes a slight adventure as shown below.  Note the need for brackets to handle the spaces and underscores to replace special characters.

SELECT [G_L Account No_]
,[Posting Date]
,[Document Type]
,[Document No_]
,[Description]
,[Amount]
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
FROM [NavR2Demo].[dbo].[CRONUS Canada, Inc_$G_L Entry]

SAVE YOURSELF THE HEADACHE!

Check out the Jet Pivotier product from Jet Reports for tools to build complex SQL SELECT statements in a jiff!.

From Classic to Role-tailored Client (RTC)

With the release of NAV 2015 and the expiration of support for NAV 2009, the time to consider a comprehensive upgrade is here.

This is a MAJOR upgrade and requires careful analysis by experienced NAV consultant/developers, because:

  1. there are some significant functionality changes in the data base such as the migration of the North America kitting feature into the base manufacturing code.
  2. the Classic client is no longer supported (except for developers who still use the old interface).  This means that users will need training on how to use the new interface
  3. the upgrade involves several steps such as from NAV5 to NAV 2009 to NAV 2009 R2 to NAV 2013 to NAV 2015.
  4. in this case BOTH executable and database versions must be upgraded

Because of the many steps involved, a careful analysis of your customizations is necessary, as it may also make sense to re-implement NAV versus upgrading through all the steps above.

Please insist on access to a highly experienced NAV developer to provide the best path forward.

 

Upgrading Executable Version Only

There are a couple of cases to always upgrade your executable version.

  1. from any version prior to NAV 5.x, SP1 to NAV 2009 – this executable upgrade will:
    1. reduce the size of your SQL Server database immediately by up to 40% because of the elimination of SIFT index tables
    2. provide a better compiler with colors for syntax checking
    3. make your system faster because of fine-tuning for SQL Server
  2. from NAV 2013 to NAV 2015 – because NAV 2015 is the version where the benefits of the role-tailored client are fully provided.