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.

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!.