Feeding the Dataport

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


In a previous post, we introduced the Universal Dataport  – a tool that can import a simple text file into any NAV table, complete with validations and change log.  The Universal Dataport (UDP) is a useful tool for importing data once NAV is in production, but is even more useful when doing the initial imports from legacy systems during new implementations.  The tricky part of these initial imports is actually obtaining and mapping the legacy data to NAV before feeding it into the UDP.  A number of years ago we developed a fairly simple spreadsheet-based tool to help define field mappings from legacy systems and to generate a file that easily feeds the UDP.

Often during implementations you won’t import data just once – normally we’ll do an initial import into a pilot or staging NAV database to highlight missing data elements, aid in discovery sessions, and perform initial training.  This initial import is never good enough to “go live” with, and is usually drastically out of date by the time go-live comes around.  So what ends up happening is that we do a number of imports, tweaking the data cleanup rules and the mappings as the requirements get more refined.  To be efficient, we’d rather not start from scratch with raw exports from the legacy system and a ton of manual editing and reformatting each time we need to do an import.

This is where the spreadsheet (DMSDataMap to be specific) comes in – it lets you create a mapping between NAV fields and the legacy data that you can save, tweak, and reuse multiple times.  (Being able to save the mapping rules is also great if there is a post-implementation audit by an external auditor).

The DMSDataMap uses the power of SQL to map NAV fields to a legacy field or expression.  This, of course, means that the legacy data needs to be in an ODBC-compliant database, with SQL Server being the preferred choice (“ODBC-compliant” does not mean we have the same range of SQL functionality provided by SQL Server).  “But”, you say, “most of the time my data comes to me in Excel or text files”.  No problem – SQL Server has a handy SSIS wizard to automagically import Excel spreadsheets (and other files) into new tables.  See this MSDN article on the wizard (there are also lots of other step-by-step examples out there).

So whether you started with the data in an existing ODBC-compliant database or you moved the data to SQL, we now have the data in a repository we can access with the DMSDataMap tools.  Let’s walk through an example of importing item data from a legacy table already in SQL.  First, we open the workbook and enable the macros (the macros are available in a text file with the download for you to examine before enabling them if you like).  The workbook has three sheets in it: a “Notes” sheet for notes on this mapping, a “Mapping” sheet for the actual field mapping, and a “Query” sheet for defining the source database and exporting data.

<<spreadsheet pic>>

The first thing we’ll do is bring in the NAV field names.  Open the Object Designer and design table 27 (Item).  In the table designer, show the “Field Type” column and filter on “Normal” to exclude any flow fields and flow filters.

<<filtered fields pic>>

Select and copy all the rows in the designer and paste into a blank sheet, then copy the list of field names into the DataMap “Mapping” sheet.  You should end up with something like the list below (which you can sort alphabetically to make it easier to find the fields).

<<nav fields pic>>

The next step is to map the available and/or required fields (or expressions) from the legacy table to the appropriate NAV fields, and indicate which ones need to be validated by the UDP as they’re imported.  Here’s a sample of some of the mapped fields (blank mappings have been filtered out).

<<mapping pic>>

Let’s take a closer look at these mappings:
  • No. : this is straightforward – the “PART_NO” field from the legacy table maps directly to the NAV “No.” field.
  • Description and Description 2: slightly trickier – here we’re converting from the single “DESCRIPTION” field in the legacy table to two NAV fields using the SQL substring function to split the text and limit the number of characters in each field.
  • Base Unit of Measure: another simple 1:1 mapping, but this time we’re validating the field.  Validating this field populates the Purchase and Sales unit of measure fields.  (Keep in mind that the UDP will automatically create the “Item Unit of Measure” record when we import this field if it doesn’t exist).
  • Replenishment System: Trickier yet.  The legacy system uses “M” to identify manufactured items, and “P” or other codes for purchased items.  The CASE statement allows us to do a simple conversion from the legacy field value to what NAV expects.
  • Vendor No.: Trickiest so far.  In this example, the default vendor for each item is stored in a separate table.  This mapping retrieves that mapping through the use of a subquery to the PART_VEND table.  Note that you can use joins, but usually a simple subquery is much easier to keep track of.
Once the field mapping is complete, we can now do a little setup and see what kind of file gets generated.  On the “Query” sheet, the cells in light green need to be filled in:

<<query sheet pic>>
  • Connection String: this is where you specify the connection details for the legacy database (not the NAV database).  Click the ellipses button for a wizard to step you through building the string.
  • “SELECT” modifier: Optional. Anything you enter here will appear immediately after the “SELECT” keyword in the generated query. Use this for modifiers like “TOP 10” to only retrieve 10 records for testing purposes.
  • Tables (“FROM” clause): Enter the table or tables from the legacy database that you’re querying.  You can specify any number of JOINs and the join conditions here if you have multiple tables.
  • “WHERE” clause: Optional. Use this to restrict which records you get from the legacy table.
  • “ORDER BY” clause: Optional.  Use this to specify the order of the returned data.
  • Export File: Enter the file name you will be exporting the data to.  Click the ellipses button to open a file selection dialog.
  • Pre-Query Lines: Optional.  Enter a full query here that you want to run before the generated export query runs.  For example, you may want to generate some temporary tables for the main query to use.
  • Post-Query Lines: Optional.  Enter a full query here that you want to run after the generated export query runs.  For example, you may want to drop temporary tables here.
  • Other export queries: Optional.  The data retrieved from queries entered here will be appended to the export file.  Only useful if you’re using custom, multi-table dataports.

Here’s an example of the fields populated with the details for our legacy table:

<<query sheet – populated pic>>

Now that we have the settings filled in, the next step is to either export the data or generate the query to see what it looks like and possibly to test it in SQL Management Studio (SSMS).  To see what the query will look like, click “Create Query”.  This generates the query and asks if you’d like to copy it to the clipboard, which can be handy for pasting into SSMS for testing.

To export the data, click (you guessed it) “Export Data”.  This will generate the query, execute it against the database specified in the connection string, and create a tab-delimited export file that’s ready for the UDP.  After the file is generated, you will be prompted to view it – if you click “Yes”, Notepad is launched to show the file:

<<export file pic>>

At this point you can run the UDP in NAV, specify the item table to import, select the exported file, and you’re done.

The real value with this tool is that we now have a data mapping that can be saved, easily modified, and executed any time the data changes or we refine our understanding of the mappings to NAV.  We can also reuse the mappings any time we convert from the same legacy system in the future (for example, we can have a library of mappings from AccPac or Syteline to NAV).

Hopefully you find the tool as useful as we do. Let us know if you have any comments or suggestions.

<<Extra notes for the end:
* some other options – if you’re using SQL to SQL, why not just populate data directly? Validations.
* we also have some generic dataports for importing item and general journals, including item tracking (lot and serial #’s) and dimensions.  We’ll publish them in an upcoming post, or give us a shout and we’ll fire ‘em off to you.
* stay tuned for an upcoming post on an XMLPort version of the UDP for NAV2013
** make sure macros are provided as separate file
>>

All I Want for Christmas is an ERP

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


We all know Santa Claus exists.  It’s a fact.  However, did you know that it takes more than magic to ensure his operations run at an optimal level?  By using an ERP system Santa has all the tools and visibility to guarantee that the world’s (nice) children receive their presents on Christmas morning.

NAV 2013 Report Issues (Solved)

Anyone who is thinking about deploying NAV 2013 via Citrix, Terminal Services, or virtual desktops may get a bit of a surprise: none of the reports will print properly unless you take additional measures.  The issue is not specifically a NAV bug, it's due to RDLC/SSRS issues when running remote sessions.  This has been an issue for a couple of years and there's no word from Microsoft on a fix yet.  There are, however, a few work-arounds.

UPDATE: There are now two fixes for this issue.  Run Server 2012 (as per NAV Team Blog), or install the Microsoft hotfix for 2008/Win7 (thanks to Duilio and Marco in the comments for the tip).  We've installed the hotfix and it works well.

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


The NAV 2013 Universal XMLport

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com

In a previous post we introduced a dataport that could be used to quickly bring data into any NAV table without any coding. It's a great little tool that is almost immediately obsolete with the release of NAV 2013. Luckily, we have a solution for that: the DMS Universal XMLport. 

Through Thick and Thin (Pt.2): Behind the Scenes

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


Going back to my Tour de France analogy from the previous post, this installment is all about the action that takes place behind the scenes to make sure that the rider is successful in his bid to win races.  Just like any well functioning and cohesive team, every individual has specific responsibilities and tasks that need to be carried out.  Specific to cycling, most teams competing at a high level have the following personnel:

Through Thick and Thin: The Case for Thin Client Computing

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


As I’m watching the 2012 Tour de France, I've started thinking about how different the race would be if participants were required to carry all of their own food, replacement and maintenance parts, tools, and any other related gear for the 3,500 km journey from Liege, Belgium to Paris, France.  The riders would be burdened with bags or maybe even small trailers attached to their bikes, stretching the trek from a few weeks to a few months. 

How to Use MVC.Net on the Dynamics NAV Database Structure

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


For the example files associated with this tutorial, visit the Mibuso downloads.

So you’ve been asked to make a web portal for some Dynamics NAV data.  You also have a desire to stay current and would like to take advantage of some of the newer web development frameworks out there.  You’ve seen, or heard great things about how quickly you can get CRUD with some of the popular MVC frameworks out there like pages working with MVC.Net or Ruby on Rails.

Web Integration Options in Microsoft NAV

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


You’re a developer, and you’ve been asked to make a part of MS Dynamics NAV web accessible.  Great!  It’s out of the box in NAV 2013.  

Uh oh, it’s not 2013 yet?  No problem, you still have options and should probably consider licensing, architecture, and integration interfaces soon.

Software Build vs. Buy: What's Best for You?

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


I’m currently in the process of buying a new house.  I have two options: buy an older home that has as many of the necessities I’m looking for as possible, or build a new house that is customized to my preferences.  The pros and cons are obvious for both, but making a decision between them is not quite that easy.

The Key to a Secure Password

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


Whether you’re an aspiring computer hacker or just someone who values their privacy, password security is an inherent concern of the internet era.  If you value your data then you should value a good password.  The password is one of the gateways to your information, confidential or otherwise, and therefore must be as secure as possible.

Introducing the DMS Universal DataPort

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


Data is the currency of the information age, so it should come as no surprise one of the more frequent tasks integrators, users, and system administrators encounter when implementing or maintaining a Microsoft Dynamic NAV installation is data migration.  The four common approaches to getting the data from where it resides into NAV are: manual entry in the NAV client, using SQL server’s database tools, using NAV's Excel mapping, or via a NAV Dataport or XMLport.

In-Depth Bank Reconciliations in Microsoft NAV

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


I like it when thing lines up as they're supposed to.  Maybe it’s a slight degree of obsessive-compulsive disorder on my part, but my personal well-being is dependent upon keeping certain aspects of my life in a state of tranquility.

When it comes to my operations, and especially my finances, I don't want to come across any unexpected surprises.  At the end of the month, I need to know that my bank statements all sync up with my bank transactions.  I don’t think that’s too much to ask???  In Microsoft Dynamics NAV, performing a bank reconciliation is quick and easy to do and helps ensure that all is in order, and here’s how:

Barcodes, We Love You

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


Unless you’ve never been in a grocery store or shopping mall, barcodes aren’t exactly a novel technology.  We’re all familiar with ubiquitous UPC codes, but in recent years the advent of complex barcode technologies and the proliferation of QR codes for social purposes has people reevaluating just how important barcodes can be.

How to Balance Project Agility and Predicitability

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


A project manager is often seen dancing in the wilderness of scope creep, balancing resourcing constraints on the head of the budgetary needle, dealing with the inevitable boogeymen on the road to integration.  Your role as a PM, in short, is to remove obstacles that enable real progress. 

Funding the Digital Economy

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


 To Invest or Not to Invest…It’s Not Even a Question


The debate on whether or not to invest in times of economic uncertainty has been raging in the years since the recent financial crisis.  In Canada, the latest budget has revealed the extent to which our government is willing to fund growth projects.  Budget 2012 has set aside nearly $500M ($400M via the private sector and $100M via government) for venture capital funding to invest in early-stage risk capital, and to support the creation of large-scale venture capital funds led by the private sector.

The Power of Collective Knowledge

Please note that all DMS news and blog content has been moved to the main website at www.dynms.com for a categorized view.

All utilities and downloads are available at www.dmsiworks.com


At DMS we strongly believe in the power of collective knowledge.  In the history of mankind, never has there been more information so readily available.  Today there’s almost anything can be learned through intuitive searching, message boards, and web videos.  Granted, it may take more than a minute to learn the quantum physics (or does it? Minute Physics) but as our collective information sources grow, so does our ability to leverage the experience of the people around us.