Presenting the Data Exchange Framework

Earlier this month Arend-Jan contacted me about being a presenter on Dutch Dynamics Community NAV Event for March 2016.  Of course I was honored and after a moments thought I accepted.

The following presentation was repeated two times for close to 140 people in total.  I had a great time and am thankful for the opportunity.

I promised to share one Codeunit – that general mapping Codeunit – and here it is: Codeunit 60000.

If you download the presentation you can also read my notes.  They should help you better understand the whole story.

 

Data Exchange Framework enhancements

I have made several enhancements to the Data Exchange Framework in NAV 2016.  Previously I wrote about text or a comma separated file import and about access to a local data file when creating a xml/json structure.  Both those enhancements are included in the objects attached to this post.

I have needed to handle JSON files where the actual data is not in the node value but in the node name.  To support this I added a boolean field to the Data Exchange Column Definition table (1223).

ConvertNodeNameToValue

To support this I added the same field to the Data Exchange Field Mapping table (1225) as a flow field, calculating the value from the definition table.

Codeunit 1203 is used to import XML and JSON files into the Data Exchange Field table.  I made two changes to the InsertColumn.  First, I added a parameter that accepts the node name and if the above switch is set to true I insert the node name into the column value instead of the node value.  The other change is that instead of finding only the first Data Exchange Column Definition I loop through all of them.  This allows me to create more than one definition based of the same XML node and therefore import into multiple columns from the same node.

To enable this scenario in the Currency Exchange Update Service I made changes to the Data Exchange Field Mapping Buffer table (1265) and the Data Exchange Setup Subform page.  More on that later.

A JSON file without a single root element will cause an error in the standard code.  Inspired my Nikola Kukrika on NAVTechDays 2015 I made changes to Codeunit 1237 utilizing the TryFunction to solve this problem.  To top this of I made a simple change to the Currency Exchange Rate Service Card page (1651) and table (1650) to allow JSON file type.

Having completed these changes I can now use a web service that delivers JSON in my Currency Exchange Rate Service.

CurrencyLayerJSON

Also inspired by Microsoft I added a transformation type to the Transformation Rule table (1237) to convert the Unix Timestamp (seconds since January 1st 1970 UTC) to a date.

UnixTimestamp

All objects and deltas are attached

DEF-Enhancements

NAV 2016 Data Exchange – file import

As promised it is time to pick more things that I have improved for NAV 2016.  Still on the Data Exchange, and looking at file import.  We can import a text file; fixed and delimited, with different encoding.

By using Codeunit 1240 for the External Data Handling action we are able to get any text file into the Data Exchange framework.  It will simply prompt the user for a local file name.

Microsoft shipped Codeunit 1241 to read a flat file.  I decided to change that a bit with two enhancements.  First, I wanted to be able to read a delimited file and second, I wanted to be able to use different encoding.

The Codeunit name is “Fixed File Import” and just the name change show the difference.  My version is named “Fixed/Delimited File Import”.

There are settings in the Data Exchange that I wanted to be able to support.

FileDetails

The standard Codeunit only reads the MSDOS File Encoding with this code

Changed

to read the four different text encoding that are supported by the Data Exchange Setup.

The PharseLine section in the standard Codeunit has this simple loop to read the fixed file.

Extending this and adding two functions gives me the ability to import both fixed and variable text file.

More to come.  Stay tuned…

 

Series of improvements/enhanchements for Dynamics NAV 2016

During my preparation for Directions sessions I studied the new enhanced Data Exchange Framework in NAV 2016.  I have done a number of things to enhance the functionality and to enable functionality that should be available according to the possible setup.

Over the next coming weeks I plan to publish these things and hope that they will be useful for some of you.  How knows, Microsoft might even decide that some of these fixes and features should be included in the standard product.

The first thing I want to suggest is regarding the functionality of getting the XML structure of a file into the Data Exchange Framework.  Going into the definition for Yahoo Currency Exchange Rate Services and try the “Get File Structure” function.

DataExchangeGetStructure

Then next page will allow you to enter a URL to the XML file.

xmlpath

The problem we face here is that the Path must be accessible by the NAV Service Tier.  So, what to do if you have the XML file on your desktop?

Utilizing the new TryFunction method.  I decided to add the new function to the File Management Codeunit (419).

getanypath

Throw any path; local path, server path, url to the MakeServerTempFile function and you will receive a Server File Name that is accessible by the Service Tier.  This function will

  1. If path if found on the server, return that path
  2. If path is found on the windows client computer, upload the file to the server
  3. Try to download the file with web client to the server
  4. Ask the user to upload the file

Add this function to Codeunits 1235 and 1237 to make sure that the Get XML Structure works as expected.

getxml

getjson

Building a clean database – remove not licensed objects

I just got a question from a client;

Gunnar,
Do you have a “King Kong” license that will allow you to delete any object?  It appears our development license does not have the rights to some of the newer LS Retail objects and I need to create a CRONUS database with just our stuff.

Well, I don’t have a “King Kong” license.  That is only for Microsoft.

There is a way to solve this dilemma.  It will take a few steps.

Start with we have two databases, one with the data we need (LSRetail), another with the application we need (CRONUS).

After the process is completed the LSRetail database will not be usable as a standalone database, so make a copy if you need one.  A new database will be created, CRONUS_APP.  To clean up it is safe to delete both these databases.

The following powershell script has two options.  Option 1 is to have the company data imported into the CRONUS database in the end.  This option requires a server instance running on the CRONUS database.  Option 2 is to create a new database with SQL Management Studio and merge the CRONUS application and the LSRetail data into that one.

[code lang=”powershell”]
$CronusDatabaseName = "CRONUS" # Database with destination Application
$CRONUSServerInstance = "DynamicsNAV80" # Instance for destination Application if using option 1
$LSRetailDatabaseName = "LSRETAIL" # LS Retail Demo Database, database with company data
$EmptyDatabaseName = "CRONUS WITH COMPANYDATA" # Create a new empty database using SQL Management Studio if using option 2
$SQLServerName = "SQL2014"
$SQLServerInstance = "NAVDEMO" # Set blank for default instance

$AppDatabaseName = $CronusDatabaseName + "_APP"
$ServiceAccount = $env:USERDOMAIN + "\" + $env:USERNAME
$ServerInstance = "UPGRADE"
$NavDataFile = (Join-Path $env:TEMP "NAVmerge.navdata")

$SelectOption = "2"

#Export Application from CRONUS Database to Application Database
Export-NAVApplication -DatabaseServer $SQLServerName -DatabaseInstance $SQLServerInstance -DatabaseName $CronusDatabaseName -DestinationDatabaseName $AppDatabaseName -ServiceAccount $ServiceAccount -Force

#Setup a temporary Server Instance for the new database
Get-Credential | New-NAVServerInstance -ServerInstance $ServerInstance -ManagementServicesPort 33555 -ClientServicesPort 33556 -SOAPServicesPort 33557 -ODataServicesPort 33558 -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $AppDatabaseName -ServiceAccount User -Force
Set-NAVServerConfiguration -ServerInstance $ServerInstance -KeyName "Multitenant" -KeyValue "true" -Force
Set-NAVServerInstance -ServerInstance $ServerInstance -Start -Force

#Prepare LSRetailDatabase for new configuration
Remove-NAVApplication -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Force

#Mount and Sync LSRetailDatabase as a tenant
Mount-NAVTenant -ServerInstance $ServerInstance -DatabaseInstance $SQLServerInstance -DatabaseServer $SQLServerName -DatabaseName $LSRetailDatabaseName -Id DEFAULT -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite -Force
Sync-NAVTenant -ServerInstance $ServerInstance -Tenant DEFAULT -Mode ForceSync -Force

if (Test-Path $NavDataFile)
{
Remove-Item -Path $NavDataFile -Force
}

#Option 1, Copy Company data to the original CRONUS database. Requies a service running on the CRONUS database
if ($SelectOption -eq "1")
{
Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -Force
Import-NAVData -ServerInstance $CRONUSServerInstance -FilePath $NavDataFile -AllCompanies -Force
}
#Option 2, Import into the new empty database created by SQL Management Studio
if ($SelectOption -eq "2")
{

Export-NAVData -ServerInstance $ServerInstance -Tenant DEFAULT -AllCompanies -FilePath $NavDataFile -IncludeApplication -IncludeApplicationData -IncludeGlobalData -Force
if ($SQLServerInstance -eq "")
{
Import-NAVData -DatabaseServer $SQLServerName -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication

}
else
{
Import-NAVData -DatabaseServer ($SQLServerName + "\" + $SQLServerInstance) -DatabaseName $EmptyDatabaseName -FilePath $NavDataFile -AllCompanies -IncludeApplicationData -IncludeGlobalData -IncludeApplication
}

}

Set-NAVServerInstance -ServerInstance $ServerInstance -Stop -Force
Remove-NAVServerInstance -ServerInstance $ServerInstance -Force

if (Test-Path $NavDataFile)
{
Remove-Item -Path $NavDataFile -Force
}
[/code]

To walk you through what happens;

  • Application from CRONUS is exported into CRONUS_APP database
  • New Service Instance is created for CRONUS_APP database
  • Service Instance is changed to Multi Tenant and started
  • Application is removed from LSRetail database
  • LSRetail database is mounted as a tenant for CRONUS_APP database
  • LSRetail database structure is force-synched to CRONUS_APP application
  • Data from CRONUS_APP and LSRetail tenant is exported to NAVData file
  • NAVData file is imported into an empty database or the existing CRONUS database

 

OData in NAV 2013 R2

Open Data Protocol (OData) is a data access protocol initially defined by Microsoft.  In NAV 2013 Microsoft first added a support for OData.  On the Developer Network Microsoft has a few walk-throughs aimed to get us started in using both SOAP and OData web services.

To get started with OData first make sure that the OData service is enabled on your developement server.

ODataEnabled

As you can see on the image above I also enable the use of NTLM Authentication.  In a production environment OData should use SSL as described in this walk-through from Microsoft.

I want to share with you a few points that I have found out and are not obvious in the walk-throughs.

When an external application makes a OData request NAV will behave the same way as a Windows Client would do.  The server will do the login routine by executing trigger 1 in Codeunit 1, CompanyOpen and when the request is finishing the server executes the CompanyClose trigger.  If the request if for a Page object then all the page triggers are executed, OnInit, OnOpenPage and so on.

The OData will only show the fields added to the Page or to the Query with the table primary key fields added.  Lets take a closer look at how the Page object works with OData.

In the OnOpenPage trigger and in the SourceTableView property it is possible to add filters.  These filters will apply to the OData stream and you will not be able to insert data into NAV outside of these filters – same functionality as if you where using the Page in the Windows Client.  The properties; Editable, InsertAllowed, ModifyAllowed and DeleteAllowed all work with OData.  Lets for example look at Page 39 – General Journal.  In the OnOpenPage trigger the code is filtering the table data with a Template Name and a Batch Name.

Page39

From OData, the variable OpenedFromBatch will always be False so the first template and batch for the PAGE::”General Journal” will always be selected and new entries will always be inserted into that journal.  This also means that it is not possible to use Page 39 to read the journal lines from any other journal then the first one.  Fields that are not visible in a Page are visible and usable in OData.

When creating a new record the code in the OnNewRecord trigger is executed.

OnNewRecord

This will all work fine for the first template and batch.  The AutoSplitKey property is also active so as long as you are fine with inserting in to the default journal then you can use this Page for your OData web service.

The easiest way is still to create a new page dedicated to the web service functionality, show the primary key fields in the page and skip the OnOpenPage and the OnNewRecord code.  I use the OnNewRecord code to put in default values for the table I am inserting into.

On the Microsoft web site walk-through it is shown how to create a new customer, look for a customer and modify a customer.

I have found that I want to add one line to that example

[code] NAV nav = new NAV(new Uri("http://localhost:7048/DynamicsNAV/OData/Company(‘CRONUS%20International%20Ltd.’)"));
nav.Credentials = CredentialCache.DefaultNetworkCredentials;
nav.IgnoreResourceNotFoundException = true;[/code]

Without IgnoreResourceNotFoundException the following code example will return an exception if the customer is not found within the given filter.

[code lang=”csharp”] private static Customer GetCustomer(NAV nav, string customerNo)
{
var customers = (from c in nav.Customer
where c.No == customerNo
select c);
foreach (Customer customer in customers)
return customer;
return null;
}[/code]

By combining a Get function like this with a New or Modify function it is easy to update the existing value for any given table in the database.

[code lang=”csharp”]private static Boolean AddNewDefaultDimensionCodeValue(NAV nav, int tableNo, string no, string dimensionCode, string dimensionCodeValue)
{
DefaultDimensions existingDefaultDimension = GetDefaultDimension(nav, tableNo, no, dimensionCode);
if (existingDefaultDimension == null)
{
DefaultDimensions newDefaultDimension = new DefaultDimensions();
newDefaultDimension.Table_ID = tableNo;
newDefaultDimension.No = no;
newDefaultDimension.Dimension_Code = dimensionCode;
newDefaultDimension.Dimension_Value_Code = dimensionCodeValue;
nav.AddToDefaultDimensions(newDefaultDimension);
nav.SaveChanges();
return true;
}
else
{
existingDefaultDimension.Dimension_Value_Code = dimensionCodeValue;
nav.UpdateObject(existingDefaultDimension);
nav.SaveChanges();
return false;
}
}
private static DefaultDimensions GetDefaultDimension(NAV nav, int tableNo, string no, string dimensionCode)
{
var dimensionValues = (from d in nav.DefaultDimensions
where d.Table_ID == tableNo && d.No == no && d.Dimension_Code == dimensionCode
select d);
foreach (DefaultDimensions dimensionValue in dimensionValues)
return dimensionValue;
return null;
}
[/code]

Remember, that without the SaveChanges nothing will be updated in NAV.

Now go ahead and use OData to integrate NAV with all your external systems and devices.  Good luck.