Updates to my Object Renumbering Tool

Back in the end of 2014 I published a renumbering tool for NAV objects.  Using DotNet I was able to increase the renumbering speed for text object files dramatically.

Since then I have been asked if I could upgrade it to work with IDs and Field Numbers.

Now I have.

What’s more, it is also on GitHub.

The Process functions are the following;

  • Read Object Lines – Creates renumbering lines base on the objects in the selected object file.
  • Suggest IDs – Suggest new object numbers in the range from 50.000 based on the available objects in the current license.
  • Read from Excel – Reads object renumbering lines from Excel Sheet created with the Write to Excel process.
  • Write to Excel – Writes current renumbering lines to a new Excel Sheet to me managed within Excel and reread into the renumbering lines.
  • Renumber Using Lines – Prompts for a file to read and for a new file to save with renumbered objects based on the rules in the renumbering lines.
  • Renumber Using Controls – Prompts for a file to read and for a new file to save with renumbered objects based on the rules in the control IDs setup.

I have done some fixes to the renumbering function and have added support for the EventSubscriber.

Go to GitHub to download Page and Table 50000, try this out and submit improvements.

When I am processing an object file I have it open in my text editor.  When I see something to renumber I update the control ranges and execute the renumbering process, reading and writing to the same object file.  My editor will reload the file and I can see the results immediately.

 

NAV Http Web Request

In my post about Json and Rest web services I showed how to use the standard Codeunit no. 1297 for web service communication.

Today I was asked to do this in NAV 2015.  I must admit, I forgot that this Codeunit was not available in NAV 2015.

So I made one.

This one has identical functionality to the one delivered with NAV 2016.  To catch and handle the errors I use the NAV Web Request Add-in that I created and published here on my blog.

Now I can easily move that Json code down to NAV 2015.

Download here –> COD1297-NAV2015

Using REST/Json web services from NAV

One of my most popular blog entry is the one about Json.  I have also had some questions outside this website about this topic.

This week I got a task.  We need to communicate with a payment service that uses REST web services and Json file format.

posapi

I got a document describing the service.  Some methods use GET and some use POST.  Here is how I did this.

In the heart of it all I use Codeunit 1297, “Http Web Request Mgt.”.

getaccesstoken

Every time we talk to this POS API we send an Access Token.  If we don’t have the token in memory (single instance Codeunit), we need to get a new one.  That is what the above code does.

The ParameterMgt Codeunit is what I want to focus on.  You can see that I start by inserting my “Authorization Key” into the RequestBodyBlob.  As usual, I use the TempBlob.Blob to get and set my unstructured data.

setapirequest

The interesting part here is that I use an XMLPort to create the data I need to post to the Api.

apiauthenticatexml

A simple one in this example, but nothing says it can’t be complex.  Then I convert the Xml to Json with a single function.

converttojson

The last TRUE variable means the the Document Element will be skipped and the Json will look like it is supposed to.

apikey

The REST service response is Json.

token

And to read the Json response we take a look at the GetAccessToken function.

getaccesstokenfunction

Here I start by converting from Json to Xml.

convertfromjson

And make sure my Document Element name is “posApi”.

apiaccesstokenxml

And I have the result.

As you can see from the documentation some of the Json data is more complex.  This method will work nevertheless.

For more complex date I always create tables that matches the Json structure.  These table I use temporary through the whole process so the don’t need to be licensed tables.  Here is an example where this XMLPORT

getauthorization

will read this Json

getauthorizationjson

I suggest that with our current NAV this is the easiest way to handle REST web services and Json.

 

Compilation error for Reports

In a brand new installation of Dynamics NAV 2016 CU2 I am getting errors when compiling reports.

AppCrash

After selecting to close the program I get this error

CrashDetails

Details in the code section below.

I did some digging and ended in a page describing a known issue for security updates 3098779 and 3097997 for the .NET Framework 4.5.1 and 4.5.2 after you install the .NET Framework 4.6 on Windows 8.1, Windows RT 8.1, and Windows Server 2012 R2.

After applying resolution to scenario 1 I am good to go.

Inspired by a Microsoft Developer

Yes it happens.

When reading the C/AL code in NAV written by other developers you normally pick up smart way to do things.  Yesterday I got one.

In my solutions I have been using a function to check if a string is numeric and another function to extract the numeric value from a string.

IsNumeric

All good and well.  Working fine so far.  But if you can write each function with a single line, would that not be better ?

NewIsNumeric

Thanks for the inspiration Microsoft.

CALCFIELDS and BLOB in newer NAV versions

Just wanted to put this out there.

In older NAV versions you needed CALCFIELDS on a BLOB field before HASVALUE.  Now you can ask for HASVALUE before CALCFIELDS.

Having unsaved data in a BLOB field and doing CALCFIELDS will try to get the saved data into the BLOB field.  Try a code like this:

[code]
TempBlob.INIT;
IF FileMgt.BLOBImport(TempBlob,’Image.png’) = ” THEN EXIT;
Storage.Blob := TempBlob.Blob;
MESSAGE(‘Blob has value: %1’,Storage.HASVALUE);
Storage.CALCFIELDS(Blob);
MESSAGE(‘Blob has value: %1’,Storage.HASVALUE);
[/code]

This CALCFIELDS will remove the data from the BLOB field.

Doing an INSERT or MODIFY before CALCFIELDS will fix the error, or simply remove the CALCFIELDS line.

JSON meets NAV

I have been using SOAP services over the last years.  Only recently the RESTful web services have become more and more popular in my integration work.  Wikipedia says:

In computing, Representational State Transfer (REST) is a software architecture style for building scalable web services. REST gives a coordinated set of constraints to the design of components in a distributed hypermedia system that can lead to a higher performing and more maintainable architecture.

RESTful systems typically, but not always, communicate over the Hypertext Transfer Protocol with the same HTTP verbs (GET, POST, PUT, DELETE, etc.) which web browsers use to retrieve web pages and to send data to remote servers. REST interfaces usually involve collections of resources with identifiers, for example /people/paul, which can be operated upon using standard verbs, such as DELETE /people/paul.

As we are used to XML as the body for our SOAP messages we can also use XML as the body for a RESTful web service.  I just finished writing a code to communicate with Azure from NAV.  This communication was using RESTful web services and XML.

So, what is JSON?  Wikipedia says:

JSON, (canonically pronounced /ˈdʒeɪsən/ JAY-sən; sometimes JavaScript Object Notation), is an open standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the primary data format used for asynchronous browser/server communication (AJAJ), largely replacing XML (used by AJAX).

Although originally derived from the JavaScript scripting language, JSON is a language-independent data format. Code for parsing and generating JSON data is readily available in many programming languages.

The JSON format was originally specified by Douglas Crockford. It is currently described by two competing standards, RFC 7159 and ECMA-404. The ECMA standard is minimal, describing only the allowed grammar syntax, whereas the RFC also provides some semantic and security considerations. The official Internet media type for JSON is application/json. The JSON filename extension is .json.

With JSON it is possible to deliver similar data structure as with XML.  JSON on the other hand requires a much less metadata.  Here is an example JSON from Wikipedia:

[code lang=”javascript”]{
"firstName": "John",
"lastName": "Smith",
"isAlive": true,
"age": 25,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "office",
"number": "646 555-4567"
}
],
"children": [],
"spouse": null
}[/code]

There is not a good support for JSON in native .NET from Microsoft.  However, with Visual Studio, Microsoft installs an external DLL in to the folder “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies”

Newtonsoft

With this Json.NET in Dynamics NAV Add-ins folder we now have some way to handle JSON files.  Using this Add-in I created a NAV Codeunit to manage JSON text.

This Codeunit contains functions to build a JSON document, like

[code lang=”csharp”]
StartJSon;
AddToJSon(‘newssn’,CompanyInformation."Registration No.");
AddToJSon(‘billtossn’,BillToCustNo);
AddToJSon(‘newcompanyname’,CompanyInformation.Name);
AddToJSon(‘newemail’,CompanyInformation."E-Mail");
AddToJSon(‘register_einvoice’,EInvoiceEnabled);
AddToJSon(‘register_supdoc’,SupDocEnabled);
AddToJSon(‘register_natreg’,NRLookupEnabled);
EndJSon;
Json := Json.Copy(GetJSon);[/code]

A function to import values from a JSON document to a temporary table, like

[code lang=”csharp”]
ReadJSon(String,TempPostingExchField);

WITH TempPostingExchField DO BEGIN
SETCURRENTKEY("Line No.","Column No.");
IF FIND(‘-‘) THEN REPEAT
SETRANGE("Column No.","Column No.");
InsertFileDetails(TempPostingExchField,WebServiceURL);
FINDLAST;
SETRANGE("Column No.");
UNTIL NEXT = 0;
END;[/code]

Or just a simple way to return a single value from a simple JSON string, like

[code lang=”csharp”]FileName := GetValueFromJsonString(String,’filename’);[/code]

With these functions NAV should be able to handle JSON files without any problems.

Now you can add JSON handling to your arsenal.

Json Codeunit and required add-ins

Using NetTcpPortSharing for NAV Servers

I just came back from three weeks vacation yesterday.  During my vacation I had made a decision to implement Tcp Port Sharing for the Instance Administration tool used in Advania Azure.

Early last year I published a function that uses the sc.exe to modify a NAV Service startup type.  When a NAV Service is installed and configured in setup, the startup type is Automatic (Delayed Start).  However, create a new service with Powershell New-NavServerIntance and the statup type is Automatic without the (Delayed Start).

To enable Tcp Port Sharing that same sc.exe function is needed.  Interestingly, after I had finished the task and was reading NAV blogs I saw that Waldo just published a powershell function on his blog to do just this.

The script lines I used and added to my Instance Administration powershell scripts are based on my fist sc.exe function but not using the function it self.  Now when a new NAV service is created by the tool the startup type is modified and if so selected by the deployment settings, the Tcp Port Sharing is also activated.

By default, the Tcp Port Sharing service is disabled.
servicedisabled

The startup type should be changed to Manual.  This can be done manually or by an administrative powershell script.

[code lang=”powershell”]#Set Startup Mode for NetTcpPortSharing to Manual
$command = ‘sc.exe \\$Computer config "NetTcpPortSharing" start= demand’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set NetTcpPortSharing to manual start. More details: $Output"
}
[/code]

Similar script is used to update the existing NAV Services to both delayed start and Tcp Port Sharing dependency.

[code lang=”powershell”]

#Stop NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Stop
#Update Startup Type and Dependency on NAV Server Instances
Get-NAVServerInstance | foreach {
$Service = $_.ServerInstance
Write-Host "Working on service $Service"
$Computer = ‘LOCALHOST’
$command = ‘sc.exe \\$Computer config "$Service" start= delayed-auto’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service to delayed start. More details: $Output"
}
$command = ‘sc.exe \\$Computer config "$Service" depend= NetTcpPortSharing/HTTP’
$Output = Invoke-Expression -Command $Command -ErrorAction Stop
if($LASTEXITCODE -ne 0){
Write-Error "$Computer : Failed to set $Service TcpPortSharing. More details: $Output" -foregroundcolor red
}

}
#Start NAV Server Instances
Get-NAVServerInstance | Set-NAVServerInstance -Start
[/code]

It should be obvious that the above script can also use the Set-ServiceStartupMode from my blog and the Enable-NAVServerInstancePortSharing function on Waldo’s blog. That would be a cleaner code and more in line with what we would like to see.

Again quoting Waldo from his previous blog, “When you’re using a dedicated service account, things might become a slight more difficult”.  That is exactly my case, I am using a dedicated service account.

After enabling Tcp Port Sharing and updating the services they would not start.  Event Viewer revealed the reason.

Server instance: CRONUS
The service MicrosoftDynamicsNavServer$CRONUS failed to start. This could be caused by a configuration error. Detailed error information:System.ServiceModel.CommunicationException: The service endpoint failed to listen on the URI ‘net.tcp://mynavserver.dynamics.is:7046/CRONUS/Service’ because access was denied. Verify that the current user is granted access in the appropriate allowAccounts section of SMSvcHost.exe.config. —> System.ComponentModel.Win32Exception: Access is denied

So I started to ask Bing what I could do.  Microsoft MSDN states:

When a net.tcp binding enables port sharing (by setting portSharingEnabled =true on the transport binding element), it implicitly allows an external process (namely the SMSvcHost.exe, which hosts the Net.TCP Port Sharing Service) to manage the TCP socket on its behalf.

Hence, I need to add the Sid of my NAV Service Account to the SMSvcHost.exe.config file.  I could do this manually, but I am a programmer!

Another powershell script was born.  This one could also be converted to a function.  Before executing the script make sure to update the user and domain in the top of the script.  Be smart and execute this function before updating the NAV Services with the script above.

[code lang=”powershell”]
#Modify User and Domain to fit your environment
$UserToAdd = ‘srvNAV’
$UserDomainToAdd = ‘DYNAMICS’

#Initial Values
$UserSidFound = ‘false’
$ConfigurationSet = ‘false’

#Net.Tcp Port Sharing Service Name
$ServiceName = ‘NetTcpPortSharing’

#Get SID for the Service User
$UserSid = ([wmi] "win32_userAccount.Domain=’$UserDomainToAdd’,Name=’$UserToAdd’").SID

#Get Path for SMSvcHost.exe.config file
$SMSvcHostPath = (Get-WmiObject win32_service | ?{$_.Name -like $ServiceName} ).PathName
$SMSvcHostPathConfig = $SMSvcHostPath + ‘.config’

Write-Host "Reading XML from $SMSvcHostPathConfig"
#Read Config file
$xmlDoc = [xml] (Get-Content $SMSvcHostPathConfig)

Write-Host "Looking for access permission for $UserSid"
#Loop through allowed accounts and search for the service user Sid
$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’
Write-Host "Found SID $ConfiguredSid"
}

#Act if Access Configuration is not enabled
if ($ConfigurationSet -eq ‘false’) {Write-Host "Access permission not configured"
$config = [xml] ‘<system.serviceModel.activation>
<net.tcp listenBacklog="10" maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10" teredoEnabled="false">
<allowAccounts>
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
</allowAccounts>
</net.tcp>
<net.pipe maxPendingConnections="100" maxPendingAccepts="2" receiveTimeout="00:00:10">
<allowAccounts>
<add securityIdentifier="S-1-5-18"/>
<add securityIdentifier="S-1-5-19"/>
<add securityIdentifier="S-1-5-20"/>
<add securityIdentifier="S-1-5-32-544" />
</allowAccounts>
</net.pipe>
<diagnostics performanceCountersEnabled="true" />
</system.serviceModel.activation>’

$configurationNode = $xmlDoc.DocumentElement
$newConfig = $xmlDoc.ImportNode($config.DocumentElement, $true)
$configurationNode.AppendChild($newConfig)

$allowAccounts = Select-Xml "configuration/system.serviceModel.activation/net.tcp/allowAccounts/add" $xmlDoc
$allowAccounts | ForEach-Object {
$ConfiguredSid = $_.Node.Attributes.Item(0).Value
Write-Host "Found SID $ConfiguredSid"
if ($ConfiguredSid -eq $UserSid) {$UserSidFound = ‘true’}
$ConfigurationSet = ‘true’
}

}

#Add Service User Sid if needed
if ($UserSidFound -ne ‘true’) {
$nettcp = $xmlDoc.SelectSingleNode("configuration/system.serviceModel.activation/net.tcp/allowAccounts")
$addNode = $xmlDoc.CreateElement(‘add’)
$secIden = $xmlDoc.CreateAttribute(‘securityIdentifier’)
$secIden.Value = $UserSid
$addNode.Attributes.Append($secIden)

$nettcp.AppendChild($addNode)
$xmlDoc.Save($SMSvcHostPathConfig)
Write-Host "Configuration Updated"
#Restart Service if running
if ((Get-Service NetTcpPortSharing).Status -eq "Running") {Restart-Service NetTcpPortSharing -Force}
}

[/code]

This script will search for the SMSvcHost.exe.config file, load it and check to see if the NAV Service User is already allowed access.  If not then the config file is updated and saved.  This script must be executed with administrative privileges.

Perhaps this should be what I started with, but the question; why do we need this, should be answered.

First, modifying the startup mode to delayed start is done to make sure that all the required networking and database processes have been started before the NAV Service starts.  This is very important if the SQL Server is running on the same server.  On a dedicated NAV Service server this is not as important but still recommended.

Secondly, accessing a NAV Service in most cases requires changes to a firewall.  Either to open a specific port or setting up a NAT from a public interface.  To minimize the number of ports used also minimizes the networking setup and maintenance.  If different network permissions or network access is required I recommend using separate ports for the NAV Services.

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

 

Masterclass Workshop in Iceland

Iceland has from the beginning been in the front row when using, developing and supporting Dynamics NAV and it predecessors.  When Mark Brummel started to talk about bringing the Masterclass Workshop to Iceland I knew that there would not be any problem finding attendees.

Mark came up to Iceland and I flew him up to the north cost to my home town.  After a few hours browsing we headed to the Drangey Music Festival (just bing-it).

WP_20150627_20_27_15_Pro

We bathed in Grettislaug and listened to music until it was time to look at the sunset.

WP_20150628_00_44_43_Pro

To our amazement the sun never went below the horizon and before we knew it we where watching a sunrise.

The workshop had twenty attendees with average experience of over ten years.  Two great days and Mark got good reviews.

WP_20150630_13_52_00_Pro

Thank you all