May 2014 – Gunnar's Blog

May 14, 2014

Scanning and attachments for Incoming Documents in Microsoft Dynamics NAV 2013 R2

A new feature in NAV 2013 R2 allows companies to store links to incoming documents and reference them in Purchase Invoices and Journal Lines. There is a video in the How Do I series on Managing Incoming Documents in Microsoft Dynamics NAV 2013 R2.

I wanted to add a functionality to scan and store the files within NAV. Using the Hardware Hub and the Hardware Hub Twain Client to easily scan documents and store them in NAV. The objects needed are attached below. You will need to put the Hardware Hub Proxy Add-in to the NAV 2013 R2 Server Add-in folder. There is a possibility of doing this without the add-in by manually creating the soap requests. I did this in the NAV 2009 R2 version for the Classic Client and if needed I should be able to make available a similar NAV 2013 R2 version.

So, how does it work ?

First step is to download and install the Hardware Hub Twain Client on the computer with a twain compatible scanner and the Hardware Hub Proxy Add-in to the Server Add-in folder.. Double click the icon in the notification area to get the program windows in foreground. Next step is to install the new and modified objects into your NAV 2013 R2 database.

Then go to Incoming Document in the Windows Client. After an incoming document entry has been created the New Attachment button will be enabled.

Click New Attachment button. The first time you open this page you will need to set up the scanner connection.

You need to compare the Hardware Hub Path and the Hardware Hub Scanner GUID to the Twain Client window. It should be enough to copy the GUID from NAV to the Twain Client.

Minimize the Twain Client and go to the ACTIONS menu to select scanner. You can test the Hardware Hub connection with the About action.

These settings will be saved with the page data personalization for the current user.

If you select to “Show Settings” the settings dialog will sometimes appear behind the NAV Windows Client – just to let you know.

You can also browse for an existing file in the File Name drill down arrow and that file will be uploaded into NAV. When you close this window the URL to the Incoming Document Store will be added to the Incoming Document. You can add multiple files to a single Incoming Document and the URL will open all of them.

This solution will work on a cloud based NAV and also via remote desktop. I was able to scan with the Web Client but that functionality is not fully tested. Your feedback is always appreciated.

Objects: IncomingDocumentStore, 2014-07-29 Update

Hardware Hub IIS Service on Objects4NAV.com

May 5, 2014

Install Client and Server update with PowerShell

It is time to share with you the scripts that I am using to install and update the NAV application in the company network. In the attached ZIP packages you will find a lot of files.

In the root folder I keep the settings files. These files must be customized. The folders are as follows;

Edge3-Internet – my internet facing server. Here I install ClickOnce, WebClient and NAV Service.
Fjarvinna-Domain – my remote desktop server. Here I install KB updates remotely.
Localhost-Domain – my intranet server. Here I install NAV Service for the domain.
NAVCmdLets – my collection of PowerShell scripts for NAV. Some a copied from the NAV DVD.
Install-KBLocally – scripts to install a KB package to the local computer.

If we first look at the Edge3-Internet folder, here I have scripts to remotely install NAV on a server. I usually create a ClickOnce container in the folder ‘c:\inebpub\ClickOnce’ and keep all my ClickOnce subfolders in there. In the Set-MachineSettings.ps1 file I set the machine name.

Next, in Fjarvinna-Domain folder I only have scripts to upload and install a KB package. Also in here I have the Set-MachineSettings.ps1 to define the machine name.

The Localhost-Domain folder includes scripts to install NAV Service and NAV Users to the localhost.

Then there are the functions that I use in the above scripts. In the NAVCmdLets folder I have a collection of scripts to handle ClickOnce, KB install and some SQL administration tasks.

Finally the Install-KBLocally folder. As the name suggest, the contained scripts are used to install a KB package on the localhost. I usually create a folder that contains this folder, the NAVCmdLets folder and for example the KB2955941 folder. The KB2955941 can be downloaded from Microsoft. I usually delete all sub folders except ADCS, NST, OUTLOOK, RTC and WEB CLIENT after I add this folder to the update package.

Also, in my KB package I have two CMD files. One to install to a 64bit machine (default) and the other to a 32bit machine. If you put this package on your network and modify the CMD files to point to the correct file share you should be able to use this for all the computers on the domain. The KB package without the Microsoft binaries is attached below. Make sure to right-click on the install CMD file and run as administrator for a succesful install.

If you are running an old operating system, you might need KB2506146 or KB2506143 installed before using the KB package.

With your network administration tools you should be able to use some of these scripts to install a KB package on all your domain computers to make sure that everyone is running the latest version recommended by Microsoft.

NAVPowerShellScripts KB

May 2, 2014

Change table layout and move data

In older versions of NAV I got used to using a report to move data from one table to another. Since the last update from Microsoft for NAV 2013 R2 I am using the SQL Management Studio more and more.

For example, yesterday, I needed to change a table layout that included a change in the primary key.

I had modified the table in the developement database but was unable to import the new table because of the existing data.

I solved this by creating a temporary table with the original layout. I can choose any number for the table that I can use it with my developement license since I will not be using the table in NAV. I designed the original table and used File-Save As to create the new table.

Then I moved over to the SQL Management Studio. There I found the original table and scripted a SELECT statement to a new query window. Next I found the newly created shadow table and scripted an INSERT statement to the clipboard. Then I combined these two statements to look like this.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$Temp Partner Inbound Mapping]
([IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,[Responsibility Center]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
GO
DELETE FROM [dbo].[NAV Company$IC Partner Inbound Mapping]
[/code]

At this stage I have deleted all data from the table and was able to import the new modified table into the production NAV database. In this case I have two new fields in the new table. The primary changed to include a line number. That meant that I needed to create a line number for each row. This can be done in with SQL.

[code lang=”sql”]USE [NAV_DATABASE]
GO

INSERT INTO [dbo].[NAV Company$IC Partner Inbound Mapping]
([IC Partner Code]
,[Line No_]
,[Responsibility Center]
,[Ship-to Code]
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code])
SELECT
[IC Partner Code]
,10000 * ROW_NUMBER() OVER(ORDER BY [IC Partner Code]) AS Row
,[Responsibility Center]
,”
,[Ship-to Name]
,[Ship-to Address]
,[Ship-to Address 2]
,[Ship-to City]
,[Inbound Mapping Group Code]
FROM [dbo].[NAV Company$Temp Partner Inbound Mapping]
GO

DELETE FROM [dbo].[NAV Company$Temp Partner Inbound Mapping][/code]

If I would have needed to start the row number with 10000 for every instance of IC Partner Code I would have needed to use a cursor and iterate through every instance of IC Partner Code from another table and execute the statement filtered by the IC Partner Code. This I used for example in the Azure Backup Script.

I finished this process by deleting the temporary table for the production database.